Class Performance - Problem

You are given a table Scores that contains student information and their assignment scores.

Table: Scores

Column NameType
student_idint
student_namevarchar
assignment1int
assignment2int
assignment3int

student_id is the column of unique values for this table.

Task: Write a SQL solution to calculate the difference between the highest total score and the lowest total score among all students. The total score for each student is the sum of all three assignments.

Return the result in any order.

Table Schema

Scores
Column Name Type Description
student_id PK int Unique identifier for each student
student_name varchar Name of the student
assignment1 int Score for assignment 1
assignment2 int Score for assignment 2
assignment3 int Score for assignment 3
Primary Key: student_id
Note: Each row represents one student with their three assignment scores

Input & Output

Example 1 — Basic Score Difference
Input Table:
student_id student_name assignment1 assignment2 assignment3
1 Alice 90 85 88
2 Bob 78 82 75
3 Charlie 95 92 97
Output:
score_difference
49
💡 Note:

Alice's total: 90+85+88 = 263, Bob's total: 78+82+75 = 235, Charlie's total: 95+92+97 = 284. The difference between highest (284) and lowest (235) is 49.
Example 2 — Same Scores
Input Table:
student_id student_name assignment1 assignment2 assignment3
1 Alice 80 80 80
2 Bob 80 80 80
Output:
score_difference
0
💡 Note:

Both students have the same total score of 240, so the difference between highest and lowest is 0.
Example 3 — Single Student
Input Table:
student_id student_name assignment1 assignment2 assignment3
1 Alice 90 85 88
Output:
score_difference
0
💡 Note:

With only one student, both the highest and lowest scores are the same (263), resulting in a difference of 0.

Constraints

  • 1 ≤ student_id ≤ 1000
  • student_name consists of lowercase English letters
  • 0 ≤ assignment1, assignment2, assignment3 ≤ 100
  • At least one student record exists in the table

Visualization

Tap to expand
Class Performance - Score Analysis INPUT Scores Table name a1 a2 a3 total Alice 85 90 88 263 Bob 70 75 80 225 Carol 95 92 98 285 SELECT MAX(a1+a2+a3) - MIN(a1+a2+a3) FROM Scores; Columns: assignment1, assignment2, assignment3 ALGORITHM STEPS 1 Calculate Total Sum all 3 assignments a1 + a2 + a3 2 Find Maximum Get highest total score MAX=285 3 Find Minimum Get lowest total score MIN=225 4 Compute Difference Subtract MIN from MAX 285 - 225 = 60 Score Range FINAL RESULT Score Difference 60 points Score Distribution Carol: 285 (MAX) Alice: 263 Bob: 225 (MIN) 60 OUTPUT: { difference: 60 } Key Insight: The optimal SQL solution uses aggregate functions MAX() and MIN() on calculated totals. By computing (a1+a2+a3) inline, we avoid creating temporary tables or using subqueries. Time Complexity: O(n) - single pass through all records. Space Complexity: O(1) - constant extra space. TutorialsPoint - Class Performance | Optimal Solution

Related Problems

Asked in
Amazon 23 Microsoft 18 Google 15
28.4K Views
Medium Frequency
~8 min Avg. Time
892 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen