DAU/WAU Stickiness — Solution & ExplanationProblems / DAU/WAU Stickiness / EditorialApproach

Dedupe to user-days. Rolling distinct counts are over *users per day*, not events.

Day-spine range join. calendar c JOIN daily d ON d.activity_day BETWEEN c.day - INTERVAL 6 DAY AND c.day. Every activity day fans out to each of the (up to 7) windows containing it — deliberate duplication that COUNT(DISTINCT ...) per day then collapses correctly.

Both metrics in one pass. WAU is the plain distinct count of the window rows; DAU is a conditional distinct count of just the rows where activity_day = c.day.

Guard the ramp-up. Days earlier than MIN(day) + 6 see partial windows (small WAU, inflated stickiness); the scalar-subquery filter drops them.

Key InsightWindow functions can roll sums and counts, but not distinct counts — COUNT(DISTINCT) OVER (... ROWS BETWEEN ...) simply doesn't exist, because distinctness can't be maintained incrementally as a frame slides. The general-purpose escape hatch is the spine range join: materialize each (day, window-member) pair, then use ordinary grouped aggregation where DISTINCT works fine. It costs a controlled fan-out (×7 here) and buys full aggregate generality — the trade every rolling-distinct metric makes.Hints

COUNT(DISTINCT) OVER a sliding window frame is not supported by SQL engines — that's why this is a join problem. Each calendar day joins to all activity days in its trailing 7-day range.

The range join intentionally duplicates: one activity day belongs to up to 7 windows. Distinct counts inside the per-day GROUP BY undo the duplication.

DAU doesn't need a second query: COUNT(DISTINCT CASE WHEN activity_day = day THEN user_id END) picks today's users out of the already-joined window rows.

The first 6 calendar days have truncated windows and would overstate stickiness — filter with day >= MIN(day) + 6 via a scalar subquery, not a hardcoded date.

Solve DAU/WAU Stickiness yourself →