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

SELECT 
    uid.uid,
    IFNULL(act_month_total,0) act_month_total,
    IFNULL(act_days_2021,0) act_days_2021,
    IFNULL(act_days_2021_exam,0) act_days_2021_exam,
    IFNULL(act_days_2021_question,0) act_days_2021_question
FROM(
    SELECT uid
    FROM user_info
    WHERE level = 6 OR level = 7
    ) uid
LEFT JOIN 
    (
    SELECT 
        uid,
        COUNT(act_month) act_month_total
    FROM(
        SELECT 
            DISTINCT uid, DATE_FORMAT(start_time,'%Y%m') act_month
        FROM exam_record
        UNION 
        SELECT 
            DISTINCT uid, DATE_FORMAT(submit_time,'%Y%m') act_month
        FROM practice_record
        ) uid_act_month
    GROUP BY uid
    ) act_month_total USING(uid)
LEFT JOIN
    (
    SELECT 
        uid,
        COUNT(act_days_2021) act_days_2021  
    FROM
        (
        SELECT 
            DISTINCT uid, DATE(start_time) act_days_2021
        FROM exam_record
        WHERE YEAR(start_time) = 2021
        UNION 
        SELECT 
            DISTINCT uid, DATE(submit_time) act_days_2021
        FROM practice_record
        WHERE YEAR(submit_time) = 2021
        ) uid_act_days_2021
    GROUP BY uid
    ) act_days_2021 USING(uid)
LEFT JOIN
    (SELECT
        uid,
        COUNT(DISTINCT DATE(start_time)) act_days_2021_exam  
    FROM exam_record
    WHERE YEAR(start_time) = 2021
    GROUP BY uid) act_days_2021_exam USING(uid)
LEFT JOIN
    (SELECT
        uid,
        COUNT(DISTINCT DATE(submit_time)) act_days_2021_question  
    FROM practice_record
    WHERE YEAR(submit_time) = 2021
    GROUP BY uid) act_days_2021_question USING(uid)

ORDER BY act_month_total DESC,act_days_2021 DESC