题解 | #未完成率较高的50%用户近三个月答卷情况#
未完成率较高的50%用户近三个月答卷情况
https://www.nowcoder.com/practice/3e598a2dcd854db8b1a3c48e5904fe1c
分析题目
需要使用的表有:exam_record(主要使用的表), user_info
限制条件:
1. 统计未完成率较高的前50%用户
2. 6或7级用户(左连接user_info)
3. 在有试卷作答记录的近三个月中(指exam_record中记录的最近的三个月的数据 可通过dense_rank(start_month) 解决)
输出:每个月的答卷数目和完成数据
排序方式:按照uid 月份升序排序
代码:
with temp as( select t2.uid, row_number() over (order by t2.rate asc) rn from( select t1.uid, t1.complete_cnt/t1.total_cnt rate from( select uid, count(start_time) total_cnt, count(score) complete_cnt from exam_record e group by uid ) t1 )t2 ), # 创建cet表temp解决条件1 temp2 as( select e.*, date_format(e.start_time,'%Y%m') st, dense_rank() over (order by date_format(e.start_time,'%Y%m') desc) dr from exam_record e ) # 创建cet表temp2解决条件3 select temp2.uid, temp2.st start_month, count(temp2.start_time) total_cnt, count(temp2.score) complete_cnt from temp2 left join user_info u on temp2.uid = u.uid where u.level in (6,7) # 左连接解决条件2 and temp2.uid in (select t3.uid from temp t3 where rn <= (select ceil(max(te.rn)*0.5) from temp te)) # 解决条件1 and temp2.dr <= 3 # 解决条件3 group by temp2.uid, temp2.st order by temp2.uid, temp2.st;