题解 | #平均活跃天数和月活人数#
平均活跃天数和月活人数
https://www.nowcoder.com/practice/9e2fb674b58b4f60ac765b7a37dde1b9
SELECT DATE_FORMAT(submit_time,'%Y%m') AS month,ROUND(COUNT( DISTINCT uid,DATE(submit_time) )/COUNT( DISTINCT uid ),2) AS avg_active_days,COUNT(DISTINCT uid) AS mau
FROM exam_record
WHERE YEAR(submit_time)=2021
FROM exam_record
WHERE YEAR(submit_time)=2021
GROUP BY DATE_FORMAT(submit_time,'%Y%m');
难点在于月活跃天数,月活跃天数是计算不同用户在同一月份不同日期的提交记录数,如果直接用COUNT(DISTINCT DATE(submit_time) ),那么当一个用户在同一天内提交了多份试卷,那么月活跃天数将远远超出实际。
因此需要对uid和DATE(submit_time) 两列去重,保证同一个用户无论一天提交多少份试卷都只算一次。