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

题目要求：

统计每个6/7级用户总活跃月份数、2021年活跃天数、2021年试卷作答活跃天数、2021年答题活跃天数，

按照总活跃月份数、2021年活跃天数降序排序

解题思路：

1、建立一个用户、活跃月份、活跃天的临时表

两个子查询：分别查询每个6/7级用户在哪几个月以及哪几天答过试卷、题

由于最后要算2021年总的活跃天数，这里需要进行结果合并，但又要查单独在试卷/答题的活跃天数，因此需要新增一个标识is_exam来区分该记录是试卷还是答题。

本题所查询的字段均从该查询结果中得到，因此保存此查询的结果为tmp

with tmp as(
select
    distinct uid,date_format(start_time, '%Y%m') distinct_month,date(start_time) distinct_day,
    1 as is_exam 
from
    user_info left join exam_record using(uid)
where user_info.level >= 6

union

select
    distinct uid,date_format(submit_time, '%Y%m') distinct_month,date(submit_time) distinct_day,
    0 as is_exam 
from
    user_info left join practice_record using(uid)
where user_info.level >= 6
)

2、根据临时表查询字段

按uid分组，group by uid

排序：order by act_month_total desc,act_days_2021 desc

查询字段：

总活跃月份数：对合并后的tmp的活跃月份列进行去重计数

count(distinct distinct_month) act_month_total

2021年活跃天数：对合并后的tmp的活跃日列进行筛选(2021年)去重计数

count(distinct if(year(distinct_day)=2021,distinct_day,null)) act_days_2021

2021年试卷/答题作答活跃天数：根据is_exam标识筛选试卷/答题活跃天数

count(distinct if(year(distinct_day)=2021 and is_exam=1,distinct_day,null)) act_days_2021_exam

count(distinct if(year(distinct_day)=2021 and is_exam=0,distinct_day,null)) act_days_2021_question

select 
    uid, 
    count(distinct distinct_month) act_month_total,
    count(distinct if(year(distinct_day)=2021,distinct_day,null)) act_days_2021,
    count(distinct if(year(distinct_day)=2021 and is_exam=1,distinct_day,null)) act_days_2021_exam,
    count(distinct if(year(distinct_day)=2021 and is_exam=0,distinct_day,null)) act_days_2021_question
from tmp
group by uid
order by act_month_total desc,act_days_2021 desc