0% completed
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
andUnitsSold
Tables: Link each sale to the corresponding price period based on thepurchase_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.
- Retrieves the
FROM Prices p
:- Specifies the
Prices
table with an aliasp
as the primary table.
- Specifies the
LEFT JOIN UnitsSold u ON ...
:- Performs a left join with the
UnitsSold
table (aliasu
) to include all products, even those with no sales.
- Performs a left join with the
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
.
- Ensures that each sale is matched to the correct price period based on the
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
andUnitsSold
tables as in Step 1 to associate sales with price periods.
- Joins the
GROUP BY product_id
:- Aggregates the calculations for each
product_id
.
- Aggregates the calculations for each
Output After Step 2:
+------------+---------------+ | product_id | average_price | +------------+---------------+ | 1 | 6.96 | | 2 | 16.96 | +------------+---------------+
.....
.....
.....
Table of Contents
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible
Contents are not accessible