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

# 月活用户数、新增用户数(最早出现的时间所在的月份、sum当月新)、目前最大每月新增用户数（sum新的max）、目前累计用户数（sum所有新）

SELECT

start_month,

COUNT(DISTINCT uid) AS mau, # 月活用户数：按照月份去重计数

SUM(IF(start_time = earliest_start_time, 1, 0)) AS month_add_uv, # 新增用户数：按照用户最早开始的月份计数

MAX(SUM(IF(start_time = earliest_start_time, 1, 0))) OVER(ORDER BY start_month) AS max_month_add_uv,

# 截止当月的单月最大新增用户数：按照月份排序取最大值

SUM(SUM(IF(start_time = earliest_start_time, 1, 0))) OVER(ORDER BY start_month) AS cum_sum_uv

# 截止当月的累积用户数：按照月份排序加总

FROM(

SELECT

uid,

DATE_FORMAT(start_time, '%Y%m') AS start_month,

start_time, MIN(start_time) OVER(PARTITION BY uid) AS earliest_start_time

FROM exam_record # 子查询，按照uid分区，找出最早开始的时间

) AS new_table

GROUP BY start_month

ORDER BY start_month