LeetCode 577 - Employee Bonus¶

Database Language: MySQL

Difficulty:

Problem Description¶

Input¶

Table: Employee¶

Column Name	Type
empId	int
name	varchar
supervisor	int
salary	int

empId is the column with unique VALUES for this table. Each row of this table indicates the name and the ID of an employee in addition to their salary and the id of their manager.

Table: Bonus¶

Column Name	Type
empId	int
bonus	int

empId is the column of unique VALUES for this table. empId is a foreign key (reference column) to empId from the Employee table. Each row of this table contains the id of an employee and their respective bonus.

Requirement¶

Write a solution to report the name and bonus amount of each employee with a bonus less than 1000.

Return the result table in any order.

The result format is in the following example.

Examples¶

Example 1¶

Input¶

Employee table:

empId	name	supervisor	salary
3	Brad	null	4000
1	John	3	1000
2	Dan	3	2000
4	Thomas	3	4000

Bonus table:

empId	bonus
2	500
4	2000

Output¶

name	bonus
Brad	null
John	null
Dan	500

SQL Schema¶

CREATE TABLE IF NOT EXISTS Employee (empId INT PRIMARY KEY, name VARCHAR(255), supervisor INT, salary INT);
CREATE TABLE IF NOT EXISTS Bonus (empId INT PRIMARY KEY, bonus INT);

TRUNCATE TABLE Employee;
INSERT INTO Employee (empId, name, supervisor, salary) VALUES ('3', 'Brad', NULL, '4000');
INSERT INTO Employee (empId, name, supervisor, salary) VALUES ('1', 'John', '3', '1000');
INSERT INTO Employee (empId, name, supervisor, salary) VALUES ('2', 'Dan', '3', '2000');
INSERT INTO Employee (empId, name, supervisor, salary) VALUES ('4', 'Thomas', '3', '4000');

TRUNCATE TABLE Bonus;
INSERT INTO Bonus (empId, bonus) VALUES ('2', '500');
INSERT INTO Bonus (empId, bonus) VALUES ('4', '2000');

Solution¶

Given that there are 2 tables provided and data from each table is needed to come out of the output, this means that a JOIN will be used to join the Employee table with the Bonus table. Looking at the Bonus table, it can be seen that not all employees have an entry in this table. Based on the sample output, if an employee does not have an entry in the Bonus table, then that employee should be included in the output. From this information, we can determine that a LEFT OUTER JOIN will be used instead of an INNER JOIN. If the output will not include those employees who do not have a record in the Bonus table, then the INNER JOIN will be used. But since the output also wants those employees who do not have bonus aside from those who had less than 1000, then the LEFT OUTER JOIN will be used with the Employee table on the left side of the join. So the join clause will look as follows:

FROM Employee LEFT OUTER JOIN Bonus
  ON Employee.empId = Bonus.empId

Since we are only interested on those employees with no bonus at all (Bonus.bonus IS NULL) or those who got less than 1000 bonus (Bonus.bonus < 1000), then we can add those conditions in the WHERE clause:

FROM Employee LEFT OUTER JOIN Bonus
  ON Employee.empId = Bonus.empId
WHERE Bonus.bonus IS NULL OR Bonus.bonus < 1000

Lastly, the requirement is to return just the name of the employee as well as the bonus received (if any):

# Final Solution Query
SELECT Employee.name, Bonus.bonus
FROM Employee LEFT OUTER JOIN Bonus
  ON Employee.empId = Bonus.empId
WHERE Bonus.bonus IS NULL OR Bonus.bonus < 1000

The query plan generated by MySQL for the query above is as follows:

id	select_type	table	partitions	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	Employee		ALL					4	100.00
1	SIMPLE	Bonus		ALL	PRIMARY				2	75.00	Using where; Using join buffer (hash join)

And here's the faster runtime for this query:

Runtime: 802ms
Beats: 90.35% as of July 15, 2024