Find the Quiet Students in All Exams

Mechanism

Rank every student twice inside each exam — once descending (so rank 1 is the top score) and once ascending (so rank 1 is the bottom score) — then any student who ever earns a rank 1 in either direction in any exam is loud; the quiet students are exactly those who took an exam but appear in neither rank-1 set.

The whole trick is that "highest in this exam" and "lowest in this exam" are both just "rank 1" under opposite sort orders. Compute both with two windowed RANK()s over the same PARTITION BY exam_id, collapse every loud student into one exclusion list, and subtract that list from everyone who sat an exam.

Problem

Table Student(student_id PK, student_name); table Exam(exam_id, student_id, score) with composite PK (exam_id, student_id) — one row per student per exam.

A quiet student took at least one exam and never scored the highest or the lowest in any exam they sat. Report (student_id, student_name) for the quiet students, ordered by student_id. Never return a student who took no exam.

The data we will trace

Everything below is computed from this input — every rank and every verdict traces back to a real score here, nothing is fabricated.

Student

Exam — note Will (5) never appears, so he must not be in the answer; exam 20 has a single taker.

The query

WITH ranked AS (
    SELECT exam_id,
           student_id,
           RANK() OVER (PARTITION BY exam_id ORDER BY score DESC) AS high_rank,
           RANK() OVER (PARTITION BY exam_id ORDER BY score ASC)  AS low_rank
    FROM Exam
)
SELECT DISTINCT s.student_id, s.student_name
FROM   Student s
JOIN   Exam   e ON e.student_id = s.student_id      -- only students who sat an exam
WHERE  s.student_id NOT IN (
           SELECT student_id
           FROM   ranked
           WHERE  high_rank = 1 OR low_rank = 1      -- ever top OR ever bottom
       )
ORDER  BY s.student_id;

The inner JOIN on Exam is what enforces "took at least one exam" — Will (5) has no Exam row, so he is dropped before the filter even runs. The NOT IN then removes every loud student.

Step 1 — rank within each exam (the ranked CTE)

Each row's two ranks are computed only from the scores in that exam. Read a row as: "in exam 10, Daniel's 90 is the highest (high_rank 1) and the 3rd-lowest (low_rank 3)."

Exam 20 is the instructive edge: with one taker, that 95 is simultaneously the highest and the lowest, so Daniel gets high_rank = low_rank = 1. A sole taker is always loud.

Step 2 — collect the loud students

Keep every ranked row where high_rank = 1 OR low_rank = 1, take the DISTINCT student_ids. Walking the table above:

• Daniel (1): high_rank 1 in exam 10, both ranks 1 in exam 20, low_rank 1 in exams 30 and 40 — loud many times over.
• Stella (3): low_rank 1 in exam 10 — loud.
• Jonathan (4): high_rank 1 in exams 30 and 40 — loud.
• Jade (2): ranks 2/2 in exam 10 and 2/2 in exam 40 — never appears here.

excluded student_id
-------------------
        1   (Daniel)
        3   (Stella)
        4   (Jonathan)

Step 3 — subtract, then the final answer

Everyone who sat an exam is {1, 2, 3, 4}. Remove the loud set {1, 3, 4} and only Jade survives. Joining back to Student for her name:

+------------+--------------+
| student_id | student_name |
+------------+--------------+
|     2      | Jade         |
+------------+--------------+

This checks out against the raw scores: Jade got 80 in exam 10 (between Daniel's 90 and Stella's 70) and 70 in exam 40 (between Jonathan's 100 and Daniel's 50) — middle both times. Will (5) is correctly absent because he has no Exam row at all.

Pitfalls

• ROW_NUMBER() instead of RANK() silently breaks on ties. Suppose two students tie for the top score in an exam. RANK() gives both rank 1, so both are flagged loud. ROW_NUMBER() hands out 1 and 2 arbitrarily — the second top-scorer gets rank 2 and survives as "quiet" even though they tied for highest. DENSE_RANK() works too; the rule is: use a function that gives tied extremes the same rank.
• NOT IN is NULL-poisoned. If the subquery after NOT IN ever yields a NULL, the whole predicate evaluates to UNKNOWN for every row and you get zero results. Here it is safe only because student_id is part of Exam's primary key and is never NULL. Make that an explicit habit — when in doubt, write NOT EXISTS (SELECT 1 FROM excluded x WHERE x.student_id = e.student_id), which is NULL-safe by construction, or add WHERE student_id IS NOT NULL to the subquery.
• Forgetting the "took an exam" guard. If you build the result from Student LEFT JOIN Exam or drive off Student alone, a student with no exams (Will) passes the NOT IN filter — he was never loud — and wrongly lands in the output. Drive the final query off Exam (or inner-join it) so non-takers are excluded structurally.
• Single-taker exams are a trap, not an exception. One student in an exam is both the max and the min, so that exam can never produce a quiet survivor — it always flags its lone taker loud. That is correct behavior, but it surprises people who assume "middle" requires three rows.

Takeaways

• "Highest" and "lowest" are the same operation under opposite sort orders — two RANK()s over one partition, both checked for rank 1.
• The exclusion model (collect the loud set once, subtract it) is cleaner and faster than per-exam anti-joins or correlated MAX/MIN comparisons.
• Use RANK()/DENSE_RANK(), not ROW_NUMBER(), whenever ties at an extreme must all count.
• Reach for NOT EXISTS over NOT IN unless you can prove the subquery is NULL-free.

Problem from LeetCode 1412 "Find the Quiet Students in All Exams". SQL window-function semantics per the PostgreSQL documentation (window functions, RANK/ROW_NUMBER/DENSE_RANK) and the SQL:2003 windowing standard; the NOT IN NULL behavior follows the SQL three-valued-logic rules also documented by PostgreSQL and Oracle. Worked dataset re-authored from scratch so every rank and the final answer trace back to a shown input table, and the RANK-vs-ROW_NUMBER and NULL-safe-NOT IN pitfalls added. Re-authored / deepened for this guide.