Problem

Table: Schools

+-------------+------+
| Column Name | Type |
+-------------+------+
| school_id   | int  |
| capacity    | int  |
+-------------+------+
school_id is the column with unique values for this table.
This table contains information about the capacity of some schools. The capacity is the maximum number of students the school can accept.

Table: Exam

+---------------+------+
| Column Name   | Type |
+---------------+------+
| score         | int  |
| student_count | int  |
+---------------+------+
score is the column with unique values for this table.
Each row in this table indicates that there are student_count students that got at least score points in the exam.
The data in this table will be logically correct, meaning a row recording a higher score will have the same or smaller student_count compared to a row recording a lower score. More formally, for every two rows i and j in the table, if scorei > scorej then student_counti <= student_countj.

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 a solution 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 result format is in the following example.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34

Input:
Schools table:
+-----------+----------+
| school_id | capacity |
+-----------+----------+
| 11        | 151      |
| 5         | 48       |
| 9         | 9        |
| 10        | 99       |
+-----------+----------+
Exam table:
+-------+---------------+
| score | student_count |
+-------+---------------+
| 975   | 10            |
| 966   | 60            |
| 844   | 76            |
| 749   | 76            |
| 744   | 100           |
+-------+---------------+
Output:
+-----------+-------+
| school_id | score |
+-----------+-------+
| 5         | 975   |
| 9         | -1    |
| 10        | 749   |
| 11        | 744   |
+-----------+-------+
Explanation: 
- School 5: The school's capacity is 48. Choosing 975 as the min score requirement, the school will get at most 10 applications, which is within capacity.
- School 10: The school's capacity is 99. Choosing 844 or 749 as the min score requirement, the school will get at most 76 applications, which is within capacity. We choose the smallest of them, which is 749.
- School 11: The school's capacity is 151. Choosing 744 as the min score requirement, the school will get at most 100 applications, which is within capacity.
- School 9: The data given is not enough to determine the min score requirement. Choosing 975 as the min score, the school may get 10 requests while its capacity is 9. We do not have information about higher scores, hence we report -1.

Solution

Method 1 – Join and Filter with Aggregation

Intuition

For each school, we want the highest possible number of applicants (student_count) that does not exceed the school’s capacity, and the smallest score that achieves this. If no such score exists, return -1.

Approach

  • For MySQL/PostgreSQL:
    1. Join Schools and Exam on all rows (cross join).
    2. For each school, filter exam scores where student_count <= capacity.
    3. For each school, select the smallest score among those with the largest possible student_count (but not exceeding capacity).
    4. If no such score exists, return -1 for that school.
  • For Python (Pandas):
    1. For each school, filter exam rows where student_count <= capacity.
    2. If no such row exists, assign -1.
    3. Otherwise, among the rows with the largest student_count, pick the smallest score.

Code

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

SELECT s.school_id,
       COALESCE(
         (
           SELECT MIN(e.score)
           FROM Exam e
           WHERE e.student_count = (
             SELECT MAX(student_count)
             FROM Exam
             WHERE student_count <= s.capacity
           ) AND e.student_count <= s.capacity
         ), -1) AS score
FROM Schools s;
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

class Solution:
    def find_cutoff_score(self, schools, exam):
        ans = []
        for _, row in schools.iterrows():
            cap = row['capacity']
            eligible = exam[exam['student_count'] <= cap]
            if eligible.empty:
                ans.append({'school_id': row['school_id'], 'score': -1})
            else:
                max_count = eligible['student_count'].max()
                min_score = eligible[eligible['student_count'] == max_count]['score'].min()
                ans.append({'school_id': row['school_id'], 'score': min_score})
        return pd.DataFrame(ans)

Complexity

  • ⏰ Time complexity: O(nm), where n is the number of schools and m is the number of exam scores, due to the cross-checking for each school.
  • 🧺 Space complexity: O(n), for storing the result for each school.