Free-Trial Conversion and Second-Month Funnel

Hard
496.

Free-Trial Conversion and Second-Month Funnel

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

ColumnType
account_idint
trial_startdate

Primary key: account_id.

One free trial per account.

Table: Payment

ColumnTypeValues
payment_idint
account_idint
paid_ondate
statusenumsuccess, failed

Primary key: payment_id.

Every autopay attempt; account_id always names a row of Trial.

Example

The rows must come back in the order shown.

Trial

account_idtrial_start
12024-03-02
22024-03-10
32024-03-20
42024-03-28
52024-04-05
62024-04-11

Payment

payment_idaccount_idpaid_onstatus
112024-03-09success
212024-04-08success
322024-03-17failed
422024-03-19success
532024-03-30success
632024-04-19success
742024-04-04success
842024-05-12success
952024-04-12success
1052024-05-06failed
1 more row

Output

trial_monthtrialsconverted_pctretained_pct
2024-0347550
2024-042500
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.

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

ColumnType
account_idint
trial_startdate

Primary key: account_id.

One free trial per account.

Table: Payment

ColumnType
payment_idint
account_idint
paid_ondate
statusenum(success, failed)

Primary key: payment_id.

Every autopay attempt; account_id always names a row of Trial.

Examples

Example 1

Trial

account_idtrial_start
12024-03-02
22024-03-10
32024-03-20
42024-03-28
52024-04-05
62024-04-11

Payment

payment_idaccount_idpaid_onstatus
112024-03-09success
212024-04-08success
322024-03-17failed
422024-03-19success
532024-03-30success
632024-04-19success
742024-04-04success
842024-05-12success
952024-04-12success
1052024-05-06failed
1162024-04-21success

Output

trial_monthtrialsconverted_pctretained_pct
2024-0347550
2024-042500

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_month

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