题解 SQL17 平均活跃天数和月活人数
平均活跃天数和月活人数
http://www.nowcoder.com/practice/9e2fb674b58b4f60ac765b7a37dde1b9
-- 交卷行为 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; ;
查看8道真题和解析
，
