Average Selling Price

Problem

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. 

Problem Definition

Write a solution to find the average selling price for each product. average_price should be rounded to 2 decimal places.

Example

Output

Try It Yourself

Solution

To calculate the average selling price for each product, considering the various price periods and the number of units sold during those periods, we can follow a structured approach. The solution involves joining the Prices and UnitsSold tables, calculating the total revenue and total units sold for each product, and then computing the average price. If a product has no sales, its average price defaults to 0 as per the provided SQL query.

• Join Prices and UnitsSold Tables: Link each sale to the corresponding price period based on the purchase_date.
• Calculate Total Revenue and Total Units Sold: For each product, compute the sum of (units sold × price) and the total units sold.
• Compute Average Selling Price: Divide the total revenue by the total units sold for each product and round the result to two decimal places.
• Handle Products with No Sales: Assign an average price of 0 for products that have no sales records.

SQL Query

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

Step-by-Step Approach

Step 1: Join Prices and UnitsSold Tables

Objective:
Associate each sale in the UnitsSold table with the appropriate price period from the Prices table based on the purchase_date.

SQL Query:

SELECT p.product_id, p.start_date, p.end_date, p.price, u.units, u.purchase_date
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;

Explanation:

• SELECT p.product_id:
  • Retrieves the product_id of products from joined table.
• FROM Prices p:
  • Specifies the Prices table with an alias p as the primary table.
• LEFT JOIN UnitsSold u ON ...:
  • Performs a left join with the UnitsSold table (alias u) to include all products, even those with no sales.
• p.product_id = u.product_id AND u.purchase_date BETWEEN p.start_date AND p.end_date:
  • Ensures that each sale is matched to the correct price period based on the purchase_date.

Tables After Joining In Step 1:

+------------+------------+------------+-------+-------+---------------+
| product_id | start_date | end_date   | price | units | purchase_date |
+------------+------------+------------+-------+-------+---------------+
| 1          | 2019-02-17 | 2019-02-28 | 5     | 100   | 2019-02-25    |
| 1          | 2019-03-01 | 2019-03-22 | 20    | 15    | 2019-03-01    |
| 2          | 2019-02-01 | 2019-02-20 | 15    | 200   | 2019-02-10    |
| 2          | 2019-02-21 | 2019-03-31 | 30    | 30    | 2019-03-22    |
+------------+------------+------------+-------+-------+---------------+

Step 2: Calculate Average Selling Price

For each product, calculate the average selling price by dividing the total revenue by the total units sold. If a product has no sales, assign an average price of 0.

SQL Query:

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

Explanation:

• SELECT p.product_id, IFNULL(Round(Sum(units * price) / Sum(units), 2), 0) AS average_price:
  • Sum(units * price): Calculates the total revenue for each product.
  • Sum(units): Calculates the total units sold for each product.
  • Sum(units * price) / Sum(units): Computes the average price per unit.
  • Round(..., 2): Rounds the average price to two decimal places.
  • IFNULL(..., 0): Assigns an average price of 0 if there are no sales (NULL).
• FROM Prices p LEFT JOIN UnitsSold u ON ...:
  • Joins the Prices and UnitsSold tables as in Step 1 to associate sales with price periods.
• GROUP BY product_id:
  • Aggregates the calculations for each product_id.

Output After Step 2:

+------------+---------------+
| product_id | average_price |
+------------+---------------+
| 1          | 6.96          |
| 2          | 16.96         |
+------------+---------------+