Find Top Scoring Students II - Solution & Explanation

HardPremiumFree on FleetCodeDatabase7 min read

Problem Statement

Table: students

+-------------+----------+
| Column Name | Type     | 
+-------------+----------+
| student_id  | int      |
| name        | varchar  |
| major       | varchar  |
+-------------+----------+
student_id is the primary key for this table. 
Each row contains the student ID, student name, and their major.

Table: courses

+-------------+-------------------+
| Column Name | Type              |       
+-------------+-------------------+
| course_id   | int               |    
| name        | varchar           |      
| credits     | int               |           
| major       | varchar           |       
| mandatory   | enum              |      
+-------------+-------------------+
course_id is the primary key for this table. 
mandatory is an enum type of ('Yes', 'No').
Each row contains the course ID, course name, credits, major it belongs to, and whether the course is mandatory.

Table: enrollments

+-------------+----------+
| Column Name | Type     | 
+-------------+----------+
| student_id  | int      |
| course_id   | int      |
| semester    | varchar  |
| grade       | varchar  |
| GPA         | decimal  | 
+-------------+----------+
(student_id, course_id, semester) is the primary key (combination of columns with unique values) for this table.
Each row contains the student ID, course ID, semester, and grade received.

Write a solution to find the students who meet 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.

Example:

Input:

students table:

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

courses table:

 +-----------+-------------------+---------+------------------+----------+
 | 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 table:

 +------------+-----------+-------------+-------+-----+
 | student_id | course_id | semester    | grade | GPA |
 +------------+-----------+-------------+-------+-----+
 | 1          | 101       | Fall 2023   | A     | 4.0 |
 | 1          | 102       | Spring 2023 | A     | 4.0 |
 | 1          | 105       | Spring 2023 | A     | 4.0 |
 | 1          | 107       | Fall 2023   | B     | 3.5 |
 | 2          | 101       | Fall 2023   | A     | 4.0 |
 | 2          | 102       | Spring 2023 | B     | 3.0 |
 | 3          | 103       | Fall 2023   | A     | 4.0 |
 | 3          | 104       | Spring 2023 | A     | 4.0 |
 | 3          | 106       | Spring 2023 | A     | 4.0 |
 | 3          | 108       | Fall 2023   | B     | 3.5 |
 | 4          | 103       | Fall 2023   | B     | 3.0 |
 | 4          | 104       | Spring 2023 | B     | 3.0 |
 +------------+-----------+-------------+-------+-----+

Output:

 +------------+
 | student_id |
 +------------+
 | 1          |
 | 3          |
 +------------+

Explanation:

Alice (student_id 1) is a Computer Science major and has taken both Algorithms and Data Structures, receiving an A in both. She has also taken Machine Learning and Operating Systems as electives, receiving an A and B respectively.
Bob (student_id 2) is a Computer Science major but did not receive an A in all required courses.
Charlie (student_id 3) is a Mathematics major and has taken both Calculus and Linear Algebra, receiving an A in both. He has also taken Probability and Statistics as electives, receiving an A and B respectively.
David (student_id 4) is a Mathematics major but did not receive an A in all required courses.

Note: Output table is ordered by student_id in ascending order.

Approach Overview

Problem Overview: You are given database tables containing students and their exam scores. The goal is to identify the top scoring students based on specific scoring conditions. The result typically requires combining multiple tables, aggregating scores, and filtering only the students who satisfy the “top score” criteria.

Since the information is spread across relational tables, the solution relies heavily on SQL operations such as JOIN, GROUP BY, and conditional filtering. Understanding how aggregation interacts with filtering clauses like HAVING is key for solving this problem efficiently.

Approach 1: Basic Joining + Aggregation (O(n) time, O(1) extra space)

The first step is combining the relevant tables using SQL JOIN operations. Typically, the score records reference student identifiers, so you join the student table with the score table to bring names and scores into the same result set. After joining, you aggregate scores using GROUP BY student_id. This lets you compute metrics such as total score or maximum score per student. The approach scans the dataset once and relies on the database engine’s grouping implementation.

Approach 2: Joining + Grouping + Conditional Filtering (O(n) time, O(1) extra space)

