Price validity ranges

price_changes is a change log: one row each time a product’s list price changed, effective from changed_at. To ask “what was the price on a given day?” you want each row to carry the range it was valid for. That’s a slowly changing dimension, type 2.

Return one row per row in price_changes with:

column	type	meaning
product_id	integer	the product
price	decimal	the list price
valid_from	date	the day this price took effect (changed_at)
valid_to	date	the day the next price for the same product took effect; NULL for the current price

Ranges are half-open: a price is valid on valid_from and every day up to, but not including, valid_to. So one row’s valid_to equals the next row’s valid_from, with no gap and no overlap.

Rows may have been inserted out of date order. Don’t rely on the table’s physical order.

Row order doesn’t matter.

Tables: price_changes.

Approach

Look one row ahead within each product’s history to find where each price stops.

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;

Why it works

A change log stores events: “from this day, the price is X”. A type 2 dimension stores states: “from this day until that day, the price was X”. The end of one state is the start of the next, so LEAD converts one form to the other.

Half-open ranges (valid_from inclusive, valid_to exclusive) are the convention worth keeping. Ranges tile with no gaps and no double-counted days, and a point lookup is always d >= valid_from AND (valid_to IS NULL OR d < valid_to). Closed ranges with valid_to = next - 1 break as soon as the grain changes from days to timestamps.

NULL for the open end is one convention. Some teams use a far-future date like 9999-12-31 so the lookup doesn’t need the IS NULL branch, and add an is_current flag for the latest row.

Under the hood

DuckDB partitions by product_id and sorts each partition by changed_at. That sort is why the physical insert order doesn’t matter. LEAD then reads the next row in the sorted partition, a single pass with no lookups. 98 rows across 40 partitions is trivial here, but the same plan works on a billion-row history.