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:
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:
Note: Output table is ordered by student_id in ascending order.
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.
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.
MySQL
| 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 |
leetcode 3188 | hard | find top scoring student II | sql scenario based interview questions • DEwithDhairy • 1,544 views views
Watch 1 more video solutions →Practice Find Top Scoring Students II with our built-in code editor and test cases.
Practice on FleetCodePractice this problem
Open in Editor