Median Employee Salary Per Department

Problem

Schema: Employee(id INT, name VARCHAR, salary INT, dept_id INT). Return each row whose salary is part of the median of its department. (Odd count → middle row; even count → the two central rows.)

Constraints

• Use window functions, not a stored procedure.

Approach — Core Patterns

This is a Core Patterns problem. The idea: pick the data structure best matched to the constraints and solve it in one clean pass. Work through the reference code below line by line, then re-derive it yourself in the editor — that's how the pattern sticks.

Complexity: see the walkthrough below.

Solution code

SQL

WITH ranked AS (
  SELECT *,
         ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary, id) AS r,
         COUNT(*)    OVER (PARTITION BY dept_id) AS c
  FROM Employee
)
SELECT id, name, salary, dept_id
FROM ranked
WHERE r IN ((c + 1) / 2, (c + 2) / 2)
ORDER BY dept_id, salary;

Practice it

Reading a solution isn't the same as being able to write it under pressure. Open this problem in the in-browser editor, hide the solution, and solve it from scratch — your code runs against real test cases instantly.