Class Performance

Class Performance

What is this problem about?

This is a SQL / Database problem where you are given a table of student scores across different classes. You need to calculate the difference between the highest total score in any class and the lowest total score in any class.

Why is this asked in interviews?

Google asks this to test your proficiency in SQL aggregation and subqueries. It evaluates your ability to use GROUP BY, SUM(), MAX(), and MIN() functions in combination. This is a fundamental skill for data analysis and backend engineering roles.

Algorithmic pattern used

The pattern is Aggregation and Scalar Subquery.

1. First, you group the data by class_id and calculate the SUM(score) for each class.
2. Then, you treat this result as a temporary table (or a Common Table Expression / CTE).
3. Finally, you select the MAX(total_score) - MIN(total_score) from that temporary table.

Example explanation

Table Scores:

student_id	class_id	score
1	A	80
2	A	90
3	B	70
4	B	60

1. Grouped Sums: Class A = 170, Class B = 130.
2. Max = 170, Min = 130.
3. Difference = 40.

Common mistakes candidates make

A common error is trying to calculate the max and min of individual student scores instead of the total score per class. Another is forgetting to group by the correct column. In some SQL dialects, you also need to ensure you're handling possible NULL values if the table could be empty.

Interview preparation tip

Practice using CTEs (WITH clauses) in SQL. They make complex multi-step queries much more readable and easier to debug than nested subqueries.