MAU in SQL: interview recipe

Why MAU is a trap question

"How many active users did we have last month?" is the most innocent-sounding question a PM can ask, and the most reliable way to lose half a day of analyst time. The number sounds atomic — one integer, one month, done — but every word in the sentence hides a decision. Active how? Last month by calendar or rolling? Users including the QA bots, the canceled accounts, the duplicates across two devices? Each answer moves the headline number by 5-30%, and if you and the PM picked different defaults, the dashboard you ship at 4pm will look wrong by 5pm.

The interview version of this question is even sharper. Every senior analyst loop at Stripe, Airbnb, DoorDash, and Notion has a variant: write SQL for monthly active users, then walk me through what could break it. The interviewer is not testing whether you know COUNT(DISTINCT user_id). They want to see whether you spot the calendar-month edge effect, the timezone drift, the bot inflation, and the qualified-MAU vs total-MAU trade-off before they have to ask. Get those right and you've earned the next question. Miss them and you've shown you've never owned a metric end-to-end.

This post is the SQL recipe plus the trap list. Copy the queries, but read the pitfalls — that's what separates a 30-minute Stack Overflow answer from something you can actually ship.

The base SQL

Assume the canonical events table: events(user_id, event_date, event_type, country, is_bot). The minimum-viable MAU query is four lines:

SELECT
    DATE_TRUNC('month', event_date) AS month,
    COUNT(DISTINCT user_id)         AS mau
FROM events
WHERE event_date >= '2026-01-01'
  AND event_date <  '2026-05-01'
  AND is_bot = FALSE
GROUP BY 1
ORDER BY 1;

The DATE_TRUNC('month', ...) puts every event into its calendar bucket. The is_bot = FALSE filter is the one most candidates skip, and it's the one that turns a clean number into a believable number. On mobile-heavy products you should also exclude event_type IN ('install', 'crash') — they fire on emulators and test farms and inflate MAU by 3-8%.

The reason the date range stops at '2026-05-01' and not '2026-05-31' is that you almost never want a partial-month MAU sitting at the end of your output as if it were complete. A row labeled May with half the month missing will be misread as a 50% MoM drop by every non-analyst who opens the dashboard.

Calendar MAU vs rolling 30-day

The biggest fork in MAU definitions is calendar month vs rolling 30 days. Calendar MAU is what finance wants — it lines up with billing periods, board decks, and quarterly close. Rolling 30-day MAU is what product wants — it smooths out the artificial cliff at month-end and gives you a daily trend line you can actually read.

Here is the rolling-30 query in Postgres syntax. It builds a date spine and joins events back with a 30-day lookback window:

WITH dates AS (
    SELECT generate_series(
        '2026-04-01'::DATE,
        '2026-04-30'::DATE,
        INTERVAL '1 day'
    )::DATE AS day
)
SELECT
    d.day,
    COUNT(DISTINCT e.user_id) AS rolling_mau_30d
FROM dates d
JOIN events e
  ON e.event_date >  d.day - INTERVAL '30 days'
 AND e.event_date <= d.day
 AND e.is_bot = FALSE
GROUP BY d.day
ORDER BY d.day;

On Snowflake or BigQuery you'd swap generate_series for GENERATE_DATE_ARRAY or a UNNEST(GENERATE_ARRAY(...)). The shape of the answer is identical.

Breakdowns the interviewer will ask for

Once the base query lands, the follow-up is always "now break it down by X". The X is one of: acquisition channel, country, plan tier, platform, or new-vs-returning. The pattern is the same — join the dimension, group by it, and let the interviewer see whether you correctly handle the NULL channel case (users from before the attribution column existed).

SELECT
    DATE_TRUNC('month', e.event_date)            AS month,
    COALESCE(u.acquisition_channel, 'unknown')   AS channel,
    COUNT(DISTINCT e.user_id)                    AS mau
FROM events e
JOIN users u USING (user_id)
WHERE e.event_date >= '2026-01-01'
  AND e.is_bot = FALSE
GROUP BY 1, 2
ORDER BY 1, mau DESC;

The COALESCE is not decorative. On every dataset older than the attribution system, 5-15% of MAU lives in NULL channel and gets silently dropped by inner joins or by the GROUP BY if you forget to coerce it.

For new-vs-returning splits, materialize the first-seen date per user once, then join. Computing MIN(event_date) inside the same query as the MAU aggregation works on small tables and falls over at 100M+ rows:

WITH first_seen AS (
    SELECT user_id, MIN(event_date) AS first_date
    FROM events
    WHERE is_bot = FALSE
    GROUP BY user_id
)
SELECT
    DATE_TRUNC('month', e.event_date) AS month,
    CASE
      WHEN DATE_TRUNC('month', fs.first_date)
         = DATE_TRUNC('month', e.event_date)
      THEN 'new' ELSE 'returning'
    END                                AS user_type,
    COUNT(DISTINCT e.user_id)          AS mau
FROM events e
JOIN first_seen fs USING (user_id)
WHERE e.event_date >= '2026-01-01'
GROUP BY 1, 2
ORDER BY 1, 2;

Qualified MAU with key events

Total MAU is what marketing reports; qualified MAU is what product trusts. The difference is a single CASE WHEN, but it changes the number by 20-60% depending on the product.

SELECT
    DATE_TRUNC('month', event_date) AS month,
    COUNT(DISTINCT user_id)                                                 AS total_mau,
    COUNT(DISTINCT CASE WHEN event_type = 'purchase'    THEN user_id END)   AS purchaser_mau,
    COUNT(DISTINCT CASE WHEN event_type = 'message_sent' THEN user_id END)  AS messager_mau
FROM events
WHERE event_date >= '2026-01-01'
  AND is_bot = FALSE
