Window Functions in Depth

12 min read

Window Functions in Depth

Window functions were introduced in SQL:2003 and are one of the most powerful analytical tools in PostgreSQL. They let you compute aggregates, ranks, and running values across a sliding "window" of rows without collapsing the result set the way GROUP BY does.

Anatomy of a Window Function

sql

-- General shape
func_name(args) OVER (
  PARTITION BY partition_col   -- divide rows into independent windows
  ORDER BY order_col           -- define row order within the window
  ROWS BETWEEN ... AND ...     -- frame specification (optional)
)

Ranking Functions

sql

-- Compare ROW_NUMBER, RANK, DENSE_RANK, and NTILE side by side
SELECT
  order_id,
  customer_id,
  total,
  ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY total DESC) AS row_num,
  RANK()       OVER (PARTITION BY customer_id ORDER BY total DESC) AS rnk,
  DENSE_RANK() OVER (PARTITION BY customer_id ORDER BY total DESC) AS dense_rnk,
  NTILE(4)     OVER (PARTITION BY customer_id ORDER BY total DESC) AS quartile
FROM orders;

Running Totals and Moving Averages

sql

-- Running total of daily revenue
SELECT
  order_date,
  daily_revenue,
  SUM(daily_revenue) OVER (ORDER BY order_date
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total,
  AVG(daily_revenue) OVER (ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW)         AS moving_avg_7d
FROM daily_revenue_view
ORDER BY order_date;

LAG and LEAD for Period Comparisons

sql

-- Compare this month's revenue to the previous month
SELECT
  month,
  revenue,
  LAG(revenue, 1, 0) OVER (ORDER BY month)  AS prev_month_revenue,
  revenue - LAG(revenue, 1, 0) OVER (ORDER BY month) AS delta
FROM monthly_revenue
ORDER BY month;

Define a WINDOW clause when you reuse the same window specification multiple times in a query — it avoids repetition and makes the intent clearer: WINDOW w AS (PARTITION BY customer_id ORDER BY total DESC) then use OVER w.

Window functions are evaluated after WHERE, GROUP BY, and HAVING, but before ORDER BY and LIMIT. To filter on a window function result, wrap the query in a CTE or subquery.

Ready to test yourself?

Practice PostgreSQL— quiz & coding exercises