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

分别写四个查询再结合思路比较简单

SELECT 
uid,
IFNULL(act_month_total,0),
IFNULL(act_days_2021,0),
IFNULL(act_days_2021_exam,0),
IFNULL(act_days_2021_question,0)
FROM
(SELECT
uid,
COUNT(DISTINCT month) AS act_month_total
FROM
(SELECT
uid,
DATE_FORMAT(er.start_time,' %Y%m') AS month
FROM user_info ui
LEFT JOIN exam_record er USING(uid)
WHERE level IN (6,7)
UNION ALL
SELECT
uid,
DATE_FORMAT(pr.submit_time,' %Y%m') AS month
FROM user_info ui
LEFT JOIN practice_record pr USING(uid)
WHERE level IN (6,7)) t1
GROUP BY uid)a
LEFT JOIN 
(SELECT
uid,
COUNT(DISTINCT dayn) AS act_days_2021
FROM
(SELECT
uid,
DATE_FORMAT(er.start_time,' %Y%m%d') AS dayn
FROM user_info ui
LEFT JOIN exam_record er USING(uid)
WHERE level IN (6,7) AND YEAR(er.start_time) = '2021'
UNION ALL
SELECT
uid,
DATE_FORMAT(pr.submit_time,' %Y%m%d') AS dayn
FROM user_info ui
LEFT JOIN practice_record pr USING(uid)
WHERE level IN (6,7) AND YEAR(pr.submit_time) = '2021') t2
GROUP BY uid)b USING(uid)

LEFT JOIN 
(SELECT
uid,
COUNT(DISTINCT DATE_FORMAT(er.start_time,' %Y%m%d')) AS act_days_2021_exam
FROM user_info ui
LEFT JOIN exam_record er USING(uid)
WHERE level IN (6,7) AND YEAR(er.start_time) = '2021'
GROUP BY uid)c USING(uid)
LEFT JOIN 
(SELECT
uid,
COUNT(DISTINCT DATE_FORMAT(pr.submit_time,' %Y%m%d')) AS act_days_2021_question
FROM user_info ui
LEFT JOIN practice_record pr USING(uid)
WHERE level IN (6,7) AND YEAR(pr.submit_time) = '2021'
GROUP BY uid)d USING(uid)
ORDER BY act_month_total DESC,act_days_2021 DESC;