题解 | #未完成率较高的50%用户近三个月答卷情况#
未完成率较高的50%用户近三个月答卷情况
https://www.nowcoder.com/practice/3e598a2dcd854db8b1a3c48e5904fe1c
这个题找了好久的bug，一直不知道问题出在哪里，后来发现自己算的是完成率，在筛选的时候却把它当成了未完成率
select
uid,
date_format(start_time, '%Y%m') as start_month,
count(start_time) as total_cnt,
count(submit_time) as complete_cnt
from (
select
uid,
percent_rank() over (
order by
count(submit_time) / count(*)
) as rank_rk
from
exam_record
join examination_info using(exam_id)
where
tag = 'SQL'
group by
uid
) as t2 left join
(
select
*,
dense_rank() over(
partition by uid
order by
date_format(start_time, '%Y%m') desc
) as rank_r
from
exam_record
where
uid in (
select
uid
from
user_info
where
level in (6, 7)
)
) as t1 using(uid)
where
rank_rk <=0.5
and rank_r <= 3
group by
uid,
date_format(start_time, '%Y%m')
order by
uid,
date_format(start_time, '%Y%m')
投递莉莉丝游戏等公司7个岗位 >
