题解 | #每个6/7级用户活跃情况#
每个6/7级用户活跃情况
https://www.nowcoder.com/practice/a32c7c8590324c96950417c57fa6ecd1
题目拆解：
一、筛选条件：
（1）用户等级：6或者7 where level in (6,7） #相关表：user_info (ui)
在user_info 表中查找出满足等级要求的用户id，即uid，以此为基础进行后续计算，因此我选择了将该表作为主表，左连接其他表（因可能存在符合等级条件的大佬不活跃的情况）
二、求解：
（1）各用户：根据用户分组 group by uid
1.1）总的活跃月份数 ： count(distinct date_format(a.submit_time,'%Y%m')) act_month_total #此处的a表说明见下，这里的submit_time包含了用户作答试卷和答题的全部提交时间
活跃是指有交卷行为，即submit_time不为空
同一个用户的submit_time 包括试卷作答记录表exam_record (er) 和题目练习记录表practice_record (pr)中的submit_time，
注：因用户可能在同一月同时练习了试卷和题目，统计该用户活跃月份时，计数1
举例：用户1001，在2021年9月1日，作答试卷，且答题；
在2021年8月1日，作答试卷，未答题；
在2021年7月1日，未作答试卷，答题。
用户总活跃月份数计为3：
因作答试卷活跃月份数为2，即8月和9月
因答题活跃月份数为2，即7月和9月
总活跃月份数应为7，8，9三个月，即因作答试卷活跃月份数和答题活跃月份数去重后的结果
要怎么样同时统计同一用户的试卷作答时间和答题时间，我想的就将试卷作答记录表exam_record和题目练习记录表practice_record中的submit_time合并起来，也就是用union all,并将合并提取后的数据作为新表a，并连接上ui表
(select uid,submit_time from exam_record
union all
select uid,submit_time from practice_record) as a
逻辑同月份，但多了一个条件，即年份为2021年
count(distinct if(year(a.submit_time)=2021,date_format(a.submit_time,'%Y%m%d'),null)) act_days_2021
1.3）2021年试卷作答活跃天数和1.4）2021年答题活跃天数
到这里要开始按照试卷作答和答题区分开来统计天数，我想的是在前面连表的基础上左连接试卷作答记录表er和答题表pr，分别计算即可
2021年试卷作答活跃天数： count(distinct if(year(er.submit_time)=2021,date_format(er.submit_time,'%Y%m%d'),null)) act_days_2021_exam
2021年答题活跃天数 count(distinct if(year(pr.submit_time)=2021,date_format(pr.submit_time,'%Y%m%d'),null)) act_days_2021_question
三、排序：
order by act_month_total desc,act_days_2021 desc;
四、完整代码：
select ui.uid,
count(distinct date_format(a.submit_time,'%Y%m')) act_month_total,
count(distinct if(year(a.submit_time)=2021,date_format(a.submit_time,'%Y%m%d'),null)) act_days_2021,
count(distinct if(year(er.submit_time)=2021,date_format(er.submit_time,'%Y%m%d'),null)) act_days_2021_exam,
count(distinct if(year(pr.submit_time)=2021,date_format(pr.submit_time,'%Y%m%d'),null)) act_days_2021_question
from user_info ui
left join
(select uid,submit_time from exam_record
union all
select uid,submit_time from practice_record) as a
on ui.uid=a.uid
left join exam_record er on ui.uid=er.uid
left join practice_record pr on ui.uid=pr.uid
where level in (6,7)
group by uid
order by act_month_total desc,act_days_2021 desc;
五、运行结果：
六、其他
看了下评论区的大佬们的解答思路，get到了在合并表的时候，可以多加一列，区分出试卷作答记录和答题记录，减少表连接，我也试试
查看8道真题和解析
投递大疆等公司10个岗位
