Product Period Weighted Average Price

Easy

An analytics warehouse tracks product pricing windows and sale events. Table: prices product_id: product identifier start_date: first day (inclusive) when the listed price is active end_date: last day (inclusive) when the listed price is active price: unit price active during that window Data rule: For the same product_id, pricing windows never overlap. Table: units_sold product_id: product identifier purchase_date: sale date units: quantity sold in that event row Task: For each product in prices, compute its weighted average selling price over matched sale events. A sale event matches a price row only when: product_id is the same, and purchase_date is between start_date and end_date (inclusive). Metric definition per product: weighted_revenue = SUM(price * units) over matched sales total_units = SUM(units) over matched sales average_price = weighted_revenue / total_units Edge behavior: If total_units is zero (no matched sold units), average_price must be 0. Round average_price to 2 decimal places. Output requirements: Return one row per distinct product_id present in prices. Return columns in this exact order: product_id average_price Row order may be returned in any order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

prices:
| product_id | start_date | end_date   | price |
|------------|------------|------------|-------|
| 1          | 2019-02-17 | 2019-02-28 | 5     |
| 1          | 2019-03-01 | 2019-03-22 | 20    |
| 2          | 2019-02-01 | 2019-02-20 | 15    |
| 2          | 2019-02-21 | 2019-03-31 | 30    |

units_sold:
| product_id | purchase_date | units |
|------------|---------------|-------|
| 1          | 2019-02-25    | 100   |
| 1          | 2019-03-01    | 15    |
| 2          | 2019-02-10    | 200   |
| 2          | 2019-03-22    | 30    |

Output

[
  {"product_id":1,"average_price":6.96},
  {"product_id":2,"average_price":16.96}
]

Explanation

Product 1 average is ((100*5)+(15*20))/115 = 6.9565..., rounded to 6.96. Product 2 average is ((200*15)+(30*30))/230 = 16.9565..., rounded to 16.96.

Example 2

Input

prices:
| product_id | start_date | end_date   | price |
|------------|------------|------------|-------|
| 10         | 2024-01-01 | 2024-01-31 | 12    |
| 10         | 2024-02-01 | 2024-02-29 | 20    |
| 11         | 2024-01-01 | 2024-03-31 | 7     |
| 12         | 2024-01-15 | 2024-02-15 | 50    |

units_sold:
| product_id | purchase_date | units |
|------------|---------------|-------|
| 10         | 2024-01-10    | 10    |
| 10         | 2024-02-10    | 5     |
| 10         | 2023-12-31    | 99    |
| 12         | 2024-02-15    | 3     |

Output

[
  {"product_id":10,"average_price":14.67},
  {"product_id":11,"average_price":0.0},
  {"product_id":12,"average_price":50.0}
]

Explanation

The 2023-12-31 sale for product 10 is outside every price window and is ignored. Product 11 has no matched sales, so it returns 0.

Example 3

Input

prices:
| product_id | start_date | end_date   | price |
|------------|------------|------------|-------|
| 21         | 2025-04-01 | 2025-04-15 | 2     |
| 21         | 2025-04-16 | 2025-04-30 | 3     |
| 22         | 2025-04-01 | 2025-04-30 | 10    |

units_sold:
| product_id | purchase_date | units |
|------------|---------------|-------|
| 21         | 2025-04-05    | 15    |
| 21         | 2025-04-20    | 25    |
| 22         | 2025-04-10    | 4     |

Output

[
  {"product_id":21,"average_price":2.63},
  {"product_id":22,"average_price":10.0}
]

Explanation

For product 21, weighted average = (2*15 + 3*25) / 40 = 2.625, which rounds to 2.63.

Constraints

1 <= number of rows in prices <= 300000
0 <= number of rows in units_sold <= 300000
1 <= product_id <= 1000000000
1 <= price <= 1000000000
1 <= units <= 1000000
start_date, end_date, purchase_date are valid dates in YYYY-MM-DD format
For each product_id in prices, intervals [start_date, end_date] do not overlap
Rows in units_sold may contain duplicates and must be counted independently
If a sale date does not fall inside any active window, that row does not contribute
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns: product_id, average_price

Loading problem...

Preparing your editor. You can read the problem while it loads.

Product Period Weighted Average Price

Easy

