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

select
    a.uid as uid,
    ifnull(b.act_month_total,0) as act_month_total,
    ifnull(b.act_days_2021,0) as act_days_2021,   
    ifnull(b.act_days_2021_exam,0) as act_days_2021_exam,
    ifnull(b.act_days_2021_question,0) as act_days_2021_question
from user_info a
left join
(
    select
      uid,
      count(distinct date_format(dt,"%Y%m")) as act_month_total,
      count(distinct (case when year(dt) = 2021 then date_format(dt,"%Y%m%d") else null end)) as act_days_2021,
      count(distinct (case when year(dt) = 2021 and record_type = "exam" then date_format(dt,"%Y%m%d") else null end)) as act_days_2021_exam,
      count(distinct (case when year(dt) = 2021 and record_type = "practice" then date_format(dt,"%Y%m%d") else null end)) as act_days_2021_question
    from (
        # 试卷作答数据
        select
        a.uid as uid,
        "exam" as record_type,
        start_time as dt
        from exam_record a

        union all

        # 题目练习作答数据
        select
        a.uid as uid,
        "practice" as record_type,
        submit_time as dt
        from practice_record a
    ) res
    group by uid
) b on a.uid = b.uid
where a.level in (6,7)
order by act_month_total desc,act_days_2021 desc