GROUP BY 1
ORDER BY 1;

For a marketplace, purchaser_mau / total_mau is the conversion floor; for a social product, messager_mau / total_mau is the engagement floor. Pick one qualified-MAU definition per product and stick with it for at least two quarters — flipping definitions every reorg is how you lose trend lines.

Common pitfalls

The first trap is reporting a partial current month as if it were complete. On the 25th, "May MAU" is whatever was DISTINCT-counted across May 1-24, which is structurally lower than full-month April. PMs read this as a 20% drop and start emergency meetings. The fix is either to exclude the partial month from the chart or to label it explicitly as MTD and project the run-rate.

The second trap is timezone drift. DATE_TRUNC('month', event_date) in UTC will assign a 2am PT event on May 1 to April for a US-pacific product. On a global product this is usually fine; on a single-market product it shifts 3-5% of MAU across month boundaries and creates phantom seasonality. The fix is DATE_TRUNC('month', event_date AT TIME ZONE 'America/Los_Angeles') or to store events already-converted in a local_event_date column. Pick one approach and document it next to the metric definition.

The third trap is dedup failure on dirty user_ids. If user_id arrives as a string with leading whitespace, mixed case, or trailing newlines from CSV ingestion, COUNT(DISTINCT user_id) treats 'u_123' and 'U_123 ' as two users and inflates MAU. The fix is upstream — normalize during ingest — but defensively COUNT(DISTINCT LOWER(TRIM(user_id))) will save you in the interview.

The fourth trap is comparing calendar MAU across months of different lengths. Feb has 28 days, May has 31; rolling-30 controls for this, calendar MAU does not. A 3% MoM drop from January to February is usually just 28/31 days, not a real decline. The fix is to either report rolling-30 or to normalize calendar MAU per day before MoM comparison.

The fifth trap is the DAU/MAU ratio interpretation flip. Candidates compute stickiness as AVG(DAU) / MAU and conclude "0.18 — good, industry standard". They then break it down by segment and report a segment with stickiness 0.45 as 2.5x better, when it's actually a tiny segment where the same five users log in daily. The fix is to gate ratio reporting on a minimum MAU floor (commonly 1,000 users) and to show both numerator and denominator alongside the ratio.

Performance notes

COUNT(DISTINCT user_id) is the slowest part of every MAU query. On Postgres at 100M+ rows it spills to disk; on Snowflake it benefits from clustering on event_date; on BigQuery APPROX_COUNT_DISTINCT returns a HyperLogLog estimate within ~1% error for 10-100x speedup, and dashboards usually don't care about the last 0.5%.

For rolling 30-day MAU on huge tables, replace the date-spine cross join with a HLL_SKETCH window approach if your warehouse supports it (Snowflake HLL_ESTIMATE, BigQuery HLL_COUNT.MERGE). The pattern: sketch each day's distinct users once, then merge the last-30 sketches in a window. Query time drops from minutes to seconds because you scan each day exactly once.

Materializing a daily mau_base(day, user_id) table — one row per user per active day — is the cheapest long-term fix. Every MAU variant becomes a COUNT(DISTINCT user_id) WHERE day BETWEEN x AND y over a much smaller, well-indexed table. Most analytics stacks already have this as part of their dbt project.

Related reading

• How to calculate DAU in SQL
• Stickiness and DAU/MAU ratio explained
• How to calculate active days in SQL
• Product metrics: DAU, MAU, ARPU, LTV
• SQL window functions interview questions

If you want to drill SQL questions like this every day, NAILDD is launching with 500+ SQL problems across exactly this pattern.

FAQ

MAU or DAU — which one should I report?

DAU is the right headline for high-frequency products where daily use is the point: messengers, social feeds, mobile games. MAU is the right headline for mid-frequency products where weekly or monthly engagement is normal: e-commerce, banking, streaming, B2B SaaS. Most mature products track both and use the ratio (stickiness) as a separate metric. Picking the wrong default is how you accidentally tell your CEO the product is shrinking when it's actually fine for the use case.

Calendar or rolling 30-day?

Calendar MAU for finance, board decks, and any number that has to reconcile with billing. Rolling 30-day MAU for product trend lines, alerting, and anything where the artificial cliff at month-end would mislead the reader. Sophisticated dashboards show both side by side and label them clearly — the worst pattern is mixing them in the same chart without a label.

What counts as "active"?

This is a definition that has to be made once, written down, and reused. Common ladders, from loose to strict: any event fires (raw MAU), an app-open event fires (session MAU), a key event fires like purchase or message-sent (qualified MAU). Pick the strictest definition that still gives you a stable trend line and stick with it. If you flip definitions every reorg, your year-over-year comparisons are worthless.

Why is my MAU different from the marketing dashboard?

Almost always one of three things: different bot filtering, different timezone handling, or different qualifying event. Less often, one team is including impersonation/admin sessions and the other isn't. The fix is to publish the SQL definition next to the number and to have one source-of-truth model in dbt that every dashboard imports.

MAU dropped — how do I diagnose it?

Decompose into three axes: acquisition (are new-user signups down?), retention (are returning users dropping out?), and engagement frequency (are the same users coming back less often?). Each axis has a different fix and a different owner. If you skip decomposition and report "MAU is down 8%", you'll spend the next week in meetings re-deriving what could have been one chart.

How does qualified MAU relate to activation rate?

Activation rate is the percentage of new users who hit the key event within a defined window (usually 7 or 30 days). Qualified MAU is the count of all users who hit the key event in a month. They share the numerator definition (key event fired) but split on the denominator: activation cares about new-user cohorts, qualified MAU cares about the full active base. Both should use the same key event definition; if they don't, you've got two different north-star metrics fighting each other.