# ## 先把提交时间转化一下：
# SELECT DATE_FORMAT(submit_time) AS start_month,COUNT(DISTINCT uid) AS mau
# FROM exam_record
# GROUP BY start_month
############################ 这里写的两句是错误的 ##############################
# ## 新增用户new_user：MIN(start_month) = MAX(start_month)
# ## 新增用户数month_add_uv：COUNT(IF(MIN(start_month = MAX(start_month),1,0) AS new_user_cnt

############################ 出错的原因就在于：没有把新用户的SQL写正确 ###############################
# 查询用户第一天登录的日期：按照uid进行分组，最早的start_time作为第一天登录new_user_day这一列
# MIN(start_time) OVER(PRITITION BY uid) AS new_user_day
# 定义新用户：新增一列new_user,如果为1代表是新用户，如果为0代表不是新用户 ~~~~~
# IF(start_time = MIN(start_time) OVER (PARTITION BY uid),1,0) AS new_user

# SELECT DATE_FORMAT(submit_time) AS start_month,COUNT(DISTINCT uid) AS mau,
# COUNT(IF(MIN(start_month) = MAX(start_month),1,0)) AS month_add_uv
# FROM exam_record
# GROUP BY start_month

# 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

SELECT start_month,COUNT(DISTINCT uid) mau,SUM(new_user) month_add_uv,
MAX(SUM(new_user)) OVER(ORDER BY start_month) max_month_add_uv,
SUM(SUM(new_user)) OVER(ORDER BY start_month) cum_sum_uv
FROM(
    SELECT *, DATE_FORMAT(start_time,'%Y%m') start_month,
    IF(start_time = MIN(start_time) OVER (PARTITION BY uid),1,0) new_user
    FROM exam_record)t1
GROUP BY start_month

# SELECT start_month ,#每个月
#     COUNT(DISTINCT uid) mau, #月活用户数   
#     SUM(new_day) month_add_uv, #新增用户
#     MAX(SUM(new_day))OVER(ORDER BY start_month) max_month_add_uv, #截止当月的单月最大新增用户数
#     SUM(SUM(new_day))OVER(ORDER BY start_month) cum_sum_uv
# FROM (
#     SELECT *,DATE_FORMAT(start_time,'%Y%m') start_month,     IF(start_time=MIN(start_time)OVER(PARTITION BY uid),1,0) new_day
#     FROM exam_record)t1
# GROUP BY start_month;