Major Curriculum Perfect-A Scholars

Medium

An academic analytics system needs to identify students who achieved strict excellence across their entire major curriculum. You are given three tables: students student_id: unique student identifier name: student display name major: student's declared major courses course_id: unique course identifier name: course name credits: credit weight major: major that owns this course enrollments student_id: student identifier course_id: course identifier semester: attempt term label grade: grade for that attempt Task: Return student_id values for students who satisfy both rules: Every course belonging to the student's major has at least one enrollment row by that student. Across all enrollment attempts for those required major courses, every grade is A. Important clarifications: Enrollments in courses outside the student's major do not affect qualification. If a required course is retaken, all attempts must still be A. Output requirements: Return exactly one column: student_id Sort by student_id ascending Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

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            |
|-----------|-----------------|---------|------------------|
| 101       | Algorithms      | 3       | Computer Science |
| 102       | Data Structures | 3       | Computer Science |
| 103       | Calculus        | 4       | Mathematics      |
| 104       | Linear Algebra  | 4       | Mathematics      |

enrollments:
| student_id | course_id | semester  | grade |
|------------|-----------|-----------|-------|
| 1          | 101       | 2023-Fall | A     |
| 1          | 102       | 2023-Fall | A     |
| 2          | 101       | 2023-Fall | B     |
| 2          | 102       | 2023-Fall | A     |
| 3          | 103       | 2023-Fall | A     |
| 3          | 104       | 2023-Fall | A     |
| 4          | 103       | 2023-Fall | A     |
| 4          | 104       | 2023-Fall | B     |

Output

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

Explanation

Students 1 and 3 cover every required major course and have only A grades on those required-course attempts.

Example 2

Input

students:
| student_id | name | major   |
|------------|------|---------|
| 10         | Eva  | Physics |

courses:
| course_id | name           | credits | major   |
|-----------|----------------|---------|---------|
| 201       | Mechanics      | 4       | Physics |
| 202       | Thermodynamics | 4       | Physics |

enrollments:
| student_id | course_id | semester    | grade |
|------------|-----------|-------------|-------|
| 10         | 201       | 2024-Spring | A     |

Output

[]

Explanation

Student 10 is missing required course 202, so the student does not qualify.

Example 3

Input

students:
| student_id | name | major   |
|------------|------|---------|
| 20         | Noor | Biology |

courses:
| course_id | name     | credits | major   |
|-----------|----------|---------|---------|
| 301       | Genetics | 3       | Biology |
| 302       | Ecology  | 3       | Biology |

enrollments:
| student_id | course_id | semester    | grade |
|------------|-----------|-------------|-------|
| 20         | 301       | 2023-Fall   | A     |
| 20         | 301       | 2024-Spring | B     |
| 20         | 302       | 2024-Spring | A     |

Output

[]

Explanation

A non-A retake on required course 301 disqualifies the student.

Constraints

1 <= number of rows in students <= 100000
1 <= number of rows in courses <= 100000
1 <= number of rows in enrollments <= 300000
1 <= student_id <= 1000000000
1 <= course_id <= 1000000000
1 <= credits <= 10
grade is one of A, B, C, D, F
students.student_id is unique
courses.course_id is unique
(student_id, course_id, semester) is unique in enrollments
Every students.major has at least one matching course in courses.major
Output must contain exactly one column: student_id
Sort output by student_id ASC
SQL submissions are evaluated with SQLite 3.27.2 semantics
Pandas submissions must return a DataFrame with column [student_id]

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 | |-----------|-----------------|---------|------------------| | 101 | Algorithms | 3 | Computer Science | | 102 | Data Structures | 3 | Computer Science | | 103 | Calculus | 4 | Mathematics | | 104 | Linear Algebra | 4 | Mathematics | enrollments: | student_id | course_id | semester | grade | |------------|-----------|-----------|-------| | 1 | 101 | 2023-Fall | A | | 1 | 102 | 2023-Fall | A | | 2 | 101 | 2023-Fall | B | | 2 | 102 | 2023-Fall | A | | 3 | 103 | 2023-Fall | A | | 3 | 104 | 2023-Fall | A | | 4 | 103 | 2023-Fall | A | | 4 | 104 | 2023-Fall | B |

students: | student_id | name | major | |------------|------|---------| | 10 | Eva | Physics | courses: | course_id | name | credits | major | |-----------|----------------|---------|---------| | 201 | Mechanics | 4 | Physics | | 202 | Thermodynamics | 4 | Physics | enrollments: | student_id | course_id | semester | grade | |------------|-----------|-------------|-------| | 10 | 201 | 2024-Spring | A |

students: | student_id | name | major | |------------|------|---------| | 20 | Noor | Biology | courses: | course_id | name | credits | major | |-----------|----------|---------|---------| | 301 | Genetics | 3 | Biology | | 302 | Ecology | 3 | Biology | enrollments: | student_id | course_id | semester | grade | |------------|-----------|-------------|-------| | 20 | 301 | 2023-Fall | A | | 20 | 301 | 2024-Spring | B | | 20 | 302 | 2024-Spring | A |