How to calculate DAU/MAU in SQL

What DAU/MAU actually measures

DAU/MAU, often called stickiness, is the share of monthly active users who come back on any given day. If MAU is 10 million and DAU is 5 million, stickiness is 50 percent — half of this month's users used the product today. The metric is the cleanest single number for whether a product has built a habit. Acquisition can be bought; stickiness can only be earned.

The prompt arrives without warning. Your PM at Meta, Snap, or Notion pings you: "Send daily stickiness for the last 90 days with a 7-day moving average, plus a breakdown by country and signup channel — leadership review tomorrow." The query is not hard, but the MAU definition, window alignment, and how you communicate the result all matter.

Benchmarks worth knowing before any interview: daily-utility products — messaging apps, the main feeds at Meta or X — sit at 50 percent or higher. Snap, Pinterest, and Reddit land between 35 and 50 percent. SaaS productivity tools like Notion, Linear, or Figma run 20 to 30 percent because the working week is 5 out of 7 days. Anything below 10 percent in a category that should be sticky is a habit problem.

The formula

The arithmetic is trivial. Take DAU for a given day, divide by MAU computed as the trailing 30 days ending on that same day, multiply by 100.

DAU/MAU = DAU(today) / MAU(today - 29 ... today) * 100%

The traps are not in the formula. They are in what counts as "active," whether MAU is calendar or rolling, and whether DAU and MAU share the same day endpoint. Most interviewers will not tell you which choice they want — they want to see you ask.

Baseline DAU/MAU query

Here is the canonical pattern. One CTE for DAU per day, one CTE for rolling 30-day MAU per day, then a join on the day column and the ratio.

WITH dau AS (
    SELECT
        activity_date              AS day,
        COUNT(DISTINCT user_id)    AS dau
    FROM activity
    WHERE activity_date >= CURRENT_DATE - INTERVAL '60 day'
    GROUP BY 1
),
mau AS (
    SELECT
        d.day,
        (
            SELECT COUNT(DISTINCT a.user_id)
            FROM activity a
            WHERE a.activity_date >= d.day - INTERVAL '29 day'
              AND a.activity_date <= d.day
        ) AS mau
    FROM dau d
)
SELECT
    d.day,
    d.dau,
    m.mau,
    ROUND(d.dau::NUMERIC * 100 / NULLIF(m.mau, 0), 2) AS stickiness_pct
FROM dau d
JOIN mau m USING (day)
ORDER BY d.day;

The correlated subquery inside mau is the cross-vendor portable way to compute rolling distinct counts. COUNT(DISTINCT ...) OVER (...) is not supported as a window function in Postgres, Redshift, or most MySQL variants. Snowflake offers approximate variants and BigQuery has APPROX_COUNT_DISTINCT over a window, but the correlated subquery above works everywhere and is what interviewers expect to see.

The 60-day lookback in the outer DAU CTE is intentional. To produce a 30-day trend of stickiness, you need 60 days of raw activity. Always extend the lookback in the base table by the size of the window; otherwise the first 29 days of your trend silently undercount MAU.

If you only need today's stickiness as one number, the query collapses to:

SELECT
    COUNT(DISTINCT user_id) FILTER (WHERE activity_date = CURRENT_DATE)::NUMERIC * 100
    / NULLIF(COUNT(DISTINCT user_id), 0) AS stickiness_pct
FROM activity
WHERE activity_date >= CURRENT_DATE - INTERVAL '29 day'
  AND activity_date <= CURRENT_DATE;

That is the version to write at a whiteboard when the prompt is "what is our stickiness right now." The trend version is for "show me how it has changed."

DAU/MAU by segment

Stickiness varies wildly by country, channel, tenure, paid versus free, and platform. A flat global stickiness can hide a US number that has been falling for six months while LATAM has been growing — they cancel out in the headline and the regression is invisible until you slice.

SELECT
    u.country,
    COUNT(DISTINCT a.user_id) FILTER (
        WHERE a.activity_date = CURRENT_DATE - INTERVAL '1 day'
    ) AS dau,
    COUNT(DISTINCT a.user_id) AS mau,
    ROUND(
        COUNT(DISTINCT a.user_id) FILTER (
            WHERE a.activity_date = CURRENT_DATE - INTERVAL '1 day'
        )::NUMERIC * 100
        / NULLIF(COUNT(DISTINCT a.user_id), 0),
        2
    ) AS stickiness_pct