The optimal solution extends the basic aggregation by adding conditional filtering using HAVING. After joining the tables and grouping by the student identifier, compute the relevant aggregate (for example SUM(score) or MAX(score)). Then apply a condition that keeps only the students whose aggregated score matches the top scoring criteria. This condition can use expressions such as HAVING aggregate_value = (SELECT MAX(...)) or conditional aggregation with CASE. The database performs a single grouped pass over the data, making the approach efficient for large datasets.

Conditional aggregation is often implemented with expressions like SUM(CASE WHEN condition THEN score ELSE 0 END). This allows filtering based on category-specific performance without additional joins. When the dataset grows large, pushing as much logic as possible into the grouped query avoids unnecessary intermediate result sets.

Problems like this frequently appear in SQL interview rounds because they test your understanding of relational data processing. You need to combine concepts from SQL, table joins, and GROUP BY aggregation while applying correct filtering logic.

Recommended for interviews: The Joining + Grouping + Conditional Filtering approach is what interviewers expect. Starting with a simple join and aggregation shows you understand relational structure. Adding HAVING or conditional aggregation to isolate top performers demonstrates real SQL proficiency and query optimization awareness.

Solution

First, we filter out students with an average GPA greater than or equal to 2.5 and record them in table T.

Next, we join the T table with the students table based on student_id, then join with the courses table based on major, and finally perform a left join with the enrollments table based on student_id and course_id.

After that, we group by student ID, use the HAVING clause to filter out students who meet the conditions, and finally sort by student ID.

Code

MySQL

Try this approach in the editor →

Detailed Complexity Analysis

Approach	Time	Space	When to Use
Basic Joining + Aggregation	O(n)	O(1)	When computing totals or maximum scores per student before applying additional logic
Joining + Grouping + Conditional Filtering	O(n)	O(1)	General case for selecting only top scoring students based on aggregated metrics
Join + Aggregation + Subquery for Max	O(n)	O(1)	Useful when filtering against the global maximum score computed in a nested query

Video Solution

leetcode 3188 | hard | find top scoring student II | sql scenario based interview questions • DEwithDhairy • 1,583 views views

Watch 1 more video solutions →

Frequently Asked Questions

Is Find Top Scoring Students II easy or hard?

Find Top Scoring Students II is categorized as a Hard database problem. The difficulty comes from combining multiple SQL concepts such as joins, aggregation, and conditional filtering. Correctly structuring GROUP BY and HAVING clauses is essential to produce the expected result.

Find Top Scoring Students II Python/Java solution

Because this is a database problem, the primary solution is written in SQL (commonly MySQL). In application code such as Python or Java, the query is executed through a database connector and the result set contains the filtered top scoring students returned by the SQL statement.

How to solve Find Top Scoring Students II in O(n)?

Use a JOIN to combine student information with score records, then GROUP BY the student identifier to compute aggregates such as total or maximum score. Apply conditional filtering with HAVING or a MAX subquery to keep only the highest scoring students. The database performs one grouped scan over the dataset, giving O(n) time complexity.

What is the best approach for Find Top Scoring Students II?

The most effective solution uses SQL joining, grouping, and conditional filtering. Join the student and score tables, group records by student identifier, compute aggregated scores, and filter the results using HAVING or a subquery that identifies the maximum score. This approach processes the dataset in a single grouped pass and is the standard interview solution.

Is Find Top Scoring Students II asked at Google/Amazon/Meta?

SQL aggregation and filtering problems similar to Find Top Scoring Students II frequently appear in interviews at companies like Amazon, Google, and Meta. They test the ability to work with relational data, joins, GROUP BY logic, and conditional filtering using HAVING or subqueries.

What data structure is used in Find Top Scoring Students II?

The problem relies on relational database tables rather than in-memory data structures. SQL operations such as JOIN, GROUP BY, and aggregate functions act on these tables. Internally, database engines often use hash aggregation or sorting structures to compute grouped results efficiently.

What is the time complexity of Find Top Scoring Students II?

The typical SQL solution runs in O(n) time where n is the number of score records scanned by the query. The database engine performs joins and grouping operations during the scan. Extra memory usage is minimal because aggregation happens inside the database execution plan.

Ready to solve this problem?

Practice Find Top Scoring Students II with our built-in code editor and test cases.

Practice on FleetCode

Combine Two Tables

Second Highest Salary

Problem Info

DifficultyHard

Acceptance37.8%

Approaches1

Reading time7 min

Practice this problem

Open in Editor