Average Selling Price

Table: Prices

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

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

Write a SQL query to find the average selling price for each product. average_price should be rounded to 2 decimal places. If a product does not have any sold units, its average selling price is assumed to be 0.

Return the result table in any order.

Example:

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

Task: Write a SQL query to calculate the average selling price for each product, considering sales within the product's price range. Round the average price to two decimal places and include products with no sales (average price = 0).

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

Explanation:

1. JOIN Tables: We join the Prices table with the UnitsSold table using product_id to link price information with sales data.
2. Filter by Date: We filter the sales records to only include those where the purchase_date falls within the start_date and end_date of the price period for each product.
3. Calculate Total Price: SUM(p.price * u.units) calculates the total price for each product by multiplying the price of each unit sold by the number of units sold and summing these values.
4. Calculate Total Units: SUM(u.units) calculates the total number of units sold for each product.
5. Calculate Average Price: We divide the total price by the total units to obtain the average selling price.
6. Round to 2 Decimal Places: ROUND(..., 2) rounds the average price to two decimal places as required.
7. Group by Product: We group the results by product_id to calculate the average price for each product.

If a product does not have any sales records within the specified date ranges, the query will not return a row for that product. To ensure that all products are included and assigned an average price of 0 when they have no sales, a LEFT JOIN and COALESCE function can be incorporated:

SELECT
    p.product_id,
    ROUND(COALESCE(SUM(p.price * u.units) / SUM(u.units), 0), 2) 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;

In this modified query:

• LEFT JOIN ensures that all products from the Prices table are included in the result, even if they have no matching sales records in the UnitsSold table.
• The ON clause of the LEFT JOIN includes the date filtering condition to ensure that only relevant sales records are joined.
• COALESCE replaces NULL values (which occur when there are no sales records for a product) with 0, ensuring that the average price is 0 for products with no sales.