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

WITH table_1 AS (
    SELECT uid, start_time AS act_time, 1 AS label
      FROM exam_record

    UNION ALL

    SELECT uid, submit_time, 0 AS label
      FROM practice_record
)

, table_2 AS (
    SELECT uid
      FROM user_info
     WHERE level BETWEEN 6 AND 7
)

SELECT t2.uid
    , COUNT(DISTINCT DATE_FORMAT(t1.act_time, "%Y%m")) AS act_month_total
    , COUNT(DISTINCT CASE WHEN YEAR(t1.act_time) = 2021 
                        THEN DATE_FORMAT(t1.act_time, "%Y%m%d")
                        ELSE NULL END) AS act_days_2021
    , COUNT(DISTINCT CASE WHEN YEAR(t1.act_time) = 2021 AND t1.label = 1
                        THEN DATE_FORMAT(t1.act_time, "%Y%m%d")
                        ELSE NULL END) AS act_days_2021_exam
    , COUNT(DISTINCT CASE WHEN YEAR(t1.act_time) = 2021 AND t1.label = 0
                        THEN DATE_FORMAT(t1.act_time, "%Y%m%d")
                        ELSE NULL END) AS act_days_2021_question
  FROM table_1 AS t1 RIGHT OUTER JOIN table_2 AS t2
    ON t2.uid = t1.uid
 GROUP BY t2.uid
 ORDER BY act_month_total DESC, act_days_2021 DESC;

思路：

1.合并试卷活跃用户和题目活跃用户的时间，为了区分，给他们打上标签。产生table_1

2.筛选6/7级用户。产生table_2

3.table_1右联结table_2，聚合得到结果