Self-joins where a window function would do

Joining a table to itself reads it twice. A window function reads it once and is usually faster as well.

What it is

Comparing each row to a previous row, a running total, or a per-group rank is often written as a self-join. Every self-join adds another full read of the referenced columns.

Why it costs money

Bytes billed scale with the number of table references, so a three-way self-join bills three scans.
Self-joins also shuffle far more data than the equivalent window function, so they burn slot time as well as bytes.
On skewed keys they can explode into far more output rows than intended.

Worked example

Expensive

SELECT a.user_id, a.event_time, MIN(b.event_time) AS next_event
FROM `analytics.events` a
JOIN `analytics.events` b
  ON a.user_id = b.user_id AND b.event_time > a.event_time
GROUP BY a.user_id, a.event_time

Cheaper

SELECT user_id, event_time,
       LEAD(event_time) OVER (PARTITION BY user_id ORDER BY event_time) AS next_event
FROM `analytics.events`

The arithmetic. Against a 200 GiB column set, the self-join bills ~400 GiB ($2.44); the window function bills ~200 GiB ($1.22), and typically finishes several times faster.

How to fix it

1Reach for LEAD, LAG, ROW_NUMBER, RANK, and SUM(...) OVER (...) before writing a self-join.
2Where a self-join is genuinely required, filter both sides as narrowly as possible first.

The analyzer detects this one. Paste your query into the BigQuery Query Cost Analyzer and it will point at the exact line.

Fixing one query is satisfying. Fixing the pattern is the win.

Finitizer finds every instance of this across your BigQuery job history, ranks them by what they actually cost, and keeps checking after you have fixed them.

Get a free savings analysis See how Finitizer works