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

#运行通过
select
    date_format (submit_time, "%Y%m") as month,
    #唯一不同就是这里的 count(distinct,submit_time)于格式化
    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(submit_time)=2021 and submit_time is not null
group by date_format(submit_time,"%Y%m");
#运行不通过

• 活跃天数：当同一个用户同一天登录两次时只能算一个活跃天数，所以天数统计 应该写作

#注意两点
#1.这里 count（）看似出现了两个参数，其实是针对distinct 的：只有当后面两个参数全部相同时才算作一个
#2.之所以要对sumbit_time进行格式化：同一个用户,同一天登录只算一天，如果不处理，那么数据范围扩大
#同一个用户，同一天的不同时间登录就被加进去了
count(distinct uid,date_format(submit_time,"%y%m%d"))