Window functions

What are window functions and how do they differ from GROUP BY?

A window function computes a value over a group of related rows (a "window"), but, unlike GROUP BY, doesn't collapse the rows: every row stays in the result and gets an additional column.

The syntax: function() OVER (PARTITION BY … ORDER BY …).

• PARTITION BY: how to split into groups (like GROUP BY, but without collapsing).
• ORDER BY: the order inside the group (for ranks and running totals).

-- each transaction + the total for its account alongside
SELECT id, account_id, amount,
       SUM(amount) OVER (PARTITION BY account_id) AS account_total
FROM transactions;

How do ROW_NUMBER, RANK and DENSE_RANK differ?

All three number rows in the ORDER BY order. The difference is in equal values:

• ROW_NUMBER: always unique numbers; among equal ones the order is arbitrary.
• RANK: equal ones get an equal rank, then a gap.
• DENSE_RANK: an equal rank without gaps.

SELECT name, salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn,
       RANK()       OVER (ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
FROM employees;

How do you find the top N records in each group?

Number the rows inside each group with a window function, and then filter by the number. You can't filter a window function directly in WHERE: it is computed later, so a CTE or a subquery is needed.

-- the 3 largest transactions of each account
WITH ranked AS (
  SELECT id, account_id, amount,
         ROW_NUMBER() OVER (PARTITION BY account_id ORDER BY amount DESC) AS rn
  FROM transactions
  WHERE status = 'completed'
)
SELECT account_id, id, amount
FROM ranked
WHERE rn <= 3
ORDER BY account_id, rn;

What do LAG and LEAD do?

LAG(col) returns the value from the previous row in the window, LEAD(col) from the next one. Useful for comparison with the previous state: the difference between events, intervals, status changes.

-- the interval between consecutive transactions of an account
SELECT account_id, id, created_at,
       created_at - LAG(created_at) OVER (PARTITION BY account_id ORDER BY created_at) AS gap
FROM transactions;

-- suspicious: several identical transactions less than 5 seconds apart (double submission?)
WITH g AS (
  SELECT id, account_id, amount, created_at,
         LAG(amount)     OVER w AS prev_amount,
         LAG(created_at) OVER w AS prev_at
  FROM transactions
  WINDOW w AS (PARTITION BY account_id ORDER BY created_at)
)
SELECT * FROM g
WHERE amount = prev_amount AND created_at - prev_at < INTERVAL '5 seconds';

How do you compute a running total?

SUM() OVER (… ORDER BY …) adds values from the first row of the window up to the current one. This is how the balance at each moment is reconstructed from the transaction history.

SELECT account_id, id, created_at, type, amount,
       SUM(CASE WHEN type = 'deposit' THEN amount ELSE -amount END)
         OVER (PARTITION BY account_id ORDER BY created_at, id) AS running_balance
FROM transactions
WHERE status = 'completed'
ORDER BY account_id, created_at, id;