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

select CONCAT(YEAR(submit_time),if(MONTH(submit_time)>=10,null,0),MONTH(submit_time)) month,
round(count(distinct uid,day(submit_time))/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 month

重点：计算月活次数的时候，一定要考虑去除同一个用户在一天内多次做答的情况

count(distinct uid,day(submit_time))