FROM activity a
JOIN users u ON u.user_id = a.user_id
WHERE a.activity_date >= CURRENT_DATE - INTERVAL '30 day'
  AND a.activity_date <= CURRENT_DATE - INTERVAL '1 day'
GROUP BY u.country
ORDER BY stickiness_pct DESC;

The DAU here is yesterday, not today, because today's data is usually incomplete until the end of the day in UTC. Reporting partial-day DAU against full-month MAU systematically depresses stickiness. Align both numerator and denominator on yesterday, or label the most recent value as partial.

The join to the users dimension also matters. An inner join silently drops users whose country was never recorded — a number that ranges from under 1 percent in mature products to 20 percent in early-stage products with messy onboarding. Use a left join and coalesce nulls to 'unknown' if the breakdown needs to reconcile with the headline.

For tenure-based stickiness — usually a much more diagnostic split than country — group by signup cohort instead of u.country. Newer cohorts almost always look stickier than older cohorts in the first 90 days because the first-day signup spike inflates DAU. The honest comparison is cohort stickiness at the same age.

The stickiness trend with a 7-day moving average

Raw stickiness is noisy because both numerator and denominator move with weekday patterns. The standard smoothing is a 7-day trailing average over daily stickiness, which cancels weekly seasonality and leaves the underlying trend visible.

WITH daily AS (
    SELECT
        d.day,
        d.dau,
        (
            SELECT COUNT(DISTINCT a.user_id)
            FROM activity a
            WHERE a.activity_date >= d.day - INTERVAL '29 day'
              AND a.activity_date <= d.day
        ) AS mau
    FROM (
        SELECT
            activity_date           AS day,
            COUNT(DISTINCT user_id) AS dau
        FROM activity
        WHERE activity_date >= CURRENT_DATE - INTERVAL '120 day'
        GROUP BY 1
    ) d
)
SELECT
    day,
    dau,
    mau,
    ROUND(dau::NUMERIC * 100 / NULLIF(mau, 0), 2) AS stickiness_pct,
    ROUND(
        AVG(dau::NUMERIC * 100 / NULLIF(mau, 0)) OVER (
            ORDER BY day
            ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
        ),
        2
    ) AS stickiness_pct_ma7
FROM daily
WHERE day >= CURRENT_DATE - INTERVAL '90 day'
ORDER BY day;

The lookback is 120 days, not 90. The MAU for the earliest day in the 90-day display window needs the 30 days preceding it, and the 7-day moving average needs another 6 days before that. Always pad your lookbacks by the sum of every window in the query. This is the most common reason a stickiness trend shows a phantom drop at the left edge of the chart.

When the smoothed line moves but the raw line does not, you are looking at a real shift in behavior. When the smoothed line is flat but raw values spike on certain weekdays, that is seasonality, not a story. Only investigate when the smoothed line itself bends.

Common pitfalls

When teams ship their first stickiness dashboard, the most common mistake is to align MAU to calendar months while computing DAU daily. The result jumps every first of the month because the MAU denominator suddenly drops by 28 to 30 days of history. The fix is to use a rolling 30-day MAU computed as "the trailing 30 days ending on day D" for every day D. Calendar MAU has its place in board decks, but it does not belong in a daily stickiness chart.

A second pitfall is confusion between WAU/MAU and DAU/MAU. WAU/MAU runs roughly 2 to 3x the value of DAU/MAU for typical consumer products — a 25 percent DAU/MAU is a 60 to 70 percent WAU/MAU in the same product. If you report one and your CEO compares it to a benchmark for the other, your healthy product can look broken. Always label the metric explicitly: "DAU/MAU (30-day rolling)" not just "stickiness."

A third pitfall is the new-user inflation effect. On the day a user signs up, they are automatically in DAU and in MAU. A heavy acquisition campaign adds the same users to both, but DAU rises faster in percentage terms because the DAU base is smaller, so stickiness rises mechanically without anyone becoming stickier. The fix is to compute stickiness on the cohort of users who signed up at least 30 days ago.

A fourth pitfall is bot and synthetic traffic. Internal QA accounts that ping on every deploy, automated browser tests, and pre-fetch traffic all sneak into DAU and MAU. Bots crank stickiness disproportionately because the same user appears in 30 out of 30 days. Maintain an excluded_users list and filter at the first CTE of every stickiness query, before any aggregation.

