Watch the video solution for Class Performance, a medium level problem involving Database. This walkthrough by Everyday Data Science has 511 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
Table: Scores
+--------------+---------+ | Column Name | Type | +--------------+---------+ | student_id | int | | student_name | varchar | | assignment1 | int | | assignment2 | int | | assignment3 | int | +--------------+---------+ student_id is column of unique values for this table. This table contains student_id, student_name, assignment1, assignment2, and assignment3.
Write a solution to calculate the difference in the total score (sum of all 3 assignments) between the highest score obtained by students and the lowest score obtained by them.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: Scores table: +------------+--------------+-------------+-------------+-------------+ | student_id | student_name | assignment1 | assignment2 | assignment3 | +------------+--------------+-------------+-------------+-------------+ | 309 | Owen | 88 | 47 | 87 | | 321 | Claire | 98 | 95 | 37 | | 338 | Julian | 100 | 64 | 43 | | 423 | Peyton | 60 | 44 | 47 | | 896 | David | 32 | 37 | 50 | | 235 | Camila | 31 | 53 | 69 | +------------+--------------+-------------+-------------+-------------+ Output +---------------------+ | difference_in_score | +---------------------+ | 111 | +---------------------+ Explanation - student_id 309 has a total score of 88 + 47 + 87 = 222. - student_id 321 has a total score of 98 + 95 + 37 = 230. - student_id 338 has a total score of 100 + 64 + 43 = 207. - student_id 423 has a total score of 60 + 44 + 47 = 151. - student_id 896 has a total score of 32 + 37 + 50 = 119. - student_id 235 has a total score of 31 + 53 + 69 = 153. student_id 321 has the highest score of 230, while student_id 896 has the lowest score of 119. Therefore, the difference between them is 111.
Problem Overview: The goal is to measure how each class performs by computing the difference between the highest and lowest student scores within the same class. The result represents the performance spread for that class.
Approach 1: Aggregation with MAX and MIN (O(n) time, O(k) space)
This problem is a straightforward aggregation task in database queries. You scan the table and group rows by class_id. For each class group, compute the highest score using MAX(score) and the lowest score using MIN(score). The class performance is simply the difference between these two values: MAX(score) - MIN(score). The database engine performs a single pass over the rows and maintains aggregates for each group.
The key insight is that you don't need to compare every pair of students in a class. Tracking the extreme values (maximum and minimum) is enough to determine the spread. SQL aggregation functions handle this efficiently during grouping, which keeps the query simple and scalable even with large datasets.
Implementation uses GROUP BY class_id so each class produces exactly one result row. The computed column can be aliased as performance or a similar descriptive name. Most relational databases, including MySQL, optimize aggregate functions well, so this query runs in linear time relative to the number of rows processed.
This approach relies on core SQL concepts: grouping rows, computing aggregate statistics, and deriving calculated columns from those aggregates. Problems like this are common in analytics workloads where you summarize performance metrics for categories such as classes, teams, or departments.
Recommended for interviews: The aggregation approach with MAX() and MIN() is exactly what interviewers expect for SQL problems involving range calculations. A naive pairwise comparison would be quadratic and unnecessary. Using aggregate functions shows you understand how relational databases compute summaries efficiently with aggregation and grouping.
| Approach | Time | Space | When to Use |
|---|---|---|---|
| Pairwise comparison per class (conceptual brute force) | O(n²) | O(1) | Rarely used; only useful for reasoning about the performance spread definition |
| GROUP BY with MAX and MIN aggregation | O(n) | O(k) | Standard SQL solution for computing range within grouped categories |
