Watch the video solution for Find Top Scoring Students, a medium level problem involving Database. This walkthrough by Everyday Data Science has 574 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
Table: students
+-------------+----------+ | Column Name | Type | +-------------+----------+ | student_id | int | | name | varchar | | major | varchar | +-------------+----------+ student_id is the primary key (combination of columns with unique values) for this table. Each row of this table contains the student ID, student name, and their major.
Table: courses
+-------------+----------+ | Column Name | Type | +-------------+----------+ | course_id | int | | name | varchar | | credits | int | | major | varchar | +-------------+----------+ course_id is the primary key (combination of columns with unique values) for this table. Each row of this table contains the course ID, course name, the number of credits for the course, and the major it belongs to.
Table: enrollments
+-------------+----------+ | Column Name | Type | +-------------+----------+ | student_id | int | | course_id | int | | semester | varchar | | grade | varchar | +-------------+----------+ (student_id, course_id, semester) is the primary key (combination of columns with unique values) for this table. Each row of this table contains the student ID, course ID, semester, and grade received.
Write a solution to find the students who have taken all courses offered in their major and have achieved a grade of A in all these courses.
Return the result table ordered by student_id in ascending order.
The result format is in the following example.
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 | +-----------+-----------------+---------+------------------+ | 101 | Algorithms | 3 | Computer Science | | 102 | Data Structures | 3 | Computer Science | | 103 | Calculus | 4 | Mathematics | | 104 | Linear Algebra | 4 | Mathematics | +-----------+-----------------+---------+------------------+
enrollments table:
+------------+-----------+----------+-------+ | student_id | course_id | semester | grade | +------------+-----------+----------+-------+ | 1 | 101 | Fall 2023| A | | 1 | 102 | Fall 2023| A | | 2 | 101 | Fall 2023| B | | 2 | 102 | Fall 2023| A | | 3 | 103 | Fall 2023| A | | 3 | 104 | Fall 2023| A | | 4 | 103 | Fall 2023| A | | 4 | 104 | Fall 2023| B | +------------+-----------+----------+-------+
Output:
+------------+ | student_id | +------------+ | 1 | | 3 | +------------+
Explanation:
Note: Output table is ordered by student_id in ascending order.
Problem Overview: The database stores students and their exam results across different subjects. Your task is to identify the students who achieve the highest scores by combining data across related tables and aggregating their results.
Approach 1: Correlated Subquery per Student (O(n²) time, O(1) space)
A straightforward approach computes each student's total or aggregated score and compares it against others using a correlated subquery. For every student, run a subquery that calculates the maximum score among all students and filter those that match it. This works but repeatedly scans the score table, which becomes expensive as the dataset grows. Use this approach only for small datasets or when writing a quick prototype.
Approach 2: Join Tables + Grouping (O(n log n) time, O(n) space)
The efficient SQL pattern joins the student table with the scores table using JOIN, then aggregates results using GROUP BY. Compute the total or relevant score metric with an aggregate function such as SUM() or AVG(). After aggregation, identify the highest value using a subquery or ordering with ORDER BY and filtering logic. This reduces repeated scans and leverages the database engine’s optimized grouping and sorting operations.
This approach relies on core relational concepts: SQL JOIN to combine related tables and GROUP BY to aggregate scores per student. Many implementations also use a subquery with MAX() or ranking logic, which ties into patterns used in SQL aggregation problems.
Recommended for interviews: The Join Tables + Grouping approach is the expected solution. It demonstrates that you understand relational modeling, aggregation, and filtering aggregated results. Showing awareness of the naive correlated subquery approach proves you understand the baseline, but using grouping and aggregation shows stronger SQL problem‑solving skills.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Correlated Subquery per Student | O(n²) | O(1) | Quick prototype or very small datasets where readability matters more than efficiency |
| Join Tables + Grouping | O(n log n) | O(n) | General case for SQL interview questions and production queries using aggregation |
