Compute Unit-Weighted Prices across Nonoverlapping Price Periods

### Problem Report each product’s average_price, rounded to two decimal places. Each sale uses the price interval covering its purchase_date, with inclusive start and end dates. Weight by units sold. A product with no sold units has average price 0. Preserve duplicate UnitsSold rows; result row order is unrestricted. ### Tables Table: `Prices` ```text +---------------+---------+ | Column Name | Type | +---------------+---------+ | product_id | int | | start_date | date | | end_date | date | | price | int | +---------------+---------+ (product_id, start_date, end_date) is the primary key (combination of columns with unique values) for this table. Each row of this table indicates the price of the product_id in the period from start_date to end_date. For each product_id there will be no two overlapping periods. That means there will be no two intersecting periods for the same product_id. ``` Table: `UnitsSold` ```text +---------------+---------+ | Column Name | Type | +---------------+---------+ | product_id | int | | purchase_date | date | | units | int | +---------------+---------+ This table may contain duplicate rows. Each row of this table indicates the date, units, and product_id of each product sold. ``` ### Constraints - Every sale falls within exactly one provided price period: the same `product_id` has a price row whose inclusive date range contains `purchase_date`. - `price` and `units` are non-null integers. Unit quantities are nonnegative. ### Example ```text Prices (product_id,start_date,end_date,price): (1,2021-01-01,2021-01-10,10), (1,2021-01-11,2021-01-20,20), (2,2021-01-01,2021-01-20,30) UnitsSold (product_id,purchase_date,units): (1,2021-01-10,3), (1,2021-01-11,1) Output (product_id,average_price): (1,12.50), (2,0.00) ``` ### What a Strong Answer Covers - Match each sale to the inclusive price period for its product. - Weight by units sold, keep repeated sales rows, and return zero for products without sold units. ### Follow-up Questions - How would you report a separate weighted price for each calendar month? - How would you identify sales without a matching price period?