What do EXCLUDE CURRENT ROW and EXCLUDE TIES do in a window frame clause?
answer
- an optional tail on the frame clause
- subtracts rows after bounds are applied
- one keeps you, one drops you
- compare a row against everyone else
- the default removes nothing
basics
~20 sEXCLUDE CURRENT ROW removes just the current row from the frame that was computed; EXCLUDE TIES removes its peers but keeps the current row. EXCLUDE GROUP removes both, and EXCLUDE NO OTHERS, the default, removes nothing.
solid answer
~40 s`EXCLUDE` is an optional tail on the frame clause that subtracts rows *after* the frame bounds have been applied. There are four spellings: `EXCLUDE NO OTHERS` is the default and changes nothing; `EXCLUDE CURRENT ROW` drops the current row only; `EXCLUDE GROUP` drops the current row together with all its peers — rows tied with it on the window `ORDER BY`; `EXCLUDE TIES` drops the peers but keeps the current row itself. The classic use is a leave-one-out comparison: `AVG(salary) OVER (PARTITION BY dept_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW)` gives each employee the average of *everyone else* in the department, with no self-join and no subtract-yourself arithmetic. Support is not universal, so check your engine.
go deeper
Know only that a frame clause can end with EXCLUDE and that it removes rows from the frame, not from the query output; it is rarely asked at this level.
List the four options and say precisely which of them keeps the current row, and explain that exclusion happens after the bounds select the frame.
Reach for EXCLUDE CURRENT ROW on leave-one-out and outlier comparisons, know it is the only clean route for MIN/MAX-style aggregates, and handle the empty-frame NULL.
Judge whether a rarely supported clause belongs in a shared analytical layer, and what the portable rewrite costs if the query must run on more than one engine.
## Where EXCLUDE fits A full frame clause is `<mode> BETWEEN <start> AND <end> [ EXCLUDE <option> ]`. The mode and bounds select a set of rows; `EXCLUDE` then removes some of them before the function sees the result. The order matters: exclusion is a subtraction applied to an already-computed frame, never a change to where the bounds fall. ## The four options - `EXCLUDE NO OTHERS` — the default. Nothing is removed; writing it is purely documentation. - `EXCLUDE CURRENT ROW` — removes the current row only. Peers tied with it stay. - `EXCLUDE GROUP` — removes the current row and every row that is its peer on the window `ORDER BY`. - `EXCLUDE TIES` — removes the peers but keeps the current row. It is the complement of `EXCLUDE GROUP` with respect to the current row, and the option people most often misremember. If the window has no `ORDER BY`, or the ordering key is unique, then a row has no peers and `EXCLUDE TIES` does nothing while `EXCLUDE GROUP` behaves exactly like `EXCLUDE CURRENT ROW`. ## The leave-one-out pattern The strongest motivating case is comparing a row against its own group *without* letting it bias the comparison: ```sql SELECT employee_id, dept_id, salary, AVG(salary) OVER (PARTITION BY dept_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING EXCLUDE CURRENT ROW) AS avg_of_peers FROM employees; ``` Each employee is compared with the average of everyone else in the department. Without `EXCLUDE`, the alternatives are clumsy: compute the department `SUM` and `COUNT` over the whole partition and derive `(sum - salary) / (count - 1)` yourself, or write a correlated subquery with `WHERE e2.employee_id <> e.employee_id`. The manual arithmetic works for `SUM`, `COUNT` and `AVG` but not for `MIN`, `MAX`, `STRING_AGG` or `COUNT(DISTINCT …)` — you cannot subtract a row from a maximum. `EXCLUDE CURRENT ROW` handles all of them uniformly, which is its real value. Outlier detection follows the same shape: flag a reading that deviates from the average of its neighbours, using a sliding frame such as `ROWS BETWEEN 5 PRECEDING AND 5 FOLLOWING EXCLUDE CURRENT ROW` so the suspicious point does not pull the baseline toward itself. `EXCLUDE GROUP` and `EXCLUDE TIES` matter when the ordering key has duplicates and "everyone else" needs a precise definition. If several employees share a salary and you want each compared against those on *different* salaries, `EXCLUDE GROUP` is the option; if the current row should count itself but not its salary-twins, `EXCLUDE TIES` is. ## Empty frames after exclusion Exclusion can empty a frame. A department with a single employee, framed over the whole partition with `EXCLUDE CURRENT ROW`, leaves no rows at all — so `AVG` returns NULL and `COUNT` returns 0. That is not an error and it is usually the honest answer (there are no peers to compare against), but downstream expressions must handle it. Wrap it in `COALESCE`, or use a `CASE` on a `COUNT(*)` over the identical frame to decide what to display. ## Interaction with the mode `EXCLUDE` composes with all three modes. With `ROWS` the exclusion is straightforward row removal. With `RANGE` or `GROUPS` the frame already contains whole peer groups, so `EXCLUDE CURRENT ROW` punches a single-row hole in the middle of a peer group — which is exactly the point, and something the bounds alone cannot express in those modes. This is the main reason `EXCLUDE` exists as a separate clause rather than being folded into the bounds. ## Portability `EXCLUDE` is a standard frame option but is not implemented everywhere. PostgreSQL supports all four spellings from version 11; other engines vary, and some support none of them. The portable fallbacks are the ones described above — subtract-yourself arithmetic where the aggregate allows it, or a correlated subquery or self-join with an inequality predicate — both more verbose, and the correlated form generally more expensive. ## The interview signal Knowing `EXCLUDE` exists is a differentiator rather than a requirement. What distinguishes a strong answer is the reasoning: naming the leave-one-out use case, noticing that the trick does not generalise to `MIN`/`MAX` without it, and remembering which of `TIES` and `GROUP` keeps the current row.
- What is the difference between EXCLUDE GROUP and EXCLUDE TIES?`EXCLUDE GROUP` removes the current row *and* all its peers — every row tied with it on the window ORDER BY. `EXCLUDE TIES` removes the peers but keeps the current row. When the ordering key is unique, a row has no peers, so `EXCLUDE TIES` does nothing and `EXCLUDE GROUP` collapses to `EXCLUDE CURRENT ROW`.
- How would you get the leave-one-out average without EXCLUDE?Compute `SUM` and `COUNT` over the whole partition and derive `(sum - salary) / (count - 1)`, guarding the divide-by-zero when the partition has one row; or use a correlated subquery with `WHERE e2.employee_id <> e.employee_id`. The arithmetic trick works only for additive aggregates — you cannot subtract a row from `MIN`, `MAX` or `STRING_AGG`, which is where `EXCLUDE` becomes hard to replace.
- What does an aggregate return if EXCLUDE empties the frame?The empty-input result: NULL for `SUM`, `AVG`, `MIN` and `MAX`, and 0 for `COUNT`. A one-row partition framed over everything with `EXCLUDE CURRENT ROW` hits this every time, so guard it with `COALESCE` or a `CASE` on a `COUNT(*)` over the same frame.
saying these in an interview costs you the question
- Thinks EXCLUDE filters rows out of the query result
- Says EXCLUDE TIES also removes the current row
- Believes EXCLUDE shifts the frame bounds rather than subtracting rows
- Assumes the leave-one-out trick works for MIN and MAX by subtraction
- Forgets that excluding can leave an empty frame returning NULL