Class Performance

#10310

You are given a table containing assignment scores of students in a class. Write a query that identifies the largest difference in total score  of all assignments.

Output just the difference in total score (sum of all 3 assignments) between a student with the highest score and a student with the lowest score.

table name: box_scores

Solution:

with cte as(
select top 1 
sum(assignment1+assignment2+assignment3) as total,
(select top 1 sum(assignment1+assignment2+assignment3) as totalMin from box_scores group by student order by totalMin ) as totalMin
from box_scores
group by student
order by total desc
)
select total-totalMin from cte

Output:

SQL Script:

USE [StrataScratch]
GO
/****** Object:  Table [dbo].[box_scores]    Script Date: 12-01-2024 15:05:17 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[box_scores](
	[id] [int] NULL,
	[student] [varchar](50) NULL,
	[assignment1] [int] NULL,
	[assignment2] [int] NULL,
	[assignment3] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[box_scores] ([id], [student], [assignment1], [assignment2], [assignment3]) VALUES (1, N'Karen Austin', 90, 98, 80)
GO
INSERT [dbo].[box_scores] ([id], [student], [assignment1], [assignment2], [assignment3]) VALUES (2, N'Matthew Ward', 85, 90, 70)
GO
INSERT [dbo].[box_scores] ([id], [student], [assignment1], [assignment2], [assignment3]) VALUES (3, N'John Miranda', 87, 75, 90)
GO
INSERT [dbo].[box_scores] ([id], [student], [assignment1], [assignment2], [assignment3]) VALUES (4, N'Scott Taylor', 60, 55, 70)
GO
INSERT [dbo].[box_scores] ([id], [student], [assignment1], [assignment2], [assignment3]) VALUES (5, N'Tom Saunders', 80, 88, 90)
GO
INSERT [dbo].[box_scores] ([id], [student], [assignment1], [assignment2], [assignment3]) VALUES (6, N'Natalie Palmer', 55, 70, 65)
GO
INSERT [dbo].[box_scores] ([id], [student], [assignment1], [assignment2], [assignment3]) VALUES (7, N'James Burgess', 72, 70, 75)
GO
INSERT [dbo].[box_scores] ([id], [student], [assignment1], [assignment2], [assignment3]) VALUES (8, N'Tracie Lopez', 89, 85, 70)
GO
INSERT [dbo].[box_scores] ([id], [student], [assignment1], [assignment2], [assignment3]) VALUES (9, N'Donna Jimenez', 91, 90, 98)
GO
INSERT [dbo].[box_scores] ([id], [student], [assignment1], [assignment2], [assignment3]) VALUES (10, N'Rachael Smith', 76, 66, 80)
GO