题解 | #未完成率较高的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;

全部评论

相关推荐

当初高考报计算机真是造大孽了啊!卷的飞起!哪都是计算机的人,考研,考公,找工作全他奶的计算机的人,太难了。国企也是。关键一届比一届卷,造大孽了!
_Lyrics_:因为计算机,没有体验到快乐的大学研究生时光,好不容易修完课程就要出去实习,看着别人专业可以一起搓麻将,游山玩水,而我却要自己一个人住在北上不到十平米的出租屋,每天两点一线
点赞 评论 收藏
分享
qq乃乃好喝到咩噗茶:院校后面加上211标签,放大加粗,招呼语也写上211
点赞 评论 收藏
分享
点赞 评论 收藏
分享
评论
点赞
收藏
分享

创作者周榜

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