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

select t1.uid,t1.month_d,count(*)as total_cnt,count(t1.submit_time) as complete_cnt from

    -- 先求出未完成率的排名

    (select uid,count(submit_time is null or null)/count(start_time) as num,

    PERCENT_RANK() over(order by count(submit_time is null or null)/count(start_time) ) as ranking

    from exam_record left join examination_info using(exam_id)

    where tag = 'SQL'

    group by uid)t

inner join (

    -- 再求出近三个月的练习记录

    select uid,date_format(start_time,'%Y%m') as month_d ,submit_time,exam_id

    ,dense_rank() over( partition by uid order by date_format(start_time,'%Y%m')  desc ) as ranking

    from exam_record

 left join user_info using(uid) where level in(6,7)

    )t1

USING (uid)

where t1.ranking <=3 and t.ranking >=0.5 -- 使用限制找到符合条件的记录

group by t1.uid,t1.month_d

order by t1.uid,t1.month_d

注意求的是所有的答题次数和完成的次数。

注意sql试卷是限制未完成率排名
6，7级用户是限制做题记录

细节较多，写起来比较费劲。在占位百分比计算时最好在本地测试一下，分开运行看一下结果。