How to Calculate DAU, WAU, and MAU in SQL

Active users is the metric everyone quotes and few people define carefully. Daily, weekly, monthly active users are simple in concept and full of small traps in practice. Here are the queries I use, plus the decisions you have to make before the number means anything.

Decide what "active" means first

This is the part people skip, and it's the part that matters. "Active" is not a property of your data. It's a choice. Does opening the app count? Does an API call from their integration count? Does a background sync that the user didn't trigger count?

Pick the event that means "this human got value," and count distinct users who fired it. If you don't pin this down, your DAU will quietly include cron jobs and health checks, and you'll wonder why it doesn't move when you change the product.

For these examples I'll assume an events table with a user_id and an occurred_at, already filtered to the events that count as meaningful activity.

Daily active users

select
  date_trunc('day', occurred_at) as day,
  count(distinct user_id) as dau
from events
where occurred_at >= now() - interval '30 days'
group by 1
order by 1;

count(distinct user_id) is the whole point. A user who fires fifty events in a day is one active user, not fifty. Forgetting the distinct is the single most common DAU mistake, and it inflates your number in a way that looks great until someone checks it.

Weekly and monthly

Same query, different bucket:

-- WAU
select date_trunc('week', occurred_at) as week, count(distinct user_id) as wau
from events
where occurred_at >= now() - interval '12 weeks'
group by 1 order by 1;
 
-- MAU
select date_trunc('month', occurred_at) as month, count(distinct user_id) as mau
from events
where occurred_at >= now() - interval '12 months'
group by 1 order by 1;

One thing to watch: date_trunc('week', ...) starts weeks on Monday in Postgres. If your company thinks of weeks as starting Sunday, your buckets will be off by a day from everyone's mental model, and you'll get a confused Slack message. Decide on purpose.

The DAU/MAU ratio (stickiness)

The ratio people actually care about is DAU divided by MAU. It's a rough measure of how many of your monthly users show up on a given day, which is a proxy for whether the product is a habit or a once-a-month visit.

with daily as (
  select count(distinct user_id) as dau
  from events
  where occurred_at >= current_date - interval '1 day'
),
monthly as (
  select count(distinct user_id) as mau
  from events
  where occurred_at >= current_date - interval '30 days'
)
select
  daily.dau,
  monthly.mau,
  round(100.0 * daily.dau / monthly.mau, 1) as stickiness_pct
from daily, monthly;

A consumer app might see 50% or higher. A B2B tool people use a few times a month might sit at 10% and be perfectly healthy. There's no universal good number. There's only your number, tracked over time.

The trap with rolling windows

If you want a true rolling 30-day MAU per day (not calendar months), you're counting distinct users in a 30-day window that slides forward one day at a time. That's a more expensive query, because each day's count overlaps the next. On a large events table, do it with a daily rollup table instead of a single monster query, or it'll be slow enough to time out.

For most teams, the simple bucketed versions above are plenty. Get the definition of "active" right, keep the distinct, agree on when weeks start, and you'll have numbers you can actually trust. The arithmetic was never the hard part.