Frames & Offset Functions Interview Questions & Answers

A window frame narrows the window to a subset of rows around the current row, within its partition. It's defined with a ROWS or RANGE clause inside OVER, and controls exactly which rows an aggregate like SUM or AVG sees.

-- 3-row moving average: current row + the two before it
SELECT day, amount,
       AVG(amount) OVER (ORDER BY day
                         ROWS BETWEEN 2 PRECEDING AND CURRENT ROW) AS moving_avg
FROM   sales;

Frames enable moving averages, running totals over a sliding window, and lookback/lookahead aggregates.

Rule of thumb: a frame is the sliding sub-window an aggregate operates on — set it with ROWS/RANGE BETWEEN ... AND ....

Both define the frame boundaries, but they count differently:

• ROWS works on physical row positions — "the 2 rows before this one," regardless of their values.
• RANGE works on value ranges of the ORDER BY column — rows whose ordering value falls within an offset, treating ties (peers) as a unit.

-- ROWS: exactly 3 physical rows
SUM(x) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

-- RANGE: all rows with the same day are included together
SUM(x) OVER (ORDER BY day RANGE BETWEEN 2 PRECEDING AND CURRENT ROW)

With duplicate ORDER BY values, RANGE includes all peer rows; ROWS counts each physically.

Rule of thumb: ROWS = count physical rows; RANGE = include all rows with values in range (peers grouped).

When you add ORDER BY to an aggregate window without an explicit frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. This gives a running (cumulative) aggregate — but because it's RANGE, tied rows are included together through the current row.

-- default frame = running total, but RANGE means peers are summed together
SUM(amount) OVER (ORDER BY day)

This trips people up: with duplicate day values, each tied row shows the same cumulative total (all peers included). Use ROWS for true row-by-row accumulation.

Rule of thumb: ORDER BY with no frame = RANGE ... UNBOUNDED PRECEDING TO CURRENT ROW; switch to ROWS to avoid peer-grouping surprises.

With no ORDER BY in the OVER clause, the frame is the entire partition — every row in the partition sees all of them. The aggregate is the same for all rows in that partition.

-- every row gets the department total (whole partition)
SUM(salary) OVER (PARTITION BY dept_id) AS dept_total

This is why SUM(x) OVER (PARTITION BY g) gives a group total on each row, while adding ORDER BY turns it into a running total.

Rule of thumb: no ORDER BY → frame is the full partition (a flat group aggregate); adding ORDER BY makes it cumulative.

A frame is BETWEEN <start> AND <end>, where each bound is one of:

• UNBOUNDED PRECEDING — the first row of the partition.
• n PRECEDING — n rows (or values) before the current row.
• CURRENT ROW — the current row (or its peers under RANGE).
• n FOLLOWING — n rows (or values) after the current row.
• UNBOUNDED FOLLOWING — the last row of the partition.

-- centered 3-row average: one before, current, one after
AVG(x) OVER (ORDER BY day ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING)

-- whole partition
SUM(x) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING)

Rule of thumb: combine UNBOUNDED/n PRECEDING, CURRENT ROW, n FOLLOWING/ UNBOUNDED to frame any sliding or anchored window.

Use AVG with a ROWS frame spanning the desired window of rows around the current row.

