题解 | #未完成率较高的50%用户近三个月答卷情况#

未完成率较高的50%用户近三个月答卷情况

https://www.nowcoder.com/practice/3e598a2dcd854db8b1a3c48e5904fe1c

select uid,date_format(start_time,'%Y%m') start_month,count(ranking) total_cnt,count(ifnull(submit_time,null)) complete_cnt from
 (
 select b.uid,start_time,submit_time,dense_rank()over(partition by b.uid order by date_format(start_time,'%Y%m') desc) ranking
from exam_record a left join user_info b on a.uid = b.uid
where b.uid in (
 select uid from (
select c.uid,(count(*)-count(ifnull(submit_time,null)))/count(*) incomplete_rate,
percent_rank() over (order by avg(case when submit_time is null then 1 else 0 end) desc) pr 
from examination_info a,exam_record b,user_info c 
where a.exam_id = b.exam_id and b.uid = c.uid
and tag = 'SQL'
group by c.uid
) d
where pr <=0.5
 ) 
 and (level = '6' or level = '7')
 ) e 
where ranking <= 3
group by uid,date_format(start_time,'%Y%m')
order by uid,start_month;

全部评论

相关推荐

05-05 21:45
已编辑
广州大学 Java
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

更多
牛客网
牛客网在线编程
牛客网题解
牛客企业服务