PostgreSQL Window Functions Tutorial

Window functions perform calculations across a set of rows related to the current row, without collapsing them into a single output row like GROUP BY does. They are one of the most powerful features in SQL.

ROW_NUMBER, RANK, and DENSE_RANK

-- Number rows within each category by price
SELECT
  name,
  category,
  price,
  ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS row_num,
  RANK()       OVER (PARTITION BY category ORDER BY price DESC) AS rank,
  DENSE_RANK() OVER (PARTITION BY category ORDER BY price DESC) AS dense_rank
FROM products;

ROW_NUMBER assigns a unique sequential number. RANK leaves gaps for ties (1, 2, 2, 4). DENSE_RANK does not leave gaps (1, 2, 2, 3). All three partition independently within each category.

LAG and LEAD

-- Compare each month's revenue to the previous month
SELECT
  DATE_TRUNC('month', created_at) AS month,
  SUM(total) AS revenue,
  LAG(SUM(total)) OVER (ORDER BY DATE_TRUNC('month', created_at)) AS prev_month,
  ROUND(
    (SUM(total) - LAG(SUM(total)) OVER (ORDER BY DATE_TRUNC('month', created_at)))
    / LAG(SUM(total)) OVER (ORDER BY DATE_TRUNC('month', created_at)) * 100,
    1
  ) AS growth_pct
FROM orders
GROUP BY DATE_TRUNC('month', created_at)
ORDER BY month;

LAG accesses the previous row's value and LEAD accesses the next row's value. They are invaluable for calculating period-over-period changes.

Running Totals and Moving Averages

-- Running total of revenue
SELECT
  created_at::date AS day,
  total,
  SUM(total) OVER (ORDER BY created_at) AS running_total
FROM orders;

-- 7-day moving average
SELECT
  day,
  revenue,
  ROUND(AVG(revenue) OVER (
    ORDER BY day
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
  ), 2) AS moving_avg_7d
FROM daily_revenue;

Practical Example: Top N per Group

-- Top 3 products per category by revenue
SELECT * FROM (
  SELECT
    p.name,
    p.category,
    SUM(oi.quantity * oi.unit_price) AS revenue,
    ROW_NUMBER() OVER (
      PARTITION BY p.category
      ORDER BY SUM(oi.quantity * oi.unit_price) DESC
    ) AS rn
  FROM order_items oi
  JOIN products p ON oi.product_id = p.id
  GROUP BY p.id, p.name, p.category
) ranked
WHERE rn <= 3;

Common Use Cases

• Ranking items within groups (top products per category, leaderboards)
• Month-over-month or week-over-week growth calculations
• Running totals, cumulative sums, and moving averages
• Deduplication by selecting only the most recent row per group