HardBusiness Analytics

DAU/MAU Engagement Stickiness Ratio

Frequently Asked By:MetaNetflixGoogle

Interactive In-Browser Workspace

Practice this problem on live schema tables in PostgreSQL 16 WASM with instant row verification and visual EXPLAIN plans.

Solve in Workbench

Problem Statement

Write a SQL query to calculate the DAU/MAU (Daily Active Users divided by Monthly Active Users) engagement ratio for the most recent date in the platform telemetry table. Return DAU, 30-day MAU, and the stickiness percentage.

Why Technical Interviewers Ask This

DAU/MAU is the gold standard engagement KPI at social networks (Meta) and streaming services (Netflix). This question checks whether you can compute distinct active users over overlapping rolling time horizons.

Common Pitfalls & Interview Traps

Counting event rows rather than DISTINCT user_id entries.

Hardcoding calendar months instead of calculating a true trailing 30-day rolling window.

Performance slowdowns on multi-million row activity tables without proper partitioning.

Canonical Modern SQL Solution

PostgreSQL 16 / ANSI SQL

WITH TargetDate AS (
  SELECT MAX(event_time::date) AS max_date FROM user_telemetry
),
DauCalculation AS (
  SELECT
    COUNT(DISTINCT t.user_id) AS dau
  FROM user_telemetry t
  CROSS JOIN TargetDate td
  WHERE t.event_time::date = td.max_date
),
MauCalculation AS (
  SELECT
    COUNT(DISTINCT t.user_id) AS mau
  FROM user_telemetry t
  CROSS JOIN TargetDate td
  WHERE t.event_time::date BETWEEN td.max_date - INTERVAL '29 days' AND td.max_date
)
SELECT
  td.max_date AS metric_date,
  d.dau,
  m.mau,
  ROUND((d.dau * 100.0) / NULLIF(m.mau, 0), 2) AS dau_mau_stickiness_pct
FROM TargetDate td
CROSS JOIN DauCalculation d
CROSS JOIN MauCalculation m;

Approach Breakdown

We isolate the latest date in the dataset. Using two focused CTEs, we calculate unique active users for that exact day (DAU) and over the trailing 30-day window (MAU), computing the stickiness quotient cleanly.

Edge Cases to Verbally Clarify with Your Interviewer

Zero active users during system outages.

New platforms with less than 30 days of historical telemetry.

Frequently Asked Follow-Up Questions

What is considered a good DAU/MAU stickiness benchmark?

In consumer social products (WhatsApp, Instagram), DAU/MAU often exceeds 50%. In enterprise B2B SaaS (Slack, Salesforce), 20% to 35% is considered best-in-class product stickiness.