-- 7-day moving average (current day + previous 6)
SELECT day, amount,
       AVG(amount) OVER (ORDER BY day
                         ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS ma_7
FROM   sales;

Use ROWS (not RANGE) for a fixed count of rows. For a centered average, frame BETWEEN 3 PRECEDING AND 3 FOLLOWING. Early rows average fewer rows unless you handle warm-up.

Rule of thumb: moving average = AVG(x) OVER (ORDER BY t ROWS BETWEEN n PRECEDING AND CURRENT ROW).

LAG(col, offset, default) returns the value of col from a previous row within the partition — offset rows back (default 1). If there's no such row, it returns default (or NULL). It's the classic tool for comparing a row to the prior one.

-- compare each day's sales to the previous day
SELECT day, amount,
       LAG(amount, 1, 0) OVER (ORDER BY day) AS prev_amount,
       amount - LAG(amount) OVER (ORDER BY day) AS day_over_day
FROM   sales;

Rule of thumb: LAG() looks backward to the previous row — perfect for period-over-period deltas.

LEAD(col, offset, default) is the mirror of LAG — it returns a value from a following row, offset rows ahead (default 1), or default/NULL past the end. Use it to look forward.

-- gap until the customer's next order
SELECT customer_id, order_date,
       LEAD(order_date) OVER (PARTITION BY customer_id
                              ORDER BY order_date) AS next_order,
       LEAD(order_date) OVER (PARTITION BY customer_id
                              ORDER BY order_date) - order_date AS days_gap
FROM   orders;

Rule of thumb: LEAD() looks forward to the next row — use it for "time until next event" or next-value comparisons.

Both take three arguments: LAG(expr [, offset [, default]]).

• expr — the column/expression to fetch.
• offset — how many rows back/forward (default 1).
• default — value returned when the offset falls outside the partition (default NULL).

-- value 2 rows back; if none, use 0 instead of NULL
LAG(amount, 2, 0) OVER (ORDER BY day)

Supplying a default is the clean way to avoid NULLs at partition edges (e.g. the first row's "previous" value).

Rule of thumb: pass offset to jump multiple rows and default to replace the edge NULLs.

FIRST_VALUE(col) returns col from the first row of the frame; LAST_VALUE(col) from the last row of the frame. They're handy for putting a partition's boundary value on every row.

SELECT name, dept_id, salary,
       FIRST_VALUE(name) OVER (PARTITION BY dept_id
                               ORDER BY salary DESC) AS highest_paid
FROM   employees;

Gotcha: with the default frame (... CURRENT ROW), LAST_VALUE returns the current row, not the partition's true last — you must widen the frame (see next question).

Rule of thumb: FIRST_VALUE/LAST_VALUE grab a frame boundary value — mind the frame, especially for LAST_VALUE.

Because the default frame with ORDER BY is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW — the frame ends at the current row, so LAST_VALUE sees the current row as the last. To get the partition's true last value, extend the frame to UNBOUNDED FOLLOWING.

-- WRONG: returns current row's value
LAST_VALUE(salary) OVER (PARTITION BY dept_id ORDER BY salary)

-- RIGHT: frame covers the whole partition
LAST_VALUE(salary) OVER (PARTITION BY dept_id ORDER BY salary
                         ROWS BETWEEN UNBOUNDED PRECEDING
                                  AND UNBOUNDED FOLLOWING)

Rule of thumb: always set an explicit ... UNBOUNDED FOLLOWING frame with LAST_VALUE, or it just returns the current row.

NTH_VALUE(col, n) returns col from the nth row of the frame (1-based). Like LAST_VALUE, it's frame-sensitive, so widen the frame to see the whole partition.

-- the 2nd highest-paid employee's name, shown on every row of the dept
SELECT name, dept_id,
       NTH_VALUE(name, 2) OVER (PARTITION BY dept_id ORDER BY salary DESC
                                ROWS BETWEEN UNBOUNDED PRECEDING
                                         AND UNBOUNDED FOLLOWING) AS second_top
FROM   employees;

Rows before the nth position return NULL. (Supported in PostgreSQL, MySQL 8+; not in SQL Server.)

Rule of thumb: NTH_VALUE(col, n) fetches the nth frame row — widen the frame to target the whole partition.

The frame decides what an aggregate accumulates:

• Running total — ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW (everything up to now).
• Sliding window total — ROWS BETWEEN n PRECEDING AND CURRENT ROW (last n+1 rows).
• Full partition total — ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING (or omit ORDER BY).

SUM(x) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) -- running
SUM(x) OVER (ORDER BY day ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)         -- last 7

Rule of thumb: the frame's start bound sets the accumulation window — UNBOUNDED PRECEDING for running, n PRECEDING for sliding.

Comparing each row to its neighbor with a self-join is verbose, needs a way to define "previous" (often a correlated subquery with MAX(... < current)), and is slow. LAG/LEAD do it in a single ordered pass, clearly and efficiently.

-- self-join approach (awkward, slower)
SELECT a.day, a.amount - b.amount AS delta
FROM sales a LEFT JOIN sales b ON b.day = a.day - 1;

-- LAG approach (clean, one pass)
SELECT day, amount - LAG(amount) OVER (ORDER BY day) AS delta FROM sales;

Rule of thumb: prefer LAG/LEAD over self-joins for previous/next comparisons — simpler and faster.

Gaps-and-islands problems (finding consecutive runs or missing ranges) are solved by spotting where sequences break — using LAG/LEAD to detect gaps, or the "ROW_NUMBER difference" trick to label islands.

-- group consecutive login days into "islands"
WITH marked AS (
    SELECT user_id, login_date,
           login_date - (ROW_NUMBER() OVER (PARTITION BY user_id
                                            ORDER BY login_date)) * INTERVAL '1 day'
           AS grp
    FROM logins
)
SELECT user_id, MIN(login_date) AS streak_start, MAX(login_date) AS streak_end,
       COUNT(*) AS streak_len
FROM marked GROUP BY user_id, grp;

Rows in the same run share a constant grp value because the date and row number advance together.

Rule of thumb: detect gaps with LAG/LEAD; identify islands with the date-minus-ROW_NUMBER constant-group trick.

Yes — RANGE frames can use a value offset, including time intervals, so the window is defined by the ORDER BY value rather than a row count. This gives true time-based windows even when rows are irregularly spaced.

-- sum of sales in the trailing 7 days by date value (not row count)
SELECT day, amount,
       SUM(amount) OVER (ORDER BY day
                         RANGE BETWEEN INTERVAL '7 days' PRECEDING
                                   AND CURRENT ROW) AS rolling_7d
FROM sales;

Unlike ROWS, this correctly handles missing days and multiple rows per day. (PostgreSQL and modern engines support RANGE with numeric/interval offsets.)

Rule of thumb: use RANGE with an interval/numeric offset for value-based windows (e.g. "last 7 days") that don't depend on row counts.

At partition edges, LAG/LEAD return their default argument (or NULL) — e.g. the first row has no previous row. They also return the actual stored value even if it's NULL; they don't skip NULL data values.

-- first row's prev is 0 (the default), not an error
LAG(amount, 1, 0) OVER (ORDER BY day)

Some databases (Oracle, and via IGNORE NULLS in standard SQL / newer engines) support IGNORE NULLS to skip NULL data values and fetch the nearest non-null.

Rule of thumb: edges yield the default/NULL; supply a default to clean them up, and use IGNORE NULLS (where supported) to skip null data.

A frame is always bounded by the partition — it never crosses partition boundaries. UNBOUNDED PRECEDING means the first row of the current partition, and LAG/LEAD stop at the partition edge.

-- running total resets at each customer; frame stays within the partition
SELECT customer_id, order_date, amount,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date
                         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rt
FROM orders;

So per-customer running totals start fresh for each customer automatically.

Rule of thumb: frames live inside their partition — UNBOUNDED and offsets are relative to the partition, not the whole table.