#注意sql试卷是限制未完成率排名
#6，7级用户是限制做题记录
#想喊个破题，同时筛选就报错，然后，先筛选出SQL和完成数及做题数的总占比(类似总数 100 A 50 B 20 C30 就分别为总占比50% 20% 和 30%)这个小于50%先筛选出来,总占比函数percent_rank()over(partition by 类别 order by 排序)
#在筛选出level 6,7的条件。做法1.子查询一个个套 2.连接后在子查询 赶脚都一样
#条件筛选出来，要不连接表，加大宽表查近3个月的记录，要不就把筛出的条件作为虚拟表，然后，在连接查去。推荐虚拟表#方式主要简洁，小白个人的看法，请大牛们，多多批评指正。
with tiaojian as (
select
t.uid
from(
select
uid,
percent_rank()over(order by count(score)/count(id) asc) as m
from exam_record
where
exam_id in (select  exam_id from examination_info where 
tag="SQL")
group by uid
) as t
where
t.m<=0.5
and uid in (select uid from user_info where level in (6,7))
)

select
t.uid,
t.month,
t.total,
t.wc
from(
select
ed.uid,
date_format(ed.start_time,"%Y%m") as month,
count(ed.id) as total ,
dense_rank()over(order by date_format(ed.start_time,"%Y%m") desc) as m,
sum(case when ed.score is not null then 1 else 0 end) as wc
from tiaojian t left join exam_record ed on t.uid=ed.uid
group by ed.uid,month
) as t 
where
t.m<=3
order by t.uid,t.month