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

select 
a.uid,
count(distinct date_format(b.start_time,'%Y%m')) act_month_total,
count(distinct case when  year(b.start_time)='2021' then date_format(b.start_time,'%Y%m%d') else null end) act_days_2021,
count(distinct case when  year(b.start_time)='2021' and b.type='exam' then date_format(b.start_time,'%Y%m%d') else null end) act_days_2021_exam,
count(distinct case when  year(b.start_time)='2021' and b.type='question' then date_format(b.start_time,'%Y%m%d') else null end) act_days_2021_question
-- select *
from 
user_info a
left join 
(select 
'exam' type,
uid,
exam_id,
start_time
from exam_record
union all
select 
'question' type,
uid,
question_id exam_id,
submit_time start_time
from practice_record) b
on a.uid=b.uid
where a.`level` in ('6','7')
group by a.uid
order by act_month_total desc,act_days_2021 desc