题解 | #注册当天就完成了试卷的名单第三页#

with t1 as (
    select uid, register_time
    from user_info
    where job = '算法'
),
t2 as (
    select a.uid, a.exam_id, a.start_time, a.submit_time, a.score, b.tag
    from exam_record as a
    left join examination_info as b
    on a.exam_id = b.exam_id
),
t3 as (
    select t2.uid, t2.exam_id, t2.start_time, t2.submit_time, t2.score, t1.register_time
    from t2
    left join t1
    on t1.uid = t2.uid
    where tag = '算法'
    and t2.uid in (
        select uid
        from t1
    )
),
t4 as (
    # 查找注册当天就完成试卷的记录
    select *
    from t3
    where DATE(submit_time) = DATE(register_time)
),
t5 as (
    select uid, register_time, max(score) as max_score
    from t4
    group by uid
    order by max_score desc
),
t6 as (
    select t5.uid, f.level, t5.register_time, t5.max_score
    from t5
    left join user_info as f
    on t5.uid = f.uid
)
select *
from t6
limit 6, 3;