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

SELECT date_format(temp.day, '%Y%m') AS month,
       round(count(*) / count(DISTINCT temp.uid), 2) AS avg_active_days,
       COUNT(DISTINCT temp.uid) AS mau
FROM (
    SELECT er.uid,
           DATE_FORMAT(er.start_time, '%Y-%m-%d') AS day
    FROM exam_record er
             JOIN (
        SELECT DISTINCT DATE_FORMAT(start_time, '%Y%m') AS month
        FROM exam_record
        WHERE score IS NOT NULL AND YEAR(start_time) = '2021'
    ) temp ON DATE_FORMAT(er.start_time, '%Y%m') = temp.month
    WHERE er.score IS NOT NULL
    GROUP BY er.uid, day
) temp
GROUP BY month;