00:00:00

Description

Editorial

Major Curriculum Perfect-A Scholars

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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]

Code

Visualizer

Solutions

14px

Test Cases4

Results

Submissions

courses =

[{"course_id":102,"name":"Data Structures","credits":3,"major":"Computer Science"},{"course_id":101,"name":"Algorithms","credits":3,"major":"Computer Science"},{"course_id":104,"name":"Linear Algebra","credits":4,"major":"Mathematics"},{"course_id":103,"name":"Calculus","credits":4,"major":"Mathematics"}]

students =

[{"student_id":4,"name":"David","major":"Mathematics"},{"student_id":2,"name":"Bob","major":"Computer Science"},{"student_id":1,"name":"Alice","major":"Computer Science"},{"student_id":3,"name":"Charlie","major":"Mathematics"}]

enrollments =

[{"student_id":1,"course_id":101,"semester":"2023-Fall","grade":"A"},{"student_id":4,"course_id":104,"semester":"2023-Fall","grade":"B"},{"student_id":1,"course_id":102,"semester":"2023-Fall","grade":"A"},{"student_id":4,"course_id":103,"semester":"2023-Fall","grade":"A"},{"student_id":2,"course_id":101,"semester":"2023-Fall","grade":"B"},{"student_id":3,"course_id":104,"semester":"2023-Fall","grade":"A"},{"student_id":2,"course_id":102,"semester":"2023-Fall","grade":"A"},{"student_id":3,"course_id":103,"semester":"2023-Fall","grade":"A"}]

Loading problem...

101

00:00:00

Description

Editorial

Major Curriculum Perfect-A Scholars

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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]

Code

Visualizer

Solutions

14px

Test Cases4

Results

Submissions

courses =

students =

enrollments =

Major Curriculum Perfect-A Scholars

Hints

Major Curriculum Perfect-A Scholars

Hints