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

# SQL试卷上未完成率较高的50%用户中的6级和7级用户 意思就是PERCENT_RANK<=0.5的6级和7级用户
with A as (
select uid from 
(select uid,count(submit_time)/count(start_time)incomplete_rate
,PERCENT_RANK()over(ORDER BY COUNT(submit_time)/COUNT(start_time))rate_rk
from exam_record
where exam_id in (select exam_id from examination_info where tag = 'SQL')
group by 1)a
where rate_rk <= 0.5
and uid in (select uid from user_info where level in (6,7))
)
# 在有试卷作答记录的近三个月中，每个月的答卷数目和完成数目
# 找最近三个月
,B as (
select uid,start_month
from 
(select uid
,date_format(start_time,'%Y%m')start_month
,dense_rank()over(partition by uid order by date_format(start_time,'%Y%m') desc)dr
from exam_record
where uid in (select uid from A))b
where dr<=3)
# 再联表筛选
select uid,start_month,count(start_month)total_cnt,count(submit_time)complete_cnt
from (
select uid,date_format(start_time,'%Y%m')start_month
,date_format(submit_time,'%Y%m')submit_time
from exam_record
)a
where (uid,start_month) in (select * from B)
group by 1,2
order by 1,2