00:00:00

Description

Editorial

Product Period Weighted Average Price

EASY15 pts

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

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 ((1005)+(1520))/115 = 6.9565..., rounded to 6.96. Product 2 average is ((20015)+(3030))/230 = 16.9565..., rounded to 16.96.

Example

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

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 = (215 + 325) / 40 = 2.625, which rounds to 2.63.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

prices =

[{"product_id":1,"start_date":"2019-02-17","end_date":"2019-02-28","price":5},{"product_id":1,"start_date":"2019-03-01","end_date":"2019-03-22","price":20},{"product_id":2,"start_date":"2019-02-01","end_date":"2019-02-20","price":15},{"product_id":2,"start_date":"2019-02-21","end_date":"2019-03-31","price":30}]

units_sold =

[{"product_id":1,"purchase_date":"2019-02-25","units":100},{"product_id":1,"purchase_date":"2019-03-01","units":15},{"product_id":2,"purchase_date":"2019-02-10","units":200},{"product_id":2,"purchase_date":"2019-03-22","units":30}]

Loading problem...

101

00:00:00

Description

Editorial

Product Period Weighted Average Price

EASY15 pts

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

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 ((1005)+(1520))/115 = 6.9565..., rounded to 6.96. Product 2 average is ((20015)+(3030))/230 = 16.9565..., rounded to 16.96.

Example

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

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 = (215 + 325) / 40 = 2.625, which rounds to 2.63.

Accepted0/0·0% Acceptance

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

Code

Visualizer

Solutions

14px

Test Cases3

Results

Submissions

prices =

units_sold =

Product Period Weighted Average Price

Hints

Product Period Weighted Average Price

Hints