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

结构化思维：
①选出近三个月各uid答题情况，用dense_rank()over()；
②选出未完成率前50%的uid，用percent()over();
③表连接选出6，7级的uid；
④通过子查询把uid限定在之前选好的范围内

#选出近3个月各uid内各个月的答题情况
select uid, start_month, count(uid), count(score) 
from
(select uid, score, date_format(start_time,'%Y%m') start_month, 
dense_rank()over(partition by uid order by date_format(start_time,'%Y%m') desc) as r
from exam_record) as aa
where r<=3 and 
#以下选出的uid作为子查询
uid in 
#选出6，7级的uid
(select uid from
#选出未完成率前50%的uid
(select uid from
(select uid, percent_rank()over(order by 1-count(score)/count(uid)) as rr
from exam_record er 
left join examination_info using(exam_id)
where tag='SQL'
group by uid) as aa
where rr>=0.5) as bb

left join user_info using(uid)
where level in (6,7)
)
group by uid, start_month
ORDER BY uid, start_month