A fifth pitfall is multi-device identity. The same human opening an app on phone, tablet, and laptop can show up as three distinct user_id values if identity stitching is weak. This deflates stickiness because each device-identity is only present on a subset of days. If stickiness looks unexpectedly low and the product is multi-device, audit identity resolution before you audit the product.

Optimization tips

Stickiness queries are expensive because every day re-scans the trailing 30 days. The highest-leverage optimization is a daily snapshot table — daily_stickiness_snapshot(day, dau, mau, stickiness_pct, stickiness_pct_ma7) — refreshed once a day in your transformation pipeline. Dashboards then read sub-second instead of recomputing distinct counts over a billion-row table every page load.

Partition the activity table by date. On Snowflake, cluster by activity_date. On BigQuery, use partitioned tables with _PARTITIONDATE. On Postgres, declarative partitioning by month. The trailing 30-day window should touch one or two partitions, not the entire table. If a stickiness query scans years of data, predicate pushdown is broken — check the EXPLAIN plan.

For non-financial reporting, approximate distinct counts are an order of magnitude faster than exact ones and accurate to within roughly 1 percent. APPROX_COUNT_DISTINCT uses HyperLogLog under the hood. Reserve exact COUNT(DISTINCT user_id) for audit-grade reports.

If you want to drill SQL questions like this until rolling-window distinct counts become muscle memory, NAILDD is launching with 500+ SQL problems built around exactly these patterns.

DAU/MAU vs WAU/MAU

WAU/MAU is the same idea but with a 7-day window in the numerator: distinct users active in the trailing 7 days, divided by distinct users active in the trailing 30 days. WAU/MAU runs roughly 2 to 3 times the DAU/MAU value for the same product.

WAU/MAU is the better metric for B2B and productivity software because the rhythm of work is weekly, not daily. A user who opens Linear every Monday and Wednesday but not Tuesday is highly engaged — DAU/MAU would mark them as unstuck on Tuesday, WAU/MAU correctly counts them as active for that week. DAU/MAU is the better metric for daily-utility consumer products.

A useful diagnostic is to compute both and look at the ratio between them. A high DAU/MAU divided by WAU/MAU (close to 1) means the same users come back every day. A low ratio (DAU/MAU at 10 percent, WAU/MAU at 60 percent) means weekly engagement is healthy but daily is not — the decomposition tells you whether you have a daily-habit problem or a weekly-retention problem.

Related reading

• How to calculate MAU in SQL
• SQL window functions interview questions
• How to calculate D1, D7, D30 retention in SQL
• How to find the aha moment in SQL

FAQ

What is a good DAU/MAU ratio?

It depends entirely on category. Daily-utility consumer products — messaging, social feeds, news — should sit at 50 percent or higher. Snap, Reddit, and Pinterest land between 35 and 50 percent. SaaS productivity tools sit at 20 to 30 percent because the working week is shorter than the calendar week. Travel, tax-filing, and infrequent-use government services live below 10 percent and that is healthy. Always benchmark against your own category.

Why is rolling 30-day MAU preferred over calendar MAU?

Calendar MAU drops by a month's worth of users every first of the month, so stickiness based on calendar MAU spikes mechanically on day 1 and decays over the month. The chart looks like a sawtooth, which makes real trends impossible to read. Rolling 30-day MAU is comparable every day of the month and produces a smooth time series where bends in the curve mean something.

My DAU/MAU dropped — what should I investigate first?

Decompose into the two terms. If DAU dropped and MAU stayed flat, existing users are pulling back — investigate retention curves, product releases, and push-notification deliverability. If DAU stayed flat and MAU went up, you have an acquisition surge that is not retaining — investigate the new cohort's day-7 and day-30 retention. The retention drop is usually more urgent because acquisition can be turned up with budget.

Should I exclude new users from stickiness?

For diagnostic stickiness, yes — compute it only on users who signed up at least 30 days ago. New users mechanically inflate the ratio because they are guaranteed to be in both DAU and MAU on signup day. For headline reporting, include them but be aware that a stickiness spike coinciding with an acquisition campaign is probably a measurement artifact rather than a behavior change.

How do I handle multi-device users in DAU/MAU?

Resolve identity before you count. Either your application maintains a canonical user ID across devices, or you stitch device IDs with a probabilistic matcher in your warehouse. Without identity resolution, the same human appears as multiple user_id values and stickiness is systematically understated. If your product is multi-device and stickiness looks low, audit identity resolution first.