Window Functions

Use AVG(revenue) OVER (ORDER BY day ROWS BETWEEN 2 PRECEDING AND CURRENT ROW). For each row the frame holds that row plus the two before it, so the average slides down the ordered rows, one row at a time.

ROW_NUMBER can still number identical rows, but no portable DELETE can target one copy, because any predicate matching a loser matches the survivor too. Rebuild the table from the rn = 1 set and swap it in.

They all land in one partition. PARTITION BY groups NULLs together as peers rather than using = comparison, so every NULL-email row after the first is numbered above 1 and a delete of rn > 1 wipes out unrelated records.

A 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.

Compare each row with the previous one using LAG(); emit 1 when the elapsed time exceeds the threshold and 0 otherwise, then take a running SUM() of that flag in the same order. The cumulative sum is a session id you can GROUP BY.

Define one named window carrying the shared PARTITION BY and ORDER BY and no frame, then let each column refine it with OVER (w ROWS ...). The partition key then lives in one place, and the base must stay frame-free to remain refinable.

No. The ORDER BY inside an OVER clause only sequences rows within each partition so the window function can be computed; it makes no promise about the order rows are returned in. Only a query-level ORDER BY guarantees output order.

NTILE(10) guarantees equal-sized buckets, not equal value ranges, so identical amounts can fall either side of a boundary and cut-points drift as the population changes. For segments that depend on the value itself, bucket on explicit cut-points with a CASE expression.

Aggregate first, rank second. Sum revenue per category and product with GROUP BY, then apply ROW_NUMBER() OVER (PARTITION BY category_id ORDER BY revenue DESC) over that grouped result, and filter rn <= 3 one level further out.

Because WHERE is applied before window functions: the window only ever sees rows that survived the filter, so its total covers the filtered subset. To divide by an unfiltered total, compute the window in an inner query over all rows and filter outside it.

Put two windowed SUMs in one SELECT: one partitioned by account plus the year and month of the transaction, one partitioned by account alone, both ordered by transaction time. Each OVER clause is evaluated independently over the same rows.

Gaps and islands names a family of SQL problems over an ordered column: islands are maximal runs of consecutive values, gaps are the stretches missing between them. The work is inventing a group key per run.

The WINDOW clause sits between HAVING and ORDER BY. A named window is scoped to the query block that defines it, and within that block OVER w can be used in the SELECT list and in the query's ORDER BY.

Both give every tied row the same value. PERCENT_RANK() is (RANK() - 1) / (rows - 1), so the first row is always 0. CUME_DIST() is the fraction of rows at or before the tie group, so the last row is always 1.

QUALIFY filters on window function results the way HAVING filters on aggregates, so you can write the rank predicate in the same query instead of wrapping it. It is a dialect extension, not part of the ISO SQL standard.

EXCLUDE 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.

Peer 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.

Build a group id with COUNT(reading) OVER (ORDER BY ts), which stays constant across a run of NULLs, then take FIRST_VALUE(reading) OVER (PARTITION BY that id ORDER BY ts). Rows before the first value stay NULL.

Because DISTINCT is not supported in the windowed form of an aggregate. PostgreSQL and SQL Server reject it outright, and MySQL does not support it either. Compute the distinct count in a grouped step and attach it to the detail rows instead.