SQL · medium · Asked at Airbnb
Asked in Data Engineer interviews, in the SQL round.
A range join, not an equality join: ON o.customer_id = d.customer_id AND o.order_ts >= d.valid_from AND o.order_ts < d.valid_to. The half-open interval is what stops an order matching two versions on a boundary. Joining on the customer key alone, or filtering d.is_current = true, silently reports today's attributes for historical orders — the classic SCD mistake, and the reason last year's revenue-by-segment number quietly changes.
A range join. The customer key alone matches every version of the customer; the timestamp picks the one that was current.
SELECT o.order_id, o.order_ts, d.segment, d.region
FROM fact_orders o
JOIN dim_customer d
ON d.customer_id = o.customer_id
AND o.order_ts >= d.valid_from
AND o.order_ts < d.valid_to;The half-open interval, >= on the start and < on the end, is the whole answer. If both bounds were inclusive, an order placed at exactly the moment a version changed would match two versions and the fact table would fan out. The current version usually has valid_to set to a far-future date rather than NULL so the comparison needs no COALESCE; if it is NULL, write AND (o.order_ts < d.valid_to OR d.valid_to IS NULL).
Filtering d.is_current = true instead is the classic mistake: it reports today's segment for every historical order, so last year's revenue by segment silently changes whenever a customer moves segment.
MAX(valid_from), which picks the newest version rather than the one current at order time.