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

with a as(
    select uid,date(start_time) dt from exam_record
    union 
    select uid,date(submit_time) dt from practice_record
    order by uid
)

select ui.uid,
count(distinct substr(a.dt,1,7) )
as 	act_month_total,
count(distinct case when year(a.dt)=2021 then dt end )
as act_days_2021,
count(distinct case when year(er.start_time) =2021 then  date(er.start_time) end ) as act_days_2021_exam,
count(distinct case when year(pr.submit_time) =2021 then  date(pr.submit_time) end ) as act_days_2021_question


from user_info ui
left join exam_record er
on ui.uid = er.uid
left join practice_record pr
on ui.uid=pr.uid
left join a
on a.uid=ui.uid

where level = 6 or level =7
group by ui.uid
order by act_month_total desc,act_days_2021 desc

从最后一个字段往第一个字段做，思路会更清晰一些

难点在于CTE实现把exam record的日子和practice record的日子合并在一起