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

select
    u_i.uid,
    count(distinct  date_format(start_time, '%Y%m')) act_month_total,
    count(distinct if(YEAR(start_time)=2021,date_format(start_time, '%Y%m%d'),null)) act_days_2021,
    count(distinct if(YEAR(start_time)=2021 and tag = "2",date_format(start_time, '%Y%m%d'),null)) act_days_2021_exam,
    count(distinct if(YEAR(start_time)=2021 and tag = "1",date_format(start_time, '%Y%m%d'),null)) act_days_2021_queston
from (select
          uid,
          submit_time start_time,
          '1' tag
      from practice_record
      union
      select
          uid,
          start_time,
          '2' tag
      from exam_record) mon
right join user_info u_i
on u_i.uid = mon.uid
where level =6 
OR level =7 
group by
    uid
order by
    act_month_total desc ,act_days_2021 desc;