Watch 4 video solutions for Find the Quiet Students in All Exams, a hard level problem involving Database. This walkthrough by Ankit Bansal has 16,856 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
Table: Student
+---------------------+---------+ | Column Name | Type | +---------------------+---------+ | student_id | int | | student_name | varchar | +---------------------+---------+ student_id is the primary key (column with unique values) for this table. student_name is the name of the student.
Table: Exam
+---------------+---------+ | Column Name | Type | +---------------+---------+ | exam_id | int | | student_id | int | | score | int | +---------------+---------+ (exam_id, student_id) is the primary key (combination of columns with unique values) for this table. Each row of this table indicates that the student with student_id had a score points in the exam with id exam_id.
A quiet student is the one who took at least one exam and did not score the highest or the lowest score.
Write a solution to report the students (student_id, student_name) being quiet in all exams. Do not return the student who has never taken any exam.
Return the result table ordered by student_id.
The result format is in the following example.
Example 1:
Input: Student table: +-------------+---------------+ | student_id | student_name | +-------------+---------------+ | 1 | Daniel | | 2 | Jade | | 3 | Stella | | 4 | Jonathan | | 5 | Will | +-------------+---------------+ Exam table: +------------+--------------+-----------+ | exam_id | student_id | score | +------------+--------------+-----------+ | 10 | 1 | 70 | | 10 | 2 | 80 | | 10 | 3 | 90 | | 20 | 1 | 80 | | 30 | 1 | 70 | | 30 | 3 | 80 | | 30 | 4 | 90 | | 40 | 1 | 60 | | 40 | 2 | 70 | | 40 | 4 | 80 | +------------+--------------+-----------+ Output: +-------------+---------------+ | student_id | student_name | +-------------+---------------+ | 2 | Jade | +-------------+---------------+ Explanation: For exam 1: Student 1 and 3 hold the lowest and high scores respectively. For exam 2: Student 1 hold both highest and lowest score. For exam 3 and 4: Student 1 and 4 hold the lowest and high scores respectively. Student 2 and 5 have never got the highest or lowest in any of the exams. Since student 5 is not taking any exam, he is excluded from the result. So, we only return the information of Student 2.
Problem Overview: You have exam scores for multiple students across different exams. A student is considered quiet if they never receive the highest score or the lowest score in any exam they participate in. The task is to return all such students with their IDs and names.
Approach 1: Window Ranking with RANK() + Group By (O(n log n) time, O(n) space)
This approach uses SQL window functions to identify the highest and lowest scorers per exam. Apply RANK() twice using PARTITION BY exam_id: once ordering scores ascending to detect the lowest score, and once ordering descending to detect the highest score. Any row with rank = 1 in either ranking represents an extreme score for that exam.
After computing the rankings, group rows by student_id. A student is quiet only if their minimum rank in both directions is greater than 1 across all their exams. This guarantees they never appear as the top or bottom scorer in any exam they took. Finally join with the student table to return names.
The key insight is that window functions let you evaluate score positions inside each exam without collapsing rows. Combining this with GROUP BY filters students who were ever ranked first or last. The ranking step typically costs O(n log n) due to sorting inside each partition, and the grouping scan adds linear overhead.
Approach 2: Aggregation with Min/Max Score Per Exam (O(n log n) time, O(n) space)
Another strategy computes the minimum and maximum score for each exam using aggregation. Join these values back to the exam table and mark students whose score equals the exam's min or max. These students are not quiet.
After identifying all non‑quiet students, exclude them from the student list using a NOT IN or anti‑join filter. This approach relies on classic SQL aggregation instead of ranking functions. It is easier to reason about but often requires multiple joins and intermediate scans compared with the window‑function solution.
Recommended for interviews: The RANK() window function approach is the cleanest and closest to what interviewers expect for modern SQL problems. It shows comfort with database analytics features and keeps the logic concise. The aggregation approach still demonstrates solid SQL fundamentals but tends to be more verbose.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| RANK() Window Function + Group By | O(n log n) | O(n) | Best general solution using window functions to detect top and bottom scores per exam |
| Min/Max Aggregation with Joins | O(n log n) | O(n) | Useful when window functions are unavailable or when using classic SQL aggregation logic |
