Distances Traveled

#Lyft

Find the top 10 users that have traveled the greatest distance. Output their id, name and a total distance traveled.

table name: lyft_users


table name: lyft_rides_log


Solution:

select top 10 u.id,u.name ,sum(r.distance) as distance from lyft_users as u
join lyft_rides_log as r on u.id = r.user_id
group by u.id,u.name
order by distance desc

Output:


SQL Script:

USE [StrataScratch]
GO
CREATE TABLE [dbo].[lyft_rides_log](
	[id] [int] NOT NULL,
	[user_id] [int] NULL,
	[distance] [int] NULL,
 CONSTRAINT [PK_lyft_rides_log] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[lyft_users](
	[id] [int] NOT NULL,
	[name] [varchar](50) NULL,
 CONSTRAINT [PK_lyft_users] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (101, 8, 93)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (102, 40, 56)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (103, 28, 83)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (104, 33, 83)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (105, 1, 87)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (106, 32, 49)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (107, 3, 5)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (108, 23, 37)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (109, 31, 62)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (110, 1, 35)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (111, 41, 89)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (112, 19, 64)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (113, 49, 57)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (114, 28, 68)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (115, 48, 94)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (116, 50, 89)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (117, 48, 29)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (118, 13, 16)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (119, 24, 58)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (120, 25, 19)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (121, 39, 13)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (122, 36, 10)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (123, 37, 38)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (124, 32, 76)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (125, 34, 61)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (126, 37, 10)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (127, 11, 61)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (128, 47, 35)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (129, 46, 17)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (130, 15, 8)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (131, 11, 36)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (132, 31, 24)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (133, 7, 96)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (134, 34, 64)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (135, 2, 75)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (136, 45, 11)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (137, 48, 58)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (138, 15, 92)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (139, 47, 88)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (140, 18, 27)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (141, 34, 67)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (142, 47, 70)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (143, 24, 52)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (144, 26, 98)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (145, 20, 45)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (146, 27, 60)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (147, 26, 94)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (148, 10, 90)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (149, 12, 63)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (150, 9, 43)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (151, 36, 18)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (152, 12, 11)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (153, 44, 76)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (154, 9, 93)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (155, 14, 82)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (156, 28, 26)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (157, 39, 68)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (158, 5, 92)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (159, 46, 91)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (160, 14, 66)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (161, 8, 47)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (162, 44, 52)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (163, 21, 81)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (164, 11, 69)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (165, 38, 82)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (166, 23, 42)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (167, 34, 85)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (168, 12, 30)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (169, 43, 85)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (170, 20, 30)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (171, 20, 50)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (172, 25, 74)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (173, 25, 96)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (174, 8, 74)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (175, 50, 46)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (176, 43, 77)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (177, 11, 40)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (178, 17, 90)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (179, 1, 78)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (180, 20, 25)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (181, 27, 31)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (182, 17, 91)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (183, 8, 29)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (184, 42, 85)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (185, 43, 95)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (186, 17, 24)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (187, 15, 42)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (188, 47, 37)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (189, 9, 15)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (190, 42, 71)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (191, 43, 9)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (192, 12, 53)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (193, 49, 73)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (194, 25, 50)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (195, 32, 85)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (196, 9, 55)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (197, 47, 98)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (198, 43, 9)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (199, 14, 66)
GO
INSERT [dbo].[lyft_rides_log] ([id], [user_id], [distance]) VALUES (200, 2, 39)
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (1, N'Dustin Smith')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (2, N'Jay Ramirez')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (3, N'Joseph Cooke')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (4, N'Melinda Young')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (5, N'Sean Parker')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (6, N'Ian Foster')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (7, N'Christopher Schmitt')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (8, N'Patrick Gutierrez')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (9, N'Dennis Douglas')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (10, N'Brenda Morris')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (11, N'Jeffery Hernandez')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (12, N'David Rice')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (13, N'Charles Foster')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (14, N'Keith Perez DVM')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (15, N'Dean Cuevas')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (16, N'Melissa Bishop')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (17, N'Alexander Howell')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (18, N'Austin Robertson')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (19, N'Sherri Mcdaniel')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (20, N'Nancy Nguyen')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (21, N'Melody Ball')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (22, N'Christopher Stokes')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (23, N'Joseph Hamilton')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (24, N'Kevin Fischer')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (25, N'Crystal Berg')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (26, N'Barbara Larson')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (27, N'Jacqueline Heath')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (28, N'Eric Gardner')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (29, N'Daniel Kennedy')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (30, N'Kaylee Sims')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (31, N'Shannon Green')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (32, N'Stacy Collins')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (33, N'Donna Ortiz')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (34, N'Jennifer Simmons')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (35, N'Michael Gill')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (36, N'Alyssa Shaw')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (37, N'Destiny Clark')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (38, N'Thomas Lara')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (39, N'Mark Diaz')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (40, N'Stacy Bryant')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (41, N'Howard Rose')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (42, N'Brian Schwartz')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (43, N'Kimberly Potter')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (44, N'Cassidy Ryan')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (45, N'Benjamin Mcbride')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (46, N'Elizabeth Ward')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (47, N'Christina Price')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (48, N'Pamela Cox')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (49, N'Jessica Peterson')
GO
INSERT [dbo].[lyft_users] ([id], [name]) VALUES (50, N'Michael Nelson')
GO

Comments (0)
Questions List