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: Finding Score RangeInput TableIDNameA1A2A31Alice9085882Bob7882753Charlie959297ProcessingCalculate Totals:Alice: 90+85+88 = 263Bob: 78+82+75 = 235Charlie: 95+92+97 = 284Find Range:MAX: 284, MIN: 235Difference: 284 - 235 = 49Outputscore_difference49
Understanding the Visualization
1
Input
Student scores across three assignments
2
Calculate
Sum assignments and find MAX/MIN
3
Output
Difference between best and worst performers
Key Takeaway
🎯 Key Insight: Use aggregate functions on calculated columns to efficiently find ranges and differences in datasets

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