Find Top Scoring Students II - Problem

You are given three tables: students, courses, and enrollments.

Write a solution to find the students who meet all of the following criteria:

  • Have taken all mandatory courses and at least two elective courses offered in their major
  • Achieved a grade of A in all mandatory courses and at least B in elective courses
  • Maintained an average GPA of at least 2.5 across all their courses (including those outside their major)

Return the result table ordered by student_id in ascending order.

Table Schema

students
Column Name Type Description
student_id PK int Primary key, unique student identifier
name varchar Student name
major varchar Student's major field of study
Primary Key: student_id
courses
Column Name Type Description
course_id PK int Primary key, unique course identifier
name varchar Course name
credits int Number of credits for the course
major varchar Major that this course belongs to
mandatory enum Whether course is mandatory ('Yes' or 'No')
Primary Key: course_id
enrollments
Column Name Type Description
student_id PK int Foreign key to students table
course_id PK int Foreign key to courses table
semester PK varchar Semester when course was taken
grade varchar Grade received (A, B, C, etc.)
GPA decimal GPA points for this course
Primary Key: (student_id, course_id, semester)

Input & Output

Example 1 — Students Meeting All Criteria
Input Tables:
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 Calculus 4 Mathematics Yes
104 Linear Algebra 4 Mathematics Yes
105 Machine Learning 3 Computer Science No
106 Probability 3 Mathematics No
107 Operating Systems 3 Computer Science No
108 Statistics 3 Mathematics No
enrollments
student_id course_id semester grade GPA
1 101 Fall 2023 A 4
1 102 Spring 2023 A 4
1 105 Spring 2023 A 4
1 107 Fall 2023 B 3.5
2 101 Fall 2023 A 4
2 102 Spring 2023 B 3
3 103 Fall 2023 A 4
3 104 Spring 2023 A 4
3 106 Spring 2023 A 4
3 108 Fall 2023 B 3.5
4 103 Fall 2023 B 3
4 104 Spring 2023 B 3
Output:
student_id
1
3
💡 Note:

Alice (student_id 1) is a Computer Science major who took both mandatory courses (Algorithms, Data Structures) with grade A, plus 2 electives (Machine Learning: A, Operating Systems: B) with B+ grades, maintaining overall GPA of 3.875.

Charlie (student_id 3) is a Mathematics major who took both mandatory courses (Calculus, Linear Algebra) with grade A, plus 2 electives (Probability: A, Statistics: B) with B+ grades, maintaining overall GPA of 3.875.

Bob failed because he got B in mandatory Data Structures. David failed because he got B in mandatory courses.
Example 2 — No Students Qualify
Input Tables:
students
student_id name major
1 Emma Physics
courses
course_id name credits major mandatory
201 Quantum Physics 4 Physics Yes
202 Mechanics 4 Physics Yes
enrollments
student_id course_id semester grade GPA
1 201 Fall 2023 B 3
Output:
student_id
💡 Note:

Emma doesn't qualify because she only took one mandatory course (missing Mechanics), got grade B instead of A in Quantum Physics, and has no elective courses completed.

Constraints

  • 1 ≤ student_id ≤ 1000
  • 1 ≤ course_id ≤ 1000
  • mandatory is either 'Yes' or 'No'
  • grade is one of 'A', 'B', 'C', 'D', 'F'
  • 1.0 ≤ GPA ≤ 4.0

Visualization

Tap to expand
Find Top Scoring Students II INPUT DATA ID Name GPA 1 Alice 3.8 2 Bob 2.9 3 Carol 2.3 Courses Code Name Type M101 Math Mandatory M102 Physics Mandatory E201 Art Elective Enrollments Student Course Grade Alice M101 A Alice M102 A Alice E201,E202 B,A Bob M101 B ALGORITHM STEPS 1 Filter by GPA Keep students with GPA >= 2.5 WHERE gpa >= 2.5 2 Check Mandatory All mandatory courses grade A COUNT(mandatory) = TOTAL AND grade='A' 3 Check Electives At least 2 electives B or higher COUNT(elective) >= 2 WHERE grade IN (A,B) 4 Join Results Intersect all conditions cond1 INTERSECT cond2 INTERSECT cond3 FINAL RESULT Top Scoring Students A Alice GPA: 3.8 Mandatory: 2/2 (A) Electives: 3 (B+) Criteria Met OK GPA >= 2.5 OK All mandatory = A OK 2+ electives B+ Output [{id: 1, name: "Alice"}] Key Insight: Use SQL subqueries with HAVING clauses to filter each condition separately, then INTERSECT results. Count mandatory courses dynamically to handle varying totals. Grade comparison uses ASCII ordering: 'A' < 'B' ensures B or higher filtering works correctly. TutorialsPoint - Find Top Scoring Students II | Optimal Solution

Related Problems

Asked in
Amazon 28 Google 22 Microsoft 18
25.4K Views
Medium Frequency
~25 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen