Joins, window functions and CTEs for flows

Single-table queries answer "what happened". Flow analysis asks more: who the counterparty was, what the transfer was worth at the time, what the balance was before and after, and whether funds moved on quickly. Those answers need joins, window functions and common table expressions (CTEs), the three SQL tools this lesson covers, together with the mistakes that most often distort their results.

The queries use the generic schema from the lesson The analytics stack and SQL for blockchain tables: token_transfers (one row per fungible token transfer, with amount_raw in base units), tokens (decimals), labels (address, entity, category, source, grade, updated_at, where grade is the A–D link grade, A strongest) and prices (asset, hour, price_usd).

Joins and the fan-out trap

A join combines rows from two tables that match on a key. An inner join (JOIN) keeps only rows that match on both sides; a left join (LEFT JOIN) keeps every row of the left table and fills the right side with NULLs when nothing matches. For enrichment (adding labels or prices to transfers) the left join is almost always right, because an unlabeled or unpriced transfer is still a transfer.

The most damaging join error is fan-out. If the right-hand table has several rows for the same key, each left row is repeated once per match. Label tables are the classic case: one exchange deposit address may carry a label from service tagging and another from the exchange's own disclosure. In the sample data, a 100 USDC deposit to such an address appears twice after a plain join, and a sum over the joined rows reports 200 USDC. Nothing errors; the number is simply wrong.

Two habits prevent this. Before joining, make the right-hand side unique on the join key. After joining, compare row counts with the original table: an enrichment join must never add rows.

Joining transfers with labels

The fix for duplicate labels is to rank them and keep one per address.

-- One label per address (best grade, then most recent)
WITH best_label AS (
  SELECT address, entity, category,
         ROW_NUMBER() OVER (PARTITION BY address
                            ORDER BY grade ASC, updated_at DESC) AS rn  -- A first
  FROM labels
)
SELECT tt.transaction_hash,
       tt.to_address,
       COALESCE(l.entity, 'unlabeled') AS counterparty,
       l.category
FROM token_transfers AS tt
LEFT JOIN best_label AS l
  ON l.address = tt.to_address
 AND l.rn = 1                       -- keep one label per address
WHERE tt.from_address = '0x5e1f…c0de';

The CTE numbers each address's labels from best to worst; the join keeps only number 1. COALESCE replaces a missing label with the word "unlabeled", which reads better in an output than NULL.

Note where l.rn = 1 sits: in the ON clause, not in WHERE. In a left join, a condition on the right-hand table placed in WHERE runs after the join, discards every row whose right side is NULL, and turns the left join back into an inner join. All unlabeled counterparties would vanish from the result, and nothing in the output would warn you.

To label both ends of each transfer, join the same CTE twice under different aliases, for example LEFT JOIN best_label AS lf ON lf.address = tt.from_address AND lf.rn = 1 and LEFT JOIN best_label AS lt ON lt.address = tt.to_address AND lt.rn = 1. Grouping the result by the two entities then turns thousands of address-level transfers into a short table of entity-to-entity flows, which is often the first view a reader of the report needs. Make sure both sides store addresses the same way: a checksummed, mixed-case address in the label table matches nothing in a lowercase transfer table.

Choosing "best grade wins" is itself an analytic decision. Record it, and carry the label's source and grade into the report (see the lesson From address to entity to person: attribution and labeling). If labels change over time, join on the label that was valid at the transfer's time, not today's.

Joining with prices

Valuing a transfer means matching it with a price at the right moment. With an hourly price table, truncate the transfer's timestamp to the hour and join on asset and hour.

-- USD value at the hour of each transfer
SELECT tt.transaction_hash,
       tt.amount_raw / POWER(10, t.decimals)               AS amount,
       p.price_usd,
       tt.amount_raw / POWER(10, t.decimals) * p.price_usd AS value_usd
FROM token_transfers AS tt
JOIN tokens AS t
  ON t.token_address = tt.token_address
LEFT JOIN prices AS p                -- LEFT: keep rows with no price
  ON p.asset = tt.token_address
 AND p.hour  = DATE_TRUNC('hour', tt.block_timestamp)
WHERE tt.from_address = '0x5e1f…c0de';

On the sample, a 500 USDC transfer at a price of 0.9998 is worth 499.90 USD, and 2 WETH at 2,500 is worth 5,000 USD. A transfer of the imitation "USDC" token returns a NULL price and NULL value. That is the correct answer: there is no market for it, and a NULL tells the reader so. Never replace a missing price with zero, and never fall back to the price of another token with the same symbol.

Equality joins need a price row for every hour. When prices are sparse, use the most recent earlier price. DuckDB, ClickHouse and Snowflake offer an ASOF JOIN for exactly this; on other engines, a window function or a range condition does the same job more slowly. The price source matters too: say which venue or index it comes from, and treat prices of thinly traded tokens as estimates that can be manipulated.

Window functions: running balances, first and last seen

