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

select 
DATE_FORMAT(day,'%Y%m') month,
FORMAT(count(uid)/count(distinct uid),2) avg_active_days,
count(distinct uid) mau
from (
    select 
    distinct uid,DATE_FORMAT(start_time,'%Y%m%d') day
    from exam_record
    where DATE_FORMAT(start_time,'%Y')='2021'
    and submit_time is not null
    group by uid,DATE_FORMAT(start_time,'%Y%m%d')
) a
group by DATE_FORMAT(day,'%Y%m')