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
| Column | Type |
|---|---|
| trial_id | int |
| workspace_name | varchar |
| channel | enum(organic, paid_search, partner, referral) |
| started_on | date |
| converted_on | date |
Primary key: trial_id.
One row per free trial; converted_on is the first paid day, or NULL.
Examples
Example 1
trials
| trial_id | workspace_name | channel | started_on | converted_on |
|---|---|---|---|---|
| 1 | Zentrix Labs | organic | 2024-03-01 | 2024-03-11 |
| 2 | Quillpad | organic | 2024-03-02 | 2024-03-06 |
| 3 | Ledgerly | organic | 2024-03-05 | NULL |
| 4 | Orbitpay | organic | 2024-03-07 | 2024-03-14 |
| 5 | Vaani AI | paid_search | 2024-03-01 | 2024-03-03 |
| 6 | Tiffinly | paid_search | 2024-03-04 | 2024-03-13 |
| 7 | Mintmark | referral | 2024-03-08 | 2024-03-09 |
| 8 | Kiranakart | partner | 2024-03-02 | NULL |
Output
| channel | converted_trials | median_days |
|---|---|---|
| organic | 3 | 7 |
| paid_search | 2 | 5.5 |
| referral | 1 | 1 |
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 channelAnother 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 →