A window function computes a value for each row from a set of related rows without collapsing them, as GROUP BY would. The syntax is function() OVER (PARTITION BY … ORDER BY … frame): partition defines the group, order the sequence, frame which rows count.

-- Running balance of one token for one address, event by event
WITH flows AS (
  SELECT block_number, log_index, block_timestamp, amount_raw AS delta
  FROM token_transfers
  WHERE token_address = '0x7a11…0001' AND to_address = '0x5e1f…c0de'
  UNION ALL
  SELECT block_number, log_index, block_timestamp, -amount_raw
  FROM token_transfers
  WHERE token_address = '0x7a11…0001' AND from_address = '0x5e1f…c0de'
)
SELECT block_timestamp,
       delta,
       SUM(delta) OVER (ORDER BY block_number, log_index
                        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS balance_raw,
       MIN(block_timestamp) OVER () AS first_seen,
       MAX(block_timestamp) OVER () AS last_seen
FROM flows
ORDER BY block_number, log_index;

The CTE turns incoming transfers into positive deltas and outgoing ones into negative deltas; UNION ALL stacks them (plain UNION would remove identical rows and lose real transfers). The window sum then adds the deltas in chain order. On the sample, the address receives 2,000 USDC and sends 500, 1,400 and 100, so the balance runs 2,000, then 1,500, then 100, then 0 (in raw units, 2,000,000,000 and so on). MIN and MAX over an empty window OVER () give the first-seen and last-seen times on every row.

Three details matter:

• Order by block number and log index, which together give the exact position of an event in the chain. Timestamps tie within a block.
• Write the frame explicitly. With ORDER BY and no frame, the default in standard SQL is RANGE … CURRENT ROW, which treats rows with equal sort keys as one and gives them the same total.
• Check against state. A balance built from events assumes the token behaves normally. Fee-on-transfer and rebasing tokens break the assumption; compare the final figure with balanceOf at the same block (see the lesson The account model and reconstructing balances).

Other useful window functions are LAG and LEAD (the previous or next row's value, for example the time since the last transfer), ROW_NUMBER (used above for labels) and FIRST_VALUE. BigQuery, Snowflake and DuckDB let you filter on a window result with a QUALIFY clause; elsewhere, wrap the query in a subquery and filter outside.

CTEs for multi-step logic

A common table expression names an intermediate result with WITH name AS (…) so the next step can use it. Investigative questions are naturally multi-step, and CTEs keep each step readable and testable. Take a typical hypothesis: which addresses received a token from a labeled exploit address and forwarded at least 90% of it within 24 hours?

WITH hop1 AS (            -- step 1: received from a labeled exploit address
  SELECT tt.to_address AS addr, SUM(tt.amount_raw) AS amt_in,
         MIN(tt.block_timestamp) AS first_in
  FROM token_transfers AS tt
  WHERE tt.token_address = '0x7a11…0001'
    AND tt.from_address IN (SELECT address FROM labels WHERE category = 'exploit')
  GROUP BY tt.to_address
),
fwd AS (                  -- step 2: amount sent on within 24 hours of first receipt
  SELECT h.addr, SUM(tt.amount_raw) AS amt_out
  FROM hop1 AS h
  JOIN token_transfers AS tt
    ON tt.from_address = h.addr AND tt.token_address = '0x7a11…0001'
   AND tt.block_timestamp >= h.first_in
   AND tt.block_timestamp <  h.first_in + INTERVAL '24' HOUR
  GROUP BY h.addr
)
SELECT h.addr, h.first_in, h.amt_in, f.amt_out
FROM hop1 AS h JOIN fwd AS f ON f.addr = h.addr
WHERE f.amt_out >= 0.9 * h.amt_in;      -- forwarded at least 90%

Step 1 finds first-hop recipients. It uses IN (subquery) rather than a join to labels, so an exploit address with two labels cannot double the amounts. Step 2 sums what each recipient sent out of the same token in the 24 hours after its first receipt. The final step keeps recipients that forwarded at least 90%. You can run each CTE on its own to check its row count before moving on.

The interval syntax varies: INTERVAL '24' HOUR is standard SQL and works in PostgreSQL, DuckDB and Trino; BigQuery uses TIMESTAMP_ADD(first_in, INTERVAL 24 HOUR). Treat the result as a lead, not a conclusion: the outflow may include funds the address held before, which is exactly the tracing-method question discussed in the lesson Tracing methods and their consequences. Multi-hop tracing can be written with WITH RECURSIVE, but the number of paths grows quickly with each hop, so cap the depth or export the edges to a graph tool.

Exam focus: inner vs left joins and why enrichment joins should be left joins; join fan-out from duplicate labels and how to detect it with row counts; keeping one label per address with ROW_NUMBER; why a right-table condition in WHERE turns a left join into an inner join; joining prices by truncated hour, ASOF joins, and leaving missing prices NULL; running balances with SUM OVER, UNION ALL of signed deltas, ordering by block number and log index, and explicit ROWS frames; first and last seen with MIN and MAX windows, LAG and QUALIFY; structuring multi-step hypotheses with CTEs and semi-joins.