题解 | #每个6/7级用户活跃情况#

先找出50%的数据，再找出和匹配出6或7级的用户uid，最后利用dense_rank进行排序筛选出最近三个月的数据

SELECT uid,start_month,total_cnt,complete_cnt
from(
SELECT uid, date_format(start_time,'%Y%m')as start_month,
count(date_format(start_time,'%Y%m'))as total_cnt,count(score)as complete_cnt,
dense_rank() over(partition by uid order by date_format(start_time, '%Y%m') desc) as recent_months
from exam_record
where uid in
(
select uid
from user_info
where uid in(select uid from(
select uid,percent_rank() over (order by a.incomplete_rate)as per
from(SELECT uid,count(case when score is null then 1 else null end)as incomplete_cnt,
count(*)as total_cnt,count(case when score is null then 1 else null end)/count(*) as incomplete_rate
from examination_info i right join exam_record r USING(exam_id)
where i.tag="SQL"
group by uid)a)b where b.per>=0.5)
and (`level` =6 or `level`=7))
group by uid,start_month)e
where recent_months<=3
order by uid,start_month asc