Windows vs Self-Joins

Concepts covered: Self-Join

A self-join pairs each row of a table with other rows of the same table, and for years it was the only way to put a row next to its own neighbours: the previous order, every earlier order, the customer's total. Each of those questions compares a row with other rows that share a key, which is a window partition, and a window answers it without building a single pair.

The same running total, 2 ways

Take 5 orders from 2 customers and compute a running total per customer by date. The inequality self-join matches every order o1 with every order o2 of the same customer dated on or before it, then GROUP BY folds the matches back to one row per order.

SELECT
  o1.order_id,
  o1.customer_id,
  o1.amount,
  SUM(o2.amount) AS running_total
FROM orders o1
JOIN orders o2
  ON o2.customer_id = o1.customer_id
  AND o2.order_date <= o1.order_date
GROUP BY o1.order_id, o1.customer_id, o1.amount
ORDER BY o1.customer_id, o1.order_id

Customer 101 has 3 orders, so the ON clause produces 1 + 2 + 3 = 6 pairs, and customer 102 produces 3 pairs from 2 orders. The join builds 9 intermediate rows from 5 input rows, then hashes them into 5 groups and sums each. For a customer with n orders the pair count is n(n + 1) / 2, which grows with the square of n, and a join that pairs every order with every other order of the same customer grows as n squared.

• 500,500
• 1 pass

The window version reads each customer's rows once in date order, carries one running sum, and writes it next to each row. Run it beside the join's output: for customer 101 the sum folds forward from 40 to 100 to 200, one row at a time, and the play button steps through that fold.

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

Both queries return the same 5 rows. The work differs: the join shuffles both copies of the table on customer_id, materialises every pair, and aggregates; the window shuffles the table once on customer_id, sorts each partition by order_date, and adds as it streams. With thousands of orders per customer the join's intermediate rows run into the millions while the window's input stays the size of the table.

Inequality self-join

• Shuffles both sides on the join key
• Builds n(n + 1) / 2 pairs per key
• Needs a GROUP BY to fold the pairs back
• Intermediate size grows with n squared

Window over the partition

• Shuffles the table once on the partition key
• Sorts n rows per key
• Emits one row per input row
• Intermediate size equals the input

Spotting a self-join that is a window in disguise

The tell is an ON clause that compares a table with itself on an equality key plus an ordering or an aggregate. The equality key becomes PARTITION BY, the comparison on a date or an amount becomes ORDER BY, and the aggregate or the row being picked becomes the window function.

Not every self-join folds this way. A join that pairs rows on a condition with no fixed order, such as matching each refund to any earlier order of the same customer with an equal amount, has no window form: a window sees a row's position in one sorted partition, and 'any earlier order with an equal amount' is a search across the partition rather than a position in it. Those stay as joins, and the inequality join techniques from the joins track apply.

Which of these self-joins has no window function equivalent?

• Pair each order with all earlier orders of the same customer and sum their amounts.
• Pair each order with the single most recent earlier order of the same customer to read its amount.
• Pair each refund with any earlier order of the same customer whose amount equals the refund.
• Pair each order with every order of the same customer to divide its amount by their sum.

About this lesson

This section is part of the Window Functions: Advanced lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.