The median salary

Step 1 Solve it yourself first

Tables you will use

employees

What it really tests

Whether you can build a statistic that SQL doesn't always provide, using row numbers and counts — and handle both odd and even group sizes.

The pattern: Median with ROW_NUMBER and COUNT

"Median", "middle value", "50th percentile".

Picture it

flowchart LR
  S["8 salaries sorted<br/>38k 45k 52k 60k 70k 70k 80k 95k"] --> M["middle positions<br/>(8+1)/2 = 4 and (8+2)/2 = 5"]
  M --> A["average of 60000 and 70000<br/>= 65000"]

The approach, step by step

1. Sort the salaries and number them 1 to n with ROW_NUMBER; get n with COUNT(*) OVER ().
2. The middle positions are (n + 1) / 2 and (n + 2) / 2 using whole-number division: the same row when n is odd, the two middle rows when n is even.
3. Average the salaries at those positions.
4. For each department, add PARTITION BY dept_id to both window functions and GROUP BY dept_id.

WITH ordered AS (
  SELECT salary,
         ROW_NUMBER() OVER (ORDER BY salary) AS rn,
         COUNT(*) OVER () AS n
  FROM employees
)
SELECT AVG(salary) AS median_salary
FROM ordered
WHERE rn IN ((n + 1) / 2, (n + 2) / 2);

WITH ordered AS (
  SELECT dept_id, salary,
         ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary) AS rn,
         COUNT(*) OVER (PARTITION BY dept_id) AS n
  FROM employees
  WHERE dept_id IS NOT NULL
)
SELECT dept_id, AVG(salary) AS median_salary
FROM ordered
WHERE rn IN ((n + 1) / 2, (n + 2) / 2)
GROUP BY dept_id
ORDER BY dept_id;

Step 2 Go further (free, optional)

Optional. The notes above are written to be enough on their own. These free resources give a second explanation and more practice.

• Read

  PostgreSQL docs — Ordered-Set Aggregate Functions (PERCENTILE_CONT)

  official documentation, free

• Practice

  DataLemur — median questions

  real interview questions, many free

Step 3 Practise

Try each one yourself first: out loud, or written and run in the practice box below, which saves every query you run. Then open the answer and compare.

1. ExplainSay what it does and when to use it, in plain words, with no notes.

   Explain why positions (n + 1) / 2 and (n + 2) / 2 work for both odd and even n.

   Show answer

   Both use whole-number division, which drops the fraction.

   • Odd n, say 7: (7 + 1) / 2 = 4 and (7 + 2) / 2 = 9 / 2 = 4. Both point at row 4, the single middle row, and the average of one value is that value.
   • Even n, say 8: (8 + 1) / 2 = 9 / 2 = 4 and (8 + 2) / 2 = 5. They point at rows 4 and 5, the two middle rows, and AVG gives the halfway point between them, which is the median of an even list.

   So one formula covers both cases, with no CASE needed.

2. Writefrom a blank page, then run it

   Find the median order amount, ignoring the order with no amount.

   Show answer

   Filter the NULL out before numbering, so it neither takes a position nor counts in n. 9 amounts remain: 30, 33, 45, 60, 75, 80, 120, 150, 200. n is 9, so the median is row 5: 75.

   sqlCopy

   WITH ordered AS (
     SELECT amount,
            ROW_NUMBER() OVER (ORDER BY amount) AS rn,
            COUNT(*) OVER () AS n
     FROM orders
     WHERE amount IS NOT NULL
   )
   SELECT AVG(amount) AS median_amount
   FROM ordered
   WHERE rn IN ((n + 1) / 2, (n + 2) / 2);

   Try it

   1 row

   median_amount
   75

3. Predictsay it before you run it

   What is the median salary if Asha leaves (7 employees)?

   Show answer

   60,000. The 7 remaining salaries in order are 38,000, 45,000, 52,000, 60,000, 70,000, 70,000 and 80,000. n is odd, so the median is the 4th: Dara's 60,000. (With all 8 it was 65,000, the average of 60,000 and 70,000.)

   Asha can't simply be deleted below, because other employees' manager_id points at her, so the query leaves her out with a WHERE instead.

   sqlCopy

   WITH ordered AS (
     SELECT salary,
            ROW_NUMBER() OVER (ORDER BY salary) AS rn,
            COUNT(*) OVER () AS n
     FROM employees
     WHERE name <> 'Asha'
   )
   SELECT AVG(salary) AS median_salary
   FROM ordered
   WHERE rn IN ((n + 1) / 2, (n + 2) / 2);

   Try it

   1 row

   median_salary
   60000

4. Edge casesSay what happens with NULLs, ties, duplicates or no rows at all, and fix it.

   What happens to the median calculation if one salary is NULL and you don't filter it out?

   Show answer

   The median comes out wrong, without any error. The NULL still gets a row number (SQLite sorts NULLs first, so it takes position 1) and is counted in n, so every real value is pushed one place along and the middle is picked at the wrong spot. If the NULL itself lands in the middle, AVG skips it and averages whatever else is there.

   The salary column can't be NULL here, so the query below does it with order amounts, which have one NULL. With it, n is 10 and the middle rows are 60 and 75, giving 67.5; the true median of the 9 real amounts is 75. Always filter WHERE x IS NOT NULL first, as AVG and COUNT(column) do automatically.

   sqlCopy

   WITH ordered AS (
     SELECT amount,
            ROW_NUMBER() OVER (ORDER BY amount) AS rn,
            COUNT(*) OVER () AS n
     FROM orders
   )
   SELECT AVG(amount) AS median_with_the_null
   FROM ordered
   WHERE rn IN ((n + 1) / 2, (n + 2) / 2);

   Try it

   1 row

   median_with_the_null
   67.5

Step 4 Interview-ready, then prove it

Your interview answer

About 20 to 40 seconds, said out loud.

If the database has PERCENTILE_CONT, the median is PERCENTILE_CONT 0.5 within group ordered by salary. Otherwise I number the sorted values with ROW_NUMBER, get the count with COUNT over the same window, and average the values at positions n plus one over two and n plus two over two with integer division — that's the single middle row for odd n and the two middle rows for even n. Partitioning both window functions gives a median per group.

Follow-up questions

Tick each check when you can do it without notes