Last updated on October 9th, 2024 at 10:13 pm

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

List of all LeetCode Solution

Average Selling Price 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

1. 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;