Average Selling Price LeetCode Solution

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

Level of Question

Easy

Average Selling Price LeetCode Solution

Average Selling Price LeetCode Solution

Problem Statement

Column NameType
product_idint
start_datedate
end_datedate
priceint
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.

Column NameType
product_idint
purchase_datedate
unitsint
Table: 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_dateend_date price
12019-02-172019-02-285
12019-03-012019-03-2220
22019-02-012019-02-2015
22019-02-212019-03-3130
Prices table:
product_id purchase_date units
12019-02-25100
12019-03-0115
22019-02-10200
22019-03-2230
UnitsSold table:

Output:

product_id average_price
16.96
216.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

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;
Scroll to Top