SQL Algorithm
LeetCode
181. Employees Earning More Than Their Managers
| Column Name |
Type |
| id |
int |
| name |
varchar |
| salary |
int |
| managerId |
int |
- id is the primary key (column with unique values) for this table.
- Each row of this table indicates the ID of an employee, their name, salary, and the ID of their manager.
- Write a solution to find the employees who earn more than their managers.
- Return the result table in any order.
SELECT A.name AS Employee
FROM Employee AS A INNER JOIN Employee AS B ON A.managerId = B.id
WHERE A.salary > B.salary;
570. Managers with at Least 5 Direct Reports
| Column Name |
Type |
| id |
int |
| name |
varchar |
| salary |
int |
| managerId |
int |
- id is the primary key (column with unique values) for this table.
- Each row of this table indicates the name of an employee, their department, and the id of their manager.
- If managerId is null, then the employee does not have a manager.
- No employee will be the manager of themself.
- Write a solution to find managers with at least five direct reports.
- Return the result table in any order.
WITH report AS
(SELECT managerId, COUNT(*) AS report_cnt
FROM Employee
GROUP BY managerId
HAVING report_cnt >= 5)
SELECT Employee.name
FROM report
INNER JOIN Employee
ON report.managerId = Employee.id
SELECT name
FROM Employee
WHERE id IN
(SELECT managerId
FROM Employee
GROUP BY managerId
HAVING COUNT(*) >= 5);
1045. Customers Who Bought All Products
| Column Name |
Type |
| customer_id |
int |
| product_key |
int |
- This table may contain duplicates rows.
- customer_id is not NULL.
- product_key is a foreign key (reference column) to Product table.
| Column Name |
Type |
| product_key |
int |
- product_key is the primary key (column with unique values) for this table.
- Write a solution to report the customer ids from the Customer table that bought all the products in the Product table.
- Return the result table in any order.
SELECT customer_id
FROM Customer, Product
GROUP BY customer_id
HAVING COUNT(DISTINCT Customer.product_key) = COUNT(DISTINCT Product.product_key);
SELECT customer_id
FROM Customer
GROUP BY customer_id
HAVING COUNT(DISTINCT product_key) = (SELECT COUNT(DISTINCT product_key) FROM Product);
https://leetcode.com/problems/employees-earning-more-than-their-managers/
https://leetcode.com/problems/managers-with-at-least-5-direct-reports/
https://leetcode.com/problems/customers-who-bought-all-products/