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

记录一下借鉴思路之后的简化sql，一共只用了两层！
注意：
1、要的是67级用户答卷答题的活跃情况，统计周期内答题答卷可能为0，主表是用户表，且限定where level between 6 and 7（含两端数值）
2、将答卷答题表union all，不去重提取全部 uid ,作答时间,外加答卷or答题的标记（即表来源）,共3列字段
3、在外层提取时，count里面用if(left(作答时间，截取位数)限定活跃月、活跃日

select 
    a.uid,
    count(distinct left(s,6)) as act_month_total,
    count(distinct if(left(s,4)='2021',s,null)) as act_days_2021,
    count(distinct if(left(s,4)='2021' and tag='e',s,null)) as act_days_2021_exam,
    count(distinct if(left(s,4)='2021' and tag='p',s,null)) as act_days_2021_question
from (
        select uid,DATE_FORMAT(submit_time,'%Y%m%d') as s,'p' tag from practice_record pr
        union all
        SELECT uid,DATE_FORMAT(start_time,'%Y%m%d') as s,'e' as tag from exam_record er 
)b
right join user_info a
on a.uid = b.uid
where a.level between 6 and 7
group by uid
order by act_month_total DESC,act_days_2021 desc