Quiet students in all exams

SQL data engineering interview problem. Difficulty: advanced. Pattern: Window Functions. About 18 minutes. Part of the Pro drill bank.

exam_students(student_id, student_name) lists registered students. exam_scores(exam_id, student_id, score) records exam results. A 'quiet student' is someone who took at least one exam and never scored the highest or lowest score in any exam they sat. If an exam has multiple students tied for highest or lowest, all tied students count as extreme scorers. Return student_id and student_name of all quiet students, ordered by student_id.

Requirements

• Output columns: student_id, student_name.
• Order by student_id ascending.

Constraints

• student_id is unique in exam_students.
• score is an integer between 0 and 100.
• Students who took zero exams are not quiet students.

Examples

Input: exam_students student_id,student_name 1,Daniel 2,Jade 3,Stella 4,Jonathan 5,Will exam_scores 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 ... Output: student_id | student_name 2 | Jade Why this passes: Jade (2) is never the highest or lowest scorer in any exam she took; students 1, 3 and 4 hit a high or low at least once, and student 5 took no exams.

Input: exam_students student_id | student_name 1 | Ann 2 | Bob 3 | Carl exam_scores exam_id | student_id | score 1 | 1 | 50 1 | 2 | 75 1 | 3 | 100 Output: student_id | student_name 2 | Bob Why this passes: Ann is min (50) and Carl is max (100). Bob is strictly in the middle, qualifying as quiet.

Topics: lakebench, sql, min-max, never-condition.

More SQL interview questions · All interview problems · Learn data engineering