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

WITH 
final_ranks AS (
    SELECT 
        grade,
        total_num,
        end_rank,
        COALESCE(LAG(end_rank) OVER(ORDER BY grade ASC), 0) + 1 AS start_rank
    FROM (
        SELECT 
            grade,
            SUM(number) OVER(ORDER BY grade ASC) AS end_rank,
            SUM(number) OVER() AS total_num
        FROM class_grade
    ) t
)
SELECT 
    grade
FROM final_ranks
WHERE 
    CASE 
        WHEN total_num % 2 = 1 THEN 
            (total_num + 1) / 2 BETWEEN start_rank AND end_rank
        ELSE 
            (total_num / 2 BETWEEN start_rank AND end_rank)
            OR 
            (total_num / 2 + 1 BETWEEN start_rank AND end_rank)
    END;