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

WITH t as(
SELECT uid,submit_time
from exam_record
UNION
SELECT uid,submit_time
from practice_record
),
t_level as(
SELECT uid
FROM user_info
WHERE level = 6 OR level = 7
),
t_act_month_total as(
SELECT uid,
COUNT(DISTINCT DATE_FORMAT(submit_time,'%Y%m')) AS act_month_total  
FROM t
GROUP BY uid  
),
t_act_days_2021 as(
SELECT uid,
COUNT(DISTINCT DATE_FORMAT(submit_time,'%Y%m%d')) AS act_days_2021  
FROM t  
WHERE YEAR(submit_time) = 2021
GROUP BY uid
),
t_act_days_2021_exam as(
SELECT uid,
COUNT(DISTINCT DATE(submit_time)) AS act_days_2021_exam
FROM exam_record
WHERE YEAR(submit_time) = 2021
GROUP BY uid
),
t_act_days_2021_question as(
SELECT uid,
COUNT(DISTINCT DATE(submit_time)) AS act_days_2021_question
FROM practice_record
WHERE YEAR(submit_time) = 2021
GROUP BY uid
)

SELECT t_level.uid,
IF(act_month_total IS NULL,0,act_month_total),
IF(act_days_2021 IS NULL,0,act_days_2021),
IF(act_days_2021_exam IS NULL,0,act_days_2021_exam),	
IF(act_days_2021_question IS NULL,0,act_days_2021_question)
FROM t_level
LEFT OUTER JOIN t_act_month_total ON t_level.uid = t_act_month_total.uid
LEFT OUTER JOIN t_act_days_2021 ON t_act_month_total.uid = t_act_days_2021.uid
LEFT OUTER JOIN t_act_days_2021_exam ON t_act_days_2021.uid = t_act_days_2021_exam.uid
LEFT OUTER JOIN t_act_days_2021_question ON t_act_days_2021_question.uid = t_act_days_2021_exam.uid
ORDER BY act_month_total DESC,act_days_2021 DESC;