SQL DAU/MAU Ratio
A stickiness metric — what fraction of your monthly users showed up on a given day?
What & Why
DAU/MAU (often called "stickiness") divides a specific day's active users by that same month's active users. A higher ratio means users come back often within the month, rather than showing up once and disappearing.
See How It Works
BUSINESS QUESTION
Growth wants a daily stickiness ratio calculated from product events.
| 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
WITH daily AS (
SELECT created_at::date AS activity_date, COUNT(DISTINCT user_id) AS dau
FROM growth.events
GROUP BY activity_date
), monthly AS (
SELECT DATE_TRUNC('month', created_at)::date AS month_start, COUNT(DISTINCT user_id) AS mau
FROM growth.events
GROUP BY month_start
)
SELECT
d.activity_date,
d.dau,
m.mau,
ROUND(d.dau::numeric / NULLIF(m.mau, 0), 4) AS dau_mau_ratio
FROM daily d
JOIN monthly m ON m.month_start = DATE_TRUNC('month', d.activity_date)::date
ORDER BY d.activity_date;RESULT — daily stickiness
| activity_date | dau | mau | dau_mau_ratio |
|---|---|---|---|
| 2024-01-15 | 1 | 1 | 1.0000 |
| 2024-01-16 | 1 | 1 | 1.0000 |
| 2024-02-10 | 1 | 1 | 1.0000 |
| 2024-03-12 | 1 | 1 | 1.0000 |
The representative monthly and daily distinct-user counts are both one.
This lesson's practice is part of Pro.
Sign up free to try it on a real business scenario