Find Top Scoring Students II - Practice Coding Problems

Find Top Scoring Students II - Problem

Database Hard

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

Understanding the Visualization

Join

Connect students, courses, and enrollments

Aggregate

Calculate GPA and count courses by type and grade

Filter

Apply all criteria to find qualifying students

Key Takeaway

🎯 Key Insight: Use CTEs to break complex multi-criteria filtering into manageable aggregation steps

Asked in

A Amazon 28 G Google 22 M Microsoft 18

Use CTEs with aggregation to calculate student statistics, then apply multiple HAVING conditions to filter students meeting all academic criteria simultaneously.

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)

Common Approaches

Approach	Time	Space	Notes
✓ Common Table Expressions with Aggregation	O(n * m)	O(n)	Use CTEs to break down complex multi-condition filtering into manageable steps

Common Table Expressions with Aggregation — Algorithm Steps

Step 1: Join all tables and calculate overall GPA per student
Step 2: Check mandatory course completion with grade A
Step 3: Count elective courses with grade B+ in student's major
Step 4: Combine all criteria with HAVING clauses

Visualization

Tap to expand

Step-by-Step Walkthrough

Join Tables

Connect students, courses, enrollments

Aggregate

Calculate GPA and count courses by type

Filter

Apply all criteria with HAVING

Code -

solution.c — C

Time & Space Complexity

Time Complexity

⏱️

O(n * m)

Where n is students and m is enrollments

✓ Linear Growth

Space Complexity

O(n)

Temporary CTE storage

⚡ Linearithmic Space

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

Table Schema

Input & Output

Constraints

Visualization

Related Problems

Table Schema

Common Approaches

Common Table Expressions with Aggregation — Algorithm Steps

Visualization

Code -

Time & Space Complexity

Select Compiler