Dream11: Second-Paid-Contest Activation Rate by Signup Month
Dream11's growth team calls a new user activated when, after their first paid contest, they come back for another paid contest quickly: a user is activated…
- Difficulty: Hard
- Topics: Subqueries, Dates, Aggregation, Joins
- Dialect: MySQL
- Problem: #646
Problem statement
Dream11's growth team calls a new user activated when, after their first paid contest, they come back for another paid contest quickly: a user is activated if they have at least two paid entries (entry fee above 0) dated no later than 7 days after the date of their first paid entry. Two paid entries on the first day count; free contests never count.
For every signup month (signup_month as 'YYYY-MM' of signup_date), return signup_month, signups (users who signed up that month), activated (how many of them are activated) and activation_pct (100 × activated / signups, rounded to 2 decimals). List every month that has a signup. Order by signup_month.
A practice scenario set at Dream11: the tables and data are invented, and this is not a question from, or endorsed by, Dream11.
Tables
Table: AppUser
| Column | Type |
|---|---|
| user_id | int |
| signup_date | date |
Primary key: user_id.
One row per account.
Table: ContestEntry
| Column | Type |
|---|---|
| entry_id | int |
| user_id | int |
| joined_on | date |
| entry_fee | int |
Primary key: entry_id.
One row per contest entry; entry_fee 0 is a free contest.
Examples
Example 1
AppUser
| user_id | signup_date |
|---|---|
| 1 | 2025-01-05 |
| 2 | 2025-01-20 |
| 3 | 2025-01-28 |
| 4 | 2025-02-02 |
| 5 | 2025-02-14 |
| 6 | 2025-03-01 |
ContestEntry
| entry_id | user_id | joined_on | entry_fee |
|---|---|---|---|
| 10 | 1 | 2025-01-06 | 49 |
| 11 | 1 | 2025-01-13 | 25 |
| 12 | 2 | 2025-01-21 | 0 |
| 13 | 2 | 2025-01-22 | 49 |
| 14 | 2 | 2025-01-30 | 49 |
| 15 | 3 | 2025-01-29 | 25 |
| 16 | 3 | 2025-01-29 | 99 |
| 17 | 4 | 2025-02-03 | 0 |
| 18 | 4 | 2025-02-04 | 0 |
| 19 | 5 | 2025-02-15 | 49 |
Output
| signup_month | signups | activated | activation_pct |
|---|---|---|---|
| 2025-01 | 3 | 2 | 66.67 |
| 2025-02 | 2 | 0 | 0 |
| 2025-03 | 1 | 0 | 0 |
How to solve Dream11: Second-Paid-Contest Activation Rate by Signup Month
The metric is a cohort ratio, so the work splits into a per-user flag and a per-month rollup.
For the flag, first_paid takes each user's earliest paid date (MIN(joined_on) over entries with a fee — a free contest does not start the clock). Joining back to the paid entries up to DATE_ADD(first_day, INTERVAL 7 DAY) collects the first entry itself plus any quick follow-ups; HAVING COUNT(*) >= 2 keeps the users with at least one more. Two paid entries on the same first day count, and day 7 is inside the window while day 8 is not.
For the rollup, every user belongs to the denominator whether or not they ever played, so the activated set is LEFT JOINed onto AppUser and grouped by the formatted month: COUNT(*) counts signups and COUNT(a.user_id) counts only matched (activated) users. Grouping by the signup_month alias keeps MySQL's ONLY_FULL_GROUP_BY happy. Months hold at most a few dozen signups here, so the rounded percentage never sits on a half.
The alternative numbers each user's paid entries and tests whether entry 2 is within 7 days of entry 1 — the same condition, since the second-earliest paid entry is in the window exactly when at least two are.
Reference solution (MySQL)
WITH first_paid AS (
SELECT user_id, MIN(joined_on) AS first_day
FROM ContestEntry
WHERE entry_fee > 0
GROUP BY user_id
),
activated AS (
SELECT f.user_id
FROM first_paid f
JOIN ContestEntry e ON e.user_id = f.user_id AND e.entry_fee > 0
AND e.joined_on <= DATE_ADD(f.first_day, INTERVAL 7 DAY)
GROUP BY f.user_id
HAVING COUNT(*) >= 2
)
SELECT DATE_FORMAT(u.signup_date, '%Y-%m') AS signup_month,
COUNT(*) AS signups,
COUNT(a.user_id) AS activated,
ROUND(100 * COUNT(a.user_id) / COUNT(*), 2) AS activation_pct
FROM AppUser u
LEFT JOIN activated a ON a.user_id = u.user_id
GROUP BY signup_month
ORDER BY signup_monthAnother way
WITH paid AS (
SELECT user_id, joined_on, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY joined_on, entry_id) AS k
FROM ContestEntry WHERE entry_fee > 0
),
flags AS (
SELECT u.user_id, LEFT(u.signup_date, 7) AS signup_month,
CASE WHEN EXISTS (SELECT 1 FROM paid a JOIN paid b ON b.user_id = a.user_id AND b.k = 2
WHERE a.user_id = u.user_id AND a.k = 1 AND DATEDIFF(b.joined_on, a.joined_on) <= 7)
THEN 1 ELSE 0 END AS is_active
FROM AppUser u
)
SELECT signup_month, COUNT(*) AS signups, SUM(is_active) AS activated,
ROUND(100 * SUM(is_active) / COUNT(*), 2) AS activation_pct
FROM flags GROUP BY signup_month ORDER BY signup_month← Dream11: Withdrawals Right After a Deposit With No Play Between · Dream11: Biggest Live Leaderboard Climb of Each Team →