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

Image scaled to 90%

Output

Image scaled to 45%

Try It Yourself

Database Exercise

Database Schema:

-- Database schema would be rendered here

Exercise Script:

-- Exercise script would be rendered here

Available actions: Execute

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