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

看题解基本都没用到with写法的，在这里放上本人用with ... as写法完成的sql，个人认为比大多数sql会比较好理解。每一部分的含义在sql中都有注释！

with user_info_filter as(
select uid from user_info where level = 6 or level = 7
), --获取等级在6和7的用户uid
exam_filter as(
select uid,
count(DISTINCT DATE_FORMAT(start_time,'%Y%m%d')) as act_days_2021_exam 
from exam_record where
uid in (select uid from user_info_filter) and year(start_time) = 2021  group by uid
), --获取在2021年且是6,7级的人的试卷联系次数
practice_filter as (
select uid,count(DISTINCT DATE_FORMAT(submit_time,'%Y%m%d')) as act_days_2021_question  from practice_record where
    uid in (select uid from practice_record)  and year(submit_time) = 2021 group by uid
), --获取在2021年且是6,7级的人的题目联系次数
time_fliter as (
    select uid,start_time as act_time from exam_record
    union all
    select uid,submit_time as act_time from practice_record
), --取出两个表中的时间，注意exam_record需要取start_time
act_month as(
select 
  uid,
  count(DISTINCT DATE_FORMAT(act_time,'%Y%m')) as act_month_total
from time_fliter group by uid
), --获取用户活跃的月数
act_day as(
select 
  uid,
  count(DISTINCT DATE_FORMAT(act_time,'%Y%m%d')) as act_days_2021
from time_fliter where year(act_time) = 2021 group by uid 
) --获取用户的活跃天数
select t1.uid,
if(t5.act_month_total is null,0,t5.act_month_total) as act_month_total,
if(t2.act_days_2021 is null,0,t2.act_days_2021) as act_days_2021,
if(t3.act_days_2021_exam is null,0,t3.act_days_2021_exam) as act_days_2021_exam,
if(t4.act_days_2021_question is null,0,t4.act_days_2021_question) as act_days_2021_question
from
user_info_filter t1
left join act_day t2
on t1.uid = t2.uid
left join exam_filter t3
on t1.uid = t3.uid
left join practice_filter t4
on t1.uid = t4.uid
left join act_month t5
on t1.uid = t5.uid
order by act_month_total desc,act_days_2021 desc
--最后将上述各个部分根据uid连接后整理得到结果