Median Salary Per Department, Two Ways

1. PERCENTILE_CONT

SELECT
  department_id,
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM salaries
GROUP BY department_id;

PERCENTILE_CONT(0.5) asks for the value at the 50th percentile — the median, by definition — under a continuous distribution model: WITHIN GROUP (ORDER BY salary) tells it which column defines the ordering to interpolate across. "Continuous" is the operative word for why this handles both parities correctly without you writing any branching logic: when there's an odd number of rows, the 50th percentile lands exactly on the middle value; when there's an even number, it lands exactly halfway between the two middle values, and PERCENTILE_CONT linearly interpolates between them — which is precisely the textbook definition of "median" for an even-count dataset (average the two middle values). This is a genuine ordered-set aggregate, not a window function, so it's used with GROUP BY directly rather than an OVER clause, and it collapses each department's rows into one output row per department, one interpolated value each.

2. Manual median with ROW_NUMBER()

WITH ranked AS (
  SELECT
    department_id,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY salary) AS rn,
    COUNT(*)     OVER (PARTITION BY department_id)                 AS cnt
  FROM salaries
)
SELECT
  department_id,
  AVG(salary) AS median_salary
FROM ranked
WHERE rn IN ( (cnt + 1) / 2, (cnt + 2) / 2 )   -- integer division
GROUP BY department_id;

The trick is entirely in that WHERE rn IN (...) filter, and it's worth deriving rather than memorizing. For an odd count (say cnt = 3), (cnt+1)/2 = 2 and (cnt+2)/2 = 2 under integer division — both expressions collapse to the same single middle position (2), so the IN list effectively has one distinct value and AVG over a single row just returns that row's salary, correctly the median. For an even count (cnt = 4), (cnt+1)/2 = 2 and (cnt+2)/2 = 3 (again integer division) — two distinct middle positions, and AVG over those two rows' salaries gives their mean, matching PERCENTILE_CONT's interpolation exactly for two adjacent ranked values. ROW_NUMBER() (not RANK/DENSE_RANK) is required here specifically because you need actual row positions to count into the middle — value-based tie handling isn't the concern in this problem the way it was in the Nth-highest-per-group case, since two employees with an identical salary at the median position should still each occupy one position, not collapse into a single rank. The COUNT(*) OVER (PARTITION BY department_id) window alongside ROW_NUMBER() avoids a second pass over the table (a separate GROUP BY subquery to get each department's row count) by computing both in a single scan.

3. Which to use

PERCENTILE_CONT in production, without hesitation, on any engine that supports it (Postgres, Snowflake, BigQuery, Redshift, SQL Server all do) — it's shorter, it's the standard, well-tested expression of "median" with no risk of an off-by-one in a hand-rolled position formula, and it generalizes for free to any percentile (0.9 for p90, 0.25 for a lower quartile) without changing the query's shape. The manual ROW_NUMBER() version is worth knowing cold anyway, because it's the fallback the moment you're on an engine or a dialect that lacks ordered-set aggregates (some older MySQL versions, certain restricted query engines), and because deriving the position formula from first principles — rather than having memorized it — is exactly the kind of reasoning an interviewer is checking for when they ask "now do it without the built-in function."