Daily active users is a count of distinct users per day, which makes it one of the easiest queries to write and one of the easiest to get quietly wrong, because the hard part is the word “active”.
The query
Assume an events table with user_id and created_at.
PostgreSQL
SELECT
DATE_TRUNC('day', created_at) AS day,
COUNT(DISTINCT user_id) AS dau
FROM events
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
GROUP BY 1
ORDER BY 1;MySQL
SELECT
DATE(created_at) AS day,
COUNT(DISTINCT user_id) AS dau
FROM events
WHERE created_at >= CURDATE() - INTERVAL 30 DAY
GROUP BY day
ORDER BY day;Define active before you query
COUNT(DISTINCT user_id) over an events table counts anyone who generated any event. That usually includes background activity: a webhook firing, a session refreshing, a scheduled job running under a user’s identity. The number comes out high and moves for reasons nobody can explain.
Decide what counts as a deliberate action and filter for it:
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
AND event_type IN ('query_executed', 'dashboard_viewed', 'login')The specific list matters less than writing it down. An undefined DAU cannot be compared against last quarter’s.
Days with no activity vanish
As with any GROUP BY over dates, a day with zero events produces no row at all. On a chart, the line joins across the gap and the dip disappears, which is the opposite of what you want, because a day with no activity is the most interesting day in the series.
-- PostgreSQL
SELECT
d.day,
COUNT(DISTINCT e.user_id) AS dau
FROM generate_series(
CURRENT_DATE - INTERVAL '30 days',
CURRENT_DATE,
INTERVAL '1 day'
) AS d(day)
LEFT JOIN events e
ON DATE_TRUNC('day', e.created_at) = d.day
GROUP BY d.day
ORDER BY d.day;Time zones move users between days
Events are usually stored in UTC. If your users are in Europe, everything that happens after 01:00 local time in summer belongs to the previous UTC day. The daily shape comes out shifted, and the weekly pattern looks subtly wrong without ever looking broken.
-- PostgreSQL
DATE_TRUNC('day', created_at AT TIME ZONE 'UTC' AT TIME ZONE 'Europe/Madrid')
-- MySQL
DATE(CONVERT_TZ(created_at, '+00:00', 'Europe/Madrid'))WAU, MAU and the ratio that matters
The weekly and monthly versions are the same query with a wider window, and the common mistake is to sum DAU over the period. A user active on five days counts five times that way. Both need their own COUNT(DISTINCT) over the whole window:
-- PostgreSQL: MAU for the last 30 days
SELECT COUNT(DISTINCT user_id) AS mau
FROM events
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days';DAU divided by MAU is the figure worth putting on a dashboard. It is the share of your monthly users who show up on an average day, so it measures habit rather than reach. A product with growing MAU and a falling ratio is acquiring people who do not come back.
-- PostgreSQL
SELECT
ROUND(
100.0 * (
SELECT COUNT(DISTINCT user_id) FROM events
WHERE created_at >= CURRENT_DATE - INTERVAL '1 day'
) / NULLIF((
SELECT COUNT(DISTINCT user_id) FROM events
WHERE created_at >= CURRENT_DATE - INTERVAL '30 days'
), 0),
1
) AS dau_mau_pct;Making it faster
COUNT(DISTINCT) over a large events table is expensive, because the database has to deduplicate. An index on events (created_at, user_id) lets it scan the date range and the user column together. If the table runs to hundreds of millions of rows, the usual answer is a daily rollup table rather than a faster query.
Or just ask for it
Every query here assumes your events table is called events, that user_id identifies a human rather than an API key, and that you have already decided which event types count.
ByeSQL reads your schema and writes the query against it, so “daily active users for the last 30 days” comes back as a chart with the SQL next to it, which is where you check whether its definition of active matches yours.