The Query That Made Me Stop Writing Self-Joins

The query was four hundred lines long. It had three nested subqueries, two of which selected from the same table with slightly different date filters, and a self-join that existed purely so one row could see the row before it. It took ninety seconds to run and nobody on the team could explain all of it, including the person who wrote it — who was me, eight months earlier.

The report it produced was simple: for each customer, show their orders, a running total of what they'd spent, and how each order compared to their previous one. That's it. Three columns of context.

The rewrite that replaced it was thirty lines. It ran in under two seconds. The difference wasn't cleverness — it was that I'd finally understood what OVER() does.

A window function lets a row see its neighbors without giving up its own identity.

That sentence sounds like nothing until it clicks, and then a whole category of ugly SQL disappears from your life. Let's make it click.

The problem GROUP BY can't solve

GROUP BY collapses. You hand it a thousand order rows and ask for a total per customer, and you get back one row per customer. The individual orders are gone. That's usually what you want — until it isn't.

-- Aggregate: 1 row per customer, individual orders destroyed
SELECT customer_id, SUM(amount) AS total_spent
FROM orders
GROUP BY customer_id;

Now suppose you want each order and that customer's total on the same line. With only GROUP BY in your toolkit, you write a subquery or a join back to an aggregated derived table. It works. It also means scanning the table twice, and it gets uglier with each additional piece of context you need.

Window functions keep every row and add the aggregate alongside it:

SELECT
    order_id,
    customer_id,
    amount,
    SUM(amount) OVER (PARTITION BY customer_id) AS customer_total
FROM orders;

Same thousand rows out. Each one now knows its customer's total. PARTITION BY is GROUP BY that doesn't delete anything — it slices the result set into groups, computes within each slice, and hands the answer back to every row in that slice.

That's the whole core concept. Everything else is variations on it.

Running totals: adding ORDER BY inside the window

Add an ORDER BY inside the OVER() clause and the window stops meaning "all rows in this partition" and starts meaning "all rows in this partition up to and including this one." That gives you running totals for free:

SELECT
    order_id,
    customer_id,
    order_date,
    amount,
    SUM(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
    ) AS running_total
FROM orders;

Notice the running total resets when the customer changes. That's PARTITION BY doing its job — each customer gets an independent window.

This is the single most common thing people build ugly self-joins for, and it's one clause.

Comparing a row to its neighbors: LAG and LEAD

LAG() reaches backward; LEAD() reaches forward. Both take an optional offset and a default value for when there's no neighbor to reach.

SELECT
    customer_id,
    order_date,
    amount,
    LAG(amount) OVER (
        PARTITION BY customer_id ORDER BY order_date
    ) AS previous_amount,
    amount - LAG(amount, 1, 0) OVER (
        PARTITION BY customer_id ORDER BY order_date
    ) AS change_vs_previous
FROM orders;

That third argument to LAG(amount, 1, 0) is the default — without it, the first order per customer gets NULL and your subtraction silently produces NULL too. This is the most common bug in beginner window-function code: forgetting that the edge rows have no neighbor.

The classic use is month-over-month change:

WITH monthly AS (
    SELECT
        DATE_TRUNC('month', order_date) AS month,
        SUM(amount) AS revenue
    FROM orders
    GROUP BY 1
)
SELECT
    month,
    revenue,
    LAG(revenue) OVER (ORDER BY month) AS prev_month,
    ROUND(
        100.0 * (revenue - LAG(revenue) OVER (ORDER BY month))
        / NULLIF(LAG(revenue) OVER (ORDER BY month), 0),
        1
    ) AS pct_change
FROM monthly
ORDER BY month;

Note the NULLIF(..., 0) guarding the division. If last month's revenue was zero, you want a NULL in that cell, not a divide-by-zero error that kills the whole report at 6 a.m.

Also note that aggregation and windowing compose cleanly: GROUP BY in the CTE, window function on top of the grouped result. You don't have to choose between them — they operate at different stages.

Ranking, and the one that actually matters

Three functions look nearly identical and behave differently on ties:

• ROW_NUMBER() — always distinct: 1, 2, 3, 4. Ties broken arbitrarily unless you add a tiebreaker.
• RANK() — ties share a rank and leave a gap: 1, 2, 2, 4.
• DENSE_RANK() — ties share a rank, no gap: 1, 2, 2, 3.

Pick by what the number means. Leaderboard where two people genuinely tied for second? RANK(). "Top 3 distinct price points"? DENSE_RANK(). Need exactly one row per group, no exceptions? ROW_NUMBER().

That last case is worth its own section, because it's the trick that pays for learning all of this. Deduplication — you have a table with accidental duplicates and you want to keep the newest record per key:

WITH ranked AS (
    SELECT
        *,
        ROW_NUMBER() OVER (
            PARTITION BY email
            ORDER BY updated_at DESC, id DESC
        ) AS rn
    FROM customers
)
SELECT * FROM ranked WHERE rn = 1;

Read it out loud: group the rows by email, sort each group newest-first, number them, keep number one. The id DESC is a deterministic tiebreaker so two rows with identical timestamps don't shuffle between runs.

The same pattern gets you "most recent order per customer," "first touch per session," "top 3 products per category" (WHERE rn <= 3). Once you see it, you see it everywhere.

Frames: the part that surprises people

By the time you're writing SUM(...) OVER (ORDER BY ...), you've been using a frame without knowing it. The default frame when you include ORDER BY is:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

Which is why you got a running total rather than a grand total. You can state the frame explicitly to get other shapes — a 3-row moving average, for example:

SELECT
    order_date,
    amount,
    AVG(amount) OVER (
        ORDER BY order_date
        ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
    ) AS moving_avg_3
FROM orders;

There's a real trap here: ROWS counts physical rows, RANGE counts logical values. With RANGE and ties in your ORDER BY column, all tied rows fall inside the frame together — so a "running total" over a date column with multiple orders per day will show every row for that day carrying the full day's total. If that's not what you meant, use ROWS. When in doubt, ROWS is the more predictable default.

Where the WHERE clause bites you

You cannot filter on a window function in WHERE:

-- This fails. Every time. In every database.
SELECT customer_id, ROW_NUMBER() OVER (ORDER BY amount DESC) AS rn
FROM orders
WHERE rn = 1;

The reason is execution order: WHERE runs before window functions are computed, so rn doesn't exist yet. The fix is to wrap it in a CTE or subquery and filter outside, which is exactly what the dedup example above does. Once you internalize the ordering — FROM → WHERE → GROUP BY → window functions → SELECT → ORDER BY — a lot of confusing error messages become obvious.

One more: window functions don't reduce row count, so they can't replace GROUP BY when you actually want fewer rows. If your report should have one line per customer, GROUP BY is still the right tool. Window functions are for when you want the aggregate and the detail.

Wrapping up

Most of the ugly SQL in a mature codebase is a row trying to find out about other rows. Self-joins to get the previous value, subqueries to get a group total, a temp table to number things. Window functions were built for precisely that, and they're supported in PostgreSQL, MySQL 8, SQL Server, Oracle, SQLite, BigQuery, Snowflake — essentially everywhere you're likely to be working.

If you only remember four things: PARTITION BY is GROUP BY that keeps your rows. Adding ORDER BY inside OVER() turns a total into a running total. ROW_NUMBER() plus a CTE plus WHERE rn = 1 solves an enormous number of "get the latest one per group" problems. And you can't filter on a window function in WHERE — wrap it first.

Go find the longest query in your project. There's a decent chance a third of it is a self-join that wants to be an OVER() clause, and the afternoon you spend rewriting it will be the afternoon SQL starts feeling less like fighting and more like describing.