RANGE Frame Rolling Sum — Solution & ExplanationProblems / RANGE Frame Rolling Sum / EditorialApproach

Pick the frame by its unit. The requirement is time-based ("last 7 days"), so the frame must be RANGE with an interval bound — RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW — ordered by the timestamp itself.

Sum, round, done. One window expression per row; rows with no week-mates just sum themselves.

Key InsightROWS and RANGE answer different questions: *how many observations back* versus *how far back in the ORDER BY's units*. They coincide exactly when the series is dense and unique per day — which is why the bug ships: it passes review on tidy test data and breaks the first time a store closes for a holiday. Sparse or irregular time series should default to RANGE+interval (or the spine-join when the engine lacks it). One nuance worth keeping: with duplicate ORDER BY values, RANGE includes all peers — for daily-grain data that's precisely the "whole day counts" semantics you want.Hints

ROWS BETWEEN 6 PRECEDING counts physical rows. If a store sold on only 3 of the last 7 days, that frame happily reaches back 3 weeks for four more rows — silently wrong on sparse data.

RANGE frames measure the ORDER BY column itself: RANGE BETWEEN INTERVAL 6 DAY PRECEDING AND CURRENT ROW admits exactly the rows whose sale_day falls within the trailing week, however many there are.

The frame width is 6, not 7: the window [day-6, day] spans 7 calendar days inclusive.

In PySpark, Window.rangeBetween only takes numeric bounds — for interval ranges, write the whole window expression in F.expr with the SQL syntax.

Solve RANGE Frame Rolling Sum yourself →