题解 | #每个6/7级用户活跃情况#
每个6/7级用户活跃情况
http://www.nowcoder.com/practice/a32c7c8590324c96950417c57fa6ecd1
问题梳理
主问题:每个6/7级用户活跃情况
1. 筛选条件:6/7级用户,即:user_info.level >= 6
2. 指标:
(1)总活跃月份数:做过试卷或做过题目的月份数(无年份限定)
(2)2021年活跃天数:2021年(限定年份),做过试卷或做过题目的天数
(3)2021年试卷作答活跃天数:2021年(限定年份),做过试卷的天数
(4)2021年答题活跃天数:2021年(限定年份),做过题目的天数
3. 返回:uid, act_month_total, act_days_2021, act_days_2021_exam, act_days_2021_question
思路
1. 合并试卷记录的活跃日期与答题记录的日期,先各自去重(group by)再合并(union all)
因为要计算总活跃月份数和某年活跃天数,即同时考虑exam和question,注意需要额外标注exam和question
2. 筛选出目标用户(user_info.level >= 6),连接上述的合并表,创建出临时表(with xxx as ...)。
3. 根据临时表,先计算总活跃月份数,得到表t1 (uid, act_month_total)
这里总活跃月份数要考虑非2021年的记录
4. 根据临时表,再计算其余三个指标,得到表t2 (uid, act_days_2021, act_days_2021_exam, act_days_2021_question)
5. 以表t1为主表,通过uid左连接表t2,注意连接后t2的记录空值转0
表t1的记录了所有用户,t2做了年份条件筛选会导致用户记录不完整,因此需要以t1为主表进行左连接
代码
with tmp as ( select t1.uid, date(t2.start_time) as dt, 1 as tp from user_info as t1 left join exam_record as t2 on t1.uid = t2.uid where level >= 6 group by t1.uid, date(t2.start_time) union all select t1.uid, date(t2.submit_time) as dt, 2 as tp from user_info as t1 left join practice_record as t2 on t1.uid = t2.uid where level >= 6 group by t1.uid, date(t2.submit_time) ) select t1.uid, t1.act_month_total, ifnull(t2.act_days_2021, 0) as act_days_2021, ifnull(t2.act_days_2021_exam, 0) as act_days_2021_exam, ifnull(t2.act_days_2021_question, 0) as act_days_2021_question from ( select uid, count(distinct date_format(dt, '%Y%m')) as act_month_total from tmp group by uid ) as t1 left join ( select uid, count(distinct dt) as act_days_2021, sum(if(tp = 1, 1, 0)) as act_days_2021_exam, sum(if(tp = 2, 1, 0)) as act_days_2021_question from tmp where year(dt) = 2021 group by uid ) as t2 on t1.uid = t2.uid order by t1.act_month_total desc, t2.act_days_2021 desc;