Mysql 解法, 执行用时: 37ms, 内存消耗: 6400KB, 提交时间: 2021-12-06

# 第一步，先找出未完成率前50%高的用户ID，注意这里需要的sql试卷
with rote_tab as 
(select t.uid,t.f_rote,row_number()over(order by t.f_rote desc,uid) as rank2
,count(t.uid)over(partition by t.tag)as cnt
from (select er.uid,ef.tag,(sum(if(submit_time is null,1,0))/count(start_time)) as f_rote
from exam_record er left join examination_info ef on ef.exam_id=er.exam_id where tag='SQL' group by uid ) t)

select  #第四步，分用户和月份进行数据统计；同时需要注意，统计的试卷数是所有类型的，不是之前仅有SQL类型
    uid
    ,start_month
    ,count(start_time) as total_cnt
    ,count(submit_time) as complete_cnt
from 
(
select # 第三步，利用窗口函数对每个用户的月份进行降序排序，以便找出最近的三个月；
    uid
    ,start_time
    ,submit_time
    ,date_format(start_time,'%Y%m') as start_month
    ,dense_rank()over(partition by uid order by date_format(start_time,'%Y%m') desc) as rank3
from exam_record 
where uid in 
    (select distinct er.uid
    from exam_record er left join user_info uf on uf.uid=er.uid
    where er.uid in (select uid from rote_tab where rank2<=round(cnt/2,0))
    and uf.level in (6,7))  # 第二步，进一步找出满足等级为6或7的用户ID
) t2
where rank3<=3
group by uid,start_month
order by uid,start_month

Mysql 解法, 执行用时: 37ms, 内存消耗: 6404KB, 提交时间: 2021-12-16

with t as(SELECT uid from(
    select uid,row_number() over(order by uncomplete_rate desc)as ranking,
    count(1) over() as num
    from(
    select uid,((count(uid) - count(submit_time))/count(uid)) as uncomplete_rate
    from exam_record join examination_info using(exam_id) where tag ="SQL"
    group by uid)a)b
    join user_info using(uid) 
    where(ranking<=(num+1)/2) and level >=6
)
    
select uid,dtime as month,count(uid),count(score) as complete_cnt from (
    select uid,
    DATE_FORMAT(start_time,"%Y%m") as dtime,
    DENSE_RANK()over(partition by uid order by DATE_FORMAT(start_time,"%y%m") desc) as ranking,
    score from exam_record)a
inner join t using(uid)
where ranking <=3
group by a.uid,month
order by a.uid,month

Mysql 解法, 执行用时: 37ms, 内存消耗: 6408KB, 提交时间: 2021-12-27

with t as
(
    select uid
    from
    (select *,row_number()over(order by incomplete_rate desc) as rn,count(1)over() as num
    from
    (select uid,count(1)-count(submit_time) as incomplete_cnt,count(1) as total_cnt,(count(1)-count(submit_time))/count(1) as incomplete_rate
    from exam_record
    where exam_id in (select exam_id from examination_info where tag = 'SQL')
    group by uid) t1) t2
    where rn<=(num+1)/2 and uid in (select uid from user_info where level in ('6','7') )
)
select uid,dtime as start_month,count(1) as total_cnt,count(score) as complete_cnt
from
(select uid,DATE_FORMAT(start_time,"%Y%m") as dtime,DENSE_RANK()over(partition by uid order by DATE_FORMAT(start_time,"%y%m") desc) as rn,score
from exam_record
where uid in (select uid from t)) t1
where rn<=3
group by uid,start_month
order by uid,start_month

Mysql 解法, 执行用时: 37ms, 内存消耗: 6420KB, 提交时间: 2022-01-22

select uid,
       date_format(start_time, '%Y%m') as start_month,
       count(1) as total_cnt,
       count(submit_time) as complete_cnt
from
(select *, dense_rank() over (partition by uid order by date_format(start_time, '%Y%m') desc) as rn
from exam_record
) tb
join
(select uid
from
(select uid,
       percent_rank() over (order by incomp_rate desc) as rnk
from
(select uid, 1-count(submit_time) / count(1) as incomp_rate
from exam_record
join examination_info using (exam_id)
where tag = 'SQL'
group by uid) t ) t1
join user_info using (uid)
where rnk <= 0.5 and level >= 6 ) t2
 using (uid)
 where rn <=3
 group by uid, date_format(start_time, '%Y%m')
 order by uid, start_month

Mysql 解法, 执行用时: 37ms, 内存消耗: 6420KB, 提交时间: 2021-12-12

SELECT uid, DATE_FORMAT(start_time,'%Y%m') start_month, COUNT(start_time), COUNT(submit_time) 
FROM (SELECT uid, start_time, submit_time,
      dense_rank() over (PARTITION BY uid ORDER BY DATE_FORMAT(start_time,'%Y%m') DESC) rk
      FROM exam_record
      WHERE uid IN (SELECT uid FROM
                    (SELECT uid, row_number() over (ORDER BY count(submit_time)/count(start_time)) rnrk
                      FROM exam_record
                      WHERE exam_id IN (SELECT exam_id FROM examination_info WHERE tag = 'SQL')
                      GROUP BY uid) t2
                    WHERE t2.rnrk<=ROUND((SELECT COUNT(DISTINCT uid) FROM exam_record)/2,0)
                   )
      AND uid IN (SELECT uid FROM user_info WHERE level=7 OR level =6)
     ) t3
WHERE rk<=3
GROUP BY uid, start_month
ORDER BY uid, start_month