SQL-每个6/7级用户活跃情况

思路

1、生成每个人答题记录2021年活跃天数

2、生成每个人作答记录2021年活跃天数

3、生成每个人2021年活跃天数

4、生成每个人活跃月数

5、过滤条件、连接变量

SELECT ui1.uid,
ifnull(act_month_total,0),
ifnull(act_days_2021,0),
ifnull(act_day_2021_exam,0) ,
ifnull(act_day_2021_question,0)
from user_info as ui1 left join (
 # 生成每个人答题记录2021年活跃天数
select t1.uid,count(distinct t1.year_day)  as act_day_2021_question 
from (
select pr1.uid,DATE_FORMAT(pr1.submit_time,'%Y-%m-%d') as year_day 
    from practice_record as pr1 
where year(pr1.submit_time)='2021' ) t1 group by t1.uid)  a1 
on ui1.uid=a1.uid
left join 
(
 # 生成每个人作答记录2021年活跃天数
select t2.uid,count(distinct t2.year_day)  as act_day_2021_exam 
from (
select pr2.uid,DATE_FORMAT(pr2.start_time,'%Y-%m-%d') as year_day 
    from exam_record as pr2 
where year(pr2.start_time)='2021' ) t2 group by t2.uid) a2
 on ui1.uid=a2.uid
# 生成2021年活跃天数
left join (
select  distinct p4.uid,count(distinct p4.time1) as act_days_2021  
from (
select distinct p1.uid,DATE_FORMAT(p1.submit_time,'%Y-%m-%d') as time1 
from practice_record as p1
UNION all 
select distinct p2.uid,DATE_FORMAT(p2.start_time,'%Y-%m-%d') as time1  
 from exam_record as p2 
) p4 where year(p4.time1)=2021 group by p4.uid
 ) a4 on ui1.uid=a4.uid
# 生成活跃月数
left join (
select distinct p3.uid,count(distinct p3.time1) as act_month_total  
    from (
select distinct p1.uid,DATE_FORMAT(p1.submit_time,'%Y-%m') as time1 
from practice_record as p1
UNION all 
select distinct p2.uid,DATE_FORMAT(p2.start_time,'%Y-%m') as time1 
 from exam_record as p2 ) 
    p3 group by p3.uid) a3 on ui1.uid=a3.uid
  where ui1.level in (6,7) order by act_month_total desc,act_days_2021 desc

备注：变量涉及到两表，由于可能存在重复，因此最好将两张表连接起来并去重，做统计

生成每个人2021年活跃天数、每个人活跃月数的另一种方法

select distinct p3.uid,
    count(distinct date_format(p3.time2,'%Y-%m')) as act_month_total,
    count(distinct if(year(p3.time2)=2021,p3.time2,null)) as act_days_2021
    from (
select distinct p1.uid,DATE_FORMAT(p1.submit_time,'%Y-%m-%d') as time2
from practice_record as p1
UNION all 
select distinct p2.uid,
        DATE_FORMAT(p2.start_time,'%Y-%m-%d') as time2
 from exam_record as p2 ) p3
    group by p3.uid