题解 | #每个6/7级用户活跃情况#

#统计每个6/7级用户总活跃月份数、2021年活跃天数、2021年试卷作答活跃天数、2021年答题活跃天数，按照总活跃月份数、2021年活跃天数降序排序
#1.找出6/7级用户 uid
#2.统计总活跃月份数（去重）分为试卷活跃 开始作答即为试卷活跃 答题活跃 提交作答即为答题活跃
#3.统计2021年活跃天数（去重），试卷作答天数（去重），答题天数（去重）

#6/7级用户 uid
select  uid
from user_info
where level in (6,7)

#活跃表
    #试卷活跃
    (select uid, date_format(start_time, '%Y%m')  act_month,date_format(start_time, '%Y%m%d')  act_day,'exam' classify
    from exam_record
    group by uid,act_month,act_day) 
    union all
    #答题活跃
    (select uid, date_format(submit_time, '%Y%m')  act_month,date_format(submit_time, '%Y%m%d')  act_day,'practice' classify
    from practice_record
    group by uid,act_month,act_day)

#开始组合以上进行查询
select uid,
    count(distinct act_month) act_month_total,
    Count(Distinct Case When Year(act_day) = 2021 Then act_day Else Null End)  act_days_2021,
    Count(Distinct Case When Year(act_day) = 2021 and classify='exam' Then act_day Else Null End)  act_days_2021_exam,
    Count(Distinct Case When Year(act_day) = 2021 and classify='practice' Then act_day Else Null End)  act_days_2021_question
from user_info
left join 
((select uid, date_format(start_time, '%Y%m')  act_month,date_format(start_time, '%Y%m%d')  act_day,'exam' classify
    from exam_record
    group by uid,act_month,act_day) 
    union all
    (select uid, date_format(submit_time, '%Y%m')  act_month,date_format(submit_time, '%Y%m%d')  act_day,'practice' classify
    from practice_record
    group by uid,act_month,act_day)) data1 using(uid)
where uid in (select  uid
         from user_info
         where level in (6,7))
group by uid
order by act_month_total desc,act_days_2021 desc