ASOF Join

ASOF JOIN in DuneSQL performs nearest-neighbor matching using inequality predicates, making it ideal for temporal lookups like “latest price at trade time” or forward-fill balance snapshots.

Overview

ASOF JOIN in DuneSQL lets you join two relations based on the closest matching row according to an inequality in the ON clause - typically on a timestamp or sequence field.

This is most useful when you need to:

• Get the latest price as of trade time
• Forward-fill sparse balance or config/state tables
• Enrich dense time grids (e.g. hourly/daily) from sparse event data
• Tracking actions of an ENS across addresses over time.
• Tracking actions of EIP-7702 enabled wallets by the contract they authorized to.

Unlike standard joins, ASOF JOIN does not require exact equality — it picks the nearest valid match according to your inequality condition.

ASOF JOIN executes entirely at query time — it does not pre-materialize or store any helper tables. It is a runtime join optimized in the DuneSQL engine.

Syntax

SELECT ...
FROM left_relation
ASOF [ LEFT ] JOIN right_relation
  ON <equality_conjuncts> AND <inequality_conjunct>

Rules

• Exactly one inequality is required, comparing a right-side expression to a left-side expression using <, <=, >, or >=.
• Any number of equality predicates (left.sym = right.sym) are allowed and encouraged.
• ASOF JOIN returns only matched left rows (like INNER JOIN).
• ASOF LEFT JOIN returns all left rows and fills NULL when no neighbor exists.

Typically, expressions compare timestamps: e.g. prices.ts <= trades.ts will pick the latest price at or before the trade.

Example

Dune’s ETH balances over time

See query on dune.com.

select 
    d.timestamp,
    b.balance
from utils.days d
asof join tokens_ethereum.balances b
on b.address = 0x7058decc644317062f90525f4C1489AaF456e6B3 --dune.eth
    and b.token_standard = 'native' -- native ETH
    and b.block_time <= d.timestamp -- ASOF JOIN condition

-- output
| timestamp                | balance               |
| ------------------------ | --------------------- |
| 2020-02-27 00:00         | 0.006315391776612467  |
| 2020-02-28 00:00         | 0.006315391776612467  |

Latest price at trade time

See query on dune.com.

WITH
  trades(ts, sym) AS (VALUES 
    (TIMESTAMP '2024-01-01 09:30:05', 'ABC'),
    (TIMESTAMP '2024-01-01 09:30:07', 'ABC')
  ),
  quotes(ts, sym, price) AS (VALUES 
    (TIMESTAMP '2024-01-01 09:30:00', 'ABC', 100.0),
    (TIMESTAMP '2024-01-01 09:30:06', 'ABC', 101.5)
  )
SELECT trades.ts, quotes.price
FROM trades 
ASOF JOIN quotes ON trades.sym = quotes.sym AND quotes.ts <= trades.ts
ORDER BY 1

-- output
| ts                       | price                 |
| ------------------------ | --------------------- |
| 2024-01-01 09:30:05      | 100.0                 |
| 2024-01-01 09:30:07      | 101.5                 |

Forward-fill with ASOF LEFT JOIN

See query on dune.com.

WITH
  readings(read_time) AS (
    VALUES (TIMESTAMP '2024-01-01 10:00:00'),
           (TIMESTAMP '2024-01-01 10:05:00')
  ),
  calibrations(effective_time, factor) AS (
    VALUES (TIMESTAMP '2024-01-01 10:01:00', 1.02)
  )
SELECT readings.read_time, calibrations.factor
FROM readings ASOF LEFT JOIN calibrations
  ON calibrations.effective_time >= readings.read_time
ORDER BY 1

-- output
| read_time                | factor                |
| ------------------------ | --------------------- |
| 2024-01-01 10:00:00      | 1.02                  |
| 2024-01-01 10:05:00      | NULL                  |

Performance

ASOF JOIN consistently outperforms LEAD() + range joins for large temporal enrichments. Below is a rounded summary from production-scale testing (millions of rows):

ASOF JOIN combines cleaner SQL and runtime performance gains when data is sparse or when “nearest match” logic is required.

When to use ASOF JOIN

✅ Recommended when:

• You need a point-in-time snapshot (latest state, price, config)
• You are forward-filling sparse events into a regular time spine
• Data is sparse or high-cardinality (wallet balances, token prices)

⚠️ Consider alternatives when:

• You just need window aggregates (e.g. moving averages) → use window functions
• You already materialize reusable helper tables with valid ranges → consider pre-filled models

Limitations

• Exactly one inequality is required in the ON clause.
• Joins based on multiple inequalities are not supported — add extra conditions via equality or WHERE.

Best Practices

• Always constrain with equality first (token, address, etc.) to avoid scanning irrelevant rows.
• Use ASOF LEFT when you need to preserve all left rows, even if no match exists.
• Prefer <= or >= for clear open/closed interval intent.
• Document the direction, as inequality can compare from right to left or from left to right (e.g., right.ts <= left.ts or left.ts >= right.ts):
  • <= → “most recent up to this timestamp” (historical lookup)
  • >= → “next upcoming record” (future-effective lookup)

Think in terms of “as of X, what was true?” — that’s the strongest signal that ASOF JOIN is appropriate.