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.
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.
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 | +------+-------+
This approach uses SQL to join the Employee and Bonus tables. We perform a left join between the Employee table and the Bonus table using the empId column. The goal is to filter employees who have a bonus less than 1000 or have no bonus (NULL). This utilizes SQL's handling of null values effectively.
The SQL query performs a LEFT JOIN on the Employee and Bonus tables. This yields all employees, and for those without a corresponding bonus entry, the bonus will be NULL. The WHERE clause filters the result set down to employees whose bonus is less than 1000 or NULL.
Time Complexity: O(N), where N is the number of employees because we are essentially making a single pass over each table.
Space Complexity: O(N) due to the storage needed for the result set of employees and their bonuses.
This approach uses a subquery with the COALESCE function to handle potential null values when checking bonus amounts. The COALESCE function allows the evaluation of potential nulls to a specific value before filtering with a WHERE clause.
This query introduces the COALESCE function which converts a NULL bonus to 0, making logical sense when comparing bonuses against 1000. A LEFT JOIN is used to ensure all Employee records are considered, making this slightly different in approach but similar in result to the previous solution.
Time Complexity: O(N) as similarly, it requires iterating over joined data which corresponds with Employee records.
Space Complexity: O(N) for the resulting dataset containing eligible employees.
This approach involves using a LEFT JOIN operation to combine the Employee and Bonus tables based on the empId column. A LEFT JOIN is suitable because it will include all employees, and match bonus records where available. A COALESCE function is used to replace NULL bonus values with 0 for comparison purposes.
This query performs the following actions:
Time Complexity: O(n + m) where n is the number of records in the Employee table and m is the no. of records in the Bonus table.
Space Complexity: O(n), for storing the result set.
This approach makes use of a subquery to achieve the desired outcome. Here, for each row in the Employee table, the subquery checks the Bonus table and retrieves the bonus if it's available and below 1000.
This query involves a subquery that:
Time Complexity: O(n * m), where n is the number of entries in the Employee table and m is the number of entries in the Bonus table, due to the subquery for each row.
Space Complexity: O(n), for storing the result set.
| Approach | Complexity |
|---|---|
| Approach 1: SQL Join with NULL Handling | Time Complexity: O(N), where N is the number of employees because we are essentially making a single pass over each table. Space Complexity: O(N) due to the storage needed for the result set of employees and their bonuses. |
| Approach 2: SQL Subquery with COALESCE | Time Complexity: O(N) as similarly, it requires iterating over joined data which corresponds with Employee records. Space Complexity: O(N) for the resulting dataset containing eligible employees. |
| SQL Approach using LEFT JOIN | Time Complexity: O(n + m) where n is the number of records in the Employee table and m is the no. of records in the Bonus table. Space Complexity: O(n), for storing the result set. |
| SQL Approach using Subquery | Time Complexity: O(n * m), where n is the number of entries in the Employee table and m is the number of entries in the Bonus table, due to the subquery for each row. Space Complexity: O(n), for storing the result set. |
Employee Bonus | Leetcode 577 | Crack SQL Interviews in 50 Qs #mysql #leetcode • Learn With Chirag • 6,643 views views
Watch 9 more video solutions →Practice Employee Bonus with our built-in code editor and test cases.
Practice on FleetCode