DowngradedOur downstream service providers are currently experiencing outages, and our engineering team is actively working on a resolution. Some services—including the Solver, Partner, and Tools—are temporarily degraded with higher latency and lower bandwidth. Rest assured, Intervipedia, Solutions, and the Question Bank features are not impacted and remain fully operational.DowngradedOur downstream service providers are currently experiencing outages, and our engineering team is actively working on a resolution. Some services—including the Solver, Partner, and Tools—are temporarily degraded with higher latency and lower bandwidth. Rest assured, Intervipedia, Solutions, and the Question Bank features are not impacted and remain fully operational.DowngradedOur downstream service providers are currently experiencing outages, and our engineering team is actively working on a resolution. Some services—including the Solver, Partner, and Tools—are temporarily degraded with higher latency and lower bandwidth. Rest assured, Intervipedia, Solutions, and the Question Bank features are not impacted and remain fully operational.DowngradedOur downstream service providers are currently experiencing outages, and our engineering team is actively working on a resolution. Some services—including the Solver, Partner, and Tools—are temporarily degraded with higher latency and lower bandwidth. Rest assured, Intervipedia, Solutions, and the Question Bank features are not impacted and remain fully operational.
DowngradedOur downstream service providers are currently experiencing outages, and our engineering team is actively working on a resolution. Some services—including the Solver, Partner, and Tools—are temporarily degraded with higher latency and lower bandwidth. Rest assured, Intervipedia, Solutions, and the Question Bank features are not impacted and remain fully operational.DowngradedOur downstream service providers are currently experiencing outages, and our engineering team is actively working on a resolution. Some services—including the Solver, Partner, and Tools—are temporarily degraded with higher latency and lower bandwidth. Rest assured, Intervipedia, Solutions, and the Question Bank features are not impacted and remain fully operational.DowngradedOur downstream service providers are currently experiencing outages, and our engineering team is actively working on a resolution. Some services—including the Solver, Partner, and Tools—are temporarily degraded with higher latency and lower bandwidth. Rest assured, Intervipedia, Solutions, and the Question Bank features are not impacted and remain fully operational.DowngradedOur downstream service providers are currently experiencing outages, and our engineering team is actively working on a resolution. Some services—including the Solver, Partner, and Tools—are temporarily degraded with higher latency and lower bandwidth. Rest assured, Intervipedia, Solutions, and the Question Bank features are not impacted and remain fully operational.
The Question
SQL

Identifying Consistent Median Performers

Given a Student table containing names and IDs, and an Exam table containing scores for various exams, define a 'Quiet Student' as one who has participated in at least one exam but has never achieved the highest or lowest score in any exam they sat for. Write a SQL query to find the student_id and student_name of all quiet students, ensuring that students who never took an exam are excluded. Note: If an exam has multiple students with the same top score, all are considered 'loud' for that exam. Sort the output by student_id.
PostgreSQL
Window Function
CTE
CASE Expression
Inner Join
Questions & Insights

Clarifying Questions

What happens if there is only one student in an exam?
If only one student takes an exam, that student by definition achieves both the highest and lowest score for that exam. Therefore, they are considered "loud" for that specific exam and fail the "quiet" criteria.
How should ties for the highest or lowest score be handled?
If multiple students tie for the maximum or minimum score in an exam, all those students are considered "loud" for that exam. A "quiet" student must never be part of the max or min set in any exam they participate in.
What defines "all exams"?
The student must be "quiet" in every single exam they have an entry for in the Exam table. If they are "loud" in even one exam, they are excluded.
Data Model Assumptions:
Student: Dimension table. student_id is the Primary Key.
Exam: Fact table. (exam_id, student_id) is a Composite Primary Key.
There is a 1:N relationship between Student and Exam.
We assume score is a non-null integer.

Thinking Process

Identify Boundary Scores: For every exam_id, we need to know what the MAX(score) and MIN(score) were. Window functions are the most efficient way to compute these values without collapsing the grain of the rows.
Tag "Loud" Instances: Create a flag for each row in the Exam table. If a student's score equals the max or min for that exam_id, label that instance as "loud".
Aggregate at Student Level: Group by student_id. A student is "quiet" if they have at least one exam record AND the count of their "loud" instances is exactly zero.
Join for Metadata: Join the resulting list of student_ids with the Student table to retrieve student_name.
Final Sort: Order the output by student_id as requested.
Implementation Breakdown

Problem Set

Goal: Find students who took at least one exam and were never the top or bottom scorer in any exam they took.
Constraints:
Exclude students who took no exams.
Exclude students who were top/bottom scorers in at least one exam.
Edge Cases:
Students with only one exam (where they are both min and max).
Tiers/Ties at the top or bottom.
Students with many exams but "loud" in only one.

Approach

Technologies: PostgreSQL 15+.
Window Functions:MAX() OVER(PARTITION BY ...) and MIN() OVER(PARTITION BY ...) to determine exam boundaries without self-joins.
CTEs: Used for modularity and readability.
Filtering: Use a HAVING clause or a subquery to filter students based on the "loud" count.
Complexity: The approach has an O(N \log N) complexity due to the window function sorting/partitioning, where N is the number of rows in the Exam table.

Implementation

Wrap Up

Advanced Topics

Indexing: To optimize this query, a composite index on Exam(exam_id, score) would significantly speed up the window function's partition and range calculations. An index on Exam(student_id) would optimize the GROUP BY and the join.
Window Function vs. Join: Using window functions is generally more performant than joining the Exam table back to an aggregated version of itself, as it avoids multiple passes over the same data.
Scalability: In a distributed environment like Greenplum or Citus (Postgres-based), the PARTITION BY exam_id would trigger a data shuffle unless the table is already partitioned or sharded by exam_id.
Alternative (NOT IN): One could also find all "loud" IDs using a subquery and use student_id NOT IN (...), but SUM(...) = 0 via a CTE often results in a cleaner execution plan for large datasets by avoiding anti-join overheads.