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

SELECT DATE_FORMAT(start_time, "%Y%m") month, 
    ROUND(COUNT(DISTINCT uid, DAY(start_time))/COUNT(DISTINCT uid),2) avg_active_days,
    COUNT(DISTINCT uid) mau
FROM exam_record
WHERE submit_time IS NOT NULL AND YEAR(start_time) = 2021
GROUP BY month

重点在于找出每月用户活跃天数总和，由于有用户在同一天多次行为的重复数据，要用COUNT(DISTINCT uid, DAY(start_time))去重并多条件搜索