This Leetcode problem Average Selling Price LeetCode Solution is done in SQL.
List of all LeetCode Solution
![Average Selling Price LeetCode Solution](https://i0.wp.com/totheinnovation.com/wp-content/uploads/2024/02/LeetCode-Problem-Solution.png?resize=200%2C200&ssl=1)
Average Selling Price LeetCode Solution
Table of Contents
Problem Statement
Column Name | Type |
product_id | int |
start_date | date |
end_date | date |
price | int |
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.
Column Name | Type |
product_id | int |
purchase_date | date |
units | int |
UnitsSold
This table may contain duplicate rows. Each row of this table indicates the date, units, and product_id of each product sold.
Write a solution to find the average selling price for each product. average_price
should be rounded to 2 decimal places.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input:
product_id | start_date | end_date | price |
1 | 2019-02-17 | 2019-02-28 | 5 |
1 | 2019-03-01 | 2019-03-22 | 20 |
2 | 2019-02-01 | 2019-02-20 | 15 |
2 | 2019-02-21 | 2019-03-31 | 30 |
product_id | purchase_date | units |
1 | 2019-02-25 | 100 |
1 | 2019-03-01 | 15 |
2 | 2019-02-10 | 200 |
2 | 2019-03-22 | 30 |
Output:
product_id | average_price |
1 | 6.96 |
2 | 16.96 |
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
Average Selling Price LeetCode Solution MySQL
select
distinct p.product_id,
round(
sum(price * units) / sum(units),
2
) as average_price
from
Prices as p
join UnitsSold as u on p.product_id = u.product_id
and u.purchase_date between p.start_date
and p.end_date
group by
p.product_id
order by
p.product_id;
Code language: SQL (Structured Query Language) (sql)