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

这个比较好看。

select
date_format(start_time,"%Y%m"),
round(
count(distinct date(submit_time),uid)/count(distinct case when score is not null then uid end),2) as avg_active_days	,
count(distinct case when score is not null then uid end)
from exam_record
where
year(start_time)=2021
group by date_format(start_time,"%Y%m")