#2117
What is the last name of the employee or employees who are responsible for the most orders?
table name:shopify_employees
table name:shopify_orders
Solution:
select top 1 with ties e.last_name from shopify_orders as o
join shopify_employees as e
on o.resp_employee_id = e.id
group by e.last_name,o.resp_employee_id
order by count(o.resp_employee_id) desc
Output:
SQL Script:
USE [StrataScratch]
GO
/****** Object: Table [dbo].[shopify_employees] Script Date: 12-01-2024 14:40:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[shopify_employees](
[id] [int] NULL,
[first_name] [varchar](50) NULL,
[last_name] [varchar](50) NULL,
[department] [varchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[shopify_orders] Script Date: 12-01-2024 14:40:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[shopify_orders](
[order_id] [int] NULL,
[shop_id] [int] NULL,
[user_id] [int] NULL,
[order_amount] [int] NULL,
[total_items] [int] NULL,
[payment_method] [varchar](50) NULL,
[created_at] [datetime] NULL,
[resp_employee_id] [int] NULL,
[carrier_id] [float] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[shopify_employees] ([id], [first_name], [last_name], [department]) VALUES (307, N'James ', N'Willard', N'Administration')
GO
INSERT [dbo].[shopify_employees] ([id], [first_name], [last_name], [department]) VALUES (308, N'Paul ', N'Sanchez', N'Administration')
GO
INSERT [dbo].[shopify_employees] ([id], [first_name], [last_name], [department]) VALUES (309, N'Kimberly ', N'Corona', N'Marketing')
GO
INSERT [dbo].[shopify_employees] ([id], [first_name], [last_name], [department]) VALUES (310, N'Mae ', N'Eccleston', N'Marketing')
GO
INSERT [dbo].[shopify_employees] ([id], [first_name], [last_name], [department]) VALUES (311, N'Joseph ', N'Garcia', N'Marketing')
GO
INSERT [dbo].[shopify_employees] ([id], [first_name], [last_name], [department]) VALUES (312, N'Clement ', N'Garner', N'Fulfillment')
GO
INSERT [dbo].[shopify_employees] ([id], [first_name], [last_name], [department]) VALUES (313, N'Betty ', N'Wadsworth', N'Fulfillment')
GO
INSERT [dbo].[shopify_employees] ([id], [first_name], [last_name], [department]) VALUES (314, N'Michael ', N'Wadsworth', N'Fulfillment')
GO
INSERT [dbo].[shopify_employees] ([id], [first_name], [last_name], [department]) VALUES (315, N'Betty ', N'Holmes', N'Fulfillment')
GO
INSERT [dbo].[shopify_employees] ([id], [first_name], [last_name], [department]) VALUES (316, N'Clarence', N'Rodriguez', N'IT')
GO
INSERT [dbo].[shopify_employees] ([id], [first_name], [last_name], [department]) VALUES (317, N'Heather ', N'Dooley', N'IT')
GO
INSERT [dbo].[shopify_employees] ([id], [first_name], [last_name], [department]) VALUES (318, N'Mary ', N'Quintal', N'IT')
GO
INSERT [dbo].[shopify_orders] ([order_id], [shop_id], [user_id], [order_amount], [total_items], [payment_method], [created_at], [resp_employee_id], [carrier_id]) VALUES (1, 53, 746, 224, 2, N'cash', CAST(N'2017-03-13T12:36:56.000' AS DateTime), 313, 485)
GO
INSERT [dbo].[shopify_orders] ([order_id], [shop_id], [user_id], [order_amount], [total_items], [payment_method], [created_at], [resp_employee_id], [carrier_id]) VALUES (2, 92, 925, 90, 1, N'cash', CAST(N'2017-03-03T17:38:52.000' AS DateTime), 315, 483)
GO
INSERT [dbo].[shopify_orders] ([order_id], [shop_id], [user_id], [order_amount], [total_items], [payment_method], [created_at], [resp_employee_id], [carrier_id]) VALUES (3, 44, 861, 144, 1, N'cash', CAST(N'2017-03-14T04:23:56.000' AS DateTime), 312, 485)
GO
INSERT [dbo].[shopify_orders] ([order_id], [shop_id], [user_id], [order_amount], [total_items], [payment_method], [created_at], [resp_employee_id], [carrier_id]) VALUES (4, 18, 935, 156, 1, N'credit_card', CAST(N'2017-03-26T12:43:37.000' AS DateTime), 313, NULL)
GO
INSERT [dbo].[shopify_orders] ([order_id], [shop_id], [user_id], [order_amount], [total_items], [payment_method], [created_at], [resp_employee_id], [carrier_id]) VALUES (5, 18, 883, 156, 1, N'credit_card', CAST(N'2017-03-01T04:35:11.000' AS DateTime), 314, 483)
GO
INSERT [dbo].[shopify_orders] ([order_id], [shop_id], [user_id], [order_amount], [total_items], [payment_method], [created_at], [resp_employee_id], [carrier_id]) VALUES (6, 58, 882, 138, 1, N'credit_card', CAST(N'2017-03-14T15:25:01.000' AS DateTime), 315, 484)
GO
INSERT [dbo].[shopify_orders] ([order_id], [shop_id], [user_id], [order_amount], [total_items], [payment_method], [created_at], [resp_employee_id], [carrier_id]) VALUES (7, 87, 915, 149, 1, N'cash', CAST(N'2017-03-01T21:37:57.000' AS DateTime), 313, 484)
GO
INSERT [dbo].[shopify_orders] ([order_id], [shop_id], [user_id], [order_amount], [total_items], [payment_method], [created_at], [resp_employee_id], [carrier_id]) VALUES (8, 22, 761, 292, 2, N'cash', CAST(N'2017-03-08T02:05:38.000' AS DateTime), 314, 483)
GO
INSERT [dbo].[shopify_orders] ([order_id], [shop_id], [user_id], [order_amount], [total_items], [payment_method], [created_at], [resp_employee_id], [carrier_id]) VALUES (9, 64, 914, 266, 2, N'debit', CAST(N'2017-03-17T20:56:50.000' AS DateTime), 314, 484)
GO
INSERT [dbo].[shopify_orders] ([order_id], [shop_id], [user_id], [order_amount], [total_items], [payment_method], [created_at], [resp_employee_id], [carrier_id]) VALUES (10, 52, 788, 146, 1, N'credit_card', CAST(N'2017-03-30T21:08:26.000' AS DateTime), 313, NULL)
GO
INSERT [dbo].[shopify_orders] ([order_id], [shop_id], [user_id], [order_amount], [total_items], [payment_method], [created_at], [resp_employee_id], [carrier_id]) VALUES (11, 66, 848, 322, 2, N'credit_card', CAST(N'2017-03-26T23:36:40.000' AS DateTime), 315, NULL)
GO
INSERT [dbo].[shopify_orders] ([order_id], [shop_id], [user_id], [order_amount], [total_items], [payment_method], [created_at], [resp_employee_id], [carrier_id]) VALUES (12, 40, 983, 322, 2, N'debit', CAST(N'2017-03-12T17:58:30.000' AS DateTime), 315, 483)
GO
INSERT [dbo].[shopify_orders] ([order_id], [shop_id], [user_id], [order_amount], [total_items], [payment_method], [created_at], [resp_employee_id], [carrier_id]) VALUES (13, 54, 799, 266, 2, N'credit_card', CAST(N'2017-03-16T14:15:34.000' AS DateTime), 315, 485)
GO
INSERT [dbo].[shopify_orders] ([order_id], [shop_id], [user_id], [order_amount], [total_items], [payment_method], [created_at], [resp_employee_id], [carrier_id]) VALUES (14, 100, 709, 111, 1, N'cash', CAST(N'2017-03-22T02:39:49.000' AS DateTime), 312, 485)
GO
INSERT [dbo].[shopify_orders] ([order_id], [shop_id], [user_id], [order_amount], [total_items], [payment_method], [created_at], [resp_employee_id], [carrier_id]) VALUES (15, 87, 849, 447, 3, N'credit_card', CAST(N'2017-03-10T11:23:18.000' AS DateTime), 315, 484)
GO
INSERT [dbo].[shopify_orders] ([order_id], [shop_id], [user_id], [order_amount], [total_items], [payment_method], [created_at], [resp_employee_id], [carrier_id]) VALUES (16, 17, 731, 176, 1, N'cash', CAST(N'2017-03-21T04:23:38.000' AS DateTime), 313, 483)
GO
INSERT [dbo].[shopify_orders] ([order_id], [shop_id], [user_id], [order_amount], [total_items], [payment_method], [created_at], [resp_employee_id], [carrier_id]) VALUES (17, 28, 752, 164, 1, N'credit_card', CAST(N'2017-03-21T12:09:07.000' AS DateTime), 314, 483)
GO
INSERT [dbo].[shopify_orders] ([order_id], [shop_id], [user_id], [order_amount], [total_items], [payment_method], [created_at], [resp_employee_id], [carrier_id]) VALUES (18, 83, 761, 258, 2, N'cash', CAST(N'2017-03-17T13:18:47.000' AS DateTime), 313, 483)
GO
INSERT [dbo].[shopify_orders] ([order_id], [shop_id], [user_id], [order_amount], [total_items], [payment_method], [created_at], [resp_employee_id], [carrier_id]) VALUES (19, 63, 898, 408, 3, N'credit_card', CAST(N'2017-03-29T15:11:52.000' AS DateTime), 313, NULL)
GO
INSERT [dbo].[shopify_orders] ([order_id], [shop_id], [user_id], [order_amount], [total_items], [payment_method], [created_at], [resp_employee_id], [carrier_id]) VALUES (20, 66, 987, 322, 2, N'cash', CAST(N'2017-03-30T20:11:59.000' AS DateTime), 315, NULL)
GO
INSERT [dbo].[shopify_orders] ([order_id], [shop_id], [user_id], [order_amount], [total_items], [payment_method], [created_at], [resp_employee_id], [carrier_id]) VALUES (21, 97, 789, 486, 3, N'credit_card', CAST(N'2017-03-04T15:44:00.000' AS DateTime), 313, 485)
GO
INSERT [dbo].[shopify_orders] ([order_id], [shop_id], [user_id], [order_amount], [total_items], [payment_method], [created_at], [resp_employee_id], [carrier_id]) VALUES (22, 88, 985, 704, 4, N'credit_card', CAST(N'2017-03-22T01:19:41.000' AS DateTime), 312, 483)
GO
INSERT [dbo].[shopify_orders] ([order_id], [shop_id], [user_id], [order_amount], [total_items], [payment_method], [created_at], [resp_employee_id], [carrier_id]) VALUES (23, 75, 964, 256, 2, N'credit_card', CAST(N'2017-03-12T03:07:42.000' AS DateTime), 312, 485)
GO
INSERT [dbo].[shopify_orders] ([order_id], [shop_id], [user_id], [order_amount], [total_items], [payment_method], [created_at], [resp_employee_id], [carrier_id]) VALUES (24, 73, 917, 495, 3, N'cash', CAST(N'2017-03-03T13:01:03.000' AS DateTime), 314, 483)
GO
INSERT [dbo].[shopify_orders] ([order_id], [shop_id], [user_id], [order_amount], [total_items], [payment_method], [created_at], [resp_employee_id], [carrier_id]) VALUES (25, 82, 848, 177, 1, N'cash', CAST(N'2017-03-25T21:35:12.000' AS DateTime), 315, 484)
GO
INSERT [dbo].[shopify_orders] ([order_id], [shop_id], [user_id], [order_amount], [total_items], [payment_method], [created_at], [resp_employee_id], [carrier_id]) VALUES (26, 47, 882, 145, 1, N'cash', CAST(N'2017-03-22T07:38:43.000' AS DateTime), 313, 485)
GO
INSERT [dbo].[shopify_orders] ([order_id], [shop_id], [user_id], [order_amount], [total_items], [payment_method], [created_at], [resp_employee_id], [carrier_id]) VALUES (27, 53, 942, 112, 1, N'credit_card', CAST(N'2017-03-17T09:41:53.000' AS DateTime), 315, 483)
GO
INSERT [dbo].[shopify_orders] ([order_id], [shop_id], [user_id], [order_amount], [total_items], [payment_method], [created_at], [resp_employee_id], [carrier_id]) VALUES (28, 40, 944, 322, 2, N'cash', CAST(N'2017-03-05T02:12:53.000' AS DateTime), 312, 484)
GO
INSERT [dbo].[shopify_orders] ([order_id], [shop_id], [user_id], [order_amount], [total_items], [payment_method], [created_at], [resp_employee_id], [carrier_id]) VALUES (29, 59, 790, 178, 1, N'credit_card', CAST(N'2017-03-04T22:49:28.000' AS DateTime), 314, 483)
GO
INSERT [dbo].[shopify_orders] ([order_id], [shop_id], [user_id], [order_amount], [total_items], [payment_method], [created_at], [resp_employee_id], [carrier_id]) VALUES (31, 76, 857, 310, 2, N'cash', CAST(N'2017-03-23T21:34:39.000' AS DateTime), 314, 484)
GO