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

#首次登录
with a as(
select uid,date_format(min(start_time),'%Y%m') as first_month
from exam_record
group by uid
)

select date_format(start_time,'%Y%m') as start_month,
count(distinct er.uid) as mau,
count(distinct case when a.first_month = date_format(start_time,'%Y%m') then er.uid else null end ) as month_add_uv,
max(count(distinct case when a.first_month = date_format(start_time,'%Y%m') then er.uid else null end ) ) over (order by date_format(start_time,'%Y%m') ) as max_month_add_uv,
sum(count(distinct case when a.first_month = date_format(start_time,'%Y%m') then er.uid else null end ) ) over (order by date_format(start_time,'%Y%m')) as cum_sum_uv

from exam_record er
left join a on a.uid=er.uid

group by date_format(start_time,'%Y%m')

中等难度