Product Price at a Given Date
Task: Find the prices of all products on 2019-08-16. Assume the price of all products before any change is 10.
Table: Products
+-------------+------+
| Column Name | Type |
+-------------+------+
| product_id | INT |
| new_price | INT |
| change_date | date |
+-------------+------+Input: Products table:
+------------+-----------+-------------+
| product_id | new_price | change_date |
+------------+-----------+-------------+
| 1 | 20 | 2019-08-14 |
| 2 | 50 | 2019-08-14 |
| 1 | 30 | 2019-08-15 |
| 1 | 35 | 2019-08-16 |
| 2 | 65 | 2019-08-17 |
| 3 | 20 | 2019-08-18 |
+------------+-----------+-------------+Output:
+------------+-------+
| product_id | price |
+------------+-------+
| 1 | 35 |
| 2 | 50 |
| 3 | 10 |
+------------+-------+View Reference Solution & Explanation ▼
This solution uses the `GROUP BY` clause to aggregate results, a `HAVING` clause to filter the aggregated groups, `UNION` to combine the result sets of multiple queries, and a subquery to filter records based on another query's result.
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 SELECT product_id, 10 AS price FROM Products GROUP BY product_id HAVING MIN(change_date) > '2019-08-16';