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

select
    date_format (submit_time, '%Y%m') as month,
    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(time,'%Y%m').
• 条件筛选：提交试卷并且年份为2021. year（time） = 2021.
• 月活天数：count(distinct uid, date_format (submit_time, '%y%m%d')).//含义，两个参数中只要有一个不一样就列出。
• 月活人数：count(distinct uid）//只计算人，不计算这个人活跃了多少天
• 日期：date_format(,'%Y%m')

本题函数：

• count函数
• date_format()格式化函数
• year()计算日期函数