nowcoder SQL135（困难）

牛客SQL135（困难）

描述
现有用户信息表user_info（uid用户ID，nick_name昵称, achievement成就值, level等级, job职业方向, register_time注册时间）：

试卷信息表examination_info（exam_id试卷ID, tag试卷类别, difficulty试卷难度, duration考试时长, release_time发布时间）：
试卷作答记录表exam_record（uid用户ID, exam_id试卷ID, start_time开始作答时间, submit_time交卷时间, score得分）：
题目练习记录表practice_record（uid用户ID, question_id题目ID, submit_time提交时间, score得分）：

请统计每个6/7级用户总活跃月份数、2021年活跃天数、2021年试卷作答活跃天数、2021年答题活跃天数，按照总活跃月份数、2021年活跃天数降序排序。
由示例数据结果输出如下：

解释：6/7级用户共有5个，其中1006在202109、202108、202008共3个月活跃过，2021年活跃的日期有20210907、20210804、20210803、20210802共4天，2021年在试卷作答区20210907活跃1天，在题目练习区活跃了3天。

解题思路：

答案格式中的数据来源很复杂，需要每一张表的参与，所以可以将问题拆解成子查询一步步化解最后连接
1.uid from user ,..month_total from exam && practice , ..exam from exam ,..question from practice
而第三列days2021的活跃天数是后两列的和，分析到这里，相信你已经爱上了出题的大佬（FU！！）
回到思路中，问题已经拆解完毕，现在我们需要将各个列的结果查询出来，然后通过‘精湛’的编程技术将他们合在一起

select uid  -- 此查询的生成的中间表为连接的左（主）表，将level 6~7的uid返回
from user_info
where level in (6,7)

 -- 此查询筛选出试卷作答2021年的活跃天数 ，返回uid作为主查询连接条件，dense_rank 此处用于计数
select distinct uid,
       dense_rank() over (partition by uid order by date (start_time)) rak2 -- 不用count是为了避免日期重复而重复计数
from exam_record
where year(start_time) = 2021

 -- 此查询筛选出练习2021年的活跃天数 ，返回uid作为主查询连接条件，dense_rank 此处用于计数
select distinct uid,
                dense_rank() over (partition by uid order by date (submit_time)) rak3
from practice_record
where year(submit_time) = 2021

-- 此查询将返回考试活跃的月份和uid，dense_rank还是计数功能（这样会有冗余的uid，不是很好）  暂时没有更好的解
 select distinct uid,
#       date_format (start_time , '%Y-%m') ex, -- 此条数据无实际意义
        dense_rank() over (partition by uid order by date_format (start_time , '%Y-%m')) rak4
from exam_record

 -- 此查询将返回答题活跃的月份和uid，dense_rank还是计数功能（这样会有冗余的uid，不是很好）  暂时没有更好的解
 select distinct uid,
#       date_format (submit_time , '%Y-%m') re,
        dense_rank() over (partition by uid order by date_format (submit_time , '%Y-%m')) rak5
from practice_record

用CTE包装起来，最终结果出炉

with t1 as (        -- 此查询筛选出试卷作答2021年的活跃天数 ，返回uid作为主查询连接条件，dense_rank 此处用于计数，最后取max或者倒序取第一都行（我用max）
                            select distinct uid,
                                                dense_rank() over (partition by uid order by date (start_time)) rak2 -- 不用count是为了避免日期重复而重复计数
                            from exam_record
                            where year(start_time) = 2021
                            ),
        t2 as (        -- 此查询同t1 查答题活跃天数
                            select distinct uid,
                            dense_rank() over (partition by uid order by date (submit_time)) rak3
                            from practice_record
                            where year(submit_time) = 2021
                            ),
        t3 as (         -- 此查询的生成的中间表为连接的左（主）表，将level 6~7的uid返回
                            select uid
                            from user_info
                            where level in (6,7)
                            ),
        t4 as (         -- 此查询将返回考试活跃的月份和uid，dense_rank还是计数功能（这样会有冗余的uid，不是很好）  暂时没有更好的解
                            select distinct uid,
#                                             date_format (start_time , '%Y-%m') ex, -- 此条数据无实际意义
                                            dense_rank() over (partition by uid order by date_format (start_time , '%Y-%m')) rak4
                            from exam_record
                            ),
        t5 as (         -- 此查询将返回答题活跃的月份和uid，dense_rank还是计数功能（这样会有冗余的uid，不是很好）  暂时没有更好的解
                            select distinct uid,
#                                             date_format (submit_time , '%Y-%m') re,
                                            dense_rank() over (partition by uid order by date_format (submit_time , '%Y-%m')) rak5
                            from practice_record
                            )
select t3.uid,   -- 将所有的子查询连接，注意左表应为user中的uid，连接条件中的uid不能乱用，只能on t3.uid
       coalesce(max(rak4) , 0) + coalesce(max(rak5) , 0) as act_month_total	,  -- 将null转换成0计算
       coalesce(max(rak2) , 0)+coalesce(max(t2.rak3),0) as act_days_2021,
        coalesce(max(rak2) , 0) act_days_2021_exam,
        coalesce(max(t2.rak3),0) act_days_2021_question
from t3 left join t1 on t3.uid = t1.uid -- 只能on t3.uid
   left  join t2 on  t3.uid = t2.uid  -- 只能on t3.uid
   left  join t4 on  t3.uid = t4.uid  -- 只能on t3.uid
   left  join t5 on  t3.uid = t5.uid  -- 只能on t3.uid
group by t3.uid      -- 分组去掉重复的id（来源于t4,t5中，因为日期要考虑不同年份，月份，再排名计数）
order by act_days_2021 desc ;

t1,t2好理解，t4,t5主要是因为之前的两个查询要筛选2021的，所以要多写一个查询，将日期改成我们希望的年+月，再去基于uid分区对月份进行排名（实际上就是计数），最终在主查询中max求和即可得到解

总结

总结：（FU！！）
本题解没有上在线编程跑过，所以不知道答案有什么问题，仅以此作为学习记录，也期望大佬分享更好的解咱们一块学习！