0/304

00:00:00

Description

Editorial

Major Track Honors Student Filter

HARD30 pts

An academic analytics team wants to identify students who are excelling within their own major track.

You are given three tables:

Students

student_id: student identifier
name: student name
major: student's declared major

Courses

course_id: course identifier
name: course name
credits: credit value
major: major that owns the course
mandatory: "Yes" or "No"

Enrollments

student_id: student identifier
course_id: course identifier
semester: semester label
grade: letter grade
gpa: numeric GPA value for that enrollment

Task: Return students who satisfy all of the following conditions.

Mandatory-major completion with A grade: For every mandatory course in the student's own major, the student must have at least one enrollment attempt with grade "A".
Elective-major breadth with strong grades: The student must have at least two distinct elective courses in their own major where at least one enrollment attempt has grade "A" or "B".
Overall GPA floor: The student's average GPA across all enrollment rows (including courses outside their major) must be at least 2.5.

Output requirements:

Return exactly one column: student_id
Return distinct rows
Sort by student_id in ascending order

Supported submission environments:

SQL (SQLite 3.27.2)
Pandas (Python)

Example

Input

Students:
| student_id | name    | major             |
|------------|---------|-------------------|
| 1          | Alice   | Computer Science  |
| 2          | Bob     | Computer Science  |
| 3          | Charlie | Mathematics       |
| 4          | David   | Mathematics       |

Courses:
| course_id | name              | credits | major            | mandatory |
|-----------|-------------------|---------|------------------|-----------|
| 101       | Algorithms        | 3       | Computer Science | Yes       |
| 102       | Data Structures   | 3       | Computer Science | Yes       |
| 103       | Operating Systems | 3       | Computer Science | No        |
| 104       | Machine Learning  | 3       | Computer Science | No        |
| 201       | Calculus          | 4       | Mathematics      | Yes       |
| 202       | Linear Algebra    | 4       | Mathematics      | Yes       |
| 203       | Probability       | 3       | Mathematics      | No        |
| 204       | Statistics        | 3       | Mathematics      | No        |

Enrollments:
| student_id | course_id | semester     | grade | gpa |
|------------|-----------|--------------|-------|-----|
| 1          | 101       | Fall 2024    | A     | 4.0 |
| 1          | 102       | Spring 2025  | A     | 4.0 |
| 1          | 103       | Spring 2025  | A     | 4.0 |
| 1          | 104       | Fall 2025    | B     | 3.2 |
| 2          | 101       | Fall 2024    | A     | 4.0 |
| 2          | 102       | Spring 2025  | B     | 3.2 |
| 2          | 103       | Fall 2025    | A     | 4.0 |
| 2          | 104       | Fall 2025    | A     | 4.0 |
| 3          | 201       | Fall 2024    | A     | 4.0 |
| 3          | 202       | Spring 2025  | A     | 4.0 |
| 3          | 203       | Spring 2025  | B     | 3.2 |
| 3          | 204       | Fall 2025    | A     | 4.0 |
| 4          | 201       | Fall 2024    | A     | 4.0 |
| 4          | 202       | Spring 2025  | A     | 4.0 |
| 4          | 203       | Spring 2025  | C     | 2.1 |

Output

[
  {"student_id": 1},
  {"student_id": 3}
]

Explanation

Student 1 and 3 satisfy all three rules. Student 2 fails mandatory-grade-A coverage, and student 4 does not have two elective courses with grade A/B.

Example

Input

Students:
| student_id | name | major   |
|------------|------|---------|
| 9          | Isha | Finance |

Courses:
| course_id | name        | credits | major   | mandatory |
|-----------|-------------|---------|---------|-----------|
| 510       | Accounting  | 3       | Finance | Yes       |
| 511       | Markets     | 3       | Finance | Yes       |
| 512       | Derivatives | 3       | Finance | No        |
| 513       | Valuation   | 3       | Finance | No        |

Enrollments:
| student_id | course_id | semester     | grade | gpa |
|------------|-----------|--------------|-------|-----|
| 9          | 510       | Fall 2024    | C     | 2.1 |
| 9          | 510       | Spring 2025  | A     | 4.0 |
| 9          | 511       | Spring 2025  | A     | 4.0 |
| 9          | 512       | Spring 2025  | B     | 3.2 |
| 9          | 513       | Fall 2025    | A     | 4.0 |

Output

[
  {"student_id": 9}
]

Explanation

Retakes are allowed in this formulation. Course 510 has at least one A attempt, and both electives are satisfied with A/B grades.

Example

Input

Students:
| student_id | name  | major |
|------------|-------|-------|
| 20         | Nora  | Stats |

Courses:
| course_id | name               | credits | major | mandatory |
|-----------|--------------------|---------|-------|-----------|
| 701       | Core Inference     | 4       | Stats | Yes       |
| 702       | Core Modeling      | 4       | Stats | Yes       |
| 703       | Elective A         | 3       | Stats | No        |
| 704       | Elective B         | 3       | Stats | No        |

