Median Employee Salary Problem
Description
LeetCode Problem 569.
The Employee table holds all employees. The employee table has three columns: Employee Id, Company Name, and Salary.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
+-----+------------+--------+
|Id | Company | Salary |
+-----+------------+--------+
|1 | A | 2341 |
|2 | A | 341 |
|3 | A | 15 |
|4 | A | 15314 |
|5 | A | 451 |
|6 | A | 513 |
|7 | B | 15 |
|8 | B | 13 |
|9 | B | 1154 |
|10 | B | 1345 |
|11 | B | 1221 |
|12 | B | 234 |
|13 | C | 2345 |
|14 | C | 2645 |
|15 | C | 2645 |
|16 | C | 2652 |
|17 | C | 65 |
+-----+------------+--------+
Write a SQL query to find the median salary of each company. Bonus points if you can solve it without using any built-in SQL functions.
1
2
3
4
5
6
7
8
9
+-----+------------+--------+
|Id | Company | Salary |
+-----+------------+--------+
|5 | A | 451 |
|6 | A | 513 |
|12 | B | 234 |
|9 | B | 1154 |
|14 | C | 2645 |
+-----+------------+--------+
MySQL Solution
1
2
3
4
5
6
select t1.Id, t1.Company, t1.Salary
from (select Id, Company, Salary,
dense_rank() over (partition by Company order by Salary, Id) as Sort,
count(1) over (partition by Company) / 2.0 as Counts
from Employee ) t1
where Sort between Counts and Counts + 1
LeetCode Database - Hard
LeetCode 185
LeetCode 262
LeetCode 569
LeetCode 571
LeetCode 579
LeetCode 601
LeetCode 615
LeetCode 618
LeetCode 1097
LeetCode 1127
LeetCode 1159
LeetCode 1194
LeetCode 1225
LeetCode 1336
LeetCode 1369
LeetCode 1384
LeetCode 1412
LeetCode 1479
LeetCode 1635
LeetCode 1645
LeetCode 1651
LeetCode 1767
LeetCode 1892
LeetCode 1917
LeetCode 1919
LeetCode 1972
More LeetCode Database
MySQL Tutorials