Monthly Active Users, New Versus Returning — SQL Problem
The MAU chart is split into new active users — who signed up in the same calendar month they were active — and returning ones, who signed up in an earlier…
- Difficulty: Medium
- Topics: Aggregation, Dates, Conditional Logic
- Dialect: MySQL
- Problem: #413
Problem statement
The MAU chart is split into new active users — who signed up in the same calendar month they were active — and returning ones, who signed up in an earlier month. A user active several times in a month counts once that month.
Return one row per month that has events, with activity_month ('YYYY-MM'), active_users, new_users and returning_users, ordered by activity_month.
Tables
Table: users
| Column | Type |
|---|---|
| user_id | int |
| signed_up_on | date |
Primary key: user_id.
One row per account holder.
Table: events
| Column | Type |
|---|---|
| event_id | int |
| user_id | int |
| occurred_at | datetime |
Primary key: event_id.
Product events; user_id always names a row of users, and no event is before its user's signup.
Examples
Example 1
users
| user_id | signed_up_on |
|---|---|
| 1 | 2024-01-15 |
| 2 | 2024-02-03 |
| 3 | 2024-02-28 |
| 4 | 2024-03-01 |
events
| event_id | user_id | occurred_at |
|---|---|---|
| 1 | 1 | 2024-02-01 09:00:00 |
| 2 | 1 | 2024-02-14 10:00:00 |
| 3 | 2 | 2024-02-03 12:00:00 |
| 4 | 3 | 2024-02-29 23:00:00 |
| 5 | 2 | 2024-03-02 08:00:00 |
| 6 | 3 | 2024-03-05 19:30:00 |
| 7 | 4 | 2024-03-01 07:45:00 |
| 8 | 4 | 2024-03-09 18:00:00 |
Output
| activity_month | active_users | new_users | returning_users |
|---|---|---|---|
| 2024-02 | 3 | 2 | 1 |
| 2024-03 | 3 | 1 | 2 |
How to solve Monthly Active Users, New Versus Returning
Each event is joined to its user's signup date and grouped by the event's month. Three distinct counts come out of one group: all users, the users whose signup month equals the activity month, and those whose signup month is earlier. The last two are conditional counts — COUNT(DISTINCT CASE WHEN cond THEN e.user_id END) — the CASE yields NULL for rows that fail the condition, and COUNT ignores NULLs.
DISTINCT matters inside each conditional count too: user 1's two February events are one returning user, not two. Since no event precedes its user's signup, every active user is either new or returning, so the last two columns always add up to the first — a good self-check.
Comparing months as 'YYYY-MM' strings is safe because the format is fixed-width and year-first. User 3 signed up on 28 February and was active on the 29th (a leap day) — new in February — and in March counts as returning. Deduplicating (month, user) first and summing 0/1 flags is the alternative; both are one scan plus a join.
Reference solution (MySQL)
SELECT DATE_FORMAT(e.occurred_at, '%Y-%m') AS activity_month,
COUNT(DISTINCT e.user_id) AS active_users,
COUNT(DISTINCT CASE WHEN DATE_FORMAT(u.signed_up_on, '%Y-%m') = DATE_FORMAT(e.occurred_at, '%Y-%m') THEN e.user_id END) AS new_users,
COUNT(DISTINCT CASE WHEN DATE_FORMAT(u.signed_up_on, '%Y-%m') < DATE_FORMAT(e.occurred_at, '%Y-%m') THEN e.user_id END) AS returning_users
FROM events e
JOIN users u ON u.user_id = e.user_id
GROUP BY DATE_FORMAT(e.occurred_at, '%Y-%m')
ORDER BY activity_monthAnother way
WITH monthly AS (
SELECT DISTINCT LEFT(e.occurred_at, 7) AS activity_month, e.user_id, LEFT(u.signed_up_on, 7) AS signup_month
FROM events e JOIN users u ON u.user_id = e.user_id
)
SELECT activity_month, COUNT(*) AS active_users,
SUM(CASE WHEN signup_month = activity_month THEN 1 ELSE 0 END) AS new_users,
SUM(CASE WHEN signup_month < activity_month THEN 1 ELSE 0 END) AS returning_users
FROM monthly GROUP BY activity_month ORDER BY activity_month← Signups and Upgrades by UTM Source · Upgrades, Downgrades and Lateral Plan Moves →