Daily Active Users and Stickiness

Difficulty: Easy · Topics: aggregation, count-distinct, self-join · Asked at: Meta, Snap, Spotify

Problem

From events(user_id, event_ts), compute for each day that has events:

• dau: distinct users active that day
• mau_28d: distinct users active in the 28 days ending that day (inclusive)
• stickiness: dau / mau_28d rounded to 2 decimals

Order by day.

Schema and sample data

CREATE TABLE events (user_id INTEGER, event_ts TEXT);
INSERT INTO events VALUES
(1,'2026-03-01 08:00'),(1,'2026-03-01 09:00'),(2,'2026-03-01 10:00'),
(1,'2026-03-02 08:00'),(3,'2026-03-02 11:00'),
(2,'2026-03-10 12:00'),
(4,'2026-03-29 07:00'),(1,'2026-03-29 08:00'),
(2,'2026-03-30 09:00');

Expected output

Hints

Solution

WITH user_days AS (
  SELECT DISTINCT user_id, DATE(event_ts) AS d FROM events
), days AS (
  SELECT DISTINCT d FROM user_days
)
SELECT days.d AS day,
       COUNT(DISTINCT CASE WHEN ud.d = days.d THEN ud.user_id END) AS dau,
       COUNT(DISTINCT ud.user_id)                                  AS mau_28d,
       ROUND(1.0 * COUNT(DISTINCT CASE WHEN ud.d = days.d THEN ud.user_id END)
                 / COUNT(DISTINCT ud.user_id), 2)                   AS stickiness
FROM days
JOIN user_days ud
  ON ud.d BETWEEN DATE(days.d, '-27 days') AND days.d
GROUP BY days.d
ORDER BY days.d;

Explanation

• Reduce to one row per user per day first; this shrinks the data and makes the join cheap.
• A rolling distinct count can’t be built from a rolling sum of daily DAU (the same user would be counted on several days), which is why a range self-join is used.
• At scale: precompute daily HyperLogLog sketches per day and union the last 28 (hll_union_agg). That’s O(28) sketches instead of a big join.

Follow-up questions

Dialect notes

Spark: approx_count_distinct(user_id) for approximate, size(collect_set(user_id) over w) works with a range window but is memory-heavy.