SQL Median
The middle value — Postgres computes it directly, no manual sorting-and-picking required.
What & Why
The median is the middle value in a sorted list — or the average of the two middle values, for an even count. PERCENTILE_CONT(0.5) computes this directly.
See How It Works
BUSINESS QUESTION
Marketing wants median campaign spend by channel.
| id | name | channel | spend | start_date | end_date | status | target_segment |
|---|---|---|---|---|---|---|---|
| 1 | Spring Launch | google_ads | 55000.00 | 2024-01-15 | 2024-03-31 | active | smb |
| 2 | Retention Webinar | 45000.00 | 2024-02-10 | 2024-04-15 | active | enterprise | |
| 3 | Finance Retargeting | 50000.00 | 2024-03-12 | 2024-05-31 | active | enterprise | |
| 4 | Enterprise Search | google_ads | 60000.00 | 2024-04-01 | 2024-06-30 | active | enterprise |
EXAMPLE QUERY
SELECT
channel,
ROUND(PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY spend)::numeric, 2) AS median_spend
FROM marketing.campaigns
GROUP BY channel
ORDER BY channel;RESULT — median campaign spend by channel
| channel | median_spend |
|---|---|
| 45000.00 | |
| google_ads | 57500.00 |
| 50000.00 |
PERCENTILE_CONT interpolates the two google_ads spends to 57,500.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario