Average Selling Price LeetCode Solution

This Leetcode problem Average Selling Price LeetCode Solution is done in SQL.

List of all LeetCode Solution

Problem Statement

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

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:

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

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)```
Scroll to Top