Free-Trial Conversion and Second-Month Funnel — SQL Problem
New accounts get a 7-day free trial, then UPI autopay charges the first month.
- Difficulty: Hard
- Topics: Conditional Logic, Dates, Subqueries
- Dialect: MySQL
- Problem: #496
Problem statement
New accounts get a 7-day free trial, then UPI autopay charges the first month. Per trial, measured in days after trial_start (DATEDIFF(paid_on, trial_start), counting only success payments):
- the trial converted when there is a successful payment on day 9 or earlier (paying during the trial counts too);
- a converted trial is retained when there is also a successful payment between day 30 and day 45, both included.
Return one row per trial start month with the columns trial_month (as YYYY-MM), trials, converted_pct and retained_pct — converted and retained trials as percentages of trials, each rounded to 2 decimal places. Order by trial_month.
Tables
Table: Trial
| Column | Type |
|---|---|
| account_id | int |
| trial_start | date |
Primary key: account_id.
One free trial per account.
Table: Payment
| Column | Type |
|---|---|
| payment_id | int |
| account_id | int |
| paid_on | date |
| status | enum(success, failed) |
Primary key: payment_id.
Every autopay attempt; account_id always names a row of Trial.
Examples
Example 1
Trial
| account_id | trial_start |
|---|---|
| 1 | 2024-03-02 |
| 2 | 2024-03-10 |
| 3 | 2024-03-20 |
| 4 | 2024-03-28 |
| 5 | 2024-04-05 |
| 6 | 2024-04-11 |
Payment
| payment_id | account_id | paid_on | status |
|---|---|---|---|
| 1 | 1 | 2024-03-09 | success |
| 2 | 1 | 2024-04-08 | success |
| 3 | 2 | 2024-03-17 | failed |
| 4 | 2 | 2024-03-19 | success |
| 5 | 3 | 2024-03-30 | success |
| 6 | 3 | 2024-04-19 | success |
| 7 | 4 | 2024-04-04 | success |
| 8 | 4 | 2024-05-12 | success |
| 9 | 5 | 2024-04-12 | success |
| 10 | 5 | 2024-05-06 | failed |
| 11 | 6 | 2024-04-21 | success |
Output
| trial_month | trials | converted_pct | retained_pct |
|---|---|---|---|
| 2024-03 | 4 | 75 | 50 |
| 2024-04 | 2 | 50 | 0 |
How to solve Free-Trial Conversion and Second-Month Funnel
A funnel is a set of per-entity flags that are then counted. Here the entity is the trial: LEFT JOIN its successful payments (the status test sits in ON, so a trial with only failed attempts still produces a row) and fold them into two flags with MAX(CASE …): converted if any payment came on day 9 or earlier, renewed if any came between day 30 and 45. A trial with no successful payment has NULL dates, the CASE conditions are not true, and both flags are 0.
The second stage is gated by the first: retained is converted AND renewed, so an account that skipped the first charge but paid on day 37 counts in neither. Then group by DATE_FORMAT(trial_start, '%Y-%m') and turn the counts into percentages of all trials, rounded. The boundaries are inclusive — day 9 converts, day 10 does not; days 30 and 45 renew, 29 and 46 do not.
The EXISTS alternative asks each question with its own subquery and date arithmetic instead of DATEDIFF. Either form reads each trial's payments once or twice; the join form does it in one pass.
Reference solution (MySQL)
WITH flags AS (
SELECT t.account_id, DATE_FORMAT(t.trial_start, '%Y-%m') AS trial_month,
MAX(CASE WHEN DATEDIFF(p.paid_on, t.trial_start) <= 9 THEN 1 ELSE 0 END) AS converted,
MAX(CASE WHEN DATEDIFF(p.paid_on, t.trial_start) BETWEEN 30 AND 45 THEN 1 ELSE 0 END) AS renewed
FROM Trial t
LEFT JOIN Payment p ON p.account_id = t.account_id AND p.status = 'success'
GROUP BY t.account_id, DATE_FORMAT(t.trial_start, '%Y-%m')
)
SELECT trial_month, COUNT(*) AS trials,
ROUND(100 * SUM(converted) / COUNT(*), 2) AS converted_pct,
ROUND(100 * SUM(CASE WHEN converted = 1 AND renewed = 1 THEN 1 ELSE 0 END) / COUNT(*), 2) AS retained_pct
FROM flags
GROUP BY trial_month
ORDER BY trial_monthAnother way
SELECT LEFT(t.trial_start, 7) AS trial_month, COUNT(*) AS trials,
ROUND(100 * SUM(IF(EXISTS (SELECT 1 FROM Payment p WHERE p.account_id = t.account_id AND p.status = 'success' AND p.paid_on <= DATE_ADD(t.trial_start, INTERVAL 9 DAY)), 1, 0)) / COUNT(*), 2) AS converted_pct,
ROUND(100 * SUM(IF(EXISTS (SELECT 1 FROM Payment p WHERE p.account_id = t.account_id AND p.status = 'success' AND p.paid_on <= DATE_ADD(t.trial_start, INTERVAL 9 DAY))
AND EXISTS (SELECT 1 FROM Payment q WHERE q.account_id = t.account_id AND q.status = 'success' AND q.paid_on BETWEEN DATE_ADD(t.trial_start, INTERVAL 30 DAY) AND DATE_ADD(t.trial_start, INTERVAL 45 DAY)), 1, 0)) / COUNT(*), 2) AS retained_pct
FROM Trial t
GROUP BY LEFT(t.trial_start, 7)
ORDER BY trial_month← Accounts Streaming Past Their Screen Limit · Daily Streams in an Album's First Week, Zero Days Included →