Window Functions

Window functions perform calculations across a set of rows related to the current row.

ROW_NUMBER()

-- Assign sequential numbers to rows
SELECT
  product_id,
  name,
  price,
  ROW_NUMBER() OVER (ORDER BY price DESC) as row_num
FROM products;

RANK() and DENSE_RANK()

-- Rank products by price (with gaps vs without gaps)
SELECT
  product_id,
  name,
  price,
  RANK() OVER (ORDER BY price DESC) as rank,
  DENSE_RANK() OVER (ORDER BY price DESC) as dense_rank
FROM products;

PARTITION BY

-- Row number within each category
SELECT
  product_id,
  name,
  category,
  price,
  ROW_NUMBER() OVER (
    PARTITION BY category
    ORDER BY price DESC
  ) as rank_in_category
FROM products;

Running Total (SUM with Window)

-- Calculate running total of order values
SELECT
  order_id,
  order_date,
  total,
  SUM(total) OVER (
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) as running_total
FROM orders;

Moving Average

-- Calculate 3-day moving average
SELECT
  order_date,
  total,
  AVG(total) OVER (
    ORDER BY order_date
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) as moving_avg_3day
FROM orders;

LAG() and LEAD()

-- Compare current row with previous and next
SELECT
  product_id,
  name,
  price,
  LAG(price, 1) OVER (ORDER BY price) as prev_price,
  LEAD(price, 1) OVER (ORDER BY price) as next_price,
  price - LAG(price, 1) OVER (ORDER BY price) as price_diff
FROM products;

FIRST_VALUE() and LAST_VALUE()

-- Get first and last values in window
SELECT
  order_id,
  user_id,
  order_date,
  total,
  FIRST_VALUE(total) OVER (
    PARTITION BY user_id
    ORDER BY order_date
  ) as first_order_total,
  LAST_VALUE(total) OVER (
    PARTITION BY user_id
    ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
  ) as last_order_total
FROM orders;

NTILE()

-- Divide rows into N equal groups (quartiles)
SELECT
  product_id,
  name,
  price,
  NTILE(4) OVER (ORDER BY price) as price_quartile
FROM products;

Cumulative Distribution (CUME_DIST)

-- Calculate cumulative distribution
SELECT
  product_id,
  name,
  price,
  CUME_DIST() OVER (ORDER BY price) as cumulative_dist,
  PERCENT_RANK() OVER (ORDER BY price) as percent_rank
FROM products;

Complex Window with Multiple Partitions

-- Analyze sales performance by category and month
SELECT
  category,
  DATE_TRUNC('month', order_date) as month,
  SUM(total) as monthly_sales,
  AVG(SUM(total)) OVER (
    PARTITION BY category
    ORDER BY DATE_TRUNC('month', order_date)
    ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
  ) as moving_avg_sales,
  RANK() OVER (
    PARTITION BY DATE_TRUNC('month', order_date)
    ORDER BY SUM(total) DESC
  ) as category_rank
FROM orders o
JOIN products p ON o.product_id = p.product_id
GROUP BY category, DATE_TRUNC('month', order_date);

Top N per Group

-- Get top 3 products per category
SELECT *
FROM (
  SELECT
    product_id,
    name,
    category,
    price,
    ROW_NUMBER() OVER (
      PARTITION BY category
      ORDER BY price DESC
    ) as rn
  FROM products
) ranked
WHERE rn <= 3;