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

易错点： 1.对于各个数据都为零的用户，也要记录在表中：用left join 2.date_format(time,'%y%m%d')函数掌握不熟悉 3.count函数里用case when then end之前没用过，新思路 4.问题拆分求解

select uid,
    count(distinct act_month) as act_month_total,
    count(distinct case when year(act_day)=2021 then act_day end) 
        as act_day_2021,
    count(distinct case when year(act_day)=2021 and tag='exam' then act_day end) 
        as act_days_2021_exam,
    count(distinct case when year(act_day)=2021 and tag='practice' then act_day end)
        as act_days_2021_question   
from user_info
left join (select uid,date_format(start_time,'%y%m%d') as act_day,left(start_time,7) as act_month,'exam' as tag
    from exam_record
    union all
    select uid,date_format(submit_time,'%y%m%d') as act_day,left(submit_time,7) as act_month,'practice' as tag
    from practice_record) as t using(uid)
where level in(6,7)
group by uid 
order by act_month_total desc,act_day_2021 desc