00:00:00

Description

Editorial

As-Of Product Price Snapshot

MEDIUM20 pts

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

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

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

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

products =

[{"product_id":1,"new_price":20,"change_date":"2019-08-14"},{"product_id":2,"new_price":50,"change_date":"2019-08-14"},{"product_id":1,"new_price":30,"change_date":"2019-08-15"},{"product_id":1,"new_price":35,"change_date":"2019-08-16"},{"product_id":2,"new_price":65,"change_date":"2019-08-17"},{"product_id":3,"new_price":20,"change_date":"2019-08-18"}]

Loading problem...

101

00:00:00

Description

Editorial

As-Of Product Price Snapshot

MEDIUM20 pts

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

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

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

Example

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.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

products =

As-Of Product Price Snapshot

Hints

As-Of Product Price Snapshot

Hints