Pattern
Effective-dated lookup
Find the rate, price or version valid on the date of each fact, from a table that stores changes.
Turn changes into periods with LEAD(valid_from) OVER (PARTITION BY key ORDER BY valid_from) AS valid_to, then join
on the key and fact_date >= valid_from AND (valid_to IS NULL OR fact_date < valid_to). Joining on the key alone
multiplies the facts by the number of versions. Use a LEFT JOIN, and test that no fact is left without a rate.
WITH rates(valid_from, rate) AS (VALUES (DATE '2025-01-01', 0.20), (DATE '2025-07-01', 0.21)),
periods AS (
SELECT valid_from, LEAD(valid_from) OVER (ORDER BY valid_from) AS valid_to, rate FROM rates
),
sales(sold_on, amount) AS (VALUES
(DATE '2025-03-10', 120.00), (DATE '2025-07-01', 121.00), (DATE '2025-09-15', 60.50))
SELECT s.sold_on, s.amount, p.rate
FROM sales s
JOIN periods p ON s.sold_on >= p.valid_from
AND (p.valid_to IS NULL OR s.sold_on < p.valid_to)
ORDER BY s.sold_on;
| sold_on | amount | rate |
|---|---|---|
| 2025-03-10 | 120.00 | 0.20 |
| 2025-07-01 | 121.00 | 0.21 |
| 2025-09-15 | 60.50 | 0.21 |