Problem

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 inany order .

The result format is in the following example.

Examples

Example 1:

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

    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.

Solution

Method 1 – Aggregate and Difference

Intuition

To find the difference between the highest and lowest total scores, we first compute the total score for each student by summing their three assignment scores, then find the maximum and minimum among these totals, and finally compute their difference.

Approach

  1. For each student, calculate the total score as the sum of assignment1, assignment2, and assignment3.
  2. Find the maximum and minimum total scores among all students.
  3. Return the difference between the maximum and minimum total scores as difference_in_score.

Code

1
2
3
4
5

SELECT MAX(total) - MIN(total) AS difference_in_score
FROM (
  SELECT assignment1 + assignment2 + assignment3 AS total
  FROM Scores
) t;
1
2
3
4
5

SELECT MAX(total) - MIN(total) AS difference_in_score
FROM (
  SELECT assignment1 + assignment2 + assignment3 AS total
  FROM Scores
) t;
1
2
3
4

class Solution:
    def difference_in_score(self, df):
        total = df['assignment1'] + df['assignment2'] + df['assignment3']
        return pd.DataFrame({'difference_in_score': [total.max() - total.min()]})

Complexity

  • ⏰ Time complexity: O(n), where n is the number of students.
  • 🧺 Space complexity: O(1) (excluding input/output).