Finding the Median

The median is the middle value once everything is sorted. It's often more honest than the average, because a few huge salaries can't drag it up.

SQLite has no MEDIAN function (PostgreSQL has PERCENTILE_CONT), so here's the classic approach with window functions:

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);

How it works:

• ROW_NUMBER numbers the salaries from lowest to highest
• COUNT(*) OVER () puts the total count on every row
• With 37 rows, both formulas give 19: the single middle row
• With an even count like 36, they give 18 and 19, and AVG takes the midpoint of the two

Compare it with the average:

SELECT AVG(salary) AS average_salary FROM employees;

The average is higher, pulled up by a few executive salaries.