LEET CODE Medium

570. Managers with at Least 5 Direct Reports

Table: Employee

+-------------+---------+

| Column Name | Type |

+-------------+---------+

| id | int |

| name | varchar |

| department | varchar |

| 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.

The result format is in the following example.

Example 1:

Input:

Employee table:

+-----+-------+------------+-----------+

| id | name | department | managerId |

+-----+-------+------------+-----------+

| 101 | John | A | null |

| 102 | Dan | A | 101 |

| 103 | James | A | 101 |

| 104 | Amy | A | 101 |

| 105 | Anne | A | 101 |

| 106 | Ron | B | 101 |

1
+-----+-------+------------+-----------+

Output:

+------+

| name |

+------+

| John |

QUERY
SELECT name FROM Employee WHERE id IN (SELECT managerId
FROM Employee GROUP BY managerId HAVING COUNT(*) >= 5)

Accepted

Runtime: 538 ms

Case 1

Input

Employee =

| id | name | department | managerId |

| --- | ----- | ---------- | --------- |

| 101 | John | A | null |

| 102 | Dan | A | 101 |

| 103 | James | A | 101 |

| 104 | Amy | A | 101 |

| 105 | Anne | A | 101 |

| 106 | Ron | B | 101 |

Output

| name |

| ---- |

| John |

Expected

| name |

| ---- |

| John |

2
Medium

1070. Product Sales Analysis III

Table: Sales

+-------------+-------+

| Column Name | Type |

+-------------+-------+

| sale_id | int |

| product_id | int |

| year | int |

| quantity | int |

| price | int |

+-------------+-------+

(sale_id, year) is the primary key (combination of columns with unique values) of this table.

product_id is a foreign key (reference column) to Product table.

Each row of this table shows a sale on the product product_id in a certain year.

Note that the price is per unit.

Table: Product

+--------------+---------+

| Column Name | Type |

+--------------+---------+

| product_id | int |

| product_name | varchar |

+--------------+---------+

product_id is the primary key (column with unique values) of this table.

Each row of this table indicates the product name of each product.

3
Write a solution to select the product id, year, quantity, and price for the first year of every product sold.

Return the resulting table in any order.

The result format is in the following example.

Example 1:

Input:

Sales table:

+---------+------------+------+----------+-------+

| sale_id | product_id | year | quantity | price |

+---------+------------+------+----------+-------+

|1 | 100 | 2008 | 10 | 5000 |

|2 | 100 | 2009 | 12 | 5000 |

|7 | 200 | 2011 | 15 | 9000 |

+---------+------------+------+----------+-------+

Product table:

+------------+--------------+

| product_id | product_name |

+------------+--------------+

| 100 | Nokia |

| 200 | Apple |

| 300 | Samsung |

+------------+--------------+

Output:

+------------+------------+----------+-------+

| product_id | first_year | quantity | price |

+------------+------------+----------+-------+

| 100 | 2008 | 10 | 5000 |

| 200 | 2011 | 15 | 9000 |

Query
4
select product_id,year as first_year, quantity, price
from Sales where (product_id, year) in (select
product_id, min(year) from Sales group by product_id);

Accepted

Runtime: 619 ms

Case 1

Input

Sales =

| sale_id | product_id | year | quantity | price |

| ------- | ---------- | ---- | -------- | ----- |

|1 | 100 | 2008 | 10 | 5000 |

|2 | 100 | 2009 | 12 | 5000 |

|7 | 200 | 2011 | 15 | 9000 |

Product =

| product_id | product_name |

| ---------- | ------------ |

| 100 | Nokia |

| 200 | Apple |

| 300 | Samsung |

Output

| PRODUCT_ID | FIRST_YEAR | QUANTITY | PRICE |

| ---------- | ---------- | -------- | ----- |

| 100 | 2008 | 10 | 5000 |

| 200 | 2011 | 15 | 9000 |

Expected

| product_id | first_year | quantity | price |

| ---------- | ---------- | -------- | ----- |

| 100 | 2008 | 10 | 5000 |

| 200 | 2011 | 15 | 9000 |

5
Hard
185. Department Top Three Salaries
Table: Employee

