Average Selling Price

Average Selling Price

What is this problem about?

The Average Selling Price interview question is a SQL task involving two tables: Prices (containing product_id, start_date, end_date, and price) and UnitsSold (containing product_id, purchase_date, and units). You need to calculate the average selling price for each product. The average price is Total Revenue / Total Units. This Average Selling Price coding problem is about joining tables on non-exact matches (date ranges).

Why is this asked in interviews?

Companies like Amazon, Apple, and Oracle use this to test a candidate's ability to join tables based on a range (purchase_date BETWEEN start_date AND end_date). It also evaluates your handling of NULL values (using IFNULL or COALESCE) and your ability to perform weighted average calculations in SQL.

Algorithmic pattern used

This follows the Database interview pattern of "Join-Group-Aggregate."

1. LEFT JOIN the Prices table with UnitsSold on product_id.
2. Add a condition that the purchase_date must be between the price's validity range.
3. GROUP BY product_id.
4. Calculate SUM(price * units) / SUM(units).

Example explanation

Product 1: Price $20 (Jan 1-15), Price$25 (Jan 16-30). Sales: 10 units on Jan 5, 20 units on Jan 20.

• Revenue: (10 * 20) + (20 * 25) = 200 + 500 = 700.
• Total Units: 10 + 20 = 30.
• Average Price: 700 / 30 = 23.33.

Common mistakes candidates make

• Incorrect Join Condition: Forgetting the BETWEEN date logic, which results in multiplying units by every possible price the product ever had.
• Handling Zero Sales: If a product was never sold, SUM(units) is 0 or NULL, leading to a division error. You should return 0 in such cases.
• Rounding: Forgetting to use ROUND(..., 2) to meet the required decimal precision.

Interview preparation tip

Practice "Range Joins" in SQL. Joining data based on a date being within a specific window is a very common real-world scenario in e-commerce and finance analytics.