题解 | #未完成率较高的50%用户近三个月答卷情况#
未完成率较高的50%用户近三个月答卷情况
https://www.nowcoder.com/practice/3e598a2dcd854db8b1a3c48e5904fe1c
select
a.uid,
a.start_month,
count(start_month) as total_cnt,
count(score) as complete_cnt
from
(
select
a.uid,
date_format(er.start_time, '%Y%m') as start_month,
er.score,
dense_rank() over(
partition by a.uid
order by
date_format(er.start_time, '%Y%m') desc
) as ranking2
from
(
select
*
from
(
select
er.uid,
percent_rank() over(
partition by ei.tag
order by
1 - count(er.score) / count(er.start_time)
) as ranking
from
examination_info ei
left join exam_record er on ei.exam_id = er.exam_id
where
ei.tag = 'SQL'
group by
er.uid
) a
where
a.uid in (
select
ui.uid
from
user_info ui
where
ui.level in('6', '7')
)
and ranking >= 0.5
) a
left join exam_record er on er.uid = a.uid
) a
where
ranking2 <= 3
group by
uid,
start_month
order by
a.uid asc,
a.start_month asc
解释几个关键点：
1、percent_rank——以试卷'tag'开窗，用未完成率'1-完成率'排序
percent_rank() over( partition by ei.tag order by 1 - count(er.score) / count(er.start_time) ) as ranking
2、求最近有过答题的三个月，因为每位用户每个月可能有n次答题，所以使用dense_rank（通俗点儿讲就是dense_rank密集排序，遇到多个相同月份时会出现1111122223344444...，外面套一层取rank<=3即可取出该用户近三月内所有答题记录）
dense_rank() over( partition by a.uid order by date_format(er.start_time, '%Y%m') desc )
深信服公司福利 832人发布