Consistently Mid-Range Exam Performers

Hard

An education analytics team wants to identify students whose performance is stable across tests. Table: student student_id: unique student identifier student_name: student display name Table: exam exam_id: exam identifier student_id: student who took the exam score: numeric score for that student in that exam (exam_id, student_id) is unique Definition: A student is considered consistently mid-range when both conditions hold: The student took at least one exam. In every exam they took, their score was never an exam maximum and never an exam minimum. Important scoring semantics: If multiple students tie for the maximum score, all of them are considered maximum scorers. If multiple students tie for the minimum score, all of them are considered minimum scorers. In an exam with exactly one participant, that participant is both minimum and maximum. Task: Return all students who satisfy this definition. Output requirements: Return columns in this exact order: student_id student_name Order by student_id ascending. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

student:
| student_id | student_name |
|------------|--------------|
| 1          | Daniel       |
| 2          | Jade         |
| 3          | Stella       |
| 4          | Jonathan     |
| 5          | Will         |

exam:
| 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":2,"student_name":"Jade"}
]

Explanation

Student 2 appears in exams and is never tied to a local minimum or maximum score. Student 5 is excluded because they did not take any exam.

Example 2

Input

student:
| student_id | student_name |
|------------|--------------|
| 10         | Aster        |
| 11         | Blair        |
| 12         | Casey        |

exam:
| exam_id | student_id | score |
|---------|------------|-------|
| 100     | 10         | 50    |
| 100     | 11         | 50    |
| 100     | 12         | 50    |
| 101     | 10         | 90    |
| 101     | 11         | 40    |

Output

[]

Explanation

In exam 100, everyone is both min and max because all scores tie. In exam 101, one student is max and one is min. No student remains non-extreme in all taken exams.

Example 3

Input

student:
| student_id | student_name |
|------------|--------------|
| 21         | Noor         |
| 22         | Priya        |
| 23         | Sam          |
| 24         | Theo         |

exam:
| exam_id | student_id | score |
|---------|------------|-------|
| 201     | 21         | 50    |
| 201     | 22         | 68    |
| 201     | 23         | 74    |
| 201     | 24         | 92    |
| 202     | 21         | 52    |
| 202     | 22         | 69    |
| 202     | 23         | 75    |
| 202     | 24         | 94    |
| 203     | 21         | 53    |
| 203     | 22         | 70    |
| 203     | 23         | 76    |
| 203     | 24         | 95    |

Output

[
  {"student_id":22,"student_name":"Priya"},
  {"student_id":23,"student_name":"Sam"}
]

Explanation

Students 22 and 23 are always internal scores in every exam they take. Students 21 and 24 hit extreme positions.

Constraints

1 <= number of rows in student <= 300000
0 <= number of rows in exam <= 500000
student_id is unique in student
(exam_id, student_id) is unique in exam
exam.student_id references student.student_id
1 <= score <= 1000000
Return only students who appear at least once in exam
Students tied at exam min or exam max are considered extreme
Return exactly two columns: student_id, student_name
Result must be ordered by student_id ascending
For SQL submissions, use SQLite 3.27.2 semantics
For Pandas submissions, return a DataFrame with columns: student_id, student_name

student: | student_id | student_name | |------------|--------------| | 1 | Daniel | | 2 | Jade | | 3 | Stella | | 4 | Jonathan | | 5 | Will | exam: | 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 |

student: | student_id | student_name | |------------|--------------| | 10 | Aster | | 11 | Blair | | 12 | Casey | exam: | exam_id | student_id | score | |---------|------------|-------| | 100 | 10 | 50 | | 100 | 11 | 50 | | 100 | 12 | 50 | | 101 | 10 | 90 | | 101 | 11 | 40 |

student: | student_id | student_name | |------------|--------------| | 21 | Noor | | 22 | Priya | | 23 | Sam | | 24 | Theo | exam: | exam_id | student_id | score | |---------|------------|-------| | 201 | 21 | 50 | | 201 | 22 | 68 | | 201 | 23 | 74 | | 201 | 24 | 92 | | 202 | 21 | 52 | | 202 | 22 | 69 | | 202 | 23 | 75 | | 202 | 24 | 94 | | 203 | 21 | 53 | | 203 | 22 | 70 | | 203 | 23 | 76 | | 203 | 24 | 95 |