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

# 2021年每个月 按月分组. 活跃指有交卷行为
# 用户平均月活跃天数avg_active_day 活跃天数总和/活跃人数
# 月度活跃人数mau 活跃人数
SELECT a.month, 
       ROUND(
            SUM(active_days)/COUNT(uid)
            ,2)  AS avg_active_days,
       COUNT(uid) AS mau
FROM(
    SELECT DATE_FORMAT(start_time,'%Y%m') AS month, uid,
           COUNT(DISTINCT DATE_FORMAT(start_time,'%Y%m%d')) AS active_days
    FROM exam_record
    WHERE YEAR(start_time) = '2021' AND submit_time IS NOT NULL
    GROUP BY DATE_FORMAT(start_time,'%Y%m'), uid
    ) AS a
GROUP BY a.month
;