AdvancedSubqueries & EXISTS

Classic: median salary without PERCENTILE_CONT

The query

SQL

-- Method using COUNT: works in all SQL databases
SELECT AVG(salary) AS median
FROM (
  SELECT salary,
    COUNT(*) OVER () AS total_count,
    ROW_NUMBER() OVER (ORDER BY salary) AS row_num
  FROM employees
  WHERE salary IS NOT NULL
) t
WHERE row_num IN (
  FLOOR((total_count + 1) / 2.0),
  CEIL((total_count + 1) / 2.0)
);

Tested against PostgreSQL 16

Note

“Manual median using row numbers. Averages middle two for even-count tables. Works across SQL dialects.

Tables referenced

employees10 cols