SQL Daily Active Users

Active Users, applied to a one-day window, repeated for every day in the data.

What & Why

DAU groups events by calendar day and counts distinct users per day — the day-by-day version of the active-users idea.

See How It Works

BUSINESS QUESTION

Growth wants one DAU value for every date 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
  created_at::date AS activity_date,
  COUNT(DISTINCT user_id) AS dau
FROM growth.events
WHERE user_id IS NOT NULL
GROUP BY activity_date
ORDER BY activity_date;

RESULT — daily active users

activity_date	dau
2024-01-15	1
2024-01-16	1
2024-02-10	1
2024-03-12	1

Each representative event day contains one distinct user.

This lesson's practice is part of Pro.

See what's included →

Advanced business practice