一、明确需求：

1. 每月完成作答的用户得平均活跃天数
2. MAU

#剔除未提交试卷的记录；提取用户年份，年月，年月日作为后续的筛选条件
with
t1 as
(
SELECT distinct *
    , year(start_time) y
    , DATE_FORMAT(start_time,'%Y%m') y_m
    , date_format(start_time,'%Y%m%d') y_m_d
from exam_record
where submit_time is not null 
    )

select y_m as month
    , round(avg(active_days),2) avg_active_days
    , count(distinct uid) mau 
from 
(
select y_m,uid
    ,count(distinct y_m_d) active_days
from t1
where y =2021
group by y_m,uid
    )x 
group by y_m

二、拓展

date_format(date,日期格式) 具体见链接官方文档