Average Salary Is 5,250 While Three of Four People Earn Under 5,000

AVG is the easiest number to compute and the easiest to mislead with. One high earner skews the entire report.

The problem

Four salaries: 3,000, 4,000, 5,000 and 9,000. The average is 5,250 — higher than what three of the four people earn. Mathematically correct, descriptively wrong.

Median: PERCENTILE_CONT

SELECT
  PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median,
  PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY salary) AS p90
FROM employees;

Works on PostgreSQL, SQL Server and Oracle. PERCENTILE_DISC returns a value that actually exists in the table; PERCENTILE_CONT interpolates between two.

Median on MySQL — no built-in function

WITH numbered AS (
  SELECT salary,
    ROW_NUMBER() OVER (ORDER BY salary) AS rn,
    COUNT(*)    OVER ()                 AS total
  FROM employees
)
SELECT AVG(salary) AS median
FROM numbered
WHERE rn IN (FLOOR((total+1)/2), CEIL((total+1)/2));

The trick: take the two middle rows and average them — correct for both odd and even counts. This is the answer to HackerRank Weather Observation Station 20.

NTILE: split into N equal buckets

SELECT
  name, salary,
  NTILE(4) OVER (ORDER BY salary) AS quartile
FROM employees;

Used for segmenting customers by spend, banding students by score, ranking products by sales.

PERCENT_RANK: relative position

SELECT name, salary,
  ROUND(100 * PERCENT_RANK() OVER (ORDER BY salary), 1) AS pct_below
FROM employees;

Answers “what percentage of people earn less than me”.

Common mistakes

1. Using AVG on skewed data

Salaries, response times and order values are all right-skewed. For those, the median describes reality better. Good reports show both.

2. Confusing NTILE with value banding

NTILE splits by row count, not by value range. With 100 people every bucket holds 25, however different the salary gaps between buckets are.

3. Forgetting NTILE needs ORDER BY

Without ORDER BY the bucketing is arbitrary and meaningless.

Practice

• HackerRank — Weather Observation Station 20 (median)
• LeetCode 569 — Median Employee Salary
• LeetCode 571 — Find Median Given Frequency of Numbers
• LeetCode 1077 — Project Employees III