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

SELECT 
    month,
    ROUND(AVG(active_days_per_user), 2) AS avg_active_days,
    COUNT(DISTINCT uid) AS mau
FROM (
    SELECT 
        DATE_FORMAT(submit_time, '%Y%m') AS month,
        uid,
        COUNT(DISTINCT DATE_FORMAT(submit_time, '%Y%m%d')) AS active_days_per_user
    FROM 
        exam_record
    WHERE 
        submit_time IS NOT NULL AND YEAR(submit_time) = 2021
    GROUP BY 
        DATE_FORMAT(submit_time, '%Y%m'), 
        uid
) AS subquery
GROUP BY 
    month;

#

高赞回答感觉不常用，这是通义千问写的