PostgreSQL Window Functions

What Are Window Functions?

Window functions perform calculations across a set of rows that are related to the current row, without collapsing them into a single output like GROUP BY. They are essential for analytics, ranking, and comparing rows within partitions.

-- Basic syntax
SELECT
  column,
  window_function() OVER (
    PARTITION BY partition_column
    ORDER BY sort_column
    ROWS BETWEEN ... AND ...
  )
FROM table;

Ranking Functions

Ranking functions assign positions to rows within each partition:

SELECT
  department,
  name,
  salary,
  ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS row_num,
  RANK()       OVER (PARTITION BY department ORDER BY salary DESC) AS rank,
  DENSE_RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS dense_rank
FROM employees;

-- ROW_NUMBER: 1, 2, 3, 4 (always unique)
-- RANK:       1, 2, 2, 4 (gaps after ties)
-- DENSE_RANK: 1, 2, 2, 3 (no gaps after ties)

-- Top 3 earners per department
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
) ranked WHERE rn <= 3;

LAG and LEAD

Access values from previous or next rows without a self-join. Perfect for calculating period-over-period changes.

-- Month-over-month revenue change
SELECT
  month,
  revenue,
  LAG(revenue, 1)  OVER (ORDER BY month) AS prev_month,
  revenue - LAG(revenue, 1) OVER (ORDER BY month) AS change,
  LEAD(revenue, 1) OVER (ORDER BY month) AS next_month
FROM monthly_revenue;

-- Days between consecutive orders per customer
SELECT
  customer_id,
  order_date,
  order_date - LAG(order_date) OVER (
    PARTITION BY customer_id ORDER BY order_date
  ) AS days_since_last_order
FROM orders;

Running Totals & Moving Averages

Window frames control which rows are included in the calculation relative to the current row.

-- Running total of revenue
SELECT
  order_date,
  amount,
  SUM(amount) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING) AS running_total
FROM orders;

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

-- Cumulative percentage
SELECT
  product,
  revenue,
  SUM(revenue) OVER (ORDER BY revenue DESC) /
    SUM(revenue) OVER () * 100 AS cumulative_pct
FROM product_revenue;

NTILE, FIRST_VALUE & LAST_VALUE

-- Divide customers into 4 quartiles by spend
SELECT
  customer_id,
  total_spend,
  NTILE(4) OVER (ORDER BY total_spend DESC) AS quartile
FROM customer_stats;

-- First and last order amounts per customer
SELECT DISTINCT
  customer_id,
  FIRST_VALUE(amount) OVER w AS first_order_amount,
  LAST_VALUE(amount)  OVER w AS latest_order_amount
FROM orders
WINDOW w AS (
  PARTITION BY customer_id
  ORDER BY order_date
  ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
);