-
Notifications
You must be signed in to change notification settings - Fork 1
/
LC569-Median-Employee-Salary
60 lines (54 loc) · 1.85 KB
/
LC569-Median-Employee-Salary
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
/*
The Employee table holds all employees. The employee table has three columns:
Employee Id, Company Name, and Salary.
+-----+------------+--------+
|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.
+-----+------------+--------+
|Id | Company | Salary |
+-----+------------+--------+
|5 | A | 451 |
|6 | A | 513 |
|12 | B | 234 |
|9 | B | 1154 |
|14 | C | 2645 |
+-----+------------+--------+
Additional comments: if the number of employees of the given company is even, display two
employees.
If there are equal salaries, display id only of the person whose id is "closer" to median id
for this company.
*/
# Write your MySQL query statement below
WITH stats AS (
SELECT Id,
Rank() OVER(PARTITION BY Company ORDER BY Salary, Id) AS rnk,
COUNT(Id) OVER(PARTITION BY Company) AS n_employees,
Company,
Salary
FROM Employee
)
SELECT Id,
Company,
Salary
FROM stats
WHERE 2 * rnk >= (n_employees) AND 2 * rnk <= (n_employees + 2)