Quiet Students
ASKED IN INTERVIEWGoogle
Problem Statement
A student is "quiet" if they took at least one exam but never scored the highest or lowest in any exam. Find these students.
Rules:
- Output columns: student_id, student_name.
- Highest/Lowest is calculated per exam_id.
- Exclude any student who was the high or low scorer even once.
- Sort by student_id ASC.
Tests your understanding of
Window Functions, Filtering, Subqueries and Exclusion Logic
Input Tables
students
| student_id(INTEGER) | student_name(VARCHAR) |
|---|---|
| 1 | Daniel |
| 2 | Jade |
| 3 | Stella |
| 4 | Jonathan |
exams
| exam_id(INTEGER) | student_id(INTEGER) | score(INTEGER) |
|---|---|---|
| 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 |
courses
| exam_id(INTEGER) | course_name(VARCHAR) |
|---|---|
| 10 | Math |
Expected Output
| student_id(INTEGER) | student_name(VARCHAR) |
|---|---|
| 2 | Jade |
Tags
HardASKED IN INTERVIEWWindow FunctionsFilteringSubqueriesExclusion Logic
30-40 min
38%