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

https://www.nowcoder.com/practice/a32c7c8590324c96950417c57fa6ecd1

```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，聚合得到结果