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

WITH a1 AS (
           SELECT 0,                                                 #试卷表里面有一个自增列，相连的话必须列相同
                  uid                                 AS e_q_uid,
                  question_id                         AS e_q_id,
                  DATE_FORMAT(submit_time,'%Y-%m-%d') AS start_time, #没有开始时间给他弄一个，试卷有一条数据是有开始时间没有提交时间，但所有做了的提都会有开始时间，没有的话就是没做
                  submit_time,
                  score,
                  'practice'                          AS tag         #给一个标签后面好筛选
           FROM practice_record
           UNION ALL
           SELECT *, 'exam' AS tag #给一个标签后面好筛选
           FROM exam_record
           ) #连接一下试卷表和练习表
SELECT uid,
       COUNT(DISTINCT DATE_FORMAT(start_time,'%Y%m'))                     AS act_month_total,
       COUNT(DISTINCT CASE WHEN YEAR(start_time) = '2021'
                               THEN DATE_FORMAT(start_time,'%Y%m%d') END) AS act_days_2021,
       COUNT(DISTINCT CASE WHEN YEAR(start_time) = '2021' AND a1.tag = 'exam'
                               THEN DATE_FORMAT(start_time,'%Y%m%d') END) AS act_days_2021_exam,
       COUNT(DISTINCT CASE WHEN YEAR(start_time) = '2021' AND a1.tag = 'practice'
                               THEN DATE_FORMAT(start_time,'%Y%m%d') END) AS act_days_2021_question
FROM a1 RIGHT JOIN user_info a ON a1.e_q_uid = a.uid
        LEFT JOIN  examination_info b ON a1.e_q_id = b.exam_id #把用户信息连上去
WHERE uid IN (
             SELECT uid
             FROM user_info
             WHERE level IN (6,7)
             )
GROUP BY uid
ORDER BY act_month_total DESC, act_days_2021 DESC;