List price at order time

Finance suspects that checkout charged the catalogue’s current price (products.price) instead of the price in effect when the order was placed. To check, they need every order line next to the list price that was valid on the order date.

The price history is in price_changes: one row per change, effective from changed_at (inclusive) until the product’s next change. A change on the order date itself applies to that order.

Return one row per row in order_items with:

column	type	meaning
order_id	integer	the order
product_id	integer	the product
order_date	date	from orders
list_price	decimal	the list price in effect on order_date
unit_price	decimal	what the line was charged
price_diff	decimal	unit_price - list_price, rounded to 2 decimals

Edge cases:

• If no price was in effect yet on the order date (the order is older than the product’s first change, or the product has no history at all), keep the line with list_price and price_diff as NULL.
• Price changes dated after the order don’t affect it.

Row order doesn’t matter.

Tables: order_items, orders, price_changes.

Approach

Turn the change log into validity ranges, then join each order line to the range that contains its order date.

WITH price_ranges AS (
  SELECT product_id,
         price,
         changed_at AS valid_from,
         LEAD(changed_at) OVER (PARTITION BY product_id ORDER BY changed_at) AS valid_to
  FROM price_changes
)
SELECT oi.order_id,
       oi.product_id,
       o.order_date,
       pr.price                         AS list_price,
       oi.unit_price,
       ROUND(oi.unit_price - pr.price, 2) AS price_diff
FROM order_items oi
JOIN orders o ON o.order_id = oi.order_id
LEFT JOIN price_ranges pr
  ON  pr.product_id = oi.product_id
  AND o.order_date >= pr.valid_from
  AND (pr.valid_to IS NULL OR o.order_date < pr.valid_to);

Why it works

This is a point-in-time join: the fact (an order line) is matched to the version of the dimension (the price) that was true when the fact happened. Because the ranges are half-open and tile with no gaps or overlaps, each order date falls inside exactly one range, or none if it’s older than the first change. So the join never duplicates a line.

The >= on valid_from makes a same-day change apply to that day’s orders. The < on valid_to stops the old price on the day the new one starts. LEFT JOIN keeps lines with no price in effect, and arithmetic with NULL gives a NULL price_diff.

Run it and look at price_diff. About 1,500 of the 2,423 lines were charged more than the list price in effect, and most of those were charged exactly today’s catalogue price. Joining to products.price would have hidden that.

DuckDB’s ASOF JOIN does the same lookup directly: for each order line it finds the latest changed_at that is <= the order date, per product. It’s shorter, but it’s not portable, and the range table is useful on its own.

Under the hood

With the range join, DuckDB hashes on product_id and then checks the date inequalities for each candidate pair. Each product has at most four ranges, so that’s a handful of comparisons per line. ASOF JOIN instead sorts both sides by (product_id, date) and merges them in one pass, keeping only the most recent price seen for each product. That’s the better plan when a dimension has many versions per key.