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

with zb as
(select er.uid,tag,level,date_format(start_time,'%Y%m') start_month,submit_time,dense_rank() over
(partition by uid order by date_format(start_time,'%Y%m') desc) rk
from user_info ui,examination_info ei,exam_record er
where ui.uid = er.uid and ei.exam_id = er.exam_id)

select uid,start_month,count(start_month) total_cnt,count(submit_time) complete_cnt
from zb
where rk <= 3 and level in (6,7) and  uid in
(select uid from
(select *,b/a avg_1,percent_rank() over(order by b/a) rk1 from
(select uid,count(start_month) a ,count(start_month)-count(submit_time) b
from zb
where tag = "SQL"
group by uid) z1) z2 where rk1 >=0.5)
group by uid,start_month
order by uid,start_month;