SQL Window functions

TLDR (opens in a new tab): Show other row data within each row

SQL window functions perform calculations across a set of table rows related to the current row, without collapsing the results into groups like aggregate functions.

They allow you to rank, sum, average, or access data from other rows while retaining the original row structure. Window functions are defined using the OVER() clause, which specifies the window or "frame" of rows to consider.

Common window functions include:

• ROW_NUMBER(): Assigns a unique sequential number to each row.
• RANK(): Assigns ranks with gaps for ties.
• DENSE_RANK(): Similar to RANK(), but without gaps.
• LEAD() and LAG(): Access values from subsequent or previous rows.
• SUM(), AVG(), or any other aggregate function: Calculate cumulative totals or averages over the specified window.
• These functions are powerful for analytics, trend analysis, and reporting, enabling complex calculations over partitions of data without altering the overall query results.

Example use cases:

Comparing This Month's Sales to Last Month's

SELECT 
  month, 
  sales, 
  LAG(sales) OVER (ORDER BY month) AS previous_month_sales, 
  sales - LAG(sales) OVER (ORDER BY month) AS sales_difference
FROM 
  monthly_sales;

Identifying Gaps

SELECT 
  order_id, 
  LAG(order_id) OVER (ORDER BY order_id) + 1 AS expected_order_id
FROM 
  orders
WHERE 
  order_id <> LAG(order_id) OVER (ORDER BY order_id) + 1;

Tracking cumulative values over time, such as monthly sales or balances

SELECT 
  month, 
  sales, 
  SUM(sales) OVER (ORDER BY month) AS running_total
FROM 
  monthly_sales;