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

# select CONCAT(YEAR(start_time), LPAD(MONTH(start_time), 2, '0')) AS month,
select date_format(submit_time, '%Y%m') AS month,
ROUND(COUNT(distinct uid,date_format(submit_time, '%y%m%d')) / COUNT(distinct uid) ,2) AS avg_active_days,	
 COUNT(distinct uid) AS mau
FROM exam_record
WHERE YEAR(start_time) = 2021 AND submit_time is NOT NULL
GROUP BY month 

COUNT(distinct uid,date_format(submit_time, '%y%m%d') 里面相当于 or 对两个条件计数

同一年月日同一个用户算一次记录

比如，当两条记录的年月日相同，只是时间不同，用户id也相同，但是一个是上午交卷，一个是下午交卷，这只能算是一条记录，

distinct uid 实现了去重，确保计算的是唯一的用户和日期组合的数量。