Monthly Active Users, New Versus Returning

Medium
413.

Monthly Active Users, New Versus Returning

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

ColumnType
user_idint
signed_up_ondate

Primary key: user_id.

One row per account holder.

Table: events

ColumnType
event_idint
user_idint
occurred_atdatetime

Primary key: event_id.

Product events; user_id always names a row of users, and no event is before its user's signup.

Example

The rows must come back in the order shown.

users

user_idsigned_up_on
12024-01-15
22024-02-03
32024-02-28
42024-03-01

events

event_iduser_idoccurred_at
112024-02-01 09:00:00
212024-02-14 10:00:00
322024-02-03 12:00:00
432024-02-29 23:00:00
522024-03-02 08:00:00
632024-03-05 19:30:00
742024-03-01 07:45:00
842024-03-09 18:00:00

Output

activity_monthactive_usersnew_usersreturning_users
2024-02321
2024-03312
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.

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

ColumnType
user_idint
signed_up_ondate

Primary key: user_id.

One row per account holder.

Table: events

ColumnType
event_idint
user_idint
occurred_atdatetime

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_idsigned_up_on
12024-01-15
22024-02-03
32024-02-28
42024-03-01

events

event_iduser_idoccurred_at
112024-02-01 09:00:00
212024-02-14 10:00:00
322024-02-03 12:00:00
432024-02-29 23:00:00
522024-03-02 08:00:00
632024-03-05 19:30:00
742024-03-01 07:45:00
842024-03-09 18:00:00

Output

activity_monthactive_usersnew_usersreturning_users
2024-02321
2024-03312

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_month

Another 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 →