First-Week Retention by Sign-Up Month

Hard
444.

First-Week Retention by Sign-Up Month

Product reviews first-week retention by sign-up cohort. A user is retained when they opened the app on any day from 1 to 7 days after their sign-up date (opening the app on the sign-up day itself does not count). A cohort is all users who signed up in the same calendar month.

Return the columns cohort_month ('YYYY-MM'), cohort_size, retained and retention_pct (retained × 100 ÷ cohort size, rounded to 1 decimal) for every month that has sign-ups, ordered by cohort_month. A user's several opens count once.

Tables

Table: users

ColumnType
user_idint
signed_up_ondate
acquisition_channelvarchar

Primary key: user_id.

One row per account.

Table: app_opens

ColumnType
user_idint
opened_ondate

Primary key (together): user_id, opened_on.

One row per user per day they opened the app; user_id is always a row of users.

Example

The rows must come back in the order shown.

users

user_idsigned_up_onacquisition_channel
12025-01-05instagram_ads
22025-01-20referral
32025-01-31organic
42025-02-02referral
52025-02-14organic
62025-02-27instagram_ads

app_opens

user_idopened_on
12025-01-05
12025-01-12
22025-01-20
22025-01-28
32025-02-01
32025-02-03
42025-02-09
62025-03-06

Output

cohort_monthcohort_sizeretainedretention_pct
2025-013266.7
2025-023266.7
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.

First-Week Retention by Sign-Up Month — SQL Dates Problem

Product reviews first-week retention by sign-up cohort. A user is retained when they opened the app on any day from 1 to 7 days after their sign-up date…

  • Difficulty: Hard
  • Topics: Dates, Aggregation, Conditional Logic
  • Dialect: MySQL
  • Problem: #444

Problem statement

Product reviews first-week retention by sign-up cohort. A user is retained when they opened the app on any day from 1 to 7 days after their sign-up date (opening the app on the sign-up day itself does not count). A cohort is all users who signed up in the same calendar month.

Return the columns cohort_month ('YYYY-MM'), cohort_size, retained and retention_pct (retained × 100 ÷ cohort size, rounded to 1 decimal) for every month that has sign-ups, ordered by cohort_month. A user's several opens count once.

Tables

Table: users

ColumnType
user_idint
signed_up_ondate
acquisition_channelvarchar

Primary key: user_id.

One row per account.

Table: app_opens

ColumnType
user_idint
opened_ondate

Primary key: user_id, opened_on.

One row per user per day they opened the app; user_id is always a row of users.

Examples

Example 1

users

user_idsigned_up_onacquisition_channel
12025-01-05instagram_ads
22025-01-20referral
32025-01-31organic
42025-02-02referral
52025-02-14organic
62025-02-27instagram_ads

app_opens

user_idopened_on
12025-01-05
12025-01-12
22025-01-20
22025-01-28
32025-02-01
32025-02-03
42025-02-09
62025-03-06

Output

cohort_monthcohort_sizeretainedretention_pct
2025-013266.7
2025-023266.7

How to solve First-Week Retention by Sign-Up Month

Retention questions go in two levels. Per user, compute a 0/1 flag: did any open fall between signed_up_on + 1 and signed_up_on + 7? The solution LEFT JOINs the opens with that window in the ON clause — moving it to WHERE would drop users with no qualifying open and shrink the cohort — and turns COUNT(o.opened_on) > 0 into 1 or 0. The alternative uses EXISTS with DATEDIFF(...) BETWEEN 1 AND 7, which also counts several opens once.

Per cohort, group the users by DATE_FORMAT(signed_up_on, '%Y-%m'): COUNT(*) is the cohort size, SUM(flag) the retained users, and their ratio × 100 rounded to one decimal the percentage (the average of a 0/1 flag is the same ratio). Opens on the sign-up day, the day before or eight days after are outside the window, and a user who signed up on 31 January is retained by an open on 1 February — the window is about days since sign-up, not the calendar month.

The cost is one join of users to their opens with a range condition; the composite key (user_id, opened_on) makes it an index range scan.

Reference solution (MySQL)

SELECT DATE_FORMAT(signed_up_on, '%Y-%m') AS cohort_month,
       COUNT(*) AS cohort_size,
       SUM(is_retained) AS retained,
       ROUND(SUM(is_retained) * 100 / COUNT(*), 1) AS retention_pct
FROM (
  SELECT u.user_id, u.signed_up_on,
         CASE WHEN COUNT(o.opened_on) > 0 THEN 1 ELSE 0 END AS is_retained
  FROM users u
  LEFT JOIN app_opens o ON o.user_id = u.user_id
   AND o.opened_on BETWEEN DATE_ADD(u.signed_up_on, INTERVAL 1 DAY) AND DATE_ADD(u.signed_up_on, INTERVAL 7 DAY)
  GROUP BY u.user_id, u.signed_up_on
) t
GROUP BY DATE_FORMAT(signed_up_on, '%Y-%m')
ORDER BY cohort_month

Another way

SELECT cohort_month, COUNT(*) AS cohort_size, SUM(flag) AS retained, ROUND(AVG(flag) * 100, 1) AS retention_pct
FROM (
  SELECT LEFT(u.signed_up_on, 7) AS cohort_month,
         IF(EXISTS (SELECT 1 FROM app_opens o WHERE o.user_id = u.user_id
                    AND DATEDIFF(o.opened_on, u.signed_up_on) BETWEEN 1 AND 7), 1, 0) AS flag
  FROM users u
) t
GROUP BY cohort_month
ORDER BY cohort_month

← Longest Daily Posting Streak per Creator · Reshare Cascades: Size, Depth and Unique Resharers →