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

SELECT user_info.uid as uid,
    ifnull(act_month_total, 0) as act_month_total,
    ifnull(act_days_2021, 0) as act_days_2021,
    ifnull(act_days_2021_exam, 0) as act_days_2021_exam,
    ifnull(act_days_2021_question, 0) as act_days_2021_question
FROM user_info
LEFT JOIN (
    -- 总活跃月份数、2021年活跃天数
    SELECT uid,
        count(distinct DATE_FORMAT(active_date, "%Y%m")) as act_month_total,
        count(distinct if(year(active_date)=2021, active_date, null)) as act_days_2021
    FROM (
        SELECT distinct uid, DATE_FORMAT(exam_record.start_time, "%Y%m%d") as active_date FROM exam_record
        UNION ALL 
        SELECT distinct uid, DATE_FORMAT(practice_record.submit_time, "%Y%m%d") as active_date FROM practice_record
    ) as t_merge_record
    GROUP BY uid
) AS t_2021_total_act ON t_2021_total_act.uid=user_info.uid

LEFT JOIN (
    -- 2021年题目练习活跃天数
    SELECT uid, count(distinct DATE_FORMAT(practice_record.submit_time, "%Y%m%d")) as act_days_2021_question
    FROM practice_record
    WHERE year(practice_record.submit_time)=2021
    GROUP BY uid
) as t_2021_act_days_question ON t_2021_act_days_question.uid=user_info.uid

LEFT JOIN (
    -- 2021试卷作答活跃天数
    SELECT uid, count(distinct DATE_FORMAT(exam_record.start_time, "%Y%m%d")) as act_days_2021_exam
    FROM exam_record
    WHERE year(exam_record.start_time)=2021
    GROUP BY uid
) as t_2021_act_days_exam ON t_2021_act_days_exam.uid=user_info.uid

WHERE user_info.level>5
ORDER BY act_month_total desc, act_days_2021 desc