题解 | #每个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 user_info
LEFT JOIN(
    SELECT uid,
            COUNT(DISTINCT DATE_FORMAT(start_time, "%Y%m")) AS act_month_total
    FROM 
    ((SELECT uid, start_time, score
    FROM exam_record)
    UNION ALL
    (SELECT uid, submit_time, score
    FROM practice_record)) AS union_exam_prac
    GROUP BY uid 
) act_mon_count USING(uid)
LEFT JOIN (
    SELECT uid,
            COUNT(DISTINCT DATE_FORMAT(start_time, "%Y%m%d")) AS act_days_2021 	
    FROM 
    ((SELECT uid, start_time, score
    FROM exam_record)
    UNION ALL
    (SELECT uid, submit_time, score
    FROM practice_record)) AS union_exam_prac
    WHERE YEAR(start_time) = "2021"
    GROUP BY uid 
) act_day_total_21 USING(uid)
LEFT JOIN (
    SELECT uid,
            COUNT(DISTINCT DATE_FORMAT(start_time, "%Y%m%d")) AS act_days_2021_exam	
    FROM exam_record
    WHERE YEAR(start_time) = "2021"
    GROUP BY uid 
) act_day_exam_21 USING(uid)
LEFT JOIN (
    SELECT uid,
            COUNT(DISTINCT DATE_FORMAT(submit_time, "%Y%m%d")) AS act_days_2021_question
    FROM practice_record
    WHERE YEAR(submit_time) = "2021"
    GROUP BY uid 
) act_day_question_21 USING(uid)

WHERE level IN (6,7)
ORDER BY act_month_total DESC, act_days_2021 DESC;

• 没错，咱就是这个傻瓜，又臭又长