An analytics warehouse tracks product pricing windows and sale events. Table: prices product_id: product identifier start_date: first day (inclusive) when the listed price is active end_date: last day (inclusive) when the listed price is active price: unit price active during that window Data rule: For the same product_id, pricing windows never overlap. Table: units_sold product_id: product identifier purchase_date: sale date units: quantity sold in that event row Task: For each product in prices, compute its weighted average selling price over matched sale events. A sale event matches a price row only when: product_id is the same, and purchase_date is between start_date and end_date (inclusive). Metric definition per product: weighted_revenue = SUM(price * units) over matched sales total_units = SUM(units) over matched sales average_price = weighted_revenue / total_units Edge behavior: If total_units is zero (no matched sold units), average_price must be 0. Round average_price to 2 decimal places. Output requirements: Return one row per distinct product_id present in prices. Return columns in this exact order: product_id average_price Row order may be returned in any order. Supported submission environments: SQL (SQLite 3.27.2) Pandas (Python)

Example 1

Input

prices:
| product_id | start_date | end_date   | price |
|------------|------------|------------|-------|
| 1          | 2019-02-17 | 2019-02-28 | 5     |
| 1          | 2019-03-01 | 2019-03-22 | 20    |
| 2          | 2019-02-01 | 2019-02-20 | 15    |
| 2          | 2019-02-21 | 2019-03-31 | 30    |

units_sold:
| product_id | purchase_date | units |
|------------|---------------|-------|
| 1          | 2019-02-25    | 100   |
| 1          | 2019-03-01    | 15    |
| 2          | 2019-02-10    | 200   |
| 2          | 2019-03-22    | 30    |

Output

[
  {"product_id":1,"average_price":6.96},
  {"product_id":2,"average_price":16.96}
]

Explanation

Product 1 average is ((100*5)+(15*20))/115 = 6.9565..., rounded to 6.96. Product 2 average is ((200*15)+(30*30))/230 = 16.9565..., rounded to 16.96.

Example 2

Input

prices:
| product_id | start_date | end_date   | price |
|------------|------------|------------|-------|
| 10         | 2024-01-01 | 2024-01-31 | 12    |
| 10         | 2024-02-01 | 2024-02-29 | 20    |
| 11         | 2024-01-01 | 2024-03-31 | 7     |
| 12         | 2024-01-15 | 2024-02-15 | 50    |

units_sold:
| product_id | purchase_date | units |
|------------|---------------|-------|
| 10         | 2024-01-10    | 10    |
| 10         | 2024-02-10    | 5     |
| 10         | 2023-12-31    | 99    |
| 12         | 2024-02-15    | 3     |

Output

[
  {"product_id":10,"average_price":14.67},
  {"product_id":11,"average_price":0.0},
  {"product_id":12,"average_price":50.0}
]

Explanation

The 2023-12-31 sale for product 10 is outside every price window and is ignored. Product 11 has no matched sales, so it returns 0.

Example 3

Input

prices:
| product_id | start_date | end_date   | price |
|------------|------------|------------|-------|
| 21         | 2025-04-01 | 2025-04-15 | 2     |
| 21         | 2025-04-16 | 2025-04-30 | 3     |
| 22         | 2025-04-01 | 2025-04-30 | 10    |

units_sold:
| product_id | purchase_date | units |
|------------|---------------|-------|
| 21         | 2025-04-05    | 15    |
| 21         | 2025-04-20    | 25    |
| 22         | 2025-04-10    | 4     |

Output

[
  {"product_id":21,"average_price":2.63},
  {"product_id":22,"average_price":10.0}
]

Explanation

For product 21, weighted average = (2*15 + 3*25) / 40 = 2.625, which rounds to 2.63.

Constraints

1 <= number of rows in prices <= 300000
0 <= number of rows in units_sold <= 300000
1 <= product_id <= 1000000000
1 <= price <= 1000000000
1 <= units <= 1000000
start_date, end_date, purchase_date are valid dates in YYYY-MM-DD format
For each product_id in prices, intervals [start_date, end_date] do not overlap
Rows in units_sold may contain duplicates and must be counted independently
If a sale date does not fall inside any active window, that row does not contribute
Use SQLite 3.27.2 semantics for SQL submissions
For Pandas submissions, return a DataFrame with columns: product_id, average_price