题解 SQL17 平均活跃天数和月活人数

-- 交卷行为 submit_time 不为空 -- 每个月 按照 month(date) 分组

-- 选出 ： 2021 活跃用户where submirt_time is not null andf year(submit_time)='2021' -- 获取用户活跃的日期去重 -- 按照月份提取月份substr(ymd,1,6) as month , 分组group by month -- 月活人数 count(distinct uid) as mau -- 平均活跃天数： count(1) /count(distinct uid )
-- 提取出年月： substr(DATE_FORMAT(submit_time,"%Y%m%d"),1,6)

-- select SUBSTR(ymd,1,6) as 'month',* -- ,count(1)/count(distinct uid ) as avg_active_days -- ,count(distinct uid ) as mau select substr(ymd,1,6) as m,round(count(*)/ count(DISTINCT uid),2),count(distinct(uid)) as mau from ( select distinct uid ,DATE_FORMAT(submit_time,"%Y%m%d"),substr(DATE_FORMAT(submit_time,"%Y%m%d"),1,6) as ymd from exam_record where submit_time is not null and YEAR(submit_time)='2021') as t_active_day group by m; ;