Average Selling Price

Task: Find the average selling price for each product. average_price = total_price / total_units.

Table: Prices

+-------------+------+
| Column Name | Type |
+-------------+------+
| product_id  | INT  |
| start_date  | date |
| end_date    | date |
| price       | INT  |
+-------------+------+

Table: UnitsSold

+---------------+------+
| Column Name   | Type |
+---------------+------+
| product_id    | INT  |
| purchase_date | date |
| units         | INT  |
+---------------+------+

Input: Prices table:

+------------+------------+------------+-------+
| 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    |
+------------+------------+------------+-------+

UnitsSold table:

+------------+---------------+-------+
| 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 | average_price |
+------------+---------------+
| 1          | 6.96          |
| 2          | 16.96         |
+------------+---------------+

View Reference Solution & Explanation ▼

This solution uses a `JOIN` to combine data from multiple tables, the `GROUP BY` clause to aggregate results, and the `ROUND()` function to format numerical output.

SELECT p.product_id, IFNULL(ROUND(SUM(p.price * u.units) / CAST(SUM(u.units) AS FLOAT), 2), 0) AS average_price FROM Prices p LEFT JOIN UnitsSold u ON p.product_id = u.product_id AND u.purchase_date BETWEEN p.start_date AND p.end_date GROUP BY p.product_id;