Metric Queries DAU MAU WAU

Why this matters

As a Product Analyst, you will routinely answer questions like: Are we growing? Did yesterday’s launch boost engagement? Which countries have improving stickiness? DAU, WAU, and MAU are core active-user metrics that power weekly business reviews, feature evaluations, and goal tracking.

• Estimate impact: Compare DAU pre/post release to validate feature effects.
• Health monitoring: Watch WAU/MAU trends to catch declines early.
• Quality insights: Use DAU/WAU ratio (stickiness) to see if users return frequently.

Concept explained simply

Active user = a user who did at least one qualifying action in a time window.

• DAU: Distinct users active on a given day.
• WAU: Distinct users who were active within the last 7 days (rolling) or in a calendar week (anchored).
• MAU: Distinct users who were active within the last 30 days (rolling) or in a calendar month (anchored).

Mental model

Think of each day as a window. For WAU, the window is 7 days wide; for MAU, 30 days. Slide the window day by day and count unique users inside it.

Data model we will use

Assume an events table:

events(
  user_id        BIGINT,
  event_name     TEXT,
  event_time     TIMESTAMP,
  platform       TEXT,
  country        TEXT
)

Active users count users with at least one qualifying event (e.g., app_open, page_view, purchase). Adjust the event list to match your product definition.

Worked examples

1) DAU over the last 30 days

-- Postgres-style
WITH filtered AS (
  SELECT user_id, DATE_TRUNC('day', event_time)::date AS event_date
  FROM events
  WHERE event_time >= CURRENT_DATE - INTERVAL '30 days'
    AND event_name IN ('app_open','page_view','purchase')
  GROUP BY 1,2
)
SELECT event_date,
       COUNT(DISTINCT user_id) AS dau
FROM filtered
GROUP BY event_date
ORDER BY event_date;

2) Rolling WAU and MAU per day (90-day horizon)

WITH daily_users AS (
  SELECT DATE_TRUNC('day', event_time)::date AS dt, user_id
  FROM events
  WHERE event_time >= CURRENT_DATE - INTERVAL '120 days'
    AND event_name IN ('app_open','page_view','purchase')
  GROUP BY 1,2
), calendar AS (
  SELECT generate_series(
           CURRENT_DATE - INTERVAL '90 days',
           CURRENT_DATE,
           INTERVAL '1 day'
         )::date AS dt
)
SELECT c.dt AS as_of_date,
       COUNT(DISTINCT CASE WHEN du.dt BETWEEN c.dt - INTERVAL '6 days'  AND c.dt THEN du.user_id END) AS wau,
       COUNT(DISTINCT CASE WHEN du.dt BETWEEN c.dt - INTERVAL '29 days' AND c.dt THEN du.user_id END) AS mau
FROM calendar c
LEFT JOIN daily_users du
  ON du.dt BETWEEN c.dt - INTERVAL '29 days' AND c.dt
GROUP BY c.dt
ORDER BY c.dt;

3) Stickiness: DAU/WAU and DAU/MAU ratios

WITH base AS (
  SELECT DATE_TRUNC('day', event_time)::date AS dt, user_id
  FROM events
  WHERE event_time >= CURRENT_DATE - INTERVAL '120 days'
    AND event_name IN ('app_open','page_view','purchase')
  GROUP BY 1,2
), cal AS (
  SELECT generate_series(CURRENT_DATE - INTERVAL '90 days', CURRENT_DATE, INTERVAL '1 day')::date AS dt
), agg AS (
  SELECT c.dt,
         COUNT(DISTINCT CASE WHEN b.dt = c.dt THEN b.user_id END) AS dau,
         COUNT(DISTINCT CASE WHEN b.dt BETWEEN c.dt - INTERVAL '6 days'  AND c.dt THEN b.user_id END) AS wau,
         COUNT(DISTINCT CASE WHEN b.dt BETWEEN c.dt - INTERVAL '29 days' AND c.dt THEN b.user_id END) AS mau
  FROM cal c
  LEFT JOIN base b ON b.dt BETWEEN c.dt - INTERVAL '29 days' AND c.dt
  GROUP BY c.dt
)
SELECT dt,
       dau,
       wau,
       mau,
       ROUND(dau::numeric / NULLIF(wau,0), 4) AS dau_wau_ratio,
       ROUND(dau::numeric / NULLIF(mau,0), 4) AS dau_mau_ratio
