题解 | 考试分数(五)

with
tb1 as(
    select id, job, score, row_number() over(
        partition by job
        order by score desc
    ) as t_rank
    from grade
),
tb2 as(
    select *, avg(t_rank) over(
        partition by job
    ) as avg_t_rank
    from tb1
),
tb3 as(
    select * from tb2
    where t_rank - avg_t_rank = 0 or abs(t_rank - avg_t_rank) = 0.5
)
select id, job, score, t_rank from tb3
order by id