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

思路：
1.先计算每位用户的最先登录时间，判断是否为新增，打标；
2.对新增的用户进行统计
具体步骤：

• 对新增用户打标，建立子表。
  • 当登录时间与用户的最小登录时间一致时，则打标：if(start_time = min(start_time) over(partition by uid),1,0) as tag
• 从子表统计月活用户数、新增用户数、截止当月的单月最大新增用户数、截止当月的累积用户数。
  • 分组：group by month
  • 月活：count(distinct uid)
  • 新增用户数：sum(tag)
  • 截止当月的单月最大新增用户数:max(新增用户数) over(order by month)
  • 截止当月的累积用户数,按照月份依次累加：sum() over(order by month)
  • 排序：order by

代码：

select
    date_format(start_time,"%Y%m") as start_month,
    count(distinct uid),
    sum(tag) as month_add_uv,
    max(sum(tag)) over(order by date_format(start_time,"%Y%m")) as max_month_add_uv,
    sum(sum(tag)) over(order by date_format(start_time,"%Y%m")) as cum_sum_uv
from(
    select
        *,
        if(start_time = min(start_time) over(partition by uid),1,0) as tag
    from exam_record
) add_uv
group by date_format(start_time,"%Y%m")
order by start_month

总结
注意min()over()，max()over()，sum()over()的应用，特别是其中的partition by,order by 的取值