题解 | #平均活跃天数和月活人数#
平均活跃天数和月活人数
https://www.nowcoder.com/practice/9e2fb674b58b4f60ac765b7a37dde1b9
with t1 as ( SELECT uid, DATE_FORMAT(submit_time,'%Y%m%d') month FROM exam_record WHERE score is not NULL and DATE_FORMAT(submit_time,'%Y') = 2021 ), t2 as ( select uid, LEFT(month,6) month from t1 group by uid, month ) select month, round(COUNT(uid)/count(DISTINCT uid),2) avg_active_days,count(DISTINCT uid) mau from t2 group by month
要点：
第一， 20201年
第二，1个用户同一天最多只记一次活跃
