Month-over-Month Active User Retention

MediumSQL

Reported in interviews at Meta

Problem statement

A social app logs every action a user takes. The growth team tracks month-over-month active-user retention: of the users who were active last month, what fraction came back and were active again this month? Write the query that produces this metric for every month.

Table (SQLite dialect):

  • user_actions(user_id INT, event_type TEXT, event_ts TEXT): one row per action; event_type is 'sign-in', 'like' or 'comment'; event_ts is 'YYYY-MM-DD HH:MM:SS' text.

Definitions:

  • A user is active in a month if they have at least one action of any type in that calendar month.
  • For a month $M$: prev_active_users = users active in the month before $M$; retained_users = how many of those are also active in $M$; retention_rate = retained_users / prev_active_users as a decimal (round to 4 decimals; unrounded is also accepted).
  • The month before January is December of the previous year.

Return month, prev_active_users, retained_users, retention_rate, with month as 'YYYY-MM', for every month $M$ that (a) has at least one active user in the month before it and (b) is not later than the latest month present in the table. A month with no activity at all that follows an active month still gets a row with 0 retained. Order by month.

Example

user_id | event_type | event_ts
1       | sign-in    | 2026-01-03 08:15:00
1       | like       | 2026-01-20 19:02:11
2       | comment    | 2026-01-11 12:00:00
3       | sign-in    | 2026-01-28 23:59:59
1       | comment    | 2026-02-02 07:45:00
3       | like       | 2026-02-14 10:10:10
4       | sign-in    | 2026-02-21 18:30:00
3       | sign-in    | 2026-03-01 00:00:01
4       | like       | 2026-03-09 09:09:09
2       | like       | 2026-03-30 21:00:00

Output:

month   | prev_active_users | retained_users | retention_rate
2026-02 | 3                 | 2              | 0.6667
2026-03 | 3                 | 2              | 0.6667

January's active users are {1, 2, 3} and users 1 and 3 return in February. February's are {1, 3, 4} and users 3 and 4 return in March. User 2 comes back in March after skipping February, which does not count as retention from February. January has no row because there is no December data.

Show hint

What is the right grain for this metric, and does a user with many actions in a month count more than once?

Relevant roles: Data Scientist and Forward Deployed Engineer.

Related free problems