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
| Column | Type |
|---|---|
| user_id | int |
| activity_date | date |
Primary key: user_id, activity_date.
One row per user per day on which they used the app.
Examples
Example 1
AppActivity
| user_id | activity_date |
|---|---|
| 1 | 2025-01-05 |
| 1 | 2025-01-20 |
| 1 | 2025-02-03 |
| 1 | 2025-04-10 |
| 2 | 2025-01-15 |
| 2 | 2025-03-02 |
| 3 | 2025-02-14 |
| 3 | 2025-03-14 |
| 3 | 2025-04-01 |
| 4 | 2025-04-30 |
| 5 | 2025-06-08 |
Output
| month | new_users | retained | resurrected | churned |
|---|---|---|---|---|
| 2025-01 | 2 | 0 | 0 | 0 |
| 2025-02 | 1 | 1 | 0 | 1 |
| 2025-03 | 0 | 1 | 1 | 1 |
| 2025-04 | 1 | 1 | 1 | 1 |
| 2025-05 | 0 | 0 | 0 | 3 |
| 2025-06 | 1 | 0 | 0 | 0 |
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.miAnother 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 →