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

首先建一个新表temp合并exam_record和practice_record并加了一个label字段方便区分两个表。

with temp as 
(select uid, exam_id as try_id, start_time as try_time, "exam" as label from exam_record
 union all
 select uid, question_id as try_id, submit_time as try_time, "prac" as label from practice_record)

然后连接temp和user_info，这里用的是right join因为要保证用户不能有缺失。根据uid分组，通过count(distinct)的方法对所需要的时间单位进行计数，在要进行条件限制的地方用if function。

SELECT
 uid,
 count(distinct date_format(try_time, "%Y-%m")) as act_month_total,
 count(distinct if(year(try_time)=2021, date_format(try_time, "%Y-%m-%d"), null)) as act_days_2021,
 count(distinct if(year(try_time)=2021 and label = "exam", date_format(try_time, "%Y-%m-%d"), null)) as act_days_2021_exam,
 count(distinct if(year(try_time)=2021 and label = "prac", date_format(try_time, "%Y-%m-%d"), null)) as act_days_2021_question
 from temp right join user_info using(uid)
 where level=6 or level=7
 group by uid
 order by act_month_total desc, act_days_2021 desc

完整版如下

with temp as 
(select uid, exam_id as try_id, start_time as try_time, "exam" as label from exam_record
union all
select uid, question_id as try_id, submit_time as try_time, "prac" as label from practice_record)

SELECT
uid,
count(distinct date_format(try_time, "%Y-%m")) as act_month_total,
count(distinct if(year(try_time)=2021, date_format(try_time, "%Y-%m-%d"), null)) as act_days_2021,
count(distinct if(year(try_time)=2021 and label = "exam", date_format(try_time, "%Y-%m-%d"), null)) as act_days_2021_exam,
count(distinct if(year(try_time)=2021 and label = "prac", date_format(try_time, "%Y-%m-%d"), null)) as act_days_2021_question
from temp right join user_info using(uid)
where level=6 or level=7
group by uid
order by act_month_total desc, act_days_2021 desc