SQL Monthly Active Users
The same pattern as DAU, with the window widened from a day to a month.
What & Why
MAU is DAU's identical query, with DATE_TRUNC('month', ...) instead of 'day'. A user only needs one single event anywhere in the month to count.
See How It Works
BUSINESS QUESTION
Growth wants one MAU value for every month represented in the events table.
| id | user_id | event_name | created_at | properties |
|---|---|---|---|---|
| 1001 | 101 | signup | 2024-01-15 09:12:00+00 | {"source":"organic"} |
| 1002 | 101 | activated | 2024-01-16 14:25:00+00 | {"step":"workspace"} |
| 1003 | 102 | signup | 2024-02-10 11:08:00+00 | {"source":"paid"} |
| 1004 | 103 | report_viewed | 2024-03-12 16:40:00+00 | {"report":"retention"} |
EXAMPLE QUERY
SELECT
DATE_TRUNC('month', created_at)::date AS activity_month,
COUNT(DISTINCT user_id) AS mau
FROM growth.events
WHERE user_id IS NOT NULL
GROUP BY activity_month
ORDER BY activity_month;RESULT — monthly active users
| activity_month | mau |
|---|---|
| 2024-01-01 | 1 |
| 2024-02-01 | 1 |
| 2024-03-01 | 1 |
User 101 appears twice in January but counts once for January MAU.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario