Problem Statement
You need a running distinct count of active users per app over time. Outline approaches and their costs.
Explanation
A pure SQL window cannot maintain a running DISTINCT across changing frames efficiently. One approach is to pre-aggregate to daily unique users per app, then apply a running SUM over those daily counts. This reduces cardinality and keeps the window light.
For exact moving distincts at row granularity, use specialized features (e.g., HyperLogLog approx algorithms) or maintain rollups in staging tables. Exact per-row distinct windows tend to be heavy and may require materialization steps.
Code Solution
SolutionRead Only
-- daily uniques first, then window WITH d AS ( SELECT app_id, day, COUNT(DISTINCT user_id) AS du FROM events GROUP BY app_id, day ) SELECT app_id, day, SUM(du) OVER (PARTITION BY app_id ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS du_7d FROM d;
