SQLDailyDrills

Window functions: OVER

Calculate across rows without collapsing them. OVER says which rows to look at, like RANK and running totals in Excel.

Syntax

ROW_NUMBER() OVER (ORDER BY column)
RANK() OVER (ORDER BY column)
SUM(column) OVER (PARTITION BY column ORDER BY column)
  • PARTITION BY restarts the calculation for each group.
  • Unlike GROUP BY, every row stays in the result.

Example

SELECT order_id, RANK() OVER (ORDER BY order_date) AS day_rank FROM orders;

Each order with its rank by date. Orders on the same day share a rank.

Tables used: orders. Try it in a lesson below.

Common mistakes

  • ROW_NUMBER gives every row a different number; RANK gives ties the same number and skips the next ones.
  • Window functions run after WHERE, so you cannot use their results in WHERE. Wrap the query in a subquery or WITH to filter on them.

Practise it