Enrollments:
| student_id | course_id | semester     | grade | gpa |
|------------|-----------|--------------|-------|-----|
| 20         | 701       | Fall 2024    | A     | 4.0 |
| 20         | 702       | Spring 2025  | A     | 4.0 |
| 20         | 703       | Spring 2025  | A     | 4.0 |
| 20         | 704       | Fall 2025    | B     | 2.0 |

Output

[
  {"student_id": 20}
]

Explanation

Average GPA is exactly 2.5, which passes the threshold.

Accepted0/0·0% Acceptance

Constraints

1 <= number of rows in Students <= 200000
1 <= number of rows in Courses <= 200000
1 <= number of rows in Enrollments <= 300000
student_id is unique in Students
course_id is unique in Courses
(student_id, course_id, semester) is unique in Enrollments
mandatory is either 'Yes' or 'No'
grade is a letter-grade string (for example A, B, C, D, F)
Each student's major is compared against Courses.major for mandatory/elective checks
A mandatory course is satisfied if at least one attempt has grade A
An elective course is counted if at least one attempt has grade A or B
At least two distinct elective courses must be counted
Average GPA is computed over all enrollment rows for that student
Output must contain distinct student_id values sorted ascending
SQL submissions run on SQLite 3.27.2
Pandas submissions must return a DataFrame with column [student_id]

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

Courses =

[{"course_id":100,"name":"Com Core 1-1","credits":3,"major":"Computer Science","mandatory":"Yes"},{"course_id":101,"name":"Com Core 1-2","credits":4,"major":"Computer Science","mandatory":"Yes"},{"course_id":102,"name":"Com Elective 1-1","credits":2,"major":"Computer Science","mandatory":"No"},{"course_id":103,"name":"Com Elective 1-2","credits":3,"major":"Computer Science","mandatory":"No"},{"course_id":104,"name":"Com Elective 1-3","credits":4,"major":"Computer Science","mandatory":"No"},{"course_id":105,"name":"Mat Core 2-1","credits":3,"major":"Mathematics","mandatory":"Yes"},{"course_id":106,"name":"Mat Core 2-2","credits":4,"major":"Mathematics","mandatory":"Yes"},{"course_id":107,"name":"Mat Elective 2-1","credits":2,"major":"Mathematics","mandatory":"No"},{"course_id":108,"name":"Mat Elective 2-2","credits":3,"major":"Mathematics","mandatory":"No"},{"course_id":109,"name":"Mat Elective 2-3","credits":4,"major":"Mathematics","mandatory":"No"}]

Students =

[{"student_id":1,"name":"Student 1","major":"Computer Science"},{"student_id":2,"name":"Student 2","major":"Computer Science"},{"student_id":3,"name":"Student 3","major":"Mathematics"},{"student_id":4,"name":"Student 4","major":"Mathematics"}]

Enrollments =

[{"student_id":1,"course_id":100,"semester":"Spring 2022","grade":"A","gpa":4},{"student_id":1,"course_id":101,"semester":"Spring 2022","grade":"A","gpa":4},{"student_id":1,"course_id":102,"semester":"Spring 2022","grade":"A","gpa":4},{"student_id":1,"course_id":103,"semester":"Spring 2022","grade":"B","gpa":3.2},{"student_id":2,"course_id":100,"semester":"Spring 2022","grade":"B","gpa":3.2},{"student_id":2,"course_id":101,"semester":"Spring 2022","grade":"A","gpa":4},{"student_id":2,"course_id":102,"semester":"Spring 2022","grade":"A","gpa":4},{"student_id":2,"course_id":103,"semester":"Spring 2022","grade":"B","gpa":3.2},{"student_id":3,"course_id":105,"semester":"Spring 2022","grade":"A","gpa":4},{"student_id":3,"course_id":106,"semester":"Spring 2022","grade":"A","gpa":4},{"student_id":3,"course_id":107,"semester":"Spring 2022","grade":"A","gpa":4},{"student_id":3,"course_id":108,"semester":"Spring 2022","grade":"B","gpa":3.2},{"student_id":3,"course_id":100,"semester":"Spring 2022","grade":"B","gpa":3.2},{"student_id":4,"course_id":105,"semester":"Spring 2022","grade":"A","gpa":4},{"student_id":4,"course_id":106,"semester":"Spring 2022","grade":"A","gpa":4},{"student_id":4,"course_id":107,"semester":"Spring 2022","grade":"A","gpa":4},{"student_id":4,"course_id":107,"semester":"Fall 2022","grade":"B","gpa":3.2},{"student_id":4,"course_id":108,"semester":"Spring 2022","grade":"C","gpa":2.1},{"student_id":4,"course_id":100,"semester":"Spring 2022","grade":"A","gpa":4},{"student_id":4,"course_id":101,"semester":"Spring 2022","grade":"B","gpa":3.2}]

Major Track Honors Student Filter

Hints

Major Track Honors Student Filter

Hints