codejump
HardPro challengeSQL

Median Salary Without MEDIAN()

SQLDatabases

SQLite has no built-in MEDIAN() function. Write a SQL query that computes
the median salary from the employees table using window functions.

The median is the middle value when rows are sorted:

  • Odd count → the single middle row.
  • Even count → the average of the two middle rows.

Table: employees

idnamesalary
1Alice40000
2Bob55000
3Carol70000
4Dave85000
5Eve95000

Sorted: 40000 · 55000 · 70000 · 85000 · 95000 → median = 70000

Expected output (column: median)

70000.0

Key insight — assign each row a rank with ROW_NUMBER() OVER (ORDER BY salary)

and compute the total row count with COUNT(*) OVER ().

For a table with N rows, the median row(s) are at positions

(N+1)/2 and (N+2)/2 (integer division). Taking AVG of those

row(s) handles both odd and even N.

Sample tests

Test #1Odd count (5) — middle row is rank 3 = 70000
Input: "CREATE TABLE employees (id INTEGER, name TEXT, salary INTEGER);\nINSERT INTO employees VALUES\n (1, 'Alice', 40000),\n (2, 'Bob', 55000),\n (3, 'Carol', 70000),\n (4, 'Dave', 85000),\n (5, 'Eve', 95000);"
Output: "70000.0"
← All challenges