SQL Interview Questions – Find Cutoff Score for Each School

Problem Description –

Every year, each school announces a minimum score requirement that a student needs to apply to it. The school chooses the minimum score requirement based on the exam results of all the students:

1. They want to ensure that even if every student meeting the requirement applies, the school can accept everyone.
2. They also want to maximize the possible number of students that can apply.
3. They must use a score that is in the Exam table.

Write an SQL query to report the minimum score requirement for each school. If there are multiple score values satisfying the above conditions, choose the smallest one. If the input data is not enough to determine the score, report -1.

Return the result table in any order.

The query result format is in the following example.

Problem Link – Cutoff Score

Difficulty Level – Medium

Solution –

select 
    school_id, 
    ifnull(min(score),-1) as score
from Schools left join Exam
on capacity >= student_count
group by school_id