#10081
Find the number of employees who received the bonus and who didn't. Bonus values in employee table are corrupted so you should use values from the bonus table. Be aware of the fact that employee can receive more than bonus.
Output value inside has_bonus column (1 if they had bonus, 0 if not) along with the corresponding number of employees for each.
table name: employee
table name: bonus
Solution:
with cte as(
select e.id, (case when b.worker_ref_id is null then 0 else 1 end) as has_bonus from employee as e
left join bonus as b on e.id= b.worker_ref_id
)
select count(distinct id) as n_employees,has_bonus from cte
group by has_bonus
Output:
SQL Script:
USE [StrataScratch]
GO
/****** Object: Table [dbo].[bonus] Script Date: 14-01-2024 20:26:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[bonus](
[worker_ref_id] [int] NULL,
[bonus_amount] [int] NULL,
[bonus_date] [datetime] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[employee] Script Date: 14-01-2024 20:26:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[employee](
[id] [int] NULL,
[first_name] [varchar](50) NULL,
[last_name] [varchar](50) NULL,
[age] [int] NULL,
[sex] [varchar](50) NULL,
[employee_title] [varchar](50) NULL,
[department] [varchar](50) NULL,
[salary] [int] NULL,
[target] [int] NULL,
[bonus] [int] NULL,
[email] [varchar](50) NULL,
[city] [varchar](50) NULL,
[address] [varchar](50) NULL,
[manager_id] [int] NULL
) ON [PRIMARY]
GO
INSERT [dbo].[bonus] ([worker_ref_id], [bonus_amount], [bonus_date]) VALUES (1, 5000, CAST(N'2020-02-16T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[bonus] ([worker_ref_id], [bonus_amount], [bonus_date]) VALUES (2, 3000, CAST(N'2011-06-16T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[bonus] ([worker_ref_id], [bonus_amount], [bonus_date]) VALUES (3, 4000, CAST(N'2020-02-16T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[bonus] ([worker_ref_id], [bonus_amount], [bonus_date]) VALUES (1, 4500, CAST(N'2020-02-16T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[bonus] ([worker_ref_id], [bonus_amount], [bonus_date]) VALUES (2, 3500, CAST(N'2011-06-16T00:00:00.000' AS DateTime))
GO
INSERT [dbo].[employee] ([id], [first_name], [last_name], [age], [sex], [employee_title], [department], [salary], [target], [bonus], [email], [city], [address], [manager_id]) VALUES (5, N'Max', N'George', 26, N'M', N'Sales', N'Sales', 1300, 200, 150, N'Max@company.com', N'California', N'2638 Richards Avenue', 1)
GO
INSERT [dbo].[employee] ([id], [first_name], [last_name], [age], [sex], [employee_title], [department], [salary], [target], [bonus], [email], [city], [address], [manager_id]) VALUES (13, N'Katty', N'Bond', 56, N'F', N'Manager', N'Management', 150000, 0, 300, N'Katty@company.com', N'Arizona', N'', 1)
GO
INSERT [dbo].[employee] ([id], [first_name], [last_name], [age], [sex], [employee_title], [department], [salary], [target], [bonus], [email], [city], [address], [manager_id]) VALUES (11, N'Richerd', N'Gear', 57, N'M', N'Manager', N'Management', 250000, 0, 300, N'Richerd@company.com', N'Alabama', N'', 1)
GO
INSERT [dbo].[employee] ([id], [first_name], [last_name], [age], [sex], [employee_title], [department], [salary], [target], [bonus], [email], [city], [address], [manager_id]) VALUES (10, N'Jennifer', N'Dion', 34, N'F', N'Sales', N'Sales', 1000, 200, 150, N'Jennifer@company.com', N'Alabama', N'', 13)
GO
INSERT [dbo].[employee] ([id], [first_name], [last_name], [age], [sex], [employee_title], [department], [salary], [target], [bonus], [email], [city], [address], [manager_id]) VALUES (19, N'George', N'Joe', 50, N'M', N'Manager', N'Management', 100000, 0, 300, N'George@company.com', N'Florida', N'1003 Wyatt Street', 1)
GO
INSERT [dbo].[employee] ([id], [first_name], [last_name], [age], [sex], [employee_title], [department], [salary], [target], [bonus], [email], [city], [address], [manager_id]) VALUES (18, N'Laila', N'Mark', 26, N'F', N'Sales', N'Sales', 1000, 200, 150, N'Laila@company.com', N'Florida', N'3655 Spirit Drive', 11)
GO
INSERT [dbo].[employee] ([id], [first_name], [last_name], [age], [sex], [employee_title], [department], [salary], [target], [bonus], [email], [city], [address], [manager_id]) VALUES (20, N'Sarrah', N'Bicky', 31, N'F', N'Senior Sales', N'Sales', 2000, 200, 150, N'Sarrah@company.com', N'Florida', N'1176 Tyler Avenue', 19)
GO
INSERT [dbo].[employee] ([id], [first_name], [last_name], [age], [sex], [employee_title], [department], [salary], [target], [bonus], [email], [city], [address], [manager_id]) VALUES (21, N'Suzan', N'Lee', 34, N'F', N'Sales', N'Sales', 1300, 200, 150, N'Suzan@company.com', N'Florida', N'1275 Monroe Avenue', 19)
GO
INSERT [dbo].[employee] ([id], [first_name], [last_name], [age], [sex], [employee_title], [department], [salary], [target], [bonus], [email], [city], [address], [manager_id]) VALUES (22, N'Mandy', N'John', 31, N'F', N'Sales', N'Sales', 1300, 200, 150, N'Mandy@company.com', N'Florida', N'2510 Maryland Avenue', 19)
GO
INSERT [dbo].[employee] ([id], [first_name], [last_name], [age], [sex], [employee_title], [department], [salary], [target], [bonus], [email], [city], [address], [manager_id]) VALUES (23, N'Britney', N'Berry', 45, N'F', N'Sales', N'Sales', 1200, 200, 100, N'Britney@company.com', N'Florida', N'3946 Steve Hunt Road', 19)
GO
INSERT [dbo].[employee] ([id], [first_name], [last_name], [age], [sex], [employee_title], [department], [salary], [target], [bonus], [email], [city], [address], [manager_id]) VALUES (25, N'Jack', N'Mick', 29, N'M', N'Sales', N'Sales', 1300, 200, 100, N'Jack@company.com', N'Hawaii', N'3762 Stratford Drive', 19)
GO
INSERT [dbo].[employee] ([id], [first_name], [last_name], [age], [sex], [employee_title], [department], [salary], [target], [bonus], [email], [city], [address], [manager_id]) VALUES (26, N'Ben', N'Ten', 43, N'M', N'Sales', N'Sales', 1300, 150, 100, N'Ben@company.com', N'Hawaii', N'3055 Indiana Avenue', 19)
GO
INSERT [dbo].[employee] ([id], [first_name], [last_name], [age], [sex], [employee_title], [department], [salary], [target], [bonus], [email], [city], [address], [manager_id]) VALUES (27, N'Tom', N'Fridy', 32, N'M', N'Sales', N'Sales', 1200, 200, 150, N'Tom@company.com', N'Hawaii', N'801 Stratford Drive', 1)
GO
INSERT [dbo].[employee] ([id], [first_name], [last_name], [age], [sex], [employee_title], [department], [salary], [target], [bonus], [email], [city], [address], [manager_id]) VALUES (29, N'Antoney', N'Adam', 34, N'M', N'Sales', N'Sales', 1300, 180, 150, N'Antoney@company.com', N'Hawaii', N'3533 Randall Drive', 1)
GO
INSERT [dbo].[employee] ([id], [first_name], [last_name], [age], [sex], [employee_title], [department], [salary], [target], [bonus], [email], [city], [address], [manager_id]) VALUES (28, N'Morgan', N'Matt', 25, N'M', N'Sales', N'Sales', 1200, 200, 150, N'Morgan@company.com', N'Hawaii', N'2641 Randall Drive', 1)
GO
INSERT [dbo].[employee] ([id], [first_name], [last_name], [age], [sex], [employee_title], [department], [salary], [target], [bonus], [email], [city], [address], [manager_id]) VALUES (6, N'Molly', N'Sam', 28, N'F', N'Sales', N'Sales', 1400, 100, 150, N'Molly@company.com', N'Arizona', N'3632 Polk Street', 13)
GO
INSERT [dbo].[employee] ([id], [first_name], [last_name], [age], [sex], [employee_title], [department], [salary], [target], [bonus], [email], [city], [address], [manager_id]) VALUES (7, N'Nicky', N'Bat', 33, N'F', N'Sales', N'Sales', 1400, 400, 100, N'Molly@company.com', N'Arizona', N'3461 Preston Street', 13)
GO
INSERT [dbo].[employee] ([id], [first_name], [last_name], [age], [sex], [employee_title], [department], [salary], [target], [bonus], [email], [city], [address], [manager_id]) VALUES (9, N'Monika', N'William', 33, N'F', N'Sales', N'Sales', 1000, 200, 100, N'Molly@company.com', N'Alabama', N'', 13)
GO
INSERT [dbo].[employee] ([id], [first_name], [last_name], [age], [sex], [employee_title], [department], [salary], [target], [bonus], [email], [city], [address], [manager_id]) VALUES (17, N'Mick', N'Berry', 44, N'M', N'Senior Sales', N'Sales', 2200, 200, 150, N'Mick@company.com', N'Florida', N'', 11)
GO
INSERT [dbo].[employee] ([id], [first_name], [last_name], [age], [sex], [employee_title], [department], [salary], [target], [bonus], [email], [city], [address], [manager_id]) VALUES (12, N'Shandler', N'Bing', 23, N'M', N'Auditor', N'Audit', 1100, 200, 150, N'Shandler@company.com', N'Arizona', N'', 11)
GO
INSERT [dbo].[employee] ([id], [first_name], [last_name], [age], [sex], [employee_title], [department], [salary], [target], [bonus], [email], [city], [address], [manager_id]) VALUES (14, N'Jason', N'Tom', 23, N'M', N'Auditor', N'Audit', 1000, 200, 150, N'Jason@company.com', N'Arizona', N'', 11)
GO
INSERT [dbo].[employee] ([id], [first_name], [last_name], [age], [sex], [employee_title], [department], [salary], [target], [bonus], [email], [city], [address], [manager_id]) VALUES (16, N'Celine', N'Anston', 27, N'F', N'Auditor', N'Audit', 1000, 200, 150, N'Celine@company.com', N'Colorado', N'', 11)
GO
INSERT [dbo].[employee] ([id], [first_name], [last_name], [age], [sex], [employee_title], [department], [salary], [target], [bonus], [email], [city], [address], [manager_id]) VALUES (15, N'Michale', N'Jackson', 44, N'F', N'Auditor', N'Audit', 700, 150, 150, N'Michale@company.com', N'Colorado', N'', 11)
GO
INSERT [dbo].[employee] ([id], [first_name], [last_name], [age], [sex], [employee_title], [department], [salary], [target], [bonus], [email], [city], [address], [manager_id]) VALUES (24, N'Adam', N'Morris', 30, N'M', N'Sales', N'Sales', 1300, 200, 100, N'Adam@company.com', N'Alabama', N'4541 Ferry Street', 19)
GO
INSERT [dbo].[employee] ([id], [first_name], [last_name], [age], [sex], [employee_title], [department], [salary], [target], [bonus], [email], [city], [address], [manager_id]) VALUES (30, N'Mark', N'Jon', 28, N'M', N'Sales', N'Sales', 1200, 200, 150, N'Mark@company.com', N'Alabama', N'2522 George Avenue', 1)
GO
INSERT [dbo].[employee] ([id], [first_name], [last_name], [age], [sex], [employee_title], [department], [salary], [target], [bonus], [email], [city], [address], [manager_id]) VALUES (8, N'John', N'Ford', 26, N'M', N'Senior Sales', N'Sales', 1500, 140, 100, N'Molly@company.com', N'Alabama', N'4832 New Creek Road', 13)
GO
INSERT [dbo].[employee] ([id], [first_name], [last_name], [age], [sex], [employee_title], [department], [salary], [target], [bonus], [email], [city], [address], [manager_id]) VALUES (1, N'Allen', N'Wang', 55, N'F', N'Manager', N'Management', 200000, 0, 300, N'Allen@company.com', N'California', N'1069 Ventura Drive', 1)
GO
INSERT [dbo].[employee] ([id], [first_name], [last_name], [age], [sex], [employee_title], [department], [salary], [target], [bonus], [email], [city], [address], [manager_id]) VALUES (2, N'Joe', N'Jack', 32, N'M', N'Sales', N'Sales', 1000, 200, 150, N'Joe@company.com', N'California', N'995 Jim Rosa Lane', 1)
GO
INSERT [dbo].[employee] ([id], [first_name], [last_name], [age], [sex], [employee_title], [department], [salary], [target], [bonus], [email], [city], [address], [manager_id]) VALUES (3, N'Henry', N'Ted', 31, N'M', N'Senior Sales', N'Sales', 2000, 200, 150, N'Henry@company.com', N'California', N'1609 Ford Street', 1)
GO
INSERT [dbo].[employee] ([id], [first_name], [last_name], [age], [sex], [employee_title], [department], [salary], [target], [bonus], [email], [city], [address], [manager_id]) VALUES (4, N'Sam', N'Mark', 25, N'M', N'Sales', N'Sales', 1000, 120, 150, N'Sam@company.com', N'California', N'4869 Libby Street', 1)
GO