⚡ Advancedadvanced

How to use window functions in SQL

Calculate running totals, rankings and moving averages without collapsing rows.

ROW_NUMBER — unique row rankAll databases

SELECT
  name,
  country,
  age,
  ROW_NUMBER() OVER (ORDER BY age DESC) AS rank
FROM users;

Assigns a unique sequential number to each row based on the ORDER BY. No ties.

RANK and DENSE_RANKAll databases

SELECT
  name,
  score,
  RANK()       OVER (ORDER BY score DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM leaderboard;
-- RANK:       1, 2, 2, 4  (skips 3)
-- DENSE_RANK: 1, 2, 2, 3  (no skips)

RANK skips numbers after ties. DENSE_RANK does not skip.

When to use: Leaderboards, competition rankings.

PARTITION BY — rank within groupsAll databases

SELECT
  country,
  name,
  age,
  ROW_NUMBER() OVER (
    PARTITION BY country
    ORDER BY age DESC
  ) AS rank_in_country
FROM users;

PARTITION BY restarts the window function for each group. Ranks users within their country.

When to use: Top N per group — the most common window function use case.

Running total with SUM OVERPostgreSQL

SELECT
  created_at::date AS date,
  total,
  SUM(total) OVER (ORDER BY created_at) AS running_total
FROM orders
ORDER BY created_at;

Calculates a cumulative sum that grows with each row.

When to use: Revenue dashboards, cumulative metrics.

LAG and LEAD — access previous/next rowsAll databases

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

LAG accesses the previous row, LEAD accesses the next row. Useful for period-over-period comparisons.

When to use: Day-over-day, month-over-month change calculations.

Common Mistakes

→Forgetting ORDER BY inside OVER() for running totals — without it, SUM OVER gives the total for all rows
→PARTITION BY ≠ GROUP BY — window functions do not collapse rows, GROUP BY does

Common Mistakes

Related Topics