题解 | #每个6/7级用户活跃情况#
每个6/7级用户活跃情况
http://www.nowcoder.com/practice/a32c7c8590324c96950417c57fa6ecd1
总体思路：
注意这个问题的计算：活跃天数 != 作答试卷的活跃天数 + 练习的活跃天数，因为完全有可能同一天做了练习，又做了试卷
1、将试卷和练习的活跃月份、日期查询出来
（1）查询试卷的活跃月份和日期
select u.uid, m.act_month, date(e.start_time) act_days_2021, date(e.start_time) act_days_2021_exam, null act_days_2021_question from user_info u LEFT JOIN exam_record e ON u.uid = e.uid and year(e.start_time) = 2021 LEFT JOIN (SELECT a.uid,DATE_FORMAT(a.start_time,'%Y%m') act_month from exam_record a where a.submit_time is not null GROUP BY a.uid,act_month) m ON u.uid = m.uid where u.level in (6,7)因为活跃的月份没有限制，所以要单独查询，这里用 null 来表示练习活跃的日期，表示没有
（2）同样的，可以查询练习的活跃月份和日期
select u.uid, m.act_month, date(p.submit_time) act_days_2021, null act_days_2021_exam, date(p.submit_time) act_days_2021_question from user_info u LEFT JOIN practice_record p ON u.uid = p.uid and year(p.submit_time) = 2021 LEFT JOIN (SELECT a.uid,DATE_FORMAT(a.submit_time,'%Y%m') act_month from practice_record a where a.submit_time is not null GROUP BY a.uid,act_month) m ON u.uid = m.uid where u.level in (6,7)
2、合并
使用 union all 将上述的两个查询合并
select u.uid, m.act_month, date(e.start_time) act_days_2021, date(e.start_time) act_days_2021_exam, null act_days_2021_question from user_info u LEFT JOIN exam_record e ON u.uid = e.uid and year(e.start_time) = 2021 LEFT JOIN (SELECT a.uid,DATE_FORMAT(a.start_time,'%Y%m') act_month from exam_record a where a.submit_time is not null GROUP BY a.uid,act_month) m ON u.uid = m.uid where u.level in (6,7) union all select u.uid, m.act_month, date(p.submit_time) act_days_2021, null act_days_2021_exam, date(p.submit_time) act_days_2021_question from user_info u LEFT JOIN practice_record p ON u.uid = p.uid and year(p.submit_time) = 2021 LEFT JOIN (SELECT a.uid,DATE_FORMAT(a.submit_time,'%Y%m') act_month from practice_record a where a.submit_time is not null GROUP BY a.uid,act_month) m ON u.uid = m.uid where u.level in (6,7)
3、去重统计
select t.uid, count(DISTINCT t.act_month) act_month_total, count(DISTINCT t.act_days_2021) act_days_2021, count(DISTINCT t.act_days_2021_exam) act_days_2021_exam, count(DISTINCT t.act_days_2021_question) act_days_2021_question from ( select u.uid, m.act_month, date(e.start_time) act_days_2021, date(e.start_time) act_days_2021_exam, null act_days_2021_question from user_info u LEFT JOIN exam_record e ON u.uid = e.uid and year(e.start_time) = 2021 LEFT JOIN (SELECT a.uid,DATE_FORMAT(a.start_time,'%Y%m') act_month from exam_record a where a.submit_time is not null GROUP BY a.uid,act_month) m ON u.uid = m.uid where u.level in (6,7) union all select u.uid, m.act_month, date(p.submit_time) act_days_2021, null act_days_2021_exam, date(p.submit_time) act_days_2021_question from user_info u LEFT JOIN practice_record p ON u.uid = p.uid and year(p.submit_time) = 2021 LEFT JOIN (SELECT a.uid,DATE_FORMAT(a.submit_time,'%Y%m') act_month from practice_record a where a.submit_time is not null GROUP BY a.uid,act_month) m ON u.uid = m.uid where u.level in (6,7) ) t GROUP BY t.uid ORDER BY act_month_total DESC,act_days_2021 DESC
投递特斯拉等公司10个岗位 >