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

SELECT
    start_month,
    COUNT(DISTINCT uid) AS mau,
    SUM(IF(rk=1,1,0)) AS month_add_uv,
    MAX(SUM(IF(rk=1,1,0))) OVER(ORDER BY start_month) AS max_month_add_uv,
    SUM(SUM(IF(rk=1,1,0))) OVER(ORDER BY start_month) AS cum_sum_uv
FROM(
    SELECT
        uid,
        DATE_FORMAT(start_time,'%Y%m') start_month,
        ROW_NUMBER() OVER(PARTITION BY uid ORDER BY DATE_FORMAT(start_time,'%Y%m')) rk
    FROM
        exam_record
)t
GROUP BY
    start_month
ORDER BY
    start_month