Product Price at a Given Date

Product Price at a Given Date

What is this problem about?

The Product Price at a Given Date SQL problem asks you to find the price of each product on a given date. The most recent price change on or before the date is the current price; if no price change exists before the date, the default price is 10. This SQL problem tests the latest-record-before-a-date pattern. The database interview pattern is demonstrated.

Why is this asked in interviews?

Microsoft, Meta, Amazon, Google, and Bloomberg ask this because it's a "point-in-time snapshot" query — find the most recent record satisfying a date constraint. This is a fundamental analytics pattern in pricing, inventory, and historical data systems.

Algorithmic pattern used

Latest record per product before date + default handling. Use a correlated subquery or window function: SELECT product_id, new_price AS price FROM Products WHERE (product_id, change_date) IN (SELECT product_id, MAX(change_date) FROM Products WHERE change_date <= '2019-08-16' GROUP BY product_id). UNION with default price for products with no changes: SELECT DISTINCT product_id, 10 AS price FROM Products WHERE product_id NOT IN (SELECT product_id FROM Products WHERE change_date <= '2019-08-16').

Example explanation

Products: prod1 changed to 25 on 2019-08-14, prod2 changed to 50 on 2019-08-20. Query date = 2019-08-16.

• prod1: latest change on/before 2019-08-16 = 2019-08-14 → price=25.
• prod2: no change on/before date → default price=10. Result: [(prod1,25),(prod2,10)].

Common mistakes candidates make

• Not handling the default price for products with no changes before the date.
• Using MAX(change_date) without filtering by the query date.
• INNER JOIN instead of UNION for the default case.
• Not selecting DISTINCT product_ids for the default case.

Interview preparation tip

Point-in-time pricing queries use the "latest record before date" pattern: MAX(date) WHERE date <= target_date GROUP BY id. Always handle the "no record before date" case with a UNION and default value. Practice similar problems: "stock price at date," "exchange rate at time," "employee salary history." These appear across fintech, e-commerce, and enterprise analytics SQL interviews.