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

SELECT
    uid,
    COUNT(DISTINCT DATE_FORMAT(active_date,"%Y%m")) act_month_total,
    SUM(IF(YEAR(active_date)=2021,1,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
    user_info 
LEFT JOIN(
    SELECT
        uid,DATE(start_time) active_date
    FROM
        exam_record
    UNION
    SELECT
        uid,DATE(submit_time) active_date
    FROM
        practice_record
)t1 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
)t2 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
)t3 USING(uid)
WHERE
    level = 6 OR level = 7
GROUP BY
    uid
ORDER BY
    act_month_total DESC,act_days_2021 DESC