Write an SQL query to report the distance
traveled by each user. Return the result table ordered by travelled_distance in
descending order, if two or more users traveled the same distance, order them
by their name in ascending order.
table name: Users, Rides
Solution:
select u.name, sum(isnull(r.distance,0)) as travelled_distance from [Users] as u
left join Rides as r on u.id = r.user_id
group by u.name,u.id
order by travelled_distance desc,u.name
Output:
SQL Script:
CREATE TABLE [dbo].[Rides](
[id][int] NULL,
[user_id][int] NULL,
[distance][int] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[Users] Script Date: 23-03-2023 13:30:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Users](
[id]
[int] NULL,
[name]
[varchar](30) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[Rides] ([id], [user_id], [distance]) VALUES (1, 1, 120)
GO
INSERT [dbo].[Rides] ([id], [user_id], [distance]) VALUES (2, 2, 317)
GO
INSERT [dbo].[Rides] ([id], [user_id], [distance]) VALUES (3, 3, 222)
GO
INSERT [dbo].[Rides] ([id], [user_id], [distance]) VALUES (4, 7, 100)
GO
INSERT [dbo].[Rides] ([id], [user_id], [distance]) VALUES (5, 13, 312)
GO
INSERT [dbo].[Rides] ([id], [user_id], [distance]) VALUES (6, 19, 50)
GO
INSERT [dbo].[Rides] ([id], [user_id], [distance]) VALUES (7, 7, 120)
GO
INSERT [dbo].[Rides] ([id], [user_id], [distance]) VALUES (8, 19, 400)
GO
INSERT [dbo].[Rides] ([id], [user_id], [distance]) VALUES (9, 7, 230)
GO
INSERT [dbo].[Users] ([id], [name]) VALUES (1, N'Alice')
GO
INSERT [dbo].[Users] ([id], [name]) VALUES (2, N'Bob')
GO
INSERT [dbo].[Users] ([id], [name]) VALUES (3, N'Alex')
GO
INSERT [dbo].[Users] ([id], [name]) VALUES (4, N'Donald')
GO
INSERT [dbo].[Users] ([id], [name]) VALUES (7, N'Lee')
GO
INSERT [dbo].[Users] ([id], [name]) VALUES (13, N'Jonathan')
GO
INSERT [dbo].[Users] ([id], [name]) VALUES (19, N'Elvis')
GO