1251 Average Selling Price

Description

Table: Prices

• (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

• This table may contain duplicate rows.
• Each row of this table indicates the date, units, and product_id of each product sold.

Problem Statement

Write a solution 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.

The result format is in the following example.

Example 1:

Input:

• Prices table:

• UnitsSold table:

Output:

Explanation:

• Average selling price = Total Price of Product / Number of products sold.
• Average selling price for product 1 = ((100 * 5) + (15 * 20)) / 115 = 6.96
• Average selling price for product 2 = ((200 * 15) + (30 * 30)) / 230 = 16.96

Solution

First of all you need to join these two tables. The join condition is slightly different in this case. You need to join verifying that product_id matches and purchase_date is between start_date and end_date from Prices table. You also need to make sure you list all products even if they had 0 units sold and report 0 as their average price instead of NULL. This is where you have to use Prices LEFT JOIN UnitsSold clause.

1SELECT *
2    FROM Prices p
3    LEFT OUTER JOIN UnitsSold u
4    ON p.product_id = u.product_id
5        AND u.purchase_date >= p.start_date AND u.purchase_date <= p.end_date

The next part is to find the average price using sum of price / total units sold equation. You can find sum of products by using price * units. Finally, you have to group by product_id.

1SELECT p.product_id, ROUND(SUM(p.price * u.units) / SUM(u.units), 2) AS average_price
2    FROM ...
3    ....
4    GROUP BY p.product_id;

You also want to ensure that if there are no units sold, you report 0 as average_price. You can use IFNULL() function for that.

The final solution looks like this.

1SELECT p.product_id, IFNULL(
2        ROUND(SUM(p.price * u.units)/SUM(u.units), 2),
3        0) AS average_price
4    FROM Prices p
5    LEFT JOIN UnitsSold u
6    ON p.product_id = u.product_id
7    AND u.purchase_date >= p.start_date AND u.purchase_date <= p.end_date
8    GROUP BY p.product_id;