Self-joins where a window function would do

Joining a table to itself may read it twice; check with a dry run.

A window function reads it once and is usually faster as well.

Paste a query and the analyzer estimates what it scans and flags the patterns it can detect statically. It runs in your browser.

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. Each side of a self-join may read the referenced columns again.

Why it costs money

A three-way self-join may read the table three times. A dry run shows the bytes the query will bill.
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, a self-join that reads the table twice 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.

See how Finitizer works