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

select 
date_format(start_time,"%Y%m") as month 
,round(count(distinct uid ,date_format(submit_time,"%Y%m%d"))/count(distinct uid),2) as avg_active_days 
,count(distinct uid) mau 
from 
exam_record 
where submit_time is not null 
and year(submit_time) = "2021"
group by date_format(start_time,"%Y%m") 
order by date_format(start_time,"%Y%m")

需要注意活跃天数，是每个用户活跃天数的总计。需要考虑有的用户一天活跃多次的情况，需要进行去重