Median Daily Spend of Each Channel — SQL Problem
Average daily spend is skewed by a few festival days, so the planning team wants the median daily spend of each channel.
- Difficulty: Hard
- Topics: Window Functions, Aggregation, Subqueries
- Dialect: MySQL
- Problem: #295
Problem statement
Average daily spend is skewed by a few festival days, so the planning team wants the median daily spend of each channel. With an odd number of days the median is the middle value; with an even number it is the average of the two middle values. Equal amounts count as separate days.
Return channel and median_daily_spend, rounded to 2 decimals, for every channel with at least one day. Order by channel.
Tables
Table: ChannelDailySpend
| Column | Type |
|---|---|
| channel | enum(search, social, display, video, email) |
| spend_date | date |
| spend | int |
Primary key: channel, spend_date.
One row per channel per day with spend, in rupees.
Examples
Example 1
ChannelDailySpend
| channel | spend_date | spend |
|---|---|---|
| search | 2024-10-28 | 20000 |
| search | 2024-10-29 | 25000 |
| search | 2024-10-30 | 90000 |
| search | 2024-10-31 | 22000 |
| social | 2024-10-28 | 15000 |
| social | 2024-10-29 | 15000 |
| social | 2024-10-30 | 60000 |
| video | 2024-10-31 | 41000 |
Output
| channel | median_daily_spend |
|---|---|
| search | 23500 |
| social | 15000 |
| video | 41000 |
How to solve Median Daily Spend of Each Channel
Sort each channel's days by spend and pick the middle. ROW_NUMBER() (ties broken by date so the numbering is fixed) gives each day its position, and COUNT(*) OVER (PARTITION BY channel) gives n on every row. The middle positions are FLOOR((n + 1) / 2) and FLOOR((n + 2) / 2): for n = 4 that is 2 and 3, for n = 3 it is 2 twice, so one AVG over the rows at those positions is the median whether n is odd or even. Equal amounts occupy separate positions, exactly as "separate days" requires; which of two equal amounts gets which number does not change the values averaged.
The alternative uses the definition instead of positions: a value lies in the middle when at least half of the days are at most it and at least half are at least it. The candidates are the lower and upper middle values (or one value when they coincide), and AVG(DISTINCT …) averages those distinct values — plain AVG would overweight a repeated middle value. It is quadratic per channel; the window version is one sort.
The average of two whole-rupee amounts ends in .0 or .5, so rounding to 2 decimals is exact.
Reference solution (MySQL)
WITH r AS (
SELECT channel, spend,
ROW_NUMBER() OVER (PARTITION BY channel ORDER BY spend, spend_date) AS rn,
COUNT(*) OVER (PARTITION BY channel) AS n
FROM ChannelDailySpend
)
SELECT channel, ROUND(AVG(spend), 2) AS median_daily_spend
FROM r
WHERE rn IN (FLOOR((n + 1) / 2), FLOOR((n + 2) / 2))
GROUP BY channel
ORDER BY channelAnother way
SELECT a.channel, ROUND(AVG(DISTINCT a.spend), 2) AS median_daily_spend
FROM ChannelDailySpend a
WHERE (SELECT COUNT(*) FROM ChannelDailySpend b WHERE b.channel = a.channel AND b.spend <= a.spend) * 2
>= (SELECT COUNT(*) FROM ChannelDailySpend b WHERE b.channel = a.channel)
AND (SELECT COUNT(*) FROM ChannelDailySpend b WHERE b.channel = a.channel AND b.spend >= a.spend) * 2
>= (SELECT COUNT(*) FROM ChannelDailySpend b WHERE b.channel = a.channel)
GROUP BY a.channel ORDER BY a.channel← Click to Sign-up to Purchase Funnel per Campaign · Dark Days Inside Campaign Flight Dates →