Median Days From Trial to Paid by Acquisition Channel

Hard
421.

Median Days From Trial to Paid by Acquisition Channel

Marketing compares acquisition channels by how quickly their trials turn into paying customers. For each converted trial, the time to convert is DATEDIFF(converted_on, started_on) days. A channel's median is the middle value of its converted trials' days, or the average of the two middle values when the count is even. Trials that never converted are ignored.

Return channel, converted_trials and median_days rounded to 1 decimal for every channel with at least one conversion, ordered by channel.

Tables

Table: trials

ColumnTypeValues
trial_idint
workspace_namevarchar
channelenumorganic, paid_search, partner, referral
started_ondate
converted_ondate

Primary key: trial_id.

One row per free trial; converted_on is the first paid day, or NULL.

Example

The rows must come back in the order shown.

trials

trial_idworkspace_namechannelstarted_onconverted_on
1Zentrix Labsorganic2024-03-012024-03-11
2Quillpadorganic2024-03-022024-03-06
3Ledgerlyorganic2024-03-05NULL
4Orbitpayorganic2024-03-072024-03-14
5Vaani AIpaid_search2024-03-012024-03-03
6Tiffinlypaid_search2024-03-042024-03-13
7Mintmarkreferral2024-03-082024-03-09
8Kiranakartpartner2024-03-02NULL

Output

channelconverted_trialsmedian_days
organic37
paid_search25.5
referral11
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 Days From Trial to Paid by Acquisition Channel

Marketing compares acquisition channels by how quickly their trials turn into paying customers.

  • Difficulty: Hard
  • Topics: Window Functions, Aggregation
  • Dialect: MySQL
  • Problem: #421

Problem statement

Marketing compares acquisition channels by how quickly their trials turn into paying customers. For each converted trial, the time to convert is DATEDIFF(converted_on, started_on) days. A channel's median is the middle value of its converted trials' days, or the average of the two middle values when the count is even. Trials that never converted are ignored.

Return channel, converted_trials and median_days rounded to 1 decimal for every channel with at least one conversion, ordered by channel.

Tables

Table: trials

ColumnType
trial_idint
workspace_namevarchar
channelenum(organic, paid_search, partner, referral)
started_ondate
converted_ondate

Primary key: trial_id.

One row per free trial; converted_on is the first paid day, or NULL.

Examples

Example 1

trials

trial_idworkspace_namechannelstarted_onconverted_on
1Zentrix Labsorganic2024-03-012024-03-11
2Quillpadorganic2024-03-022024-03-06
3Ledgerlyorganic2024-03-05NULL
4Orbitpayorganic2024-03-072024-03-14
5Vaani AIpaid_search2024-03-012024-03-03
6Tiffinlypaid_search2024-03-042024-03-13
7Mintmarkreferral2024-03-082024-03-09
8Kiranakartpartner2024-03-02NULL

Output

channelconverted_trialsmedian_days
organic37
paid_search25.5
referral11

How to solve Median Days From Trial to Paid by Acquisition Channel

There is no MEDIAN in MySQL, so it is built from a ranking. Keep only converted trials, compute each one's days, and in a window per channel number them by days (ROW_NUMBER, with trial_id breaking equal days so the numbering is deterministic) and count them (COUNT(*) OVER).

For a count n, positions FLOOR((n + 1) / 2) and FLOOR((n + 2) / 2) are the middle: for n = 3 both are 2, for n = 2 they are 1 and 2. Keeping those rows and averaging their days gives the median in both cases — 4, 7, 10 → 7.0 for organic, and 2, 9 → 5.5 for paid search. Equal day counts do not matter: whichever tied row sits in the middle position has the same value. Because the average of two integers ends in .0 or .5, rounding to one decimal is exact.

The window-free alternative uses the definition of a median: a value is a middle value when at least half the values are ≤ it and at least half are ≥ it. Those values are the one or two medians (possibly repeated), so AVG(DISTINCT days) over them is the median. It runs four correlated counts per row — quadratic — while the window plan is one sort per channel.

Reference solution (MySQL)

WITH conv AS (
  SELECT channel, DATEDIFF(converted_on, started_on) AS days,
         ROW_NUMBER() OVER (PARTITION BY channel ORDER BY DATEDIFF(converted_on, started_on), trial_id) AS rn,
         COUNT(*) OVER (PARTITION BY channel) AS cnt
  FROM trials
  WHERE converted_on IS NOT NULL
)
SELECT channel, MAX(cnt) AS converted_trials, ROUND(AVG(days), 1) AS median_days
FROM conv
WHERE rn IN (FLOOR((cnt + 1) / 2), FLOOR((cnt + 2) / 2))
GROUP BY channel
ORDER BY channel

Another way

WITH conv AS (
  SELECT trial_id, channel, DATEDIFF(converted_on, started_on) AS days FROM trials WHERE converted_on IS NOT NULL
)
SELECT c.channel,
       (SELECT COUNT(*) FROM conv x WHERE x.channel = c.channel) AS converted_trials,
       ROUND(AVG(DISTINCT c.days), 1) AS median_days
FROM conv c
WHERE (SELECT COUNT(*) FROM conv x WHERE x.channel = c.channel AND x.days <= c.days) * 2 >= (SELECT COUNT(*) FROM conv x WHERE x.channel = c.channel)
  AND (SELECT COUNT(*) FROM conv x WHERE x.channel = c.channel AND x.days >= c.days) * 2 >= (SELECT COUNT(*) FROM conv x WHERE x.channel = c.channel)
GROUP BY c.channel
ORDER BY c.channel

← Month-End MRR From Subscription Periods · MRR Movements: New, Expansion, Contraction and Churn →