Mastering SQL Window Functions

In data analytics, summarizing trends and tracking sequential metrics is a daily necessity. Whether calculating rolling averages, identifying consecutive logon streaks, or finding month-over-month growth, standard GROUP BY aggregates are often insufficient.

SQL Window Functions solve these challenges by performing calculations across a set of table rows related to the current row, without collapsing them into a single summary line.

Here is a practical guide to mastering the most common window functions.

1. The Syntax: OVER and PARTITION BY

Every window function relies on the OVER clause, which defines the “window” of data.

1
SELECT
2
  employee_id,
3
  department,
4
  salary,
5
  AVG(salary) OVER(PARTITION BY department) AS avg_dept_salary
6
FROM employees;

• PARTITION BY: Divides the rows into groups (e.g. by department) to calculate the function separately.
• ORDER BY: Orders the rows within each partition (important for rankings or chronological offsets).

2. Row Rankings: ROW_NUMBER vs. DENSE_RANK

Rank functions allow you to index rows within a group. Knowing the difference between them is crucial to avoiding analytical discrepancies.

1
SELECT
2
  product_id,
3
  sales_amount,
4
  DENSE_RANK() OVER(ORDER BY sales_amount DESC) AS sales_rank
5
FROM product_sales;

3. Offsets: LAG and LEAD

LAG fetches data from a previous row in the partition, while LEAD fetches data from a subsequent row. These are essential for tracking chronological changes.

1
-- Calculating Month-over-Month Sales Growth
2
WITH monthly_revenue AS (
3
  SELECT
4
    DATE_TRUNC('month', order_date) AS order_month,
5
    SUM(total_amount) AS revenue
6
  FROM orders
7
  GROUP BY 1
8
)
9
SELECT
10
  order_month,
11
  revenue,
12
  LAG(revenue, 1) OVER (ORDER BY order_month) AS prev_month_revenue,
13
  revenue - LAG(revenue, 1) OVER (ORDER BY order_month) AS mom_growth
14
FROM monthly_revenue;

4. Rolling Sums and Cumulative Moving Averages

By extending the ORDER BY clause within OVER, you instruct SQL to evaluate values cumulatively from the start of the partition up to the current row:

1
SELECT
2
  transaction_date,
3
  amount,
4
  SUM(amount) OVER (ORDER BY transaction_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_balance
5
FROM bank_transactions;

Window functions allow you to compute complex analytics directly within the database engine, minimizing data transfer overhead and speeding up report generation.