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

select uid,count(distinct concat(year(time),month(time)) ) act_month_total,count(distinct if(year(time) = '2021',time,null)) act_days_2021,
 count(distinct if(rc = 1 and year(time) = '2021',time,null)) act_days_2021_exam,count(distinct if( rc = 2 and year(time) = '2021',time,null)) act_days_2021_question
 from 
 (
 (select a.uid,date(b.start_time) time,1 rc from user_info a 
left join exam_record b on a.uid = b.uid 
where level = 6 or level = 7)
union all
(select a.uid,date(c.submit_time),2 rc from user_info a
left join practice_record c on a.uid = c.uid 
where level = 6 or level = 7)
 ) d
 group by uid
 order by act_month_total desc,act_days_2021 desc;