Find the Quiet Students in All Exams

Problem

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.

Problem Definition

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.

Example

Output

Try It Yourself

Solution

To identify quiet students—those who have taken at least one exam and never scored the highest or the lowest in any of their exams—we can follow a structured approach using SQL's window functions and subqueries. This involves ranking students within each exam, filtering out those who achieved top or bottom ranks, and finally selecting the remaining students who meet the quiet student criteria.

Approach Overview

1. Rank Students' Scores Within Each Exam:
   • Assign ranks to students based on their scores in each exam to identify the highest and lowest scorers.
2. Identify Students with Highest or Lowest Scores:
   • Extract students who have ever ranked first (highest score) or last (lowest score) in any exam.
3. Determine Quiet Students:
   • Exclude students identified in the previous step from the list of all students who have taken at least one exam. The remaining students are the quiet students.
4. Retrieve Quiet Students' Details:
   • Fetch the student_id and student_name of the quiet students from the Student table.
5. Finalize the Results:
   • Select distinct candidate details and present them in the desired format, ordered by student_id.

SQL Query

WITH cte AS 
(
    SELECT exam_id, 
           student_id, 
           RANK() OVER(PARTITION BY exam_id ORDER BY score DESC) AS high_score,
           RANK() OVER(PARTITION BY exam_id ORDER BY score) AS low_score
    FROM Exam 
)

SELECT DISTINCT e.student_id, s.student_name
FROM Exam e 
LEFT JOIN Student s ON s.student_id = e.student_id
WHERE e.student_id NOT IN (
    SELECT student_id 
    FROM cte 
    WHERE high_score = 1 OR low_score = 1
)
ORDER BY e.student_id;

Step-by-Step Approach

Step 1: Rank Students' Scores Within Each Exam (cte)

Assign ranks to students within each exam to determine who scored the highest and the lowest.

SQL Query:

WITH cte AS 
(
    SELECT exam_id, 
           student_id, 
           RANK() OVER(PARTITION BY exam_id ORDER BY score DESC) AS high_score,
           RANK() OVER(PARTITION BY exam_id ORDER BY score) AS low_score
    FROM Exam 
)
SELECT * FROM cte;

Explanation:

• RANK() OVER(PARTITION BY exam_id ORDER BY score DESC) AS high_score:
  • Assigns a rank to each student in descending order of their scores within each exam_id. The student(s) with the highest score receive a rank of 1.
• RANK() OVER(PARTITION BY exam_id ORDER BY score) AS low_score:
  • Assigns a rank to each student in ascending order of their scores within each exam_id. The student(s) with the lowest score receive a rank of 1.

Intermediate Output After Step 1 (cte):

+---------+------------+-----------+----------+
| exam_id | student_id | high_score| low_score|
+---------+------------+-----------+----------+
| 10      | 1          | 3         | 1        |
| 10      | 2          | 2         | 2        |
| 10      | 3          | 1         | 3        |
| 20      | 1          | 1         | 1        |
| 30      | 1          | 3         | 1        |
| 30      | 3          | 2         | 2        |
| 30      | 4          | 1         | 3        |
| 40      | 1          | 3         | 1        |
| 40      | 2          | 2         | 2        |
| 40      | 4          | 1         | 3        |
+---------+------------+-----------+----------+

Step 2: Identify Students with Highest or Lowest Scores

Find all students who have ever ranked first (highest score) or last (lowest score) in any exam.

SQL Query:

WITH cte AS 
(
    SELECT exam_id, 
           student_id, 
           RANK() OVER(PARTITION BY exam_id ORDER BY score DESC) AS high_score,
           RANK() OVER(PARTITION BY exam_id ORDER BY score) AS low_score
    FROM Exam 
)
SELECT DISTINCT student_id
FROM cte 
WHERE high_score = 1 OR low_score = 1;

Explanation:

• WHERE high_score = 1 OR low_score = 1:
  • Filters the cte to include only those students who have either the highest (high_score = 1) or the lowest (low_score = 1) score in any exam.

Intermediate Output After Step 2:

+------------+
| student_id |
+------------+
| 1          |
| 3          |
| 4          |
+------------+

Step 3: Determine Quiet Students

Exclude students identified in Step 2 from those who have taken at least one exam. The remaining students are the quiet students.

SQL Query:

WITH cte AS 
(
    SELECT exam_id, 
           student_id, 
           RANK() OVER(PARTITION BY exam_id ORDER BY score DESC) AS high_score,
           RANK() OVER(PARTITION BY exam_id ORDER BY score) AS low_score
    FROM Exam 
),
excluded AS (
    SELECT DISTINCT student_id
    FROM cte 
    WHERE high_score = 1 OR low_score = 1
)

SELECT DISTINCT e.student_id, s.student_name
FROM Exam e 
JOIN Student s ON s.student_id = e.student_id
WHERE e.student_id NOT IN (SELECT student_id FROM excluded)
ORDER BY e.student_id;

Explanation:

• JOIN Student s ON s.student_id = e.student_id:
  • Associates each exam record with the corresponding student's details.
• WHERE e.student_id NOT IN (SELECT student_id FROM excluded):
  • Filters out students who have ever scored the highest or the lowest in any exam.
• SELECT DISTINCT e.student_id, s.student_name:
  • Ensures that each quiet student appears only once in the final output, even if they have taken multiple exams.

Intermediate Output After Step 3:

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

Explanation of Output:

• Student 2 (Jade):
  • Exam 10: Scored 80 (middle score)
  • Exam 40: Scored 70 (middle score)
  • Never scored the highest or the lowest in any exam.

Note:

• Students 1, 3, and 4 are excluded as they have scored the highest or lowest in at least one exam.