Median Salary Per Department

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Description

You are given a table of employee salaries. Each row has an employee name, a department, and a salary. Write a SQL query to find the median salary for each department. For departments with an even number of employees, return the average of the two middle values. Return the result ordered by department. Table: Salaries

Column Name	Type	Description
id	INT	Primary key
employee	VARCHAR	Employee name
department	VARCHAR	Department name
salary	INT	Annual salary

Database Schema (Inferred)

Salaries

Column Name	Example Value
id	1
employee	Alice
department	Eng
salary	90000

Example

Salaries

id	employee	department	salary
1	Alice	Eng	90000
2	Bob	Eng	80000
3	Carol	Eng	70000
4	Dave	Eng	60000
5	Eve	HR	50000
6	Frank	HR	55000
7	Grace	HR	45000

Output

department	median_salary
Eng	75000
HR	50000

Explanation:

Eng has 4 employees (even). Sorted: 60000, 70000, 80000, 90000. Median = (70000+80000)/2 = 75000. HR has 3 employees (odd). Sorted: 45000, 50000, 55000. Median = 50000.

Approach hint

Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.

Common mistake

Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.