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

/*
* 月活用户数：COUNT(DISTINCT uid)
* 新增用户数：
*    找出每个用户第一次活跃的月份，当第一次活跃的月份等于当前月份时，即该用户为新增活跃用户
* 截止当月的单月最大新增用户数：
*    max(month_add_uv) over(ORDER BY start_month)
* 截止当月的累积用户数：
*    计算列 月新增用户数 的累计和即可
*/
SELECT start_month, mau, month_add_uv,
    MAX(month_add_uv) OVER(ORDER BY start_month) AS max_month_add_uv,
    SUM(month_add_uv) OVER(ORDER BY start_month) AS cum_sum_uv
FROM (
    SELECT start_month,
        COUNT(DISTINCT uid) AS mau,
        COUNT(DISTINCT IF(start_month = first_act_month, uid, NULL)) AS month_add_uv
    FROM (
        SELECT uid,
            DATE_FORMAT(start_time, '%Y%m') AS start_month,
            MIN(DATE_FORMAT(start_time, '%Y%m')) OVER(PARTITION BY uid ORDER BY DATE_FORMAT(start_time, '%Y%m')) AS first_act_month
        FROM exam_record
    )first_t
    GROUP BY start_month
)cal_t
ORDER BY start_month