题解 | #每月及截止当月的答题情况#

WITH a AS (
    SELECT
        uid,
        DATE_FORMAT(start_time, '%Y%m' ) AS start_month,
        MIN(DATE_FORMAT(start_time, '%Y%m')) OVER (PARTITION BY uid) AS first_month
    FROM
        exam_record
),

b AS (
    SELECT
        start_month,
        COUNT(DISTINCT uid) AS mau
    FROM
        a
    GROUP BY
        start_month
),

c AS (
    SELECT
        start_month,
        COUNT(DISTINCT uid) AS month_add_uv
    FROM
        a
    WHERE
        start_month = first_month 
    GROUP BY
        start_month
),

d AS (
    SELECT
        start_month,
        mau,
        IFNULL(month_add_uv, 0) AS month_add_uv,
        MAX(IFNULL(month_add_uv, 0)) OVER (ORDER BY start_month) AS max_month_add_uv,
        SUM(IFNULL(month_add_uv, 0)) OVER (ORDER BY start_month) AS cum_sum_uv
    FROM
        b
    LEFT JOIN
        c USING(start_month)
)

SELECT
    *
FROM
    d
ORDER BY
    start_month