As-Of Product Price Snapshot

Medium

A merchandising platform stores catalog price updates as an event log. Table: products product_id: product identifier new_price: price that becomes effective at change_date change_date: date when the new_price starts applying Data rules: (product_id, change_date) is unique. A product can have multiple price changes across time. Before any recorded change, every product has baseline price 10. Task: Compute the effective price for every product on 2019-08-16. Pricing semantics: If a product has one or more changes with change_date <= 2019-08-16, use the row with the latest such date. If a product has no change on or before 2019-08-16, use price 10. Output requirements: Return one row per distinct product_id present in products. Return columns in exact order: product_id price Row order is not constrained unless your local checker enforces deterministic ordering. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

products:
| 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":1,"price":35},
  {"product_id":2,"price":50},
  {"product_id":3,"price":10}
]

Explanation

Product 1 uses its change on 2019-08-16. Product 2 ignores the 2019-08-17 update and keeps 50 from 2019-08-14. Product 3 has no update on or before 2019-08-16, so it falls back to baseline 10.

Example 2

Input

products:
| product_id | new_price | change_date |
|------------|-----------|-------------|
| 10         | 15        | 2019-08-20  |
| 10         | 18        | 2019-09-01  |
| 11         | 7         | 2019-08-01  |
| 11         | 9         | 2019-08-10  |
| 11         | 50        | 2019-08-17  |
| 12         | 25        | 2019-08-16  |

Output

[
  {"product_id":10,"price":10},
  {"product_id":11,"price":9},
  {"product_id":12,"price":25}
]

Explanation

Product 10 has only future-dated changes and therefore remains at baseline 10. Product 11 picks the latest qualifying date before cutoff (2019-08-10). Product 12 has an exact-cutoff change, so price is 25.

Example 3

Input

products:
| product_id | new_price | change_date |
|------------|-----------|-------------|
| 101        | 80        | 2019-08-01  |
| 101        | 100       | 2019-08-16  |
| 102        | 22        | 2019-07-30  |
| 102        | 18        | 2019-08-15  |
| 103        | 40        | 2019-10-01  |

Output

[
  {"product_id":101,"price":100},
  {"product_id":102,"price":18},
  {"product_id":103,"price":10}
]

Explanation

The query must choose the latest qualifying change per product, not the highest price. Product 103 has no qualifying change and defaults to 10.

Constraints

1 <= number of rows in products <= 20,000
1 <= product_id <= 1,000,000,000
1 <= new_price <= 1,000,000,000
change_date is a valid date in YYYY-MM-DD format
(product_id, change_date) is unique
All products start with baseline price 10 before their first change
Return exactly one row per distinct product_id
Use the latest change_date <= 2019-08-16 when it exists
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns: product_id, price

products: | 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 |

products: | product_id | new_price | change_date | |------------|-----------|-------------| | 10 | 15 | 2019-08-20 | | 10 | 18 | 2019-09-01 | | 11 | 7 | 2019-08-01 | | 11 | 9 | 2019-08-10 | | 11 | 50 | 2019-08-17 | | 12 | 25 | 2019-08-16 |

products: | product_id | new_price | change_date | |------------|-----------|-------------| | 101 | 80 | 2019-08-01 | | 101 | 100 | 2019-08-16 | | 102 | 22 | 2019-07-30 | | 102 | 18 | 2019-08-15 | | 103 | 40 | 2019-10-01 |