DuckDB ASOF JOIN: Find the Price at the Time of an Order

An order arrived at 09:15. The price changed at 09:10 and again at 09:25. An equality join finds no price at 09:15. A plain “earlier than” join finds too many. We want the most recent price already in effect.

That is an as-of match: pair each event with the applicable earlier state of the same entity.

Two timelines that do not line up

A price applies from its effective timestamp, including that instant, until the next price for the same SKU. The fixture uses local, timezone-free timestamps under one shared clock. A production system must normalize its time semantics before comparing events.

WREN has three price updates. FIR has a separate history. Order 101 arrives before any WREN price, so we cannot legitimately assign it one from this dataset.

Why an ordinary inequality join returns too much

Look at order 104, placed at 09:30. A regular join on an earlier-or-equal price time keeps all three WREN prices:

SELECT o.id, p.effective_at::VARCHAR AS price_time, p.price
FROM orders o LEFT JOIN prices p
  ON o.sku = p.sku AND o.ordered_at >= p.effective_at
WHERE o.id = 104 ORDER BY p.effective_at;

Every row satisfies the condition. Only the last is the applicable price. If you multiplied the quantity by every matching price and summed, you would invent extra charges.

A regular equality join has the opposite problem: it only matches an order placed exactly when a price was published. An unchanged price remains applicable between updates.

Ask for the closest earlier match

ASOF JOIN combines the equality key with the time inequality. The left side contains events; the right side contains the state changes we want to look backward through.

SELECT o.id, o.sku, p.effective_at::VARCHAR AS price_time, p.price
FROM orders o
ASOF LEFT JOIN prices p
  ON o.sku = p.sku AND o.ordered_at >= p.effective_at
ORDER BY o.id;

The SKU condition keeps the two product histories separate. ordered_at >= effective_at chooses the latest right-hand timestamp that is not after the order. The equality part of that inequality makes the 09:00 price valid for the 09:00 order.

LEFT preserves order 101 with NULL price fields. An inner as-of join would remove an order with no match, which is inconvenient if the task is to audit every order.

The result has at most one matched right-hand row per left-hand row. Do not rely on an arbitrary winner if your price history contains duplicate (sku, effective_at) keys. Resolve those duplicates under a documented rule before joining; the latest-record lesson demonstrates an explicit tie-break.

Calculate with the matched state

Once the applicable price is attached, the amount is quantity times price:

SELECT o.id, o.qty, p.price, o.qty * p.price AS amount
FROM orders o
ASOF LEFT JOIN prices p
  ON o.sku = p.sku AND o.ordered_at >= p.effective_at
ORDER BY o.id;

Order 103 uses 12.00, producing 36.00. Order 104 uses 11.50, producing 23.00. Order 101 stays unpriced. Replacing that missing price with zero would convert “we cannot price it” into “it was free,” which is a different business statement.

SELECT o.id
FROM orders o
ASOF LEFT JOIN prices p
  ON o.sku = p.sku AND o.ordered_at >= p.effective_at
WHERE p.id IS NULL ORDER BY o.id;

Put boundaries around “applicable”

This example assumes a price stays valid until the next update. If prices expire after a fixed interval, add a policy that checks the age of the matched state. An earlier match can be technically correct yet too stale for the business question.

If timestamps come from different time zones, decide whether they represent local wall-clock values or actual instants. Normalize them consistently; changing a display format is not a time-zone conversion.

The same pattern works for exchange rates, sensor readings, and account status at the time of an event. Each needs the same decisions: the equality key, the direction of time, whether an exact-time match counts, what happens before the first state, and how stale a match may be.