Average Selling Price
Preview mode. Log in to edit, run, submit, and save progress.
Average Selling Price
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.
Examples
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.