| comments | difficulty | edit_url | tags | |
|---|---|---|---|---|
true |
Hard |
|
Table: Employee
+--------------+---------+ | Column Name | Type | +--------------+---------+ | id | int | | company | varchar | | salary | int | +--------------+---------+ id is the primary key (column with unique values) for this table. Each row of this table indicates the company and the salary of one employee.
Write a solution to find the rows that contain the median salary of each company. While calculating the median, when you sort the salaries of the company, break the ties by id.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: Employee table: +----+---------+--------+ | 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 | +----+---------+--------+ Output: +----+---------+--------+ | id | company | salary | +----+---------+--------+ | 5 | A | 451 | | 6 | A | 513 | | 12 | B | 234 | | 9 | B | 1154 | | 14 | C | 2645 | +----+---------+--------+ Explanation: For company A, the rows sorted are as follows: +----+---------+--------+ | id | company | salary | +----+---------+--------+ | 3 | A | 15 | | 2 | A | 341 | | 5 | A | 451 | <-- median | 6 | A | 513 | <-- median | 1 | A | 2341 | | 4 | A | 15314 | +----+---------+--------+ For company B, the rows sorted are as follows: +----+---------+--------+ | id | company | salary | +----+---------+--------+ | 8 | B | 13 | | 7 | B | 15 | | 12 | B | 234 | <-- median | 11 | B | 1221 | <-- median | 9 | B | 1154 | | 10 | B | 1345 | +----+---------+--------+ For company C, the rows sorted are as follows: +----+---------+--------+ | id | company | salary | +----+---------+--------+ | 17 | C | 65 | | 13 | C | 2345 | | 14 | C | 2645 | <-- median | 15 | C | 2645 | | 16 | C | 2652 | +----+---------+--------+
Follow up: Could you solve it without using any built-in or window functions?
# Write your MySQL query statement below
WITH
t AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY company
ORDER BY salary ASC
) AS rk,
COUNT(id) OVER (PARTITION BY company) AS n
FROM Employee
)
SELECT
id,
company,
salary
FROM t
WHERE rk >= n / 2 AND rk <= n / 2 + 1;