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

with rk_table as (select
grade,
sum(number) over(order by grade) t_rank
from
class_grade),
start_end as (with total_table as (select sum(number) as total from class_grade)
select 
case when total % 2 = 0 then floor(total / 2) else  floor(total / 2) + 1 end start,
floor(total / 2) + 1  end
from total_table)
select min(grade) from rk_table, start_end
where t_rank >= start
union
select min(grade) from rk_table, start_end
where t_rank >= end