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 INPUT Prices Table id start end price 1 01-01 02-28 5 1 03-01 05-31 20 2 01-01 12-31 15 Sales Table id date units 1 02-15 100 1 03-15 50 2 06-01 200 Match sales with price periods sale_date BETWEEN start_date AND end_date ALGORITHM STEPS 1 LEFT JOIN Tables Join on product_id and date within price period 2 Calculate Revenue revenue = price * units 5*100 + 20*50 = 1500 3 GROUP BY product Aggregate per product_id Product 1 rev=1500 units=150 Product 2 rev=3000 units=200 4 Weighted Average avg = SUM(rev) / SUM(units) IFNULL(SUM/SUM, 0) COALESCE handles no-sales case FINAL RESULT Average Price Per Product Product 1 1500 / 150 = 10.00 avg = 10.00 Product 2 3000 / 200 = 15.00 avg = 15.00 Output Table product_id avg_price 1 10.00 2 15.00 OK - Verified Key Insight: Weighted average requires multiplying each price by its units sold, then dividing total revenue by total units. Using LEFT JOIN ensures products without sales return NULL, which IFNULL/COALESCE converts to 0. The date range condition (BETWEEN start_date AND end_date) matches each sale to its correct price period. TutorialsPoint - Average Selling Price | Optimal Solution

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