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

select concat(substr(submit_time,1,4),substr(submit_time,6,2)) as month,
round(count(distinct uid,day(submit_time))/count(distinct uid),2) as avg_active_days,count(distinct uid) as mau
from exam_record
where year(submit_time)='2021'
group by month

substr(submit_time,1,4)：取出submit_time的前四位，即年份

substr(submit_time,6,2)：取出submit_time的5-6位，即月份

concat(substr(submit_time,1,4),substr(submit_time,6,2))：将年份月份拼接在一起，例如：202107

count(distinct uid,day(submit_time))：月度活跃天数，由于不同的用户可能会在同一天答题，所以要采用uid,day(submit_time)联合去重，比如说：2021年7月有2人活跃，共活跃了3天（1001活跃1天，1002活跃2天，其中2021-07-02两个用户都活跃了）

count(distinct uid)：月度活跃人数，由于同一用户在一个月中会答题多次，故需要去重

round(...,2):保留两位小数

补充：使用date_format(submit_time, '%Y%m')可以直接取出年份和月份