Find Product Price as of a Target Date in SQL

Question

Given a `products` table (product_id, new_price, change_date), find the price of all distinct products on '2026-08-16'. Assume all products had an initial base price of 10 before any price change.

Accepted Answer

Optimal Solution:
WITH latest_prices AS (
  SELECT 
    product_id,
    new_price,
    ROW_NUMBER() OVER (PARTITION BY product_id ORDER BY change_date DESC) AS rn
  FROM products
  WHERE change_date <= '2026-08-16'
),
all_products AS (
  SELECT DISTINCT product_id FROM products
)
SELECT 
  ap.product_id,
  COALESCE(lp.new_price, 10) AS price
FROM all_products ap
LEFT JOIN latest_prices lp 
  ON ap.product_id = lp.product_id 
 AND lp.rn = 1
ORDER BY ap.product_id ASC;

Technical Explanation:
Filter rows to `change_date <= '2026-08-16'`, rank with `ROW_NUMBER() OVER(PARTITION BY product_id ORDER BY change_date DESC)`, and LEFT JOIN to all distinct products with fallback `COALESCE(..., 10)`.

product_id	new_price	change_date
1	20	2026-08-14
2	50	2026-08-14
1	30	2026-08-15
1	35	2026-08-18
3	30	2026-08-17