Find the students who attended at least one exam but not the Max nor the Min score

Question

My solution passed the first test case, but got the wrong answer after final submission. I am thankful for anyone willing to point out my mistakes. Thanks!

The question is as below:

Table: Student

+---------------------+---------+
| Column Name         | Type    |
+---------------------+---------+
| student_id          | int     |
| student_name        | varchar |
+---------------------+---------+

student_id is the primary key 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 for this table. Student with student_id got score points in exam with id exam_id.

A "quite" student is the one who took at least one exam and didn't score neither the high score nor the low score.

Write an SQL query to report the students (student_id, student_name) being "quiet" in ALL exams.

Don't return the student who has never taken any exam. Return the result table ordered by student_id.

The query result format is in the following example.

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     |
+------------+--------------+-----------+

Result table:

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

Explanation in the question:

For exam 1: Student 1 and 3 hold the lowest and high score respectively. For exam 2: Student 1 hold both highest and lowest score. For exam 3 and 4: Studnet 1 and 4 hold the lowest and high score respectively. Student 2 and 5 have never got the highest or lowest in any of the exam. Since student 5 is not taking any exam, he is excluded from the result. So, we only return the information of Student 2.

My answer was to create two tables, the one is to list eligible student, with at least one exam. The other is to find the max(score) and the min(score) of the exam table. And use <> to locate the quiet students' id and then join with Student table to find this student_id's name, as below:

-- get eligible student

with eligible_student as
(
select distinct student_id as eligible_id from Exam
    group by 1
    order by 1
),

-- get the high and low score
high_low as
(select student_id, max(score) as high_score, min(score) as low_score
from Exam), 

result as 
 (select eligible_student.eligible_id as student_id
 from eligible_student inner join
 high_low 
 on eligible_student.eligible_id <> high_low.student_id
-- left join Student
-- on eligible_student.eligible_id = Student.student_id 
 group by student_id
 order by student_id
 )
 
 select result.student_id, s.student_name as student_name
 from result left join Student s
 on result.student_id = s.student_id
 order by student_id;

Answer 1

I would use window functions and aggregation:

select s.*
from student s
inner join (
    select e.*, 
        rank() over(partition by exam_id order by score) as rn_asc,
        rank() over(partition by exam_id order by score desc) as rn_desc
    from exam e
) e on e.student_id = s.student_id
group by s.student_id
having min(rn_asc) > 1 and min(rn_desc) > 1

The subquery ranks records having he same exam by ascending and descending score, allowing ties. We can then join that with the student table (which eliminates students that have no exam at all), group by student, and filter on those whose both ranks never reached 1.

Answer 2

This query:

SELECT *,
     (MIN(score) OVER (PARTITION BY exam_id) = score) +
     (MAX(score) OVER (PARTITION BY exam_id) = score) flag
FROM exam

returns a flag column which has the value 0 when the student's score is neither the min nor the max of of the exam.

You can aggregate on the results of the above query to get all the students that have not a single flag with value different than 0:

WITH cte AS (
  SELECT *,
         (MIN(score) OVER (PARTITION BY exam_id) = score) +
         (MAX(score) OVER (PARTITION BY exam_id) = score) flag
  FROM exam
)
SELECT s.student_id, s.student_name
FROM student s INNER JOIN cte c
ON c.student_id = s.student_id
GROUP BY s.student_id, s.student_name
HAVING SUM(c.flag) = 0

Or:

WITH cte AS (
  SELECT student_id
  FROM (
    SELECT *,
         (MIN(score) OVER (PARTITION BY exam_id) = score) +
         (MAX(score) OVER (PARTITION BY exam_id) = score) flag
    FROM exam
  ) t
  GROUP BY student_id
  HAVING SUM(flag) = 0
)
SELECT * 
FROM student
WHERE student_id IN (SELECT student_id FROM cte)

See the demo.
Results:

> student_id | student_name
> ---------: | :-----------
>          2 | Jade        

Answer 3

This part is wrong:

with high_low as
(select student_id, max(score) as high_score, min(score) as low_score
from Exam)

because it outputs:

+------------+------------+-----------+
| student_id | high_score | low_score |
+------------+------------+-----------+
|          1 |         90 |        60 |
+------------+------------+-----------+

and student_id=1 has no relation to the found high_score or low_score.

After this the found (but incorrect) student_id is used in the selection for the cte result.

A solution:

with high_low as
(select max(score) as high_score, min(score) as low_score
from Exam) 
select student.student_id, student_name
from (
  select exam.student_id 
   from exam
   group by exam.student_id
   having max(score) <> (select high_score from high_low)
      and min(score) <> (select low_score from high_low)) x
inner join student on student.student_id=x.student_id;

or:

select 
   student.*
from exam 
inner join student on student.student_id=exam.student_id
group by student_id 
having max(score) not in (select max(score) from exam) 
   and min(score) not in (select min(score) from exam);