-- 请计算2021年每个月里试卷作答区用户平均月活跃天数avg_active_days和月度活跃人数mau,
-- 平均月活跃天数=总的活跃天数/月度活跃人数   月度活跃人数=提交的人数  总的活跃天数=每个用户活跃天数之和
-- 1、过滤非活跃数据 2、日期字符列 3、月分组 4、统计 COUNT(DISTINCT uid, std_str) 总的活跃天数,用户id和提交日期去重统计
SELECT 
    st_str,
    ROUND(COUNT(DISTINCT uid, std_str) / COUNT(DISTINCT uid),
            2) avg_active_days,
    COUNT(DISTINCT uid) mau
FROM
    (SELECT 
        *,
            DATE_FORMAT(submit_time, '%Y%m') st_str,
            DATE_FORMAT(submit_time, '%Y%m%d') std_str
    FROM
        exam_record
    WHERE
        submit_time IS NOT NULL
            AND submit_time < STR_TO_DATE('2022-01-01', '%Y-%m-%d')
            AND submit_time >= STR_TO_DATE('2021-01-01', '%Y-%m-%d')) a
GROUP BY st_str;