+--------------+---------+
| Column Name | Type |
+--------------+---------+
| id | int |
| name | varchar |
| salary | int |
| departmentId | int |
+--------------+---------+
id is the primary key (column with unique values) for this table.
departmentId is a foreign key (reference column) of the ID from the Department table.
Each row of this table indicates the ID, name, and salary of an employee. It also contains the ID
of their department.

Table: Department
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| id | int |
| name | varchar |
+-------------+---------+
id is the primary key (column with unique values) for this table.
Each row of this table indicates the ID of a department and its name.

A company's executives are interested in seeing who earns the most money in each of the
company's departments. A high earner in a department is an employee who has a salary in the
top three unique salaries for that department.

Write a solution to find the employees who are high earners in each of the departments.

Return the result table in any order.

The result format is in the following example.

6
Example 1:

Input:
Employee table:
+----+-------+--------+--------------+
| id | name | salary | departmentId |
+----+-------+--------+--------------+
| 1 | Joe | 85000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
| 7 | Will | 70000 | 1 |
+----+-------+--------+--------------+
Department table:
+----+-------+
| id | name |
+----+-------+
| 1 | IT |
| 2 | Sales |
+----+-------+
Output:
+------------+----------+--------+
| Department | Employee | Salary |
+------------+----------+--------+
| IT | Max | 90000 |
| IT | Joe | 85000 |
| IT | Randy | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
+------------+----------+--------+
Explanation:
In the IT department:
- Max earns the highest unique salary
- Both Randy and Joe earn the second-highest unique salary
- Will earns the third-highest unique salary

In the Sales department:

- Henry earns the highest salary
- Sam earns the second-highest salary
- There is no third-highest salary as there are only two employees

7
Constraints:
There are no employees with the exact same name, salary and department.

SELECT
d.name AS Department,
e.name AS Employee,
e.salary AS Salary
FROM
Employee e
JOIN Department d ON e.departmentId = d.id
WHERE
(
SELECT COUNT(DISTINCT salary)
FROM Employee e2
WHERE e2.departmentId = e.departmentId AND
e2.salary >= e.salary
) <= 3
ORDER BY
Department, Salary DESC;
Accepted
Runtime: 497 ms
Case 1
Input
Employee =
| id | name | salary | departmentId |
| -- | ----- | ------ | ------------ |
| 1 | Joe | 85000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
View more
Department =
| id | name |
| -- | ----- |
| 1 | IT |
| 2 | Sales |
Output
| DEPARTMENT | EMPLOYEE | SALARY |
| ---------- | -------- | ------ |
| IT | Max | 90000 |
| IT | Joe | 85000 |
8
| IT | Randy | 85000 |
| IT | Will | 70000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
Expected
| Department | Employee | Salary |
| ---------- | -------- | ------ |
| IT | Joe | 85000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Will | 70000 |

select d.Name Department, e1.Name Employee,

e1.Salary
from Employee e1
join Department d
on e1.DepartmentId = d.Id
where 3 > (select
count(distinct(e2.Salary))
from Employee e2
where e2.Salary >
e1.Salary
and e1.DepartmentId =
e2.DepartmentId
);
Accepted
Runtime: 468 ms
Case 1
Input
Employee =
| id | name | salary | departmentId |
| -- | ----- | ------ | ------------ |
| 1 | Joe | 85000 | 1 |
| 2 | Henry | 80000 | 2 |
| 3 | Sam | 60000 | 2 |
| 4 | Max | 90000 | 1 |
| 5 | Janet | 69000 | 1 |
| 6 | Randy | 85000 | 1 |
9
| 7 | Will | 70000 | 1 |
View less
Department =
| id | name |
| -- | ----- |
| 1 | IT |
| 2 | Sales |
Output
| DEPARTMENT | EMPLOYEE | SALARY |
| ---------- | -------- | ------ |
| IT | Joe | 85000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Will | 70000 |
Expected
| Department | Employee | Salary |
| ---------- | -------- | ------ |
| IT | Joe | 85000 |
| Sales | Henry | 80000 |
| Sales | Sam | 60000 |
| IT | Max | 90000 |
| IT | Randy | 85000 |
| IT | Will | 70000 |

10