Formatted question description: https://leetcode.ca/all/569.html
569. Median Employee Salary
Level
Hard
Description
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 |
+-----+------------+--------+
Solution
Use the definition of median. If the number of employees is odd, then there is only one median, and the number of employees with a higher salary equals the number of employees with a lower salary. If the number of employees is even, then there are two medians, and for each median, the absolute difference between the number of employees with a higher salary equals the number of employees with a lower salary is 1.
# Write your MySQL query statement below
select e1.id, e1.Company, e1.Salary
from Employee e1, Employee e2
where e1.Company = e2.Company
group by e1.Company, e1.Salary
having sum(
case when e1.Salary = e2.Salary then 1 else 0 end
) >= abs(sum(sign(e1.Salary - e2.Salary)))
order by e1.Id;