Median Salary per Department

SQL coding challenge · Difficulty: hard · +180 XP

Table: salaries

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

| Column     | Type    |

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

| emp_id     | INT     |
| department | VARCHAR |
| salary     | DECIMAL |

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

Problem

-------

Find the median salary for each department.

Median rules:

• Odd count: middle value

• Even count: average of two middle values

• Round to 2 decimal places

Return: department, median_salary

Order: department ASC

Example Input

-------------

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

| emp_id | department | salary  |

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

|   1    | Eng        | 80000   |
|   2    | Eng        | 90000   |
|   3    | Eng        | 70000   |
|   4    | Eng        | 100000  |
|   5    | Sales      | 50000   |
|   6    | Sales      | 60000   |
|   7    | Sales      | 55000   |

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

Expected Output

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

| department | median_salary |

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

| Eng        |    85000.00   |
| Sales      |    55000.00   |

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

Explanation

-----------

Eng (sorted): 70k, 80k, 90k, 100k

→ even count: (80k + 90k) / 2 = 85,000

Sales (sorted): 50k, 55k, 60k

→ odd count: middle = 55,000

Hint

----

Use PERCENTILE_CONT(0.5) or

ROW_NUMBER + COUNT window functions.