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

select month,format(count(month)/count(distinct uid),2) avg_active_days,count(distinct uid) mau 
from 
(select date_format(t1.submit_time,'%Y%m') month,uid 
 from 
 (select distinct date_format(submit_time,'%Y%m%d') submit_time,uid 
  from exam_record 
  where submit_time is not null 
  and submit_time not like '2020%') t1 
  order by submit_time) t2 
  group by month;

解题思路:

一:先取出不同的提交日期

二:把日期的号去掉改成年月的格式

三:用统计的月份数量除以不同uid的用户数量

四:用年月的格式来排序