Skip to main content
Talentd

Class Performance - Solution & Explanation

MediumPremiumFree on FleetCodeDatabase4 min readAsked at: Google
Practice this problem

Problem Statement

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.

Approach Overview

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.

Solution

We can use the MAX and MIN functions to get the maximum and minimum sums of assignment1, assignment2, and assignment3, respectively. Then, subtract the minimum from the maximum.

Code

MySQL

MySQL

Try this approach in the editor ā†’

Detailed Complexity Analysis

ApproachTimeSpaceWhen 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 aggregationO(n)O(k)Standard SQL solution for computing range within grouped categories

Video Solution

Leetcode MEDIUM 2989 - Class Performance- Solved by Everyday Data Science | MIN() MAX() CTE ā€¢ Everyday Data Science ā€¢ 530 views views

Watch 1 more video solutions ā†’

Frequently Asked Questions

Is Class Performance easy or hard?
Class Performance is considered a medium-level SQL problem. The logic is simple once you recognize it as a grouped range calculation, but it tests whether you understand SQL aggregation functions and grouping semantics.
Class Performance Python/Java solution
This problem is primarily a SQL database query rather than an algorithm implemented in Python or Java. The solution relies on SQL aggregation using GROUP BY with MAX(score) and MIN(score) to compute the score range per class.
How to solve Class Performance in O(n)?
Use a GROUP BY query on class_id and compute MAX(score) and MIN(score) within each group. The class performance is the difference between those two values. SQL engines compute aggregates during a single table scan, which keeps the complexity linear.
What is the best approach for Class Performance?
The best approach uses SQL aggregation with MAX(score) and MIN(score) grouped by class_id. The performance metric is computed as MAX(score) - MIN(score) for each class. This runs in O(n) time because the database scans the rows once while maintaining aggregates for each group.
Is Class Performance asked at Google/Amazon/Meta?
SQL aggregation problems similar to Class Performance frequently appear in data engineering and analytics interviews at companies like Amazon, Meta, and Google. They test your ability to summarize grouped data using functions such as MAX, MIN, COUNT, and AVG.
What data structure is used in Class Performance?
Conceptually, the database engine uses a hash-based or sort-based grouping structure to maintain aggregates for each class_id. For each group it tracks the current maximum and minimum score while scanning the table.
What is the time complexity of Class Performance?
The optimal SQL solution runs in O(n) time where n is the number of rows in the table. The database performs a single scan and maintains aggregates for each class group. Space complexity is O(k) where k is the number of distinct classes stored in the grouping structure.

Ready to solve this problem?

Practice Class Performance with our built-in code editor and test cases.

Practice on FleetCode
Previous

Combine Two Tables

Next

Second Highest Salary