What does RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW define as a frame?
answer
- seven of what, exactly?
- gaps in the data change the answer
- the bound is arithmetic on the ordering value
- one ORDER BY column only
- calendar window, not row-count window
basics
~20 sA value-based frame: every row whose ORDER BY date lies within seven days before the current row's date, through the current row and its peers. Both endpoints are inclusive, and days with no rows simply contribute nothing.
solid answer
~40 sIt is a calendar window rather than a row-count window. The engine computes `current_row_value - INTERVAL '7' DAY` and includes every row whose ordering value falls between that boundary and the current row's value inclusive — so a day with twenty rows contributes twenty rows and a day with none contributes zero. That is exactly what a `ROWS n PRECEDING` frame cannot express, because ROWS counts rows and knows nothing about the gaps between dates. Offset RANGE bounds carry requirements: the window must have exactly one `ORDER BY` column, of a type where value plus or minus the offset is defined, and the offset itself must be non-negative and non-null. Support arrived later than basic window support, so check your engine.
go deeper
Recognise that a frame bound can be expressed in days rather than rows, and that the two differ as soon as the data has gaps or several rows per day.
Explain the arithmetic the engine performs on the ordering value and state the single-ORDER-BY-column and non-negative-offset requirements.
Translate a business metric such as "trailing 30-day revenue" into the correct frame, call out the sparse-data denominator trap, and check engine support before shipping it.
Own the metric definition itself: whether rolling windows are calendar-based or observation-based across the organisation, and how partial windows at series edges are reported so dashboards agree.
## Two ways to say "the last week" `ROWS BETWEEN 6 PRECEDING AND CURRENT ROW` means seven rows. `RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW` means seven days. On a series with exactly one row per day and no missing days these coincide, which is why the distinction is easy to miss — and why it silently breaks the moment the data has gaps or duplicates. With an offset RANGE bound the engine evaluates, for each row, the boundary value `current_value - offset`, then includes every row of the partition whose ordering value lies between that boundary and the current row's value. Both endpoints are inclusive. Row counts inside the frame vary freely: a busy day widens the frame, a missing day narrows it, and the window still means "the last seven days" either way. ## The requirements Offset bounds in RANGE mode are more constrained than ROWS bounds: - The window must have **exactly one** `ORDER BY` column. The engine has to do arithmetic on the ordering value, and there is no defined arithmetic on a composite key. `ORDER BY reading_date, sensor_id` with an offset RANGE frame is an error. - The ordering column's type must support subtracting and adding the offset. A date or timestamp column takes an interval offset; a numeric column takes a numeric one (`RANGE BETWEEN 5 PRECEDING AND 5 FOLLOWING` on an integer score column is a value band, not five rows). - The offset must be **non-negative and non-null**. `PRECEDING` and `FOLLOWING` already carry the direction, so a negative offset is rejected rather than reversing it. - `DESC` ordering is fine — the engine applies the offset in the direction the ordering implies, so `PRECEDING` still means "earlier in the window order". ## Worked shape ```sql SELECT reading_date, value, AVG(value) OVER (ORDER BY reading_date RANGE BETWEEN INTERVAL '7' DAY PRECEDING AND CURRENT ROW) AS avg_last_7_days FROM sensor_readings; ``` If `sensor_readings` skips weekends, the ROWS version of this query silently reaches back nine or ten calendar days near a Monday; the RANGE version does not. If the table has several readings per day, the ROWS version covers a fraction of a day while the RANGE version covers the intended week. Whenever the metric is stated in time units — "7-day rolling average", "revenue in the trailing 30 days" — the value-based frame is the faithful translation. Note also that `CURRENT ROW` as the end bound keeps its RANGE meaning: the frame runs through the last row sharing the current row's ordering value, so all rows on the same date see the same window and the same average. That is usually what a daily metric wants. ## Interaction with partitions and gaps The frame is still clipped to the partition, so a per-sensor rolling average (`PARTITION BY sensor_id ORDER BY reading_date RANGE …`) never mixes sensors, and each sensor's first week is computed over however many of its own readings exist. Early rows therefore average fewer days — an edge effect worth stating in the report definition rather than discovering in a chart. A subtlety worth knowing: because the frame is defined by values, a completely missing day is invisible. "Average over the last 7 days" computed this way is the average of the readings that exist within that span, not a divide-by-seven. If the metric definition requires a fixed denominator, generate a dense date series first and left-join the readings onto it, so every day contributes a row. ## Portability Basic RANGE frames built from `UNBOUNDED PRECEDING`, `CURRENT ROW` and `UNBOUNDED FOLLOWING` are available wherever window functions are. Offset RANGE bounds are a later addition: PostgreSQL gained them in version 11, and MySQL supports them from 8.0 using its own unquoted interval spelling (`INTERVAL 7 DAY`). Other engines differ — verify before relying on them, and keep in mind the portable fallback: a self-join or a correlated subquery with an explicit date predicate expresses the same window in plain SQL, more verbosely and usually less efficiently. ## The interview signal What an interviewer is listening for is whether you notice that "last 7 days" and "last 7 rows" are different specifications, and whether you can name the constraint that trips people up — the single-ordering-column rule. Candidates who reach for `ROWS 6 PRECEDING` for a calendar metric usually have never worked with sparse time series.
- Why does an offset RANGE frame require exactly one ORDER BY column?Because the bound is computed by arithmetic on the ordering value — the engine needs `current_value - offset` to be well defined. A composite ordering key has no such arithmetic, so the standard restricts offset RANGE frames to a single ordering column of a type that supports adding and subtracting the offset. `ROWS` has no such restriction, since it only counts positions.
- When is ROWS 6 PRECEDING actually the right choice over a 7-day RANGE frame?When the metric is genuinely about observations rather than time: the average of the last seven measurements, the maximum of the last ten trades, a smoothing window over an evenly sampled series. ROWS is also the only option when the ordering key is not arithmetic-friendly, or when the window must contain a fixed number of rows regardless of how they are spread in time.
- Does a 7-day RANGE frame divide by seven when some days have no rows?No. `AVG` divides by the number of rows actually inside the frame, and missing days contribute no rows at all. If the metric requires a fixed denominator, build a dense date series and left-join the data onto it so every day yields a row, or compute `SUM(...) / 7` explicitly instead of using `AVG`.
saying these in an interview costs you the question
- Says ROWS 6 PRECEDING and a 7-day window are the same thing
- Uses an offset RANGE frame with two ORDER BY columns
- Assumes AVG over a 7-day frame always divides by seven
- Writes a negative offset to look backwards
- Believes every engine supports offset RANGE bounds