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

select date_format(submit_time,'%Y%m') as month,
round(count(distinct uid,day(submit_time))/count(distinct uid),2) as avg_active_days,
count(distinct uid) as mau
from exam_record
where year(submit_time)=2021
group by date_format(submit_time,'%Y%m')

思路：返回一个month为202107的格式，首先先对submit_time格式进行转换

平均月活跃天数avg_active_days

要先计算出一共活跃了多少天，再除以去重以后的活跃人数count(distinct uid)

计算活跃多少天不能直接count submit_time，因为同一个人可能在同一天活跃多次，所以得对uid和submit_time一起进行count

月度活跃人数mau，直接count distinct uid即可