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

with users as (
    select distinct concat(substring(start_time,1,4),substring(start_time,6,2)) as start_month,uid
    from exam_record
),
first_time as (
    select distinct uid,min(start_month)over (partition by uid) as first_month
    from users
),
month_add as (
    select start_month,sum(if(start_month=first_month,1,0)) as addd
from users u left join first_time f
on u.uid=f.uid
group by start_month
)

select a.start_month,count(distinct uid) as mau,addd as month_add_uv,max(addd) over (order by a.start_month) as max_month_add_uv,sum(addd) over (order by a.start_month) as cum_sum_uv
from users u right join month_add a on u.start_month=a.start_month
group by a.start_month
order by a.start_month

有一个点，最开始做users表的时候，没有distinct。

会导致month_add的时候，sumif，出现同一个uid在同一个月份被加了n次的情况，导致最后的新增数错误