SQL Window Functions: The Underused Superpower

Window functions have been in Postgres, MySQL 8, SQL Server and SQLite for years, yet they are still the #1 thing developers reach for GROUP BY to do badly.

The mental model

A window function:

• Computes across a window of rows related to the current row.
• Does not collapse rows like GROUP BY.
• Is written as function(...) OVER (PARTITION BY ... ORDER BY ...).

ROW_NUMBER, RANK, DENSE_RANK

SELECT
  user_id,
  order_id,
  amount,
  ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at) AS order_seq
FROM orders;

ROW_NUMBER always increments by 1; RANK leaves gaps for ties; DENSE_RANK does not.

”Latest row per group” (the classic)

The cleanest way to get “the most recent order per customer”:

SELECT * FROM (
  SELECT *,
    ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn
  FROM orders
) WHERE rn = 1;

No correlated subquery, no self-join, no DISTINCT ON (Postgres-only).

LAG and LEAD — peek at neighbors

SELECT
  date,
  revenue,
  revenue - LAG(revenue) OVER (ORDER BY date) AS daily_delta,
  LEAD(revenue) OVER (ORDER BY date) AS next_day_revenue
FROM daily_revenue;

Running totals and moving averages

SELECT
  date,
  revenue,
  SUM(revenue) OVER (ORDER BY date) AS cumulative,
  AVG(revenue) OVER (ORDER BY date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS rolling_7d
FROM daily_revenue;

The ROWS BETWEEN ... clause is the key to moving windows.

Frame clauses cheat sheet

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW    -- running total
ROWS BETWEEN 6 PRECEDING AND CURRENT ROW            -- 7-row window
RANGE BETWEEN INTERVAL '7 days' PRECEDING           -- time-based window

Performance notes

• Index the PARTITION BY and ORDER BY columns.
• For huge tables, consider LATERAL joins or pre-aggregated tables.
• EXPLAIN ANALYZE shows window function cost separately.

TL;DR

Window functions turn 30-line procedural SQL into 3-line expressive SQL. Learn the ones above and you’ll never write a self-join for “latest row” again.