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

#挑选6/7级的uid
with t_id as(
    select uid
    from user_info
    where level between 6 and 7
)
#在试卷作答表中选出2021年试卷作答活跃天数
/*select uid,count(distinct date_format(start_time,'%Y%m%d')) as act_days_2021_exam
from exam_record
where uid in (select uid from t_id) and year(start_time) = 2021
group by uid*/
#在题目练习表中选出2021年答题活跃天数
/*select uid,count(distinct date_format(submit_time,'%Y%m%d')) as act_days_2021_question
from practice_record
where uid in (select uid from t_id) and year(submit_time) = 2021
group by uid*/
#拼接试卷作答表和题目练习表，从中选出不限年份的活跃月份数和2021年的活跃天数
/*select uid,count(distinct date_format(start_time,'%Y%m')) as act_month_total,
    count(distinct if(year(start_time) != 2021,null,date_format(start_time,'%Y%m%d'))) as act_days_2021
from (
    select uid,start_time from exam_record where uid in (select uid from t_id)
    union all
    select uid,submit_time from practice_record where uid in (select uid from t_id)
) as a
group by uid*/
#将简化后的三张所需信息表，用left join拼接成一个完整的结果表
select t_id.uid as uid,
    if(aa.act_month_total is null,0,aa.act_month_total) as act_month_total,
    if(aa.act_days_2021 is null,0,aa.act_days_2021) as act_days_2021,
    if(bb.act_days_2021_exam is null,0,bb.act_days_2021_exam) as act_days_2021_exam,
    if(cc.act_days_2021_question is null,0,cc.act_days_2021_question) as act_days_2021_question
from t_id
left join (
    select uid,count(distinct date_format(start_time,'%Y%m')) as act_month_total,
        count(distinct if(year(start_time) != 2021,null,date_format(start_time,'%Y%m%d'))) as act_days_2021
    from (
        select uid,start_time from exam_record where uid in (select uid from t_id)
        union all
        select uid,submit_time from practice_record where uid in (select uid from t_id)
    ) as a
    group by uid
) as aa
on t_id.uid = aa.uid
left join(
    select uid,count(distinct date_format(start_time,'%Y%m%d')) as act_days_2021_exam
    from exam_record
    where uid in (select uid from t_id) and year(start_time) = 2021
    group by uid
) as bb
on t_id.uid = bb.uid
left join(
    select uid,count(distinct date_format(submit_time,'%Y%m%d')) as act_days_2021_question
    from practice_record
    where uid in (select uid from t_id) and year(submit_time) = 2021
    group by uid
) as cc
on t_id.uid = cc.uid
order by aa.act_month_total desc,aa.act_days_2021 desc