Problem

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 inascending order.

The result format is in the following example.

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

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:
* Alice (student_id 1) is a Computer Science major and has taken both "Algorithms" and "Data Structures", receiving an 'A' in both.
* 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.
* 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.

Solution

Method 1 – SQL: Window Functions and Ranking

Intuition

To find the top scoring students, we need to sum the scores for each student and select those with the highest total. If there are ties, all students with the top score should be included.

Approach

  1. Join the students and exams tables on student_id.
  2. Group by student_id and name, and sum the scores.
  3. Use a window function to rank students by total score.
  4. Select students with the highest total score.
  5. Return their student_id and name.

Code

1
2
3
4
5
6
7
8
9

SELECT student_id, name
FROM (
    SELECT s.student_id, s.name, SUM(e.score) AS total_score,
           RANK() OVER (ORDER BY SUM(e.score) DESC) AS rnk
    FROM students s
    JOIN exams e ON s.student_id = e.student_id
    GROUP BY s.student_id, s.name
) t
WHERE rnk = 1;
1
2
3
4
5
6
7
8
9

SELECT student_id, name
FROM (
    SELECT s.student_id, s.name, SUM(e.score) AS total_score,
           RANK() OVER (ORDER BY SUM(e.score) DESC) AS rnk
    FROM students s
    JOIN exams e ON s.student_id = e.student_id
    GROUP BY s.student_id, s.name
) t
WHERE rnk = 1;
1
2
3
4
5
6
7
8

import pandas as pd

def find_top_scoring_students(students: pd.DataFrame, exams: pd.DataFrame) -> pd.DataFrame:
    merged = students.merge(exams, on='student_id')
    scores = merged.groupby(['student_id', 'name'])['score'].sum().reset_index()
    max_score = scores['score'].max()
    res = scores[scores['score'] == max_score][['student_id', 'name']]
    return res

Complexity

  • ⏰ Time complexity: O(n) — n = number of students and exam records.
  • 🧺 Space complexity: O(n) — For storing intermediate results.