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

这道题做了好久老是报错，后面才发现是理解错了【用户平均月活跃天数】

我以为是

用户平均月活跃天数=count(uid)/count(distinct uid)

但后面发现，一个用户在[一天内]可能会提交[多份]试卷，所以需要按照uid和天数来去重，所以：

用户平均月活跃天数=count(distinct uid,date_format(submit_time,'%Y%m%d')/distinct uid

代码如下：

select date_format(submit_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) as mau
from exam_record
where year(submit_time)=2021 and submit_time is not null 
group by date_format(submit_time,'%Y%m')
order by month;