Window functions

Window functions calculate across related rows without collapsing them into one row per group. That makes them ideal for ranking, latest-row selection, running balances, comparisons with previous events, and analytics.

OVER and PARTITION BY

SELECT id,
       name,
       department_id,
       salary,
       AVG(salary) OVER (PARTITION BY department_id) AS department_avg
FROM employees
ORDER BY department_id, id;

Run the query to see the result.

Every employee remains visible while the department average is repeated for the relevant partition.

ROW_NUMBER, RANK, and DENSE_RANK

ROW_NUMBER gives a unique sequence. RANK leaves gaps after ties. DENSE_RANK does not leave gaps.

SELECT id,
       name,
       salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC, id) AS row_number,
       RANK() OVER (ORDER BY salary DESC) AS salary_rank,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dense_salary_rank
FROM employees
ORDER BY salary DESC, id;

Run the query to see the result.

Choose based on the business meaning of ties.

Top N per group

A classic SQL task is “top two employees in each department.” Calculate a row number inside each department, then filter in an outer query.

WITH ranked AS (
  SELECT id,
         name,
         department_id,
         salary,
         ROW_NUMBER() OVER (
           PARTITION BY department_id
           ORDER BY salary DESC, id
         ) AS rn
  FROM employees
)
SELECT id, name, department_id, salary
FROM ranked
WHERE rn <= 2
ORDER BY department_id, rn;

Run the query to see the result.

LAG and LEAD

LAG accesses a previous row in window order; LEAD accesses a following row. This is useful for event transitions and detecting gaps.

SELECT entity_id,
       sequence_no,
       status,
       LAG(sequence_no) OVER (
         PARTITION BY entity_id
         ORDER BY sequence_no
       ) AS previous_sequence
FROM events
ORDER BY entity_id, sequence_no;

Run the query to see the result.

Running totals

SELECT account_id,
       occurred_at,
       amount,
       SUM(amount) OVER (
         PARTITION BY account_id
         ORDER BY occurred_at, id
         ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_balance
FROM ledger
ORDER BY account_id, occurred_at, id;

Run the query to see the result.

Specify the frame explicitly when cumulative semantics matter; default frames can surprise you when ordering values tie.

Latest row per entity

Window functions are one of the cleanest solutions to “latest event per entity.” Rank each entity's rows by timestamp descending and keep rn = 1.

Execution-order implication

Window functions are evaluated after WHERE/GROUP BY/HAVING. You normally cannot filter directly on a window alias in WHERE; use a subquery or CTE, as shown in the top-N example.

Practice: return the latest status for each entity from events, resolving equal timestamps by the highest id.