Interview Question Bank · Range join · half-open intervals · NULL valid_to
9. Point-in-time join to an SCD2 dimension
HardData engineeringQuestion #249
Concept: Range join · half-open intervals · NULL valid_toshow
A type 2 slowly changing dimension keeps one row per version, valid from valid_from (inclusive) up to valid_to (exclusive), with valid_to NULL on the current row. To attach the version that was true when a fact happened, join on the business key and fact_time >= valid_from AND (valid_to IS NULL OR fact_time < valid_to). BETWEEN includes both ends, so a fact at the exact switch moment would match two versions.
Example (different data):
SELECT s.sale_id, p.list_price FROM sales s LEFT JOIN price_versions p ON p.sku = s.sku AND s.sold_at >= p.valid_from AND (p.valid_to IS NULL OR s.sold_at < p.valid_to);
Your task
Fernhill keeps customer tier history in de_dim_customer_scd. Finance wants each order in de_fact_orders tagged with the tier and region the customer had when they ordered, not the tier they have today.
Return every order with order_id, customer_id, ordered_at, and the customer_sk, tier and region of the customer version valid at ordered_at. If no version covers the order (the customer's first dimension row was created later), return customer_sk and region as NULL and tier as 'unknown'. Sort by order_id.
Starting Postgres in your browser…
Checking your access…