In ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING, which rows does the frame contain?
answer
- count outwards from the current row
- the current row is always included here
- partition edges cut the window short
- two before, me, one after
- aggregate over nothing is NULL, COUNT is 0
basics
~20 sAt most four rows in window ORDER BY order: the two rows before the current row, the current row itself, and the one after it. Near the start or end of a partition the frame is simply truncated.
solid answer
~40 sIt is a sliding window of up to four rows: two preceding, the current row, one following, measured by position because the mode is `ROWS`. Frames never cross a partition boundary, so for the first row of a partition the two preceding rows do not exist and the frame holds just the current row and the next one — the missing rows are dropped, not treated as NULL or borrowed from the neighbouring partition. The bound vocabulary is `UNBOUNDED PRECEDING`, `n PRECEDING`, `CURRENT ROW`, `n FOLLOWING`, `UNBOUNDED FOLLOWING`; the start bound must not fall after the end bound. If a frame ends up selecting no rows at all, aggregates over it return NULL, except `COUNT`, which returns 0.
go deeper
Be able to read any frame clause aloud in plain rows and say how many rows it covers, including at the first and last row of a partition.
Explain the shorthand single-bound form, the illegal bound orderings, and exactly what aggregates return over an empty frame.
Anticipate the edge effects in real reports — early rows of a moving average computed over a short window, NULLs from forward-looking frames — and specify what the output should show there.
Decide how smoothed or windowed metrics are defined organisationally: whether partial windows at series edges are published, suppressed, or backfilled, so different teams' charts agree.
## Reading a frame clause A frame clause has a **mode** (`ROWS`, `RANGE` or `GROUPS`) and one or two **bounds**. `ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING` reads: in units of physical rows, start two rows before the current row and end one row after it. Because the current row is always inside such a window, the frame is up to four rows wide, and it slides forward one row at a time as the function is evaluated across the partition. ## The five bounds - `UNBOUNDED PRECEDING` — the first row of the partition. Legal only as a start bound. - `n PRECEDING` — n units before the current row. - `CURRENT ROW` — the current row (in `ROWS` mode; in `RANGE` mode it means the edge of the current row's peer group). - `n FOLLOWING` — n units after the current row. - `UNBOUNDED FOLLOWING` — the last row of the partition. Legal only as an end bound. The start bound must not come after the end bound; something like `ROWS BETWEEN CURRENT ROW AND 1 PRECEDING` is rejected. There is also a shorthand: when you give a single bound, it is the *start* and the end defaults to `CURRENT ROW`. So `ROWS 3 PRECEDING` means `ROWS BETWEEN 3 PRECEDING AND CURRENT ROW`, and `ROWS UNBOUNDED PRECEDING` means everything from the partition start through the current row. ## Edges truncate; they do not pad A frame is always clipped to the partition. Number five rows 1..5 in one partition and evaluate `ROWS BETWEEN 2 PRECEDING AND 1 FOLLOWING`: | current row | frame | rows in frame | |---|---|---| | 1 | 1–2 | 2 | | 2 | 1–3 | 3 | | 3 | 1–4 | 4 | | 4 | 2–5 | 4 | | 5 | 3–5 | 3 | This matters for averages. A four-row moving average computed near the start of a partition is really a two- or three-row average, so early values in a smoothed series are noisier than the rest. If a report must show a value only once a full window exists, add a guard such as a `CASE` on `COUNT(*)` over the same frame, or filter out the first rows. `PARTITION BY` boundaries behave the same way: each partition's first rows are truncated independently, and no frame ever reaches into the neighbouring partition. ## Empty frames It is perfectly legal for a frame to select nothing. `ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING` selects no rows when the current row is the last of its partition. The rule then is the ordinary aggregate-over-no-rows rule: `SUM`, `AVG`, `MIN` and `MAX` return NULL, and `COUNT` returns 0. Value functions such as `FIRST_VALUE` also return NULL over an empty frame. Downstream arithmetic must handle that NULL — `COALESCE` it, or use `CASE` to decide what an empty window should mean. ## Order matters, and ties make it wobble Because `ROWS` counts positions, the frame's contents depend entirely on the window `ORDER BY`. If the ordering key has duplicates, the relative order of the tied rows is unspecified, so a positional window over them is not deterministic between executions. Adding a unique tiebreaker — `ORDER BY event_time, event_id` — makes the ordering total and the frame reproducible. Writing a `ROWS` frame with no window `ORDER BY` at all is worse: there is no defined order for it to count along, and some engines reject it outright. ## Symmetric and forward-looking windows Centred windows like `ROWS BETWEEN 3 PRECEDING AND 3 FOLLOWING` are used for smoothing, where you want a value influenced equally by past and future observations. Forward-only frames like `ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING` answer "what remains from here on" questions — a remaining-balance column, or the maximum still to come. Nothing forces a frame to look backwards; only `UNBOUNDED PRECEDING` and `UNBOUNDED FOLLOWING` are restricted to the start and end positions respectively. ## The habit to build When you see a frame clause, say it out loud in rows: "two before, me, one after, clipped at the partition edges." Then ask two questions — what happens at the first and last rows, and can this frame ever be empty? Those two checks catch most frame bugs before the query is ever run.
- What does an aggregate return when the frame selects no rows at all?The same as an aggregate over an empty input: `SUM`, `AVG`, `MIN` and `MAX` return NULL, and `COUNT` returns 0. This happens routinely at partition edges — `ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING` is empty for the last row of every partition — so wrap the result in `COALESCE` if downstream arithmetic cannot tolerate NULL.
- What does ROWS 3 PRECEDING mean when no end bound is written?It is shorthand for `ROWS BETWEEN 3 PRECEDING AND CURRENT ROW`: with a single bound the engine treats it as the start and defaults the end to the current row. The same shorthand gives you `ROWS UNBOUNDED PRECEDING`, meaning the partition start through the current row.
- Can a frame include rows from the neighbouring partition when the current row is the first in its partition?No. The frame is always clipped to the current partition — `PARTITION BY` boundaries are hard walls. The first row of each partition simply gets a shorter frame, which is why moving averages are computed over fewer rows at the start of every partition, not just at the start of the result set.
saying these in an interview costs you the question
- Thinks a truncated frame at the edge yields NULL for the whole row
- Believes frames can span partition boundaries
- Counts the frame width as three rows instead of four
- Assumes a moving average always covers the full window length
- Says an empty frame makes COUNT return NULL