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

select ui.uid,
count(distinct date_format (un.time, '%y%m')) as act_month_total,
count(distinct case when year (un.time) = 2021 then date_format (un.time, '%y%m%d')else null end) as act_days_2021,
count(distinct case when year (un.time) = 2021 and tag = 'exam' then date_format (un.time, '%y%m%d') else null end) as act_days_2021_exam,
count(distinct case when year (un.time) = 2021 and tag = 'practice' then date_format (un.time, '%y%m%d') else null end) as act_days_2021_question
from
user_info ui
left join (
        select uid,start_time as time,'exam' as tag
        from exam_record er
        union
        select uid,submit_time as time,'practice' as tag
        from practice_record pr
          ) as un 
on ui.uid = un.uid
where ui.level >5
group by ui.uid
order by act_month_total desc,act_days_2021 desc