Average Selling Price

Preview mode. Log in to edit, run, submit, and save progress.

Medium

Description

You are given two tables - Prices and UnitsSold. The Prices table holds the price of each product within a specific date range. The date ranges for the same product do not overlap. The UnitsSold table logs how many units of each product were sold on a given purchase date. Write a SQL query to find the average selling price for each product. The average selling price is defined as: average_price = SUM(price * units) / SUM(units) Round the result to 2 decimal places. If a product has no sales, report 0 as the average price. Return the result in any order.

Database Schema

Prices

Column Name	Example Value
product_id	1
start_date	2019-02-17
end_date	2019-02-28
price	5

UnitsSold

Column Name	Example Value
product_id	1
purchase_date	2019-02-25
units	100

Example

Prices

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

UnitsSold

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:

Product 1: ((100 * 5) + (15 * 20)) / 115 = 800/115 = 6.96. Product 2: ((200 * 15) + (30 * 30)) / 230 = 3900/230 = 16.96.

Approach hint

Start with a simple approach, explain the trade-off, then move toward a cleaner or more scalable solution.

Common mistake

Skipping assumptions, edge cases, or trade-offs can make an otherwise good answer feel incomplete.