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

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 |

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 |

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 |