Meta: Growth Accounting of Monthly Active Users

Hard
767.

Meta: Growth Accounting of Monthly Active Users

Meta's growth teams break each month's actives into where they came from. For a new app like Threads, a data scientist asks for that table from the activity log. A user is active in a month if they have at least one activity date in it. For each month, classify:

  • new_users — active this month, and this is the first month they were ever active;
  • retained — active this month and also in the previous calendar month;
  • resurrected — active this month, not in the previous month, but active in some earlier month;
  • churned — active in the previous month but not this month.

Return one row for every calendar month from the first month with any activity to the last, including months in between with no activity at all, with the columns month (YYYY-MM), new_users, retained, resurrected and churned, ordered by month. An empty log returns no rows.

A practice scenario set at Meta: the tables and data are invented, and this is not a question from, or endorsed by, Meta.

Tables

Table: AppActivity

ColumnType
user_idint
activity_datedate

Primary key (together): user_id, activity_date.

One row per user per day on which they used the app.

Example

The rows must come back in the order shown.

AppActivity

user_idactivity_date
12025-01-05
12025-01-20
12025-02-03
12025-04-10
22025-01-15
22025-03-02
32025-02-14
32025-03-14
32025-04-01
42025-04-30
1 more row

Output

monthnew_usersretainedresurrectedchurned
2025-012000
2025-021101
2025-030111
2025-041111
2025-050003
2025-061000
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.

Meta: Growth Accounting of Monthly Active Users

Meta's growth teams break each month's actives into where they came from.

  • Difficulty: Hard
  • Topics: Window Functions, Subqueries, Dates
  • Dialect: MySQL
  • Problem: #767

Problem statement

Meta's growth teams break each month's actives into where they came from. For a new app like Threads, a data scientist asks for that table from the activity log. A user is active in a month if they have at least one activity date in it. For each month, classify:

  • new_users — active this month, and this is the first month they were ever active;
  • retained — active this month and also in the previous calendar month;
  • resurrected — active this month, not in the previous month, but active in some earlier month;
  • churned — active in the previous month but not this month.

Return one row for every calendar month from the first month with any activity to the last, including months in between with no activity at all, with the columns month (YYYY-MM), new_users, retained, resurrected and churned, ordered by month. An empty log returns no rows.

A practice scenario set at Meta: the tables and data are invented, and this is not a question from, or endorsed by, Meta.

Tables

Table: AppActivity

ColumnType
user_idint
activity_datedate

Primary key: user_id, activity_date.

One row per user per day on which they used the app.

Examples

Example 1

AppActivity

user_idactivity_date
12025-01-05
12025-01-20
12025-02-03
12025-04-10
22025-01-15
22025-03-02
32025-02-14
32025-03-14
32025-04-01
42025-04-30
52025-06-08

Output

monthnew_usersretainedresurrectedchurned
2025-012000
2025-021101
2025-030111
2025-041111
2025-050003
2025-061000

How to solve Meta: Growth Accounting of Monthly Active Users

Growth accounting classifies every (user, month) pair, so the first move is to reduce the daily log to distinct active months, encoded as YEAR * 12 + MONTH - 1. With that number, the previous calendar month is mi - 1 even across January, and the label is rebuilt with DIV, MOD and LPAD.

The output needs every month between the first and the last, including empty ones (they still have churn from the month before), so a recursive CTE counts up from the minimum to the maximum month. The reference then fills each month with four correlated counts: new users are those whose first month is this one; retained users are active here and in mi - 1; resurrected users are active here, not in mi - 1, with a first month earlier still; churned users were active in mi - 1 and are missing now. The three active categories partition the month's actives, and an empty month gets zeros except for churn.

The alternative classifies each pair once with window functions: LAG(mi) gives the previous active month of the user (NULL → new, mi - 1 → retained, anything older → resurrected), and LEAD(mi) finds the end of a streak, which emits one churn event in the following month. A LEFT JOIN of these events onto the calendar and conditional sums finish the table. It sorts each user's months once instead of probing per month.

Reference solution (MySQL)

WITH RECURSIVE ua AS (
  SELECT DISTINCT user_id, YEAR(activity_date) * 12 + MONTH(activity_date) - 1 AS mi FROM AppActivity
), bounds AS (
  SELECT MIN(mi) AS lo, MAX(mi) AS hi FROM ua
), months AS (
  SELECT lo AS mi FROM bounds WHERE lo IS NOT NULL
  UNION ALL
  SELECT mi + 1 FROM months WHERE mi < (SELECT hi FROM bounds)
), firsts AS (
  SELECT user_id, MIN(mi) AS first_mi FROM ua GROUP BY user_id
)
SELECT CONCAT(m.mi DIV 12, '-', LPAD(m.mi MOD 12 + 1, 2, '0')) AS month,
       (SELECT COUNT(*) FROM firsts f WHERE f.first_mi = m.mi) AS new_users,
       (SELECT COUNT(*) FROM ua a JOIN ua p ON p.user_id = a.user_id AND p.mi = a.mi - 1 WHERE a.mi = m.mi) AS retained,
       (SELECT COUNT(*) FROM ua a JOIN firsts f ON f.user_id = a.user_id
         WHERE a.mi = m.mi AND f.first_mi < m.mi
           AND NOT EXISTS (SELECT 1 FROM ua p WHERE p.user_id = a.user_id AND p.mi = m.mi - 1)) AS resurrected,
       (SELECT COUNT(*) FROM ua p WHERE p.mi = m.mi - 1
           AND NOT EXISTS (SELECT 1 FROM ua a WHERE a.user_id = p.user_id AND a.mi = m.mi)) AS churned
FROM months m
ORDER BY m.mi

Another way

WITH RECURSIVE ua AS (
  SELECT DISTINCT user_id, YEAR(activity_date) * 12 + MONTH(activity_date) - 1 AS mi FROM AppActivity
), tagged AS (
  SELECT user_id, mi,
         LAG(mi) OVER (PARTITION BY user_id ORDER BY mi) AS prev_mi,
         LEAD(mi) OVER (PARTITION BY user_id ORDER BY mi) AS next_mi
  FROM ua
), months AS (
  SELECT MIN(mi) AS mi, MAX(mi) AS hi FROM ua HAVING MIN(mi) IS NOT NULL
  UNION ALL
  SELECT mi + 1, hi FROM months WHERE mi < hi
), moves AS (
  SELECT mi, CASE WHEN prev_mi IS NULL THEN 'new' WHEN prev_mi = mi - 1 THEN 'retained' ELSE 'resurrected' END AS kind FROM tagged
  UNION ALL
  SELECT mi + 1, 'churned' FROM tagged WHERE next_mi IS NULL OR next_mi > mi + 1
)
SELECT CONCAT(m.mi DIV 12, '-', LPAD(m.mi MOD 12 + 1, 2, '0')) AS month,
       SUM(CASE WHEN v.kind = 'new' THEN 1 ELSE 0 END) AS new_users,
       SUM(CASE WHEN v.kind = 'retained' THEN 1 ELSE 0 END) AS retained,
       SUM(CASE WHEN v.kind = 'resurrected' THEN 1 ELSE 0 END) AS resurrected,
       SUM(CASE WHEN v.kind = 'churned' THEN 1 ELSE 0 END) AS churned
FROM months m
LEFT JOIN moves v ON v.mi = m.mi
GROUP BY m.mi
ORDER BY m.mi

← Meta: WhatsApp Business Conversation Windows · Meta: Trending Reels Tags Split Out of Comma-Separated Lists →