Netflix: a 7-day unique count you cannot add up

Constraints

1. 1dau is the number of distinct profiles that streamed on that day.
2. 2wau_7d is the number of distinct profiles that streamed in the 7-day window ending on that day, inclusive. A profile active on three of those days counts once.
3. 3stickiness_pct is dau over wau_7d as a percentage, rounded to 1 decimal place.
4. 4The first days have less than 7 days of history behind them. Report the window that exists rather than suppressing the row.
5. 5Sessions by the same profile on the same day count once towards dau.
6. 6Order by activity_day.

Worked example

On 2026-03-08 the distinct profiles active that day are p9, p1, p2 and p10 — a dau of 4. Across the seven days from 2 March to 8 March, ten distinct profiles streamed, so wau_7d is 10 and stickiness is 40.0%. The natural move is SUM(dau) OVER (ORDER BY activity_day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW). That gives 18, not 10, because p1 streamed on six of those days and is counted six times. Distinct counts are not additive: seven daily uniques cannot be combined into a weekly unique without going back to the underlying rows.

What this tests

That COUNT(DISTINCT ...) does not compose over a window frame. Recognising which measures are additive and which are not is the difference between a metric that is wrong by 80% and one that is right.