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

with tiaojian as (
select
uid
from user_info
where
level in (6,7)
),
tiaojian1 as (
select
uid,exam_id,"exam_id" as pt,date(submit_time) as pday,date_format(submit_time,"%Y-%m") as month
from exam_record
union all
select
uid,question_id, "question_id" as pt,date(submit_time) as pday,date_format(submit_time,"%Y-%m") as month
from practice_record
)

select
t.uid,
count(distinct t1.month) as month,
count(distinct case when year(t1.pday)=2021 then t1.pday end) as pday,
count(distinct case when year(t1.pday)=2021 and t1.pt="exam_id" then t1.pday end),
count(distinct case when year(t1.pday)=2021 and t1.pt="question_id" then t1.pday end)
from tiaojian  t left join tiaojian1 t1 on t.uid=t1.uid
group by t.uid
order by month desc,pday desc