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.

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.

The result format is in the following example.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39

Input: 
Student table:
+-------------+---------------+
| student_id  | student_name  |
+-------------+---------------+
| 1           | Daniel        |
| 2           | Jade          |
| 3           | Stella        |
| 4           | Jonathan      |
| 5           | Will          |
+-------------+---------------+
Exam table:
+------------+--------------+-----------+
| 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     |
| 40         |     1        |    60     |
| 40         |     2        |    70     |
| 40         |     4        |    80     |
+------------+--------------+-----------+
Output: 
+-------------+---------------+
| student_id  | student_name  |
+-------------+---------------+
| 2           | Jade          |
+-------------+---------------+
Explanation: 
For exam 1: Student 1 and 3 hold the lowest and high scores respectively.
For exam 2: Student 1 hold both highest and lowest score.
For exam 3 and 4: Student 1 and 4 hold the lowest and high scores respectively.
Student 2 and 5 have never got the highest or lowest in any of the exams.
Since student 5 is not taking any exam, he is excluded from the result.
So, we only return the information of Student 2.

Solution

Method 1 – SQL: Group By and Having

Intuition

A student is quiet in all exams if they never scored the highest in any exam. For each exam, find the highest score, then for each student, check if they never had the highest score in any exam.

Approach

  1. For each exam, find the highest score.
  2. For each student, check if they never had the highest score in any exam.
  3. Return the list of such students.

Code

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

SELECT s.student_id, s.student_name
FROM Student s
WHERE s.student_id NOT IN (
    SELECT e.student_id
    FROM Exam e
    JOIN (
        SELECT exam_id, MAX(score) AS max_score
        FROM Exam
        GROUP BY exam_id
    ) m ON e.exam_id = m.exam_id AND e.score = m.max_score
)
ORDER BY s.student_id;
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

SELECT s.student_id, s.student_name
FROM Student s
WHERE s.student_id NOT IN (
    SELECT e.student_id
    FROM Exam e
    JOIN (
        SELECT exam_id, MAX(score) AS max_score
        FROM Exam
        GROUP BY exam_id
    ) m ON e.exam_id = m.exam_id AND e.score = m.max_score
)
ORDER BY s.student_id;
1
2
3
4
5
6
7

import pandas as pd

def find_quiet_students(student: pd.DataFrame, exam: pd.DataFrame) -> pd.DataFrame:
    max_scores = exam.groupby('exam_id')['score'].max().reset_index()
    top = exam.merge(max_scores, on=['exam_id', 'score'])
    quiet = student[~student['student_id'].isin(top['student_id'])]
    return quiet.sort_values('student_id')[['student_id', 'student_name']]

Complexity

  • ⏰ Time complexity: O(n + m) — n = number of students, m = number of exam records.
  • 🧺 Space complexity: O(n + m) — For storing intermediate results.