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

SELECT
    ui.uid uid,
    IF (c.act_month_total IS NULL, 0, c.act_month_total) act_month_total,
    IF (pp.act_days_2021 IS NULL, 0, pp.act_days_2021) act_days_2021,
    IF (
        b.act_days_2021_exam IS NULL,
        0,
        b.act_days_2021_exam
    ) act_days_2021_exam,
    IF (
        a.act_days_2021_question IS NULL,
        0,
        a.act_days_2021_question
    ) act_days_2021_question
FROM
    user_info ui
    LEFT JOIN (
        SELECT
            count(act_days_2021) act_days_2021,
            uid
        from
            (
                SELECT
                    uid,
                    DATE_FORMAT (submit_time, '%Y%m%d') act_days_2021
                FROM
                    practice_record
                WHERE
                    score IS NOT NULL
                    AND YEAR (submit_time) = '2021'
                UNION
                SELECT
                    uid,
                    DATE_FORMAT (submit_time, '%Y%m%d') act_days_2021
                FROM
                    exam_record
                WHERE
                    score IS NOT NULL
                    AND YEAR (submit_time) = '2021'
            ) jb
        GROUP BY
            uid
    ) pp on pp.uid = ui.uid
    LEFT JOIN (
        SELECT
            uid,
            count(DISTINCT DATE_FORMAT (submit_time, '%Y%m%d')) act_days_2021_question
        FROM
            practice_record
        WHERE
            score IS NOT NULL
            AND YEAR (submit_time) = '2021'
        GROUP BY
            uid
    ) a ON a.uid = ui.uid
    LEFT JOIN (
        SELECT
            uid,
            count(DISTINCT DATE_FORMAT (submit_time, '%Y%m%d')) act_days_2021_exam
        FROM
            exam_record
        WHERE
            score IS NOT NULL
            AND YEAR (submit_time) = '2021'
        GROUP BY
            uid
    ) b ON b.uid = ui.uid
    LEFT JOIN (
        SELECT
            count(DISTINCT act_month) act_month_total,
            uid
        FROM
            (
                SELECT
                    DATE_FORMAT (start_time, '%Y%m') act_month,
                    uid
                FROM
                    exam_record
                WHERE
                    score IS NOT NULL
                UNION
                (
                    SELECT
                        DATE_FORMAT (submit_time, '%Y%m') act_month,
                        uid
                    FROM
                        practice_record
                    WHERE
                        score IS NOT NULL
                )
            ) tmp
        GROUP BY
            uid
    ) c ON c.uid = ui.uid
WHERE
    ui.LEVEL IN (6, 7)
ORDER BY
    act_month_total DESC,
    act_days_2021 DESC