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

select
    date_format (submit_time, '%Y%m'),
    round((count(distinct uid, date_format(submit_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
    date_format (submit_time, '%Y%m')

本题难度在于第一group by必须要转换日期格式使用date_format(XX,''),其次计算活跃天数必须要取uid和submite的唯一值，因为有可能同一个id在同一天内考了2次考试，本质就算一天，代码如上