题解 | #每个6/7级用户活跃情况#--多个子查询表连接

# 合并试卷作答记录表和题目练习表，并用类型标签标注所属类型
with k1 as(
    select uid, exam_id, start_time,
    date_format(start_time, '%Y%m') start_month, date_format(start_time, '%Y%m%d') start_date, 'exam' s_tag
    from exam_record
    union all
    select uid, question_id, submit_time,
    date_format(submit_time, '%Y%m') start_month, date_format(submit_time, '%Y%m%d') start_date, 'question' s_tag
    from practice_record
)
# 以下用到各个子查询分别用来查询每个6/7级用户的总活跃月份数、2021年活跃天数、2021年试卷作答活跃天数、2021年答题活跃天数
select ui.uid, if(act_month_total is null, 0, act_month_total) act_month_total, 
if(act_days_2021 is null, 0, act_days_2021) act_days_2021,
if(act_days_2021_exam is null, 0, act_days_2021_exam) act_days_2021_exam,
if(act_days_2021_question is null, 0, act_days_2021_question) act_days_2021_question
from (select uid from user_info where level in (6,7)) ui
left join (
    select ui.uid, count(distinct start_month) act_month_total
    from k1
    join user_info ui
    on k1.uid=ui.uid
    where level in (6,7)
    group by uid
) t1
on ui.uid = t1.uid
left join (
    select ui.uid, count(distinct start_date) act_days_2021
    from k1
    join user_info ui
    on k1.uid=ui.uid
    where level in (6,7)
    and year(start_time) = '2021'
    group by uid
) t2
on ui.uid = t2.uid
left join (
    select ui.uid, count(distinct start_date) act_days_2021_exam
    from k1
    join user_info ui
    on k1.uid=ui.uid
    where level in (6,7)
    and year(start_time) = '2021'
    and s_tag = 'exam'
    group by uid
) t3
on ui.uid = t3.uid
left join (
    select ui.uid, count(distinct start_date) act_days_2021_question
    from k1
    join user_info ui
    on k1.uid=ui.uid
    where level in (6,7)
    and year(start_time) = '2021'
    and s_tag = 'question'
    group by uid
) t4
on ui.uid = t4.uid
order by act_month_total desc, act_days_2021 desc;