Find Cutoff Score for Each School - Practice Coding Problems

Find Cutoff Score for Each School - Problem

Database Medium

🏫 School Admission Cutoff Calculator

Every year, schools face a crucial challenge: setting the perfect minimum score requirement for student applications. Set it too high, and they miss out on qualified students. Set it too low, and they get overwhelmed with applications they can't accommodate!

You're given two tables:

Schools table: Contains each school's school_id and maximum capacity (number of students they can accept)
Exam table: Contains score values and student_count (number of students who achieved at least that score)

Each school wants to:

📈 Maximize the number of potential applicants
🎯 Ensure they can accept everyone who meets the requirement (even if all qualified students apply)
📊 Use only scores that exist in the Exam table
⬇️ Choose the smallest score if multiple scores work

Goal: Find the optimal minimum score requirement for each school. Return -1 if no suitable score exists.

Input & Output

example_1.sql — Basic Case

$ Input: Schools: [[1,2],[2,3]] Exam: [[500,4],[600,3],[700,1]]

› Output: [[1,700],[2,600]]

💡 Note: School 1 (capacity 2): Can accept scores 700(1 student) or 600(3 students). Since 3>2, only 700 works. School 2 (capacity 3): Can accept 700(1 student) or 600(3 students). Choose 600 for maximum students.

example_2.sql — No Valid Score

$ Input: Schools: [[1,1]] Exam: [[500,5],[600,3],[700,2]]

› Output: [[1,-1]]

💡 Note: School 1 has capacity 1, but all exam scores have student counts ≥ 2. No valid cutoff score exists, so return -1.

example_3.sql — Tie Breaking

$ Input: Schools: [[1,3],[2,5]] Exam: [[400,3],[500,3],[600,1]]

› Output: [[1,400],[2,400]]

💡 Note: Both schools can accept scores 400 and 500 (both have 3 students). Since both give same max students, choose the smaller score (400).

Constraints

1 ≤ number of schools ≤ 10³
1 ≤ school capacity ≤ 10⁶
1 ≤ number of exam records ≤ 10³
1 ≤ score ≤ 10⁶
0 ≤ student_count ≤ 10⁶
Important: Higher scores always have ≤ student counts than lower scores

Visualization

Tap to expand

Understanding the Visualization

Analyze Demand

Cross-reference school capacities with student score distributions

Find Sweet Spot

Identify scores where student count ≤ school capacity

Maximize Efficiency

Choose the score that accepts the most students possible

Optimize Selection

When tied, pick the lowest score to be more inclusive

Key Takeaway

🎯 Key Insight: Find the highest student count that fits within capacity, then pick the lowest score that achieves it - this maximizes both accessibility and enrollment!

Asked in

G Google 35 a Amazon 28 f Meta 22 ⊞ Microsoft 18

The optimal solution uses SQL window functions to efficiently rank valid score-school combinations. By ordering by student_count DESC, score ASC and selecting ROW_NUMBER() = 1, we get the maximum students with the minimum score in a single pass. This approach is much more efficient than nested subqueries and handles all edge cases elegantly.

Common Approaches

Approach	Time	Space	Notes
✓ Brute Force (Nested Subqueries)	O(S × E²)	O(S + E)	Check each school against every possible score with nested queries
Window Functions (Optimal)	O(S × E × log(E))	O(S × E)	Use window functions to efficiently find the best cutoff score in one pass

Brute Force (Nested Subqueries) — Algorithm Steps

For each school, scan all available scores
For each score, check if student_count ≤ school capacity
Among valid scores, find the one with maximum student_count
If multiple scores have same max student_count, pick the smallest score
Return -1 if no valid scores exist

Visualization

Tap to expand

Step-by-Step Walkthrough

Cross Join

Create all combinations of schools and exam scores

Filter Valid

Keep only combinations where student_count ≤ capacity

Find Maximum

For each school, find the maximum valid student_count

Select Minimum Score

Among scores with max student_count, pick the smallest

Code -

solution.c — C

// Brute force SQL solution
SELECT 
    s.school_id,
    CASE 
        WHEN COUNT(valid_scores.score) = 0 THEN -1
        ELSE MIN(valid_scores.score)
    END as score
FROM Schools s
LEFT JOIN (
    SELECT 
        s2.school_id,
        e.score
    FROM Schools s2
    CROSS JOIN Exam e
    WHERE e.student_count <= s2.capacity
        AND e.student_count = (
            SELECT MAX(e2.student_count)
            FROM Exam e2 
            WHERE e2.student_count <= s2.capacity
        )
) valid_scores ON s.school_id = valid_scores.school_id
GROUP BY s.school_id

Time & Space Complexity

Time Complexity

⏱️

O(S × E²)

S schools × E exam scores, with nested subqueries for each combination

✓ Linear Growth

Space Complexity

O(S + E)

Space for storing school and exam table data

✓ Linear Space

67.2K Views

Medium Frequency

~25 min Avg. Time

1.8K Likes

Ln 1, Col 1

Smart Actions

💡 Explanation

AI Ready

💡 Suggestion Tab to accept Esc to dismiss

// Output will appear here after running code

Code Editor Closed

Click the red button to reopen

🏫 School Admission Cutoff Calculator

Input & Output

Constraints

Visualization

Related Problems

Common Approaches

Brute Force (Nested Subqueries) — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler