题解 | 考试分数(五)

with
t1 as(
    select
        id,
        job,
        score,
        dense_rank()over(partition by job order by score desc) as t_rank,
        count(id)over(partition by job) as total
    from
        grade
),
t2 as(
    select
        id,
        job,
        score,
        t_rank
    from
        t1
    where
        floor((total+1)/2)=t_rank or ceiling((total+1)/2)=t_rank
)

select * from t2 order by id