Price Effective at Order Time
price_history records price changes: a price applies from its effective_from date (inclusive) until the product's next change. For each order line, find the unit price in effect on the order_date, meaning the row with the latest effective_from on or before that date. If the product had no price yet on that date, both unit_price and line_total are NULL.
line_total is unit_price * quantity, ROUND(x, 2).
Return columns order_id, product_id, unit_price, line_total. Order by order_id ascending.
Schema
CREATE TABLE price_history ( product_id INTEGER NOT NULL, price REAL NOT NULL, effective_from TEXT NOT NULL, PRIMARY KEY (product_id, effective_from) ); CREATE TABLE orders ( order_id INTEGER PRIMARY KEY, product_id INTEGER NOT NULL, order_date TEXT NOT NULL, quantity INTEGER NOT NULL );
Sample data
price_history
| product_id | price | effective_from |
|---|---|---|
| 1 | 10 | 2026-01-01 |
| 1 | 12.5 | 2026-03-01 |
| 1 | 11 | 2026-05-15 |
| 2 | 99 | 2026-02-01 |
| 2 | 89 | 2026-04-01 |
orders
| order_id | product_id | order_date | quantity |
|---|---|---|---|
| 1 | 1 | 2026-02-10 | 3 |
| 2 | 1 | 2026-03-01 | 2 |
| 3 | 2 | 2026-03-20 | 1 |
| 4 | 1 | 2026-06-01 | 4 |
| 5 | 2 | 2026-04-01 | 2 |
| 6 | 2 | 2026-01-15 | 1 |
Hint 1
A correlated subquery with ORDER BY effective_from DESC LIMIT 1 picks the latest price on or before the order date.
Hint 2
A price effective on the order date itself applies to that order.
Hint 3
If the subquery finds no row it returns NULL, and NULL * quantity is NULL.
Good practice for: Backend Developer, Data Engineer, Data Analyst, Full Stack Developer
Run checks your query on the sample data (Ctrl + '). Submit also checks hidden databases.