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

select uid,
count(distinct uid,date_format(tm,"%Y%m")) as act_month_total,
count(distinct case when year(tm)= 2021 then uid + date_format(tm,"%Y%m%d") else NULL end) as act_days_2021,
count(distinct case when (year(tm)= 2021 and left(exam_id,1)=9) then uid + date_format(tm,"%Y%m%d") else NULL end ) as act_days_2021_exam,
count(distinct case when (year(tm)= 2021 and left(exam_id,1)=8) then uid + date_format(tm,"%Y%m%d") else NULL end ) as act_days_2021_question
from
(select uid,exam_id,start_time as tm from exam_record
union
select uid,question_id,submit_time as tm from practice_record) ep
right join user_info using(uid)
where level in (6,7)
group by uid
order by act_month_total desc,act_days_2021 desc