牛客SQL126——平均活跃天数和月活人数

题目描述
https://www.nowcoder.com/practice/9e2fb674b58b4f60ac765b7a37dde1b9

思路：返回一个month为202107的格式，首先先对submit_time格式进行转换
平均月活跃天数avg_active_days
要先计算出一共活跃了多少天，再除以去重以后的活跃人数count(distinct uid)
计算活跃多少天不能直接count submit_time，因为同一个人可能在同一天活跃多次，所以得对uid和submit_time一起进行count
月度活跃人数mau，直接count distinct uid即可

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')