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

with t_youxiao_user as(
    select uid from user_info where level = 6 or level = 7  
), t_activate_time as(
    select uid, start_time tmp_time from exam_record
    union 
    select uid, submit_time tmp_time from practice_record
), t_act_month_total as(
    select uid, count(distinct(date_format(tmp_time, "%Y%m"))) act_month_total
    from t_youxiao_user left join t_activate_time using(uid)
    group by uid
), t_act_days_2021 as(
    select a.uid, count(distinct(date_format(b.tmp_time, "%Y%m%d"))) act_days_2021
    from t_youxiao_user a left join t_activate_time b on a.uid = b.uid and year(b.tmp_time)=2021 
    group by a.uid
), t_act_days_2021_exam as(
    select a.uid, count(distinct(date_format(b.submit_time, "%Y%m%d"))) act_days_2021_exam
    from t_youxiao_user a left join exam_record b on a.uid = b.uid and year(b.submit_time)=2021
    group by a.uid
), t_act_days_2021_question as(
    select a.uid, count(distinct(date_format(b.submit_time, "%Y%m%d"))) act_days_2021_question
    from t_youxiao_user a left join practice_record b on a.uid = b.uid and year(b.submit_time)=2021
    group by a.uid
)
select uid, act_month_total, act_days_2021, act_days_2021_exam, act_days_2021_question
from t_youxiao_user 
    join t_act_month_total using(uid)
    join t_act_days_2021 using(uid)
    join t_act_days_2021_exam using(uid)
    join t_act_days_2021_question using(uid)
order by act_month_total desc, act_days_2021 desc;

思路：

1. 找到有效的用户id（等级等于6或7的用户）；
2. 找到所有用户的活跃时间；
3. 分别计算有效用户的act_month_total，act_days_2021，act_days_2021_exam，act_days_2021_question；
4. 最后通过uid将结果关联起来并对结果进行排序输出。