00:00:00

Description

Editorial

Consistently Mid-Range Exam Performers

HARD30 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

exam =

[{"exam_id":10,"student_id":1,"score":70},{"exam_id":10,"student_id":2,"score":80},{"exam_id":10,"student_id":3,"score":90},{"exam_id":20,"student_id":1,"score":80},{"exam_id":30,"student_id":1,"score":70},{"exam_id":30,"student_id":3,"score":80},{"exam_id":30,"student_id":4,"score":90},{"exam_id":40,"student_id":1,"score":60},{"exam_id":40,"student_id":2,"score":70},{"exam_id":40,"student_id":4,"score":80}]

student =

[{"student_id":1,"student_name":"Daniel"},{"student_id":2,"student_name":"Jade"},{"student_id":3,"student_name":"Stella"},{"student_id":4,"student_name":"Jonathan"},{"student_id":5,"student_name":"Will"}]

Loading problem...

101

00:00:00

Description

Editorial

Consistently Mid-Range Exam Performers

HARD30 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

exam =

student =

Consistently Mid-Range Exam Performers

Hints

Consistently Mid-Range Exam Performers

Hints