牛客SQL144——每月及截止当月答题情况

题目描述
https://www.nowcoder.com/practice/1ce93d5cec5c4243930fc5e8efaaca1e

思路：因为有一个项是要新增人数，所以需要筛选出每个用户第一次登录时间，然后连接exam_record表直接把每月总的人数和新增人数计算出来

#每个月总的人数和新增人数
select date_format(r.start_time,"%Y%m") as start_month,count(distinct r.uid) as mau,count(distinct t.uid) as month_add_uv
from exam_record as r left join (
    select uid,min(date_format(start_time,"%Y%m")) as first
    from exam_record
    group by uid) as t 
on date_format(r.start_time,"%Y%m") =t.first
group by start_month

#他要的是每个月不同的人数，可以把各用户第一次登录时间记下来
# select uid,min(date_format(start_time,"%Y%m"))
# from exam_record
# group by uid

剩下的最大每月新增人数和累计新增人数，可以通过窗口函数进行计算

select a.*,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 date_format(r.start_time,"%Y%m") as start_month,count(distinct r.uid) as mau,count(distinct t.uid) as month_add_uv
from exam_record as r left join (
    select uid,min(date_format(start_time,"%Y%m")) as first
    from exam_record
    group by uid) as t 
on date_format(r.start_time,"%Y%m") =t.first
group by start_month
) as a