SQL Window Functions (OVER, PARTITION BY, LAG/LEAD, Running Totals)

• Window function – computes a value across a set of rows (a "window") related to the current row, defined by an OVER clause, while still returning one output row per input row.
• PARTITION BY – divides rows into independent groups (like a per-account or per-branch grouping) so the window function restarts for each group.
• ORDER BY (inside OVER) – orders rows within each partition, which determines the meaning of "previous" and "next" for offset functions.
• Frame clause (ROWS BETWEEN ...) – controls which neighbouring rows are included in the window, enabling running totals, moving averages, and sliding calculations.
• Key difference from GROUP BY – GROUP BY collapses each group into one summary row; a window function keeps every row and attaches the computed value alongside it.

What are window functions?

A window function performs a calculation across a set of rows that are related to the current row, called the "window." Unlike an aggregate with GROUP BY, a window function does not collapse multiple rows into one. Every input row remains in the output, and the computed value appears as an additional column.

The window is defined by the OVER clause. OVER () with no arguments treats the entire result set as one window. OVER (PARTITION BY account_id ORDER BY txn_date) splits rows into one partition per account and orders each partition by date.

-- Every transaction with a running balance per account
SELECT txn_id, account_id, txn_date, amount,
       SUM(amount) OVER (
           PARTITION BY account_id
           ORDER BY txn_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_balance
FROM transactions
WHERE account_id = 'SAV2001'
ORDER BY txn_date;

txn_id | account_id | txn_date   | amount      | running_balance
-------+------------+------------+-------------+----------------
T001   | SAV2001    | 2024-01-01 |  100000.00  |  100000.00
T002   | SAV2001    | 2024-01-05 |  -25000.00  |   75000.00
T003   | SAV2001    | 2024-01-10 |   50000.00  |  125000.00
T004   | SAV2001    | 2024-01-15 |  -10000.00  |  115000.00

How do window functions differ from GROUP BY?

GROUP BY collapses each group into a single row. You lose the individual transaction details and only get aggregated values. A window function keeps every detail row and attaches the aggregate alongside it.

| Feature | GROUP BY | Window Function |
|---------|----------|----------------|
| Output rows | One per group | One per input row |
| Detail visibility | Lost | Preserved |
| Use case | Summary reports | Per-row analytics |
| Can mix detail + aggregate | No | Yes |

-- GROUP BY: one row per account (detail lost)
SELECT account_id, SUM(amount) AS total
FROM transactions
GROUP BY account_id;

-- Window function: every row preserved with total alongside
SELECT txn_id, account_id, amount,
       SUM(amount) OVER (PARTITION BY account_id) AS account_total
FROM transactions;

How do you use LAG and LEAD?

LAG(column, n) returns the value of column from n rows before the current row within the partition. LEAD(column, n) looks n rows ahead. The first row for LAG and the last row for LEAD return NULL (or a supplied default).

At National Bank of India, Rajesh wants to see how each transaction amount differs from the previous one.

SELECT txn_id, txn_date, amount,
       LAG(amount) OVER (PARTITION BY account_id ORDER BY txn_date) AS prev_amount,
       amount - LAG(amount) OVER (PARTITION BY account_id ORDER BY txn_date) AS change
FROM transactions
WHERE account_id = 'SAV2001';

txn_id | txn_date   | amount     | prev_amount | change
-------+------------+------------+-------------+-----------
T001   | 2024-01-01 | 100000.00  | NULL        | NULL
T002   | 2024-01-05 | -25000.00  | 100000.00   | -125000.00
T003   | 2024-01-10 |  50000.00  | -25000.00   |  75000.00

Using LEAD, the fraud team checks whether the next transaction is suspiciously large:

SELECT txn_id, amount,
       LEAD(amount) OVER (PARTITION BY account_id ORDER BY txn_date) AS next_amount
FROM transactions
WHERE account_id = 'SAV2001';

How do you compute running totals and moving averages?

A running total uses SUM with a cumulative frame. A moving average uses AVG with a sliding frame.

-- Running balance (cumulative sum)
SELECT txn_id, amount,
       SUM(amount) OVER (
           PARTITION BY account_id
           ORDER BY txn_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_balance
FROM transactions;

-- 3-transaction moving average (fraud smoothing)
SELECT txn_id, amount,
       AVG(amount) OVER (
           PARTITION BY account_id
           ORDER BY txn_date
           ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
       ) AS moving_avg_3
FROM transactions;

The frame clause ROWS BETWEEN is what distinguishes a cumulative calculation from a whole-partition one. UNBOUNDED PRECEDING AND CURRENT ROW means everything up to and including this row. 2 PRECEDING AND CURRENT ROW means the current row and the two before it.

What are FIRST_VALUE and LAST_VALUE?

FIRST_VALUE(column) returns the first value in the window frame. LAST_VALUE(column) returns the last value in the frame.

A common gotcha: LAST_VALUE with the default frame (which ends at the current row) returns the current row's value, not the partition's last. To get the true last value, you must specify the full frame.

-- Wrong: LAST_VALUE returns current row's value
SELECT txn_id, amount,
       LAST_VALUE(amount) OVER (
           PARTITION BY account_id ORDER BY txn_date
       ) AS last_amount  -- returns current row, not the partition's last!
FROM transactions;

-- Correct: specify full frame
SELECT txn_id, amount,
       LAST_VALUE(amount) OVER (
           PARTITION BY account_id ORDER BY txn_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS last_amount  -- returns the actual last transaction amount
FROM transactions;

What is the frame clause and how does it work?

The frame clause defines which rows relative to the current row are included in the window calculation. It comes after ORDER BY in the OVER clause.

| Frame specification | Meaning |
|--------------------|---------|
| ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW | All rows from the start to here (running total) |
| ROWS BETWEEN 2 PRECEDING AND CURRENT ROW | Current row and 2 before it (3-row moving window) |
| ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING | Entire partition |
| ROWS BETWEEN CURRENT ROW AND 1 FOLLOWING | Current row and the one after it |

-- Monthly running total of deposits for National Bank of India
SELECT txn_date, amount,
       SUM(amount) OVER (
           ORDER BY txn_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS cumulative_deposits
FROM transactions
WHERE account_id = 'SAV2001' AND amount > 0;

What about performance and indexing?

Window functions require the database to sort rows within each partition. An index on the partition and order columns avoids a full sort.

-- Index for PARTITION BY account_id ORDER BY txn_date
CREATE INDEX idx_txn_account_date
    ON transactions (account_id, txn_date);

For large tables, this index is critical. Without it, the database performs a full table sort for every window function evaluation.

How do you use window functions in Java?

The window function is computed entirely in SQL. Java simply executes the query and processes the result set.

package com.bank.service;

import java.math.BigDecimal;
import java.sql.*;

public class AccountStatementService {

    private final Connection connection;

    public AccountStatementService(Connection connection) {
        this.connection = connection;
    }

    public void printRunningBalance(String accountId) throws SQLException {
        String sql = """
            SELECT txn_id, txn_date, amount,
                   SUM(amount) OVER (
                       PARTITION BY account_id
                       ORDER BY txn_date
                       ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
                   ) AS running_balance
            FROM transactions
            WHERE account_id = ?
            ORDER BY txn_date
            """;
        try (PreparedStatement ps = connection.prepareStatement(sql)) {
            ps.setString(1, accountId);
            try (ResultSet rs = ps.executeQuery()) {
                while (rs.next()) {
                    String txnId = rs.getString("txn_id");
                    BigDecimal amount = rs.getBigDecimal("amount");
                    BigDecimal balance = rs.getBigDecimal("running_balance");
                    System.out.printf("%s | Rs.%s | Balance: Rs.%s%n", txnId, amount, balance);
                }
            }
        }
    }
}

What are common mistakes?

The most common mistakes with window functions are:

1. Forgetting the LAST_VALUE frame gotcha (default frame ends at current row, not at the end of the partition).
2. Using GROUP BY when a window function would preserve the detail rows you need.
3. Trying to use a window function in WHERE instead of wrapping in a subquery.

-- Mistake: GROUP BY loses detail
SELECT account_id, SUM(amount) FROM transactions GROUP BY account_id;
-- You cannot see individual transactions.

-- Correct: window function preserves detail
SELECT txn_id, account_id, amount,
       SUM(amount) OVER (PARTITION BY account_id) AS account_total
FROM transactions;
-- Every transaction visible with the account total alongside.

What are common interview follow-up questions?

• "What is the default frame when ORDER BY is specified?" It is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, which groups rows with the same order value together.
• "Can you use multiple window functions in the same SELECT?" Yes, each can have a different OVER clause.
• "Can you name a window to reuse it?" Yes: WINDOW w AS (PARTITION BY account_id ORDER BY txn_date) then SUM(amount) OVER w.