FROM agg
ORDER BY dt;

4) Anchored weekly and monthly active users

-- Weekly anchored (calendar weeks)
SELECT DATE_TRUNC('week', event_time)::date AS week_start,
       COUNT(DISTINCT user_id) AS wau_weekly
FROM events
WHERE event_time >= DATE_TRUNC('week', CURRENT_DATE - INTERVAL '12 weeks')
  AND event_name IN ('app_open','page_view','purchase')
GROUP BY 1
ORDER BY 1;

-- Monthly anchored (calendar months)
SELECT DATE_TRUNC('month', event_time)::date AS month_start,
       COUNT(DISTINCT user_id) AS mau_monthly
FROM events
WHERE event_time >= DATE_TRUNC('month', CURRENT_DATE - INTERVAL '12 months')
  AND event_name IN ('app_open','page_view','purchase')
GROUP BY 1
ORDER BY 1;

How to write DAU/WAU/MAU queries (step-by-step)

1. Define active event(s): Decide which events count as engagement (e.g., app_open, page_view).
2. Normalize time: Convert timestamps to a single timezone and truncate to day where needed.
3. Dedupe: Build a user-per-day set to avoid double counting.
4. Build a calendar: Generate dates to ensure continuous timelines.
5. Window selection: Choose rolling (7/30-day) or anchored (week/month).
6. Aggregate and ratio: Compute DAU, WAU, MAU, and stickiness ratios with safe division.
7. Segment: Break down by platform, country, or cohort as needed.

Exercises

The test is available to everyone; if you log in, your progress will be saved automatically.

Exercise 1 — Single-row DAU/WAU/MAU for yesterday

Write a query that returns one row with columns dau, wau, mau as of yesterday using rolling 7-day and 30-day windows.

• Use events and qualify event_name in ('app_open','page_view','purchase').
• Use DATE_TRUNC and a deduplicated daily user set.
• Expected output: one row, three integer columns.

Exercise 2 — Weekly active users by country (last 8 weeks)

Return week_start, country, wau_weekly for the last 8 calendar weeks.

• Group by DATE_TRUNC('week', event_time) and country.
• Expected output: multiple rows, sorted by week_start, country.
• Optional: filter to countries with wau_weekly >= 100 to reduce noise.

Common mistakes and self-check

• Mixing timezones: Users near midnight double-count or disappear. Self-check: Is your day boundary UTC-consistent?
• No deduping before counting: Heavy users inflate DAU. Self-check: Do you first reduce to one row per user per day?
• Wrong window length: Using 7 vs 8 days by accident. Self-check: Confirm BETWEEN logic includes both endpoints for 7 exact days.
• Bot/noise events included: Self-check: Review event list; exclude heartbeats and system pings.
• Division by zero in ratios: Self-check: Use NULLIF in denominators.

Mini challenge

Compute DAU, rolling WAU, rolling MAU, and DAU/MAU for the last 60 days segmented by platform. Return columns: as_of_date, platform, dau, wau, mau, dau_mau_ratio. Sort by as_of_date, platform.

Who this is for

• Product Analysts who need reliable engagement metrics.
• Data-savvy PMs and Growth Analysts building KPI dashboards.

Prerequisites

• Comfort with SELECT, GROUP BY, WHERE, and COUNT DISTINCT.
• Basic understanding of date_trunc and generated calendars.

Learning path

• Start: DAU per day using deduped daily users.
• Next: Rolling WAU/MAU with a calendar table.
• Then: Stickiness ratios and segmentation.
• Finally: Anchored weekly/monthly reporting for exec reviews.

Practical projects

• Build a 90-day DAU/WAU/MAU chart with stickiness and WoW deltas.
• Create a platform- and country-segmented active-user dashboard.
• Add anomaly detection rules: alert when DAU drops more than 15% day-over-day.

Next steps

• Turn these queries into scheduled reports or dashboard tiles.
• Agree on an official “active user” definition with your team and document it.
• Proceed to the Quick Test to reinforce key ideas.