Price As-Of Order Time — Solution & ExplanationProblems / Price As-Of Order Time / EditorialApproach

Bounded join. orders ⋈ price_history on product and effective_from <= order_time gathers, for every order, all prices that had taken effect by then.

Rank newest-first per order. ROW_NUMBER() OVER (PARTITION BY order_id ORDER BY effective_from DESC); rn = 1 is the price in force. The partition key matters: per *order*, because the same product resolves to different prices at different times.

Arithmetic. unit_price * quantity, rounded, ordered by order.

Key InsightEffective-dated tables (prices, tax rates, exchange rates, SCD dimensions) record change points, not intervals — so the lookup is inherently an *as-of join*: latest right-row not after the left row's timestamp. The bounded-join + rank-desc + keep-first shape is the portable SQL spelling of it. The equality-join instinct (ON product AND effective_from = order_time) matches almost nothing; the other classic failure is partitioning the rank by product, which crowns one global price and bills every order with it.Hints

price_history has no end dates — 'the price at time T' means the newest price whose effective_from is not after T. That's a bounded inequality join, not an equality join.

After joining all qualifying (older-or-equal) prices to each order, ROW_NUMBER per order_id ordered by effective_from DESC ranks the newest qualifying price 1.

Partition by order_id, not product_id — two orders for the same product at different times need different prices.

Boundary rule: a price effective exactly at order_time applies (<=, not <). The data has an order placed at the very second a price change lands.

Solve Price As-Of Order Time yourself →