SQL Window Functions — Ranking & Analytic

0. The Sample Tables

We use customers (now 7 rows, with a deliberate tie) and a transactions table with dates.

1. What Is a Window Function?

A window function performs a calculation across a set of rows (the "window") while keeping every row in the output. Unlike GROUP BY, it doesn't collapse rows — you get the original detail and the aggregate side by side.

-- Show each customer's balance AND the overall maximum
SELECT customer_name,
       balance,
       MAX(balance) OVER () AS overall_max
FROM customers;

The OVER () clause is what marks a function as a window function. An empty OVER () means "the whole table is the window."

2. ROW_NUMBER, RANK, and DENSE_RANK

The three ranking functions differ only in how they handle ties.

SELECT customer_name,
       balance,
       ROW_NUMBER() OVER (ORDER BY balance DESC) AS row_num,
       RANK()       OVER (ORDER BY balance DESC) AS rank_,
       DENSE_RANK() OVER (ORDER BY balance DESC) AS dense
FROM customers
ORDER BY balance DESC;

3. PARTITION BY — Rank Within Groups

PARTITION BY divides rows into groups and applies the window function within each group. This powers the canonical "top-N per group" pattern.

-- Rank customers by balance WITHIN each city
SELECT city, customer_name, balance,
       RANK() OVER (PARTITION BY city ORDER BY balance DESC) AS city_rank
FROM customers;

The "top-N per group" pattern (memorize this)

-- Top 1 customer (by balance) in each city
SELECT city, customer_name, balance
FROM (
    SELECT city, customer_name, balance,
           ROW_NUMBER() OVER (PARTITION BY city ORDER BY balance DESC) AS rn
    FROM customers
) ranked
WHERE rn = 1;

4. LAG and LEAD — Compare to Neighbors

LAG returns the value from a previous row; LEAD returns the value from the next row.

-- Month-over-month change in transaction volume
WITH monthly AS (
    SELECT DATE_TRUNC('month', txn_date) AS month,
           SUM(amount) AS revenue
    FROM transactions
    GROUP BY DATE_TRUNC('month', txn_date)
)
SELECT month,
       revenue,
       LAG(revenue) OVER (ORDER BY month) AS prev_month,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change
FROM monthly
ORDER BY month;

The first row has no previous month, so its LAG is NULL — which is correct.

5. Running Totals and Percent of Total

Running total (cumulative sum)

SELECT txn_date,
       amount,
       SUM(amount) OVER (
           ORDER BY txn_date
           ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
       ) AS running_total
FROM transactions
ORDER BY txn_date;

The frame clause ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW is what makes it a running total instead of a grand total.

Percent of total (share of a group)

-- Each customer's balance as a share of their city's total
SELECT customer_name, city, balance,
       ROUND(100.0 * balance / SUM(balance) OVER (PARTITION BY city), 1) AS pct_of_city
FROM customers;

6. The "Filter a Window Function" Trap

You cannot reference a window function directly in WHERE, because WHERE runs before window functions are computed.

-- ❌ WRONG: WHERE can't see rn
SELECT customer_name, ROW_NUMBER() OVER (ORDER BY balance DESC) AS rn
FROM customers
WHERE rn = 1;

-- ✅ CORRECT: wrap in a subquery/CTE and filter outside
SELECT * FROM (
    SELECT customer_name, ROW_NUMBER() OVER (ORDER BY balance DESC) AS rn
    FROM customers
) ranked
WHERE rn = 1;