SQL Window Functions

1. What Is It?

Window functions perform calculations across a set of rows that are related to the current row — without collapsing those rows into a single group. Unlike GROUP BY aggregation, each row in the result set keeps its own identity and gets an additional computed column based on a surrounding "window" of rows.

The name comes from the "window" of rows the function looks at for each output row. That window is defined by the OVER clause.

Window functions solve problems that GROUP BY can't: "rank users by spend", "show each order alongside the user's running total", "compare each day's revenue to yesterday's". Before window functions, these required correlated subqueries or application-side post-processing — both slow and complex.

2. How It Works

The syntax:

Copy
function_name(col) OVER (
    [PARTITION BY partition_cols]
    [ORDER BY order_cols]
    [ROWS/RANGE BETWEEN frame_start AND frame_end]
)

Basic example: rank customers by total spend

Copy
SELECT
    user_id,
    SUM(total_cents)                                    AS total_spent,
    RANK() OVER (ORDER BY SUM(total_cents) DESC)        AS spend_rank,
    ROW_NUMBER() OVER (ORDER BY SUM(total_cents) DESC)  AS row_num
FROM orders
WHERE status = 'paid'
GROUP BY user_id;

Here, GROUP BY collapses orders per user, then the window function ranks across those groups. Both can appear in the same query.

PARTITION BY — restart the window per group

Copy
-- Rank orders by amount within each user's own orders
SELECT
    id,
    user_id,
    total_cents,
    RANK() OVER (PARTITION BY user_id ORDER BY total_cents DESC) AS rank_within_user
FROM orders;
-- Each user's orders are ranked 1, 2, 3... independently

Without PARTITION BY, the window spans the entire result set. With PARTITION BY, the window resets for each partition value.

Running totals and moving aggregates

Copy
-- Running total of revenue per day
SELECT
    placed_at::DATE         AS order_date,
    total_cents,
    SUM(total_cents) OVER (
        ORDER BY placed_at::DATE
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_total
FROM orders
WHERE status = 'paid'
ORDER BY placed_at;

LAG and LEAD — access adjacent rows

Copy
-- Compare each day's revenue to the previous day
SELECT
    order_date,
    daily_revenue,
    LAG(daily_revenue, 1) OVER (ORDER BY order_date)  AS prev_day_revenue,
    daily_revenue - LAG(daily_revenue, 1) OVER (ORDER BY order_date) AS day_over_day_change
FROM daily_revenue_summary
ORDER BY order_date;

LAG(col, n) fetches the value from n rows before the current row in the window order. LEAD(col, n) fetches from n rows ahead.

Common window functions:

3. What SDEs Actually Need to Know

Window functions run after WHERE, GROUP BY, and HAVING but before ORDER BY and LIMIT. You cannot filter by a window function result in a WHERE clause. If you need to filter by a window result, wrap it in a CTE or subquery:

Copy
-- Find each user's top-3 most expensive orders
WITH ranked_orders AS (
    SELECT
        id,
        user_id,
        total_cents,
        ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY total_cents DESC) AS rn
    FROM orders
)
SELECT id, user_id, total_cents
FROM ranked_orders
WHERE rn <= 3;

This "top N per group" pattern — a CTE with ROW_NUMBER() and a WHERE on the rank — is one of the most common window function uses in application code.

ROW_NUMBER vs. RANK vs. DENSE_RANK for ties:

Values: 100, 100, 80, 60
ROW_NUMBER:  1, 2, 3, 4   (arbitrary tiebreak, unique always)
RANK:        1, 1, 3, 4   (tie gets same rank; gap after)
DENSE_RANK:  1, 1, 2, 3   (tie gets same rank; no gap)

Use ROW_NUMBER when you need a unique identifier per row (dedup, pagination). Use RANK or DENSE_RANK when the rank value needs to reflect ties.

LAST_VALUE gotcha: When ORDER BY is present, the default window frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW, not the full partition. Without specifying ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING, LAST_VALUE returns the current row's value for most rows in the partition. Always specify the frame explicitly for LAST_VALUE and FIRST_VALUE.

Performance: Window functions require sorting the partition, which can be expensive on large tables. An index on the PARTITION BY and ORDER BY columns can help significantly.

4. Tradeoffs & Decisions

Window functions vs. self-joins

Before window functions, the "running total" required a correlated subquery:

Copy
-- Old way: correlated subquery for running total
SELECT o1.id, SUM(o2.total_cents)
FROM orders o1
JOIN orders o2 ON o2.placed_at <= o1.placed_at
GROUP BY o1.id;

This is O(n²) — every row scans all prior rows. The window function equivalent is O(n log n) (sort + single pass). Always prefer window functions over self-join aggregations.

Multiple window definitions

If you use the same OVER clause multiple times, define it once with WINDOW:

Copy
SELECT
    user_id,
    total_cents,
    SUM(total_cents) OVER w   AS running_total,
    AVG(total_cents) OVER w   AS running_avg,
    COUNT(*) OVER w           AS order_count
FROM orders
WINDOW w AS (PARTITION BY user_id ORDER BY placed_at
             ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW);

Application-side post-processing vs. window functions

Some teams fetch sorted rows from the DB and compute ranks or deltas in application code. This works until the dataset grows beyond what fits in memory. Moving the computation into SQL keeps it close to the data and allows the database to optimize it.

5. Interview Cheat Sheet

Key sentences:

• "Window functions let you compute values across related rows without collapsing them — unlike GROUP BY, each row keeps its identity."
• "PARTITION BY is to window functions what GROUP BY is to aggregation: it restarts the calculation for each partition."
• "The top-N-per-group pattern uses ROW_NUMBER() with PARTITION BY, then filters in a CTE: WHERE rn <= N."
• "LAG and LEAD let you access adjacent rows in a sorted sequence — useful for period-over-period comparisons."

Common follow-ups:

Q: Why can't you use a window function in a WHERE clause? A: Window functions are evaluated after WHERE, so you can't filter by their result in the same query. You wrap the window function in a subquery or CTE, then filter the outer query.

Q: What's the difference between RANK and DENSE_RANK? A: Both assign the same rank to tied values. RANK leaves a gap after the tie (1, 1, 3), so the next unique rank matches its actual position count. DENSE_RANK never gaps (1, 1, 2), so ranks are always consecutive. Use DENSE_RANK when you want continuous rank numbers (e.g., percentile buckets); use RANK when the position in the sorted list matters.

Q: How do you compute a 7-day moving average? A:

Copy
AVG(daily_revenue) OVER (
    ORDER BY order_date
    ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
)

This looks at the current row plus the 6 rows before it (7 rows total) and returns their average.