Average Selling Price - Problem

You are given two tables: Prices and UnitsSold.

The Prices table contains the price of each product during different time periods. Each product can have multiple price periods, but they never overlap.

The UnitsSold table contains records of product sales with the purchase date and units sold.

Write a SQL query to find the average selling price for each product. The average price should be rounded to 2 decimal places.

Important: If a product has no sales records, its average selling price should be 0.

  • Calculate weighted average: (total revenue) / (total units sold)
  • Match sales dates with corresponding price periods
  • Handle products with no sales

Table Schema

Prices
Column Name Type Description
product_id PK int Product identifier
start_date PK date Price period start date
end_date PK date Price period end date
price int Product price during this period
Primary Key: (product_id, start_date, end_date)
UnitsSold
Column Name Type Description
product_id int Product identifier
purchase_date date Date when product was sold
units int Number of units sold
Primary Key: None (can have duplicates)

Input & Output

Example 1 — Basic Price Calculation
Input Tables:
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
💡 Note:

For product 1: Sales on 2019-02-25 (price=5, units=100) and 2019-03-01 (price=20, units=15). Total revenue = 5×100 + 20×15 = 800. Total units = 115. Average = 800/115 = 6.96.

For product 2: Sales on 2019-02-10 (price=15, units=200) and 2019-03-22 (price=30, units=30). Total revenue = 15×200 + 30×30 = 3900. Total units = 230. Average = 3900/230 = 16.96.
Example 2 — Product with No Sales
Input Tables:
Prices
product_id start_date end_date price
1 2019-02-17 2019-02-28 5
3 2019-02-01 2019-02-28 10
UnitsSold
product_id purchase_date units
1 2019-02-25 100
Output:
product_id average_price
1 5.00
3 0.00
💡 Note:

Product 1 has one sale matching its price period, so average price = 5.00.

Product 3 has no sales records, so its average price is 0.00 as specified in the problem requirements.

Constraints

  • 1 ≤ product_id ≤ 1000
  • 0 ≤ price ≤ 1000
  • 1 ≤ units ≤ 1000
  • Date format: YYYY-MM-DD
  • No overlapping price periods for the same product

Visualization

Tap to expand
Average Selling Price Problem OverviewInput: Two TablesPrices Tableproduct_id | price | dates1 | 5 | Feb 17-281 | 20 | Mar 1-22UnitsSold Tableproduct_id | units | date1 | 100 | Feb 251 | 15 | Mar 1LEFT JOINon product_idand date rangeSQL OperationMATCH SALES TO PRICESFeb 25 → price 5Mar 1 → price 20Calculate: (5×100+20×15)/115GROUP BYproduct_idOutputAverage Pricesproduct_id | avg_price1 | 6.96Key: Weighted average = Total Revenue ÷ Total Units
Understanding the Visualization
1
Input Tables
Prices with date ranges and UnitsSold with purchase dates
2
LEFT JOIN
Match sales to price periods by date range
3
Calculate
Weighted average price per product
Key Takeaway
🎯 Key Insight: Use LEFT JOIN with date range conditions to match sales with correct pricing periods, then calculate weighted averages

Related Problems

Asked in
Amazon 15 Facebook 12 Microsoft 8
28.5K Views
Medium Frequency
~12 min Avg. Time
856 Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen