Fantasy App Signup Cohorts Retained Into the Next Month

Hard
470.

Fantasy App Signup Cohorts Retained Into the Next Month

The fantasy app groups users into cohorts by the calendar month they signed up in. A user is retained when they entered at least one contest in the calendar month right after their signup month (a user who signed up on 31 January is retained by any entry in February; entries in the signup month itself or two months later do not count).

Return one row per signup month with the columns cohort_month (YYYY-MM), cohort_size (users who signed up that month), retained_users and retention_pct = 100 * retained_users / cohort_size, rounded to 2 decimal places. Cohorts with nobody retained show 0. Order the rows by cohort_month.

Tables

Table: AppUser

ColumnType
user_idint
signed_up_ondate
cityvarchar

Primary key: user_id.

One row per registered user.

Table: ContestJoin

ColumnType
join_idint
user_idint
joined_ondate
entry_feeint

Primary key: join_id.

One row per contest entry; user_id is always in AppUser and joined_on is never before that user's signup.

Example

The rows must come back in the order shown.

AppUser

user_idsigned_up_oncity
12024-01-05Pune
22024-01-20Mumbai
32024-01-31Delhi
42024-02-10Kochi
52024-02-14Jaipur
62024-03-02Chennai

ContestJoin

join_iduser_idjoined_onentry_fee
112024-01-0649
212024-02-0349
312024-02-1799
422024-03-0125
532024-02-0149
642024-02-1199
752024-03-2925
862024-03-0549

Output

cohort_monthcohort_sizeretained_usersretention_pct
2024-013266.67
2024-022150
2024-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.

Fantasy App Signup Cohorts Retained Into the Next Month

The fantasy app groups users into cohorts by the calendar month they signed up in.

  • Difficulty: Hard
  • Topics: Dates, Joins, Aggregation
  • Dialect: MySQL
  • Problem: #470

Problem statement

The fantasy app groups users into cohorts by the calendar month they signed up in. A user is retained when they entered at least one contest in the calendar month right after their signup month (a user who signed up on 31 January is retained by any entry in February; entries in the signup month itself or two months later do not count).

Return one row per signup month with the columns cohort_month (YYYY-MM), cohort_size (users who signed up that month), retained_users and retention_pct = 100 * retained_users / cohort_size, rounded to 2 decimal places. Cohorts with nobody retained show 0. Order the rows by cohort_month.

Tables

Table: AppUser

ColumnType
user_idint
signed_up_ondate
cityvarchar

Primary key: user_id.

One row per registered user.

Table: ContestJoin

ColumnType
join_idint
user_idint
joined_ondate
entry_feeint

Primary key: join_id.

One row per contest entry; user_id is always in AppUser and joined_on is never before that user's signup.

Examples

Example 1

AppUser

user_idsigned_up_oncity
12024-01-05Pune
22024-01-20Mumbai
32024-01-31Delhi
42024-02-10Kochi
52024-02-14Jaipur
62024-03-02Chennai

ContestJoin

join_iduser_idjoined_onentry_fee
112024-01-0649
212024-02-0349
312024-02-1799
422024-03-0125
532024-02-0149
642024-02-1199
752024-03-2925
862024-03-0549

Output

cohort_monthcohort_sizeretained_usersretention_pct
2024-013266.67
2024-022150
2024-03100

How to solve Fantasy App Signup Cohorts Retained Into the Next Month

Retention is a per-user fact aggregated per cohort, so it pays to do it in two steps. First decide, for each user, whether some entry falls in the month after the signup month. Comparing month labels avoids day arithmetic: the target month is DATE_FORMAT(DATE_ADD(signed_up_on, INTERVAL 1 MONTH), '%Y-%m'). DATE_ADD by a month clamps 31 January to the end of February, so the label is still February — the month, not "30 days later", is what the statement asks for. DISTINCT (or EXISTS) makes a user with several entries count once.

Then group users by signup month. The LEFT JOIN to the retained list keeps every user, so COUNT(*) is the cohort size and COUNT(r.user_id) counts only the matched ones; a cohort where nobody came back gets 0 rather than disappearing. The percentage is rounded at the end.

The alternative computes a 0/1 flag per user with EXISTS over the next month's first and last day (LAST_DAY), which is the date-range version of the same idea and can use an index on joined_on. Each plan reads the users once plus one probe per user.

Reference solution (MySQL)

WITH retained AS (
  SELECT DISTINCT u.user_id
  FROM AppUser u
  JOIN ContestJoin j ON j.user_id = u.user_id
  WHERE DATE_FORMAT(j.joined_on, '%Y-%m') = DATE_FORMAT(DATE_ADD(u.signed_up_on, INTERVAL 1 MONTH), '%Y-%m')
)
SELECT DATE_FORMAT(u.signed_up_on, '%Y-%m') AS cohort_month,
       COUNT(*) AS cohort_size,
       COUNT(r.user_id) AS retained_users,
       ROUND(100 * COUNT(r.user_id) / COUNT(*), 2) AS retention_pct
FROM AppUser u
LEFT JOIN retained r ON r.user_id = u.user_id
GROUP BY DATE_FORMAT(u.signed_up_on, '%Y-%m')
ORDER BY cohort_month

Another way

SELECT cohort_month, COUNT(*) AS cohort_size, SUM(kept) AS retained_users, ROUND(100 * SUM(kept) / COUNT(*), 2) AS retention_pct
FROM (
  SELECT DATE_FORMAT(u.signed_up_on, '%Y-%m') AS cohort_month,
         CASE WHEN EXISTS (
           SELECT 1 FROM ContestJoin j
           WHERE j.user_id = u.user_id
             AND j.joined_on >= DATE_ADD(DATE_SUB(u.signed_up_on, INTERVAL DAY(u.signed_up_on) - 1 DAY), INTERVAL 1 MONTH)
             AND j.joined_on <= LAST_DAY(DATE_ADD(DATE_SUB(u.signed_up_on, INTERVAL DAY(u.signed_up_on) - 1 DAY), INTERVAL 1 MONTH))
         ) THEN 1 ELSE 0 END AS kept
  FROM AppUser u
) t
GROUP BY cohort_month
ORDER BY cohort_month

← Median Fantasy Points per Gameweek by Player Role · Days Each Player Was Out Injured, Merging Overlapping Records →