What does GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING count in a window frame?
answer
- a third mode beyond ROWS and RANGE
- its unit is not the row
- ties are the unit of counting
- one step back means one distinct value back
- works where values are irregularly spaced
basics
~20 sPeer groups, not rows or values. The frame covers all rows tied with the current row, plus every row of the one tied group before it and the one tied group after it, however many rows each group contains.
solid answer
~40 s`GROUPS` is the third frame mode, alongside `ROWS` and `RANGE`, and its unit is the **peer group** — the set of rows sharing an `ORDER BY` value. `GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING` therefore means "my whole tied group, the whole previous distinct group, and the whole next distinct group", regardless of how many rows sit in each. It fills a real gap: `ROWS` cannot express "the previous distinct day" because it does not know how many rows that day has, and `RANGE` cannot either when the ordering values are irregular — offset RANGE bounds count value distance, so a missing day or an uneven numeric step changes what "1 PRECEDING" reaches. `GROUPS` requires an `ORDER BY`, and engine support is narrower than for the other two modes.
go deeper
Just know that a third frame mode exists beside ROWS and RANGE and that its unit is the group of tied rows; it is rarely required at this level.
Define a peer group, state that GROUPS counts those groups, and contrast one step of GROUPS with one step of ROWS on data with several rows per date.
Recognise the sparse or irregular series where neither ROWS nor RANGE expresses the requirement, and have the portable collapse-then-frame rewrite ready when the engine lacks GROUPS.
Weigh the cost of a rarely supported frame mode against portability: whether a clearer one-line GROUPS frame is worth pinning an analytical layer to specific engines.
## Three units of distance Every frame bound is measured in some unit, and the mode chooses it: - `ROWS` — physical rows. `1 PRECEDING` is the row immediately before this one. - `RANGE` — ordering values. `1 PRECEDING` (with a numeric ordering column) reaches back to values greater than or equal to `current_value - 1`. - `GROUPS` — peer groups. `1 PRECEDING` is the whole set of rows carrying the previous *distinct* ordering value. A peer group is all rows sharing the same window `ORDER BY` value. `GROUPS` is the only mode whose bounds are counted in those groups. ## Why the other two modes cannot do this Suppose `daily_orders(order_date, amount)` has several rows per date and some dates missing entirely, and you want, for each row, an aggregate over this date plus the previous and next dates present in the data. `ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING` is wrong immediately: it reaches back exactly one row, which may be another order on the same date. To use ROWS you would first have to know how many rows each date holds, which is precisely what you are trying to aggregate. `RANGE BETWEEN INTERVAL '1' DAY PRECEDING AND INTERVAL '1' DAY FOLLOWING` is closer, and correct while the series is dense — but if 2024-03-05 is missing, the row on 2024-03-06 reaches back to an absent day and gets a narrower window than intended. RANGE measures value distance; it cannot say "the previous value that actually exists". `GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING` says exactly that. It steps one distinct ordering value backwards and one forwards, taking every row of each, and it does not care about the arithmetic distance between those values or the row counts inside them. ```sql SELECT order_date, amount, SUM(amount) OVER (ORDER BY order_date GROUPS BETWEEN 1 PRECEDING AND 1 FOLLOWING) AS three_day_window_sum FROM daily_orders; ``` Every row on a given date sees the same total — the sum over that date, the previous date present, and the next date present. ## Rules and constraints - `GROUPS` requires a window `ORDER BY`; without ordering there are no peer groups to count. - The offsets are counts of groups and must be non-negative. `UNBOUNDED PRECEDING`, `CURRENT ROW` and `UNBOUNDED FOLLOWING` are still available as bounds, and `CURRENT ROW` in GROUPS mode means the current row's entire peer group, as in RANGE mode. - Unlike offset `RANGE` bounds, `GROUPS` places no restriction on the *number* or *type* of ordering columns — the ordering key can be composite, since counting distinct groups needs no arithmetic. That makes it usable where offset RANGE is rejected. ## When it is genuinely the right tool - **Sparse or irregular series.** "This period and the adjacent periods that exist" — trading days, delivery dates, release versions — where calendar distance is not the intent. - **Ordering keys with no arithmetic.** A composite key, or a text ordering key, where an offset RANGE bound is impossible but "the neighbouring distinct value" is meaningful. - **Group-level neighbourhoods over detail rows.** Comparing a row against its own tied group and the neighbouring groups without first collapsing to one row per group. If your ordering key is unique per row, `GROUPS` degenerates to `ROWS`, because each row is its own peer group. That is a good sanity check: `GROUPS` only differs from `ROWS` when ties exist, and only differs from `RANGE` when the values are irregularly spaced. ## Portability caution `GROUPS` is the least widely implemented of the three modes. PostgreSQL supports it from version 11; other engines vary, and several support only `ROWS` and `RANGE`. Before using it, confirm support in your target engine — the portable fallback is to aggregate to one row per distinct ordering value in a derived table or CTE, then apply a `ROWS`-mode frame over that dense, one-row-per-group result, and join the detail rows back if you need them. ## The interview signal Mentioning `GROUPS` at all marks someone who has read the window-function specification rather than only the tutorials. What earns the credit is naming the unit — peer groups — and giving a case where neither `ROWS` nor `RANGE` expresses the requirement.
- When does a GROUPS frame behave identically to the equivalent ROWS frame?When the window `ORDER BY` key is unique for every row. Each row is then its own peer group, so counting groups and counting rows are the same operation. `GROUPS` only earns its keep when the ordering key has ties — otherwise it is a more obscure spelling of `ROWS` with narrower engine support.
- How would you express a GROUPS-style neighbourhood in an engine that does not support GROUPS?Collapse first: aggregate to one row per distinct ordering value in a CTE or derived table, apply a `ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING` frame over that dense result, then join the per-group figures back onto the detail rows if you need row-level output. It is more verbose but uses only universally supported frame modes.
- Can a GROUPS frame use a composite ORDER BY key?Yes. Counting distinct peer groups needs no arithmetic on the ordering value, so `GROUPS` accepts a multi-column `ORDER BY` — where an offset `RANGE` bound would be rejected outright. Peers are then rows agreeing on all the ordering columns.
saying these in an interview costs you the question
- Says GROUPS is just another name for PARTITION BY
- Thinks GROUPS counts rows like ROWS does
- Believes GROUPS relates to the GROUP BY clause
- Assumes every engine implements all three frame modes
- Cannot name a case where ROWS and RANGE both fail