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

# 请统计每个6/7级用户总活跃月份数、2021年活跃天数、2021年试卷作答活跃天数、2021年答题活跃天数，按照总活跃月份数、2021年活跃天数降序排序。

with t as(
    select ui.uid as uid, level,
    start_time as act_time,
    date_format(start_time, '%Y%m') as act_month, 
    date_format(start_time, '%Y%m%d') as act_days, 
    'exam' as tags
    from user_info ui left join exam_record er on ui.uid=er.uid
    union all
    select ui.uid as uid, level,
    submit_time as act_time,
    date_format(submit_time, '%Y%m') as act_month,
    date_format(submit_time, '%Y%m%d') as act_days,
    'question' as tags
    from user_info ui left join practice_record pr on ui.uid=pr.uid
)

select uid, 
count(distinct act_month) as act_month_total,
count(distinct case when year(act_time)=2021 then act_days end) as act_days_2021,
count(distinct case when year(act_time)=2021 and tags='exam' then act_days end) as act_days_2021_exam,
count(distinct case when year(act_time)=2021 and tags='question' then act_days end) as act_days_2021_question
from t
where level in (7,6)
group by uid 
order by act_month_total desc, act_days_2021 desc;

思路：要将两个表结合起来并贴上标签，利用case when来筛选。

一个小坑：date_format格式后的字段act_month和act_days不能用year来过滤，需要新增字段act_time来过滤。