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

select extract(year_month from start_time) month,
    round(count( distinct if(submit_time is null, null, concat(uid, date(submit_time))))/count(distinct if(submit_time is null, null, uid)),2)
    avg_active_days,
    count(distinct if(submit_time is null, null, uid)) mau
from exam_record
where year(start_time) = '2021'
group by extract(year_month from start_time) ;

中间的函数写的特别麻烦

select extract(year_month from start_time) month,
    round(count(distinct uid, if(submit_time is null, null, date(submit_time)))/count(distinct if(submit_time is null, null, uid)),2)
    avg_active_days,
    count(distinct if(submit_time is null, null, uid)) mau
from exam_record
where year(start_time) = '2021'
group by extract(year_month from start_time) ;