题解 | #未完成率较高的50%用户近三个月答卷情况#
未完成率较高的50%用户近三个月答卷情况
https://www.nowcoder.com/practice/3e598a2dcd854db8b1a3c48e5904fe1c
select t3.*
from
(
select
uid
,start_month
,sum(total_cnt) total_cnt
,sum(complete_cnt) complete_cnt
from
(
select
uid
,date_format(start_time,'%Y%m') start_month
,count(start_time) total_cnt
,count(submit_time) complete_cnt
,row_number()over(partition by uid order by date_format(start_time,'%Y%m') desc) rn
from exam_record
left join user_info using(uid)
where level in('6','7')
group by uid,date_format(start_time,'%Y%m')
) t2
where t2.rn<=3
group by uid
,start_month
) t3
join
(
select
uid
from
(
select
uid,ntile(2)over(order by rn) nt
from
(
select
uid
,count(submit_time) finish
,count(a.exam_id) al
,row_number()over(order by count(case when submit_time is null then 1 else null end) /count(a.exam_id) desc) rn
from exam_record a
left join examination_info b on a.exam_id=b.exam_id
where tag='SQL'
group by uid
) t
) t1
where nt=1
) t4 using(uid)
order by uid,start_month
按照 t,t1,t2,t3,t4分别写逻辑,注意ntile()over()用法
查看9道真题和解析