Dream11: Second-Paid-Contest Activation Rate by Signup Month

Hard
646.

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

ColumnType
user_idint
signup_datedate

Primary key: user_id.

One row per account.

Table: ContestEntry

ColumnType
entry_idint
user_idint
joined_ondate
entry_feeint

Primary key: entry_id.

One row per contest entry; entry_fee 0 is a free contest.

Example

The rows must come back in the order shown.

AppUser

user_idsignup_date
12025-01-05
22025-01-20
32025-01-28
42025-02-02
52025-02-14
62025-03-01

ContestEntry

entry_iduser_idjoined_onentry_fee
1012025-01-0649
1112025-01-1325
1222025-01-210
1322025-01-2249
1422025-01-3049
1532025-01-2925
1632025-01-2999
1742025-02-030
1842025-02-040
1952025-02-1549

Output

signup_monthsignupsactivatedactivation_pct
2025-013266.67
2025-02200
2025-03100
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.

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

ColumnType
user_idint
signup_datedate

Primary key: user_id.

One row per account.

Table: ContestEntry

ColumnType
entry_idint
user_idint
joined_ondate
entry_feeint

Primary key: entry_id.

One row per contest entry; entry_fee 0 is a free contest.

Examples

Example 1

AppUser

user_idsignup_date
12025-01-05
22025-01-20
32025-01-28
42025-02-02
52025-02-14
62025-03-01

ContestEntry

entry_iduser_idjoined_onentry_fee
1012025-01-0649
1112025-01-1325
1222025-01-210
1322025-01-2249
1422025-01-3049
1532025-01-2925
1632025-01-2999
1742025-02-030
1842025-02-040
1952025-02-1549

Output

signup_monthsignupsactivatedactivation_pct
2025-013266.67
2025-02200
2025-03100

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_month

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