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

with sql_id as
(select *from (
    select 
        a.*,
        @row_num:=@row_num+1 as row_num 
    from 
        (
     select uid,incomplete_rate,
     row_number() over(order by incomplete_rate desc) as incomplete_rank from
        (select uid,
        count(submit_time) as complete_cnt,
        count(start_time) as total_cnt,
        1-count(submit_time)/count(start_time) as incomplete_rate
        from
        exam_record er
        left join examination_info ei
        on er.exam_id=ei.exam_id
        where ei.tag='SQL'
        group by uid) a
     order by incomplete_rate desc
 
)
 a , (select @row_num:=0) b  
    order by 
        incomplete_rank
) base
where 
    base.row_num <= ceiling(@row_num*0.5)
)

select uid,start_month,
count(start_month) as total_cnt,
count(submit_time) as complete_cnt 
from
(select er.uid,er.exam_id,submit_time,SUBSTR(DATE_FORMAT(start_time,'%Y%m%d'),1,6) as start_month,
DENSE_RANK() OVER(PARTITION BY uid ORDER BY YEAR(start_time) desc,MONTH(start_time) DESC) as month_rank
from exam_record er
left join user_info ui
on er.uid=ui.uid
where ui.level=6 or ui.level=7) a
where month_rank<=3 and uid IN (select uid from sql_id)
group by uid,start_month
order by uid,start_month