Median Daily Spend of Each Channel

Hard
295.

Median Daily Spend of Each Channel

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

ColumnTypeValues
channelenumsearch, social, display, video, email
spend_datedate
spendint

Primary key (together): channel, spend_date.

One row per channel per day with spend, in rupees.

Example

The rows must come back in the order shown.

ChannelDailySpend

channelspend_datespend
search2024-10-2820000
search2024-10-2925000
search2024-10-3090000
search2024-10-3122000
social2024-10-2815000
social2024-10-2915000
social2024-10-3060000
video2024-10-3141000

Output

channelmedian_daily_spend
search23500
social15000
video41000
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
channelenum(search, social, display, video, email)
spend_datedate
spendint

Primary key: channel, spend_date.

One row per channel per day with spend, in rupees.

Examples

Example 1

ChannelDailySpend

channelspend_datespend
search2024-10-2820000
search2024-10-2925000
search2024-10-3090000
search2024-10-3122000
social2024-10-2815000
social2024-10-2915000
social2024-10-3060000
video2024-10-3141000

Output

channelmedian_daily_spend
search23500
social15000
video41000

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 channel

Another 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 →