题解 | #平均活跃天数和月活人数#
平均活跃天数和月活人数
https://www.nowcoder.com/practice/9e2fb674b58b4f60ac765b7a37dde1b9
努力练习sql中!
题目要求:
- 计算2021年每个月里试卷作答区用户平均月活跃天数avg_active_days和月度活跃人数mau
- 结果保留两位小数
分析:用户平均活跃天数 = 所有用户活跃总天数/活跃用户总人数
这题我自己的解题思路是先求出2021年每天的数据,再统计每月的数据
select month,round((count(*)/count(distinct(uid))),2) as avg_active_days,count(distinct(uid)) as mau from ( select --时间转换为月,便于后面按月分组,这里不能用时间函数匹配了 substring_index (time, '-', 1) as month, uid from ( --先求出2021年每天的数据,同一个用户一天可能多次提交,要去重 select distinct uid, date_format (submit_time, '%Y%m-%d') as time from exam_record where submit_time is not null and year (submit_time) = '2021' ) t1 )t2 group by month
我的方法比较不灵活(各种细节使用还不够熟练),很多地方可以优化,不用那么多select,但是应该比较好懂。
这是大佬的代码!思路都一样的,就是写一起了。
select date_format(submit_time, '%Y%m') as month, round((count(distinct uid, date_format(submit_time, '%y%m%d'))) / count(distinct uid), 2) as avg_active_days, count(distinct uid) as mau from exam_record where submit_time is not null and year(submit_time) = 2021 group by date_format(submit_time, '%Y%m')#sql##sql练习日常##找工作好难#