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

select a.uid, count(distinct  year(a.time),month(a.time)) 'act_month_total',
count(distinct case when year(a.time)='2021' then date(a.time) else null end) 'act_days_2021',
count(distinct case when (year(a.time) = '2021' and a.type = 'exam') then date(a.time) else null end) 'act_days_2021_exam',
count(distinct case when (year(a.time) = '2021' and a.type = 'question') then date(a.time) else null end) 'act_days_2021_question'
from (select i.uid,exam_id,r.start_time 'time','exam' as 'type'
from user_info i left join exam_record r on i.uid = r.uid
union all
select i.uid, r.question_id,r.submit_time 'time','question' as 'type'
from user_info i left join practice_record r on i.uid = r.uid
) a
where a.uid in
(select uid
from user_info
where level = '7' or level = '6')
group by a.uid
order by act_month_total desc,act_days_2021 desc