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

SELECT uid, 
        COUNT(DISTINCT DATE_FORMAT(submit_time, '%Y%m')) AS act_month_total, 
        COUNT(DISTINCT IF(YEAR(submit_time)= 2021, DATE(submit_time), NULL)) AS act_days_2021,
        COUNT(DISTINCT IF(YEAR(submit_time) = 2021 AND tag = 1, DATE(submit_time), NULL)) AS act_days_2021_exam,
        COUNT(DISTINCT IF(YEAR(submit_time) = 2021 AND tag = 2, DATE(submit_time), NULL)) AS act_days_2021_question

FROM 

(SELECT u.uid, er.submit_time, 1 AS 'tag'
FROM exam_record AS er
RIGHT OUTER JOIN user_info AS u
ON er.uid = u.uid
WHERE u.level = 6 OR u.level = 7 

UNION ALL

SELECT u.uid, pr.submit_time, 2 AS 'tag'
FROM practice_record AS pr
RIGHT OUTER JOIN user_info AS u
ON pr.uid = u.uid
WHERE u.level = 6 OR u.level = 7) AS t

GROUP BY uid
ORDER BY act_month_total DESC, act_days_2021 DESC;

刚开始用的表联结方式查询，但总活跃月份与天数很难计算；还是用合并查询UNION+添加标签的方式更清晰明了