题解 | 平均活跃天数和月活人数

with t0 as(select distinct date_format(start_time,'%Y%m') as month,uid,date_format(submit_time,'%Y-%m-%d') as dt from exam_record where score is not null order by 1)

select month,round(count(dt)/count(distinct case when dt is not null then uid end),2) as avg_active_days,count(distinct case when dt is not null then uid end) as mau from t0 where month like '2021%' group by 1