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

select month,round(sum(active_days)/count(uid),2) as avg_active_days,
sum(au) as mau
from(

select date_format(submit_time,'%Y%m') as month,uid,
count(distinct  uid,date_format(submit_time,'%Y%m%d')) as active_days,
count(distinct uid) as au

from exam_record
where year(submit_time)=2021


group by date_format(submit_time,'%Y%m'),uid
)a
group by month

先在a层求每个月每个用户的活跃天数、个数

再在外层求每个月的总活跃天数，总用户个数，用总活跃天数/总用户个数得到平均活跃天数，

细节count(distinct uid,date_format(submit_time,'%Y%m%d')) as active_days，因为有出现同一个用户一天做多个试卷的情况