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

/*
select
    ym,
    round(count(uid) / count(distinct uid ), 2) as avg_active_days,
    count(distinct uid )as mau
from
    (select 
        uid,
        exam_id,
        date(submit_time),
        concat(year(submit_time),  LPAD(month(submit_time),2,'0') )  as ym,
        row_number() over(partition by uid, date(submit_time) order by submit_time) as rk
    from exam_record 
    where submit_time is not null and year(submit_time) = 2021) a
where rk=1
group by ym
*/

-- 参考《要成为一个厉害的人》的代码，更好
select DATE_FORMAT(start_time,"%Y%m") as month,
round(count(distinct uid,date_format(start_time,"%Y%m%d"))/count(distinct uid),2) as avg_active_days,
count(distinct uid) as mau
from exam_record
where submit_time is not NULL
and YEAR(submit_time) =2021
group by month;