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

###第一次思路，代码冗余
SELECT 
a1.uid
,COUNT(DISTINCT a2.active_month) act_month_total	
,COUNT(DISTINCT a3.active_day) act_days_2021
,COUNT(DISTINCT DATE_FORMAT(t3.submit_time,'%Y%m%d'))act_days_2021_exam
,COUNT(DISTINCT DATE_FORMAT(t4.submit_time,'%Y%m%d'))	act_days_2021_question
FROM  (SELECT uid FROM user_info t1 WHERE LEVEL BETWEEN 6 AND 7) a1
LEFT JOIN exam_record t3 ON a1.uid=t3.uid AND YEAR(t3.submit_time)=2021
LEFT JOIN practice_record t4 ON a1.uid=t4.uid AND YEAR(t4.submit_time)=2021
LEFT JOIN
  (SELECT DISTINCT uid, DATE_FORMAT(start_time, "%Y%m") AS active_month FROM exam_record
  UNION ALL 
  SELECT DISTINCT uid, DATE_FORMAT(submit_time, "%Y%m") AS active_month FROM practice_record) a2 ON a1.uid=a2.uid
LEFT JOIN
(SELECT DISTINCT uid, DATE_FORMAT(start_time, "%Y%m%d") AS active_day FROM exam_record WHERE YEAR(submit_time)=2021
  UNION ALL 
  SELECT DISTINCT uid, DATE_FORMAT(submit_time, "%Y%m%d") AS active_day FROM practice_record WHERE YEAR(submit_time)=2021
) a3 ON a1.uid=a3.uid
GROUP BY a1.uid
ORDER BY act_month_total DESC,act_days_2021 DESC*/

###第二次思路，看了评论区大佬做的，代码简洁不少
SELECT 
t1.uid
,COUNT(DISTINCT act_month) act_month_total
,COUNT(DISTINCT CASE WHEN act_year=2021 THEN act_day END ) act_days_2021
,COUNT(DISTINCT CASE WHEN act_year=2021 AND tag='exam' THEN act_day END) 	act_days_2021_exam
,COUNT(DISTINCT CASE WHEN act_year=2021 AND tag='question' THEN act_day END) act_days_2021_question
FROM user_info t1
LEFT JOIN
(SELECT uid,YEAR(start_time) act_year,DATE_FORMAT(start_time,'%Y%m') act_month,DATE_FORMAT(start_time,'%Y%m%d') act_day,'exam' AS tag FROM exam_record
UNION ALL
SELECT uid,YEAR(submit_time) act_year,DATE_FORMAT(submit_time,'%Y%m') act_month,DATE_FORMAT(submit_time,'%Y%m%d') act_day,'question' AS tag FROM practice_record
ORDER BY uid
)a1 ON t1.uid=a1.uid
 WHERE t1.level BETWEEN 6 AND 7
GROUP BY uid 
ORDER BY act_month_total DESC,act_days_2021 DESC