10. Average Selling Price

Grokking SQL for Tech Interviews

Ask Author

Back to course home

0% completed

Vote For New Content

10. Average Selling Price

Table of Contents

Contents are not accessible

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

MYSQL

. . . .

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.