题解 | 最差是第几名(二)

with sum_class_grade as (
    select
        grade,
        (select sum(number) from class_grade) as total,
        sum(number) over (order by grade) as sum,
        sum(number) over (order by grade desc) as sum_desc
    from 
        class_grade
)

select
    grade
from 
    sum_class_grade
where
    sum >= round(total / 2 ,0)
and 
    sum_desc >= round(total / 2 ,0)
order by 
    grade