Window functions

A window function is written as function() OVER (PARTITION BY ... ORDER BY ... frame). PARTITION BY splits rows into groups, ORDER BY sorts rows inside each group, and the optional frame says which nearby rows count (for example the 7 previous rows). Every input row stays in the output and gets its own computed value.

Ranking functions: ROW_NUMBER() numbers rows 1, 2, 3 with no ties; RANK() gives ties the same rank and then skips (1, 2, 2, 4); DENSE_RANK() gives ties the same rank without gaps (1, 2, 2, 3); NTILE(n) splits rows into n buckets.

Offset functions: LAG(col) reads a previous row, LEAD(col) a following row, and FIRST_VALUE / LAST_VALUE / NTH_VALUE read positions in the frame. Any aggregate (SUM, AVG, COUNT, MIN, MAX) can also be used as a window function, which gives running totals and moving averages. PostgreSQL has had window functions since 8.4; MySQL since 8.0.

1. 1Window functions are computed after FROM, WHERE, GROUP BY and HAVING, and before the final ORDER BY and LIMIT. That is why you cannot use them in WHERE; wrap the query in a CTE or subquery and filter outside (some warehouses like Snowflake and BigQuery add a QUALIFY clause for this, PostgreSQL and MySQL do not have it).
2. 2PARTITION BY splits rows into independent groups. Without it, the whole result is one partition.
3. 3ORDER BY inside OVER sorts rows within each partition. Ranking and offset functions need it to mean anything.
4. 4The frame decides which rows an aggregate sees. With ORDER BY and no explicit frame, the default is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: from the start of the partition to the current row, including all rows that tie with it (its peers). Without ORDER BY, the frame is the whole partition.
5. 5For a precise moving window, write the frame yourself: ROWS BETWEEN 6 PRECEDING AND CURRENT ROW is the current row plus the 6 before it (a 7-row moving window).
6. 6The engine typically sorts rows by (partition keys, order keys) once, then streams through them computing each row's value.

-- employees(id, name, dept, salary)
-- Top 2 earners per department, ties broken by name
WITH ranked AS (
  SELECT name, dept, salary,
         ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC, name) AS rn,
         RANK()       OVER (PARTITION BY dept ORDER BY salary DESC)       AS rnk,
         DENSE_RANK() OVER (PARTITION BY dept ORDER BY salary DESC)       AS drnk
  FROM employees
)
SELECT name, dept, salary, rn, rnk, drnk
FROM ranked
WHERE rn <= 2;          -- window results can only be filtered outside

-- daily_sales(day, amount): running total and change vs previous day
SELECT day, amount,
       SUM(amount) OVER (ORDER BY day ROWS UNBOUNDED PRECEDING) AS running_total,
       amount - LAG(amount) OVER (ORDER BY day)                AS change
FROM daily_sales
ORDER BY day;

For Eng with Asha 120, Farah 110, Chen 110, Gita 95, the first query returns Asha (rn 1) and Chen (rn 2, because ties are broken by name and Chen sorts before Farah), both with rnk and drnk as in the walkthrough. The second returns running totals 100, 150, 220 and changes NULL, -50, 20 (with day stored as a date so the order is chronological).

Cost: Usually one sort of the input by partition and order keys, O(n log n), then a linear pass. An index on (partition key, order key) can remove the sort. Aggregates over sliding ROWS frames are computed incrementally in most engines.