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

select e2.month_date,ifnull(t2.mau,0),ifnull(t3.month_add_uv,0),
max(t3.month_add_uv) over(order by e2.month_date) ,
sum(t3.month_add_uv) over (order by e2.month_date) 
 from (
   select distinct date_format(e1.start_time,'%Y%m') as month_date from exam_record as e1 )e2 
   left join (
-- 计算新增用户数:时间-每月新增用户数
select t1.month_date,count(*) as month_add_uv from (
select date_format(start_time,'%Y%m')  as month_date,
row_number() over(partition by uid order by start_time asc ) as rank_date 
from exam_record  ) t1 where t1.rank_date=1 group by t1.month_date ) t3 
on e2.month_date=t3.month_date
left join 
(
-- 计算每个月活跃用户数：时间-月活
select date_format(start_time,'%Y%m')  as month_date
    ,count(distinct uid) as mau 
from exam_record group by date_format(start_time,'%Y%m')
) t2
on e2.month_date=t2.month_date ;