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

# 6/7级用户
# 总活跃月份数，2021年活跃天数,2021年试卷作答活跃天数，2021年答题活跃天数
# 总活跃月份数,2021年活跃天数降序排序
# 注意user_info表联结后再进行聚合，否则符合条件的部份用户数据将为NULL

SELECT u.uid, 
    COUNT( DISTINCT a.active_month) AS act_month_total,
    COUNT(DISTINCT CASE 
            WHEN YEAR(a.active_day)= '2021' THEN a.active_day END
            ) AS act_days_2021,
    COUNT(DISTINCT CASE 
            WHEN YEAR(a.active_day)= '2021'  AND a.category='试卷作答' THEN a.active_day END
            ) AS act_days_2021_exam,
    COUNT(DISTINCT CASE 
            WHEN YEAR(a.active_day)= '2021'  AND a.category='题目练习' THEN a.active_day END
            ) AS act_days_2021_question
FROM user_info AS u
LEFT JOIN (
    (SELECT uid, 
        DATE_FORMAT(start_time,'%Y%m' ) AS active_month,
        DATE_FORMAT(start_time,'%Y%m%d' ) AS active_day,
        '试卷作答' AS category
    FROM exam_record)
    UNION ALL
    (SELECT uid, 
        DATE_FORMAT(submit_time,'%Y%m' ) AS active_month,
        DATE_FORMAT(submit_time,'%Y%m%d' ) AS active_day,
        '题目练习' AS category
    FROM practice_record)
    ) AS a 
ON u.uid=a.uid
WHERE u.level >= 6
GROUP BY u.uid
ORDER BY act_month_total DESC, act_days_2021 DESC
;