Articles
SQL ASOF Join Interview Questions: Match Events to the Latest Valid State
Practice SQL ASOF joins with deterministic ties, unmatched events, stale states, and knowledge-time cutoffs using tested temporal queries.
An event happens at 10:03. The state table contains updates at 10:00 and 10:05. An equality join finds neither; a careless nearest-time join might use the future update. An ASOF problem asks which state was applicable at the event's time, usually by finding the latest eligible record at or before it.
A strong answer defines eligibility, direction, ties, and unmatched rows before choosing syntax. This guide uses an original price-history exercise, compares a native DuckDB ASOF join with a lateral lookup, and explains where “latest” stops being equivalent to “valid.” Official database documentation supports engine behavior. The examples and interview recommendations are editorial reasoning, not employer-specific questions or candidate reports.
For related matching mistakes, practice Find gaps between intervals and compare its interval reasoning with the predecessor lookup below.
Start with the matching contract
Assume events(event_id, symbol, event_time) and states(state_id, symbol, effective_at, revision, price). Event IDs and state IDs are unique; symbols and timestamps are non-NULL. All timestamps represent the same time basis. A higher revision supersedes a lower revision for the same symbol and effective time; state ID breaks any remaining tie by an explicitly chosen rule.
The requested output contains exactly one row per event, including events with no eligible state. A state qualifies when its symbol matches and effective_at <= event_time. Among qualifying states, choose the largest effective time, then the highest revision, then the highest state ID. Additional validity conditions will be introduced separately.
Those details prevent several different questions from being conflated. A backward match uses a prior state; a forward match asks for the next state. A nearest match minimizes distance and may look into the future. A strict backward match excludes an update at the exact event timestamp. None is universally correct without a business contract.
Before coding, ask whether the state describes a value that persists until replaced, or an observation that becomes stale. The first interpretation supports ordinary carry-forward behavior. The second needs a maximum age or an explicit validity period.
Work through a small adversarial history
All times below are on the same date. State history is:
| State ID | Symbol | Effective time | Revision | Price |
|---|---|---|---|---|
| 1 | A | 10:00 | 1 | 100 |
| 2 | A | 10:05 | 1 | 110 |
| 3 | A | 10:05 | 2 | 111 |
| 4 | B | 10:01 | 1 | 200 |
Events and expected answers are:
| Event ID | Symbol | Event time | Selected state | Explanation |
|---|---|---|---|---|
| 1 | A | 09:59 | NULL | No prior state |
| 2 | A | 10:03 | 1 | State 2 is in the future |
| 3 | A | 10:05 | 3 | Equality is allowed; revision 2 wins |
| 4 | B | 10:03 | 4 | Symbol is part of the match |
| 5 | C | 10:03 | NULL | This symbol has no history |
The answer has five rows. It does not multiply event 3 into two rows, drop events 1 and 5, or borrow an A price for B. Write these expected outputs before running SQL. Each row tests a separate condition rather than repeating the happy path.
Make native ASOF input deterministic
Official DuckDB behavior: its ASOF join returns at most one right-hand match per left row. An ASOF LEFT JOIN preserves unmatched left rows with NULL right-hand values; an inner ASOF join can remove them. The DuckDB ASOF guide documents both forms.
For this fixture, first resolve duplicate effective times according to our revision rule. Then perform the native join:
WITH ranked_states AS (
SELECT s.*,
ROW_NUMBER() OVER (
PARTITION BY symbol, effective_at
ORDER BY revision DESC, state_id DESC
) AS rn
FROM states AS s
), canonical_states AS (
SELECT state_id, symbol, effective_at, price
FROM ranked_states
WHERE rn = 1
)
SELECT e.event_id, e.symbol, e.event_time,
s.state_id, s.effective_at, s.price
FROM events AS e
ASOF LEFT JOIN canonical_states AS s
ON e.symbol = s.symbol
AND e.event_time >= s.effective_at
ORDER BY e.event_id;
The equality predicate keeps each symbol's history separate. The inequality defines the backward direction and includes exact timestamps. The canonicalization step means the nearest qualifying effective time identifies one state under our policy.
Do not use an arbitrary surrogate ID as a revision policy without explaining why it represents the desired precedence. Here, descending state ID is a declared final tie-breaker, not evidence that larger IDs always mean newer business truth. In production, unresolved conflicting revisions may deserve rejection instead of automatic selection.
Use a lateral lookup when the dialect supports it
The same logical answer can be expressed as a per-event ordered lookup:
SELECT e.event_id, e.symbol, e.event_time,
s.state_id, s.effective_at, s.price
FROM events AS e
LEFT JOIN LATERAL (
SELECT h.state_id, h.effective_at, h.price
FROM states AS h
WHERE h.symbol = e.symbol
AND h.effective_at <= e.event_time
ORDER BY h.effective_at DESC,
h.revision DESC,
h.state_id DESC
LIMIT 1
) AS s ON TRUE
ORDER BY e.event_id;
Official PostgreSQL semantics: a LATERAL subquery can reference preceding FROM items. A left join to that subquery can retain the source row even when the lookup produces no result. See table expressions and LATERAL.
The ordering contains the full selection rule; LIMIT 1 implements one chosen state rather than merely hiding duplicates. Without a deterministic order, limiting output would conceal ambiguity instead of resolving it. Selecting MAX(price) alongside MAX(effective_at) is also wrong: the largest price need not belong to the latest state.
Both complete queries here were executed in DuckDB 1.5.2 and returned the five expected event rows. The lateral syntax follows the documented PostgreSQL pattern, but this article does not claim a PostgreSQL runtime test or benchmark.
Know the Snowflake syntax difference
Official Snowflake behavior: the native form uses ASOF JOIN with a separate MATCH_CONDITION, and unmatched left rows receive NULL right-hand values. This differs from DuckDB's need to specify the outer form. Snowflake also documents that tied right-hand matches can produce nondeterministic results. See its ASOF JOIN reference.
For the already canonicalized relation above, the matching portion becomes ASOF JOIN canonical_states s MATCH_CONDITION(e.event_time >= s.effective_at) ON e.symbol = s.symbol. This is a dialect translation, not a third query claimed to have been executed here.
In an interview, name the engine before writing native syntax. Explaining the contract and providing a valid ordered lookup is preferable to confidently combining clauses from two databases. Keep the tie policy even when the native operator guarantees only one returned row: one result is not automatically a reproducible result.
When the latest state is no longer valid
Suppose the selected observation is 45 minutes old but the business accepts only observations at most ten minutes old. A maximum-age rule can mark the matched row stale. Preserve the event and return a missing or stale status if that is the requested output; filtering the entire row away would change the population.
For a pure age threshold measured from effective time, if the latest prior observation is too old, every earlier observation is older still. There is no useful fallback to an earlier record. That argument explains why finding the predecessor and then checking age is logically sound for this specific rule.
Explicit expiration can behave differently. Imagine state X effective at 10:00 and valid until 12:00, plus state Y effective at 10:05 and valid until 10:06. At 10:07, a plain predecessor lookup selects Y, which is expired. If the business permits X to remain valid independently, selecting among currently valid states would return X. Selecting first and then rejecting Y returns no state. Those are different models.
If newer states supersede older states permanently, falling back to X may be wrong. Clarify the model, then place valid_to eligibility inside the candidate selection when appropriate. For half-open validity intervals, require effective_at <= event_time and either an unbounded end or event_time < valid_to. Do not assume a native ASOF operator alone enforces every validity constraint.
Event time and knowledge time can disagree
A correction effective at 10:00 may arrive at 11:00. For an event at 10:03, a retrospective report using today's corrected history may select it. A replay of what the system actually knew at 10:03 must not.
That distinction needs another timestamp, such as recorded_at, and a clearly defined knowledge cutoff. A point-in-time reconstruction might require both an effective-time condition and a recorded-time condition. The lookup then selects from records that were known by the cutoff, not from all revisions now stored in the table.
This also changes canonicalization. Choosing the globally highest revision before applying the knowledge cutoff can erase the older revision that was actually available then. Apply knowledge eligibility before ranking revisions for that reconstruction, or use a data model designed to represent both timelines.
This is editorial design reasoning, not a claim that every ASOF implementation automatically handles bitemporal data. The database can evaluate a predicate; it cannot infer which historical question the business intended.
Avoid three plausible but incorrect rewrites
An ordinary inequality join matches every prior state. For event 3, that includes states 1, 2, and 3. If you use that approach, rank candidates within the unique event ID and select the intended row. Partitioning only by symbol would collapse multiple events into one result.
An equality join to a daily or minute-truncated timestamp changes the matching problem. It can miss the prior state across a bucket boundary and can introduce multiple candidates within a bucket. Bucketing is an approximation only when the task explicitly permits it.
A global maximum state time ignores each event's timestamp. It may assign a later state to an earlier event, creating future leakage. Even a correct symbol grouping does not fix the missing per-event eligibility test.
For each rewrite, explain which fixture row exposes the error. A concrete counterexample communicates more than calling a query “unsafe.”
Test correctness and then inspect performance
Useful invariants include output cardinality equal to event cardinality for the chosen left-preserving contract, matching symbols for every non-NULL state, no selected effective time after its event, and no better eligible candidate under the complete ordering rule.
The local verification for this article checks native and lateral outputs against an independent Python predecessor calculation, including exact matches, missing histories, ties, and reordered input. It also changes the comparison from inclusive to strict to confirm that event 3 then selects state 1. A future-state insertion should not change earlier answers.
Performance depends on engine and workload. An ordered index supporting symbol and effective time can help some lookup plans; a native operator may use a different strategy for large analytical joins. Inspect actual plans and representative data. Do not promise that lateral syntax always means one efficient index seek per event or that ASOF removes all sorting costs.
Keep both event time and selected state time in diagnostic output. Their difference makes staleness visible and helps distinguish a correct old match from a missing match or a timezone error.
Practice the reasoning around the operator
| Question | Relevant skill |
|---|---|
| Find gaps between intervals | Define temporal boundaries explicitly |
| Solve window-function SQL without joins | Explain partitioning and deterministic ordering |
| Query conversion and retention with SQL windows | Preserve event grain through time-based matching |
| Reason About Composite Join Keys and Predicate Placement | Keep symbol and tenant boundaries intact |
| Debug row loss after SQL joins | Preserve events without eligible states |
Use Find gaps between intervals to practice boundary explanations, then narrate why this fixture's event 3 selects revision 2 while event 1 remains in the result with NULL state fields.