Average Selling Price | LeetCode 1251

Mastering interval-based conditional joins, aggregate calculations, floating-point rounding, and handling edge cases with NULL values in SQL.

1. Problem Overview and Objective

The Average Selling Price problem (LeetCode 1251) is a classic SQL interview question that tests your ability to work with date intervals, perform conditional joins, calculate weighted averages, and handle edge cases when relational data is missing.

• Objective: Write a SQL query to calculate the average selling price for each product_id.
• Constraint: The resulting average price must be rounded to 2 decimal places.
• Return Order: The result table can be returned in any order.

2. Database Schema & Table Structure

The problem provides two primary tables: Prices and UnitsSold.

Table 1: Prices

This table stores the active price interval for each product.

• product_id (Primary Key component)
• start_date (Primary Key component)
• end_date (Primary Key component)
• price
• Key Property: For each product_id, there are no overlapping periods. That means no two date intervals intersect for the same product.

Table 2: UnitsSold

This table records every individual sales transaction for products.

• product_id
• purchase_date
• units
• Key Property: This table may contain duplicate rows and multiple sales events on different dates.

3. Real-Life Mental Model & Analogy

Real-Life Analogy: Imagine running a retail catalog where a product's price changes over time (e.g., a winter discount versus a summer markup). Customers buy items on specific dates.

To calculate the true average selling price per item, you cannot just take a simple mathematical average of all price tags. Instead, you must look at each sale receipt, check the calendar to see which pricing season that specific purchase date fell into, multiply that specific price by the quantity of units sold, sum up all revenues, and divide by the total number of units sold. If an item was never sold, its average price should safely default to 0.

4. Naive Approach vs. Correct Interval Join Strategy

The Naive Mistake

Beginners often attempt to join the tables purely on product_id:

-- Naive / Incorrect approach
SELECT p.product_id, AVG(p.price) 
FROM Prices p
JOIN UnitsSold u ON p.product_id = u.product_id;

Why this fails: This cartesian product or naive join ignores time bounds. It pairs every sale with every price record for that product, resulting in massively inflated revenue numbers and incorrect averages.

The Correct Interval Strategy

We must join the tables where the product_id matches AND the purchase_date falls strictly within the inclusive range of start_date and end_date.

5. Step-by-Step Query Evolution

Let's build the SQL query incrementally from the foundation up to production grade.

Step A: Establishing the Left Join and Date Range Condition

We use a LEFT JOIN on Prices (p) to UnitsSold (u) to ensure that even if a product was never sold (or has missing sales data), the product still appears in our final report.

SELECT 
    p.product_id
FROM Prices p
LEFT JOIN UnitsSold u 
    ON p.product_id = u.product_id
    AND u.purchase_date >= p.start_date
    AND u.purchase_date <= p.end_date;

Step B: Calculating the Weighted Average Selling Price

The average selling price is calculated as:

In SQL aggregation terms:

SUM(p.price * u.units) / SUM(u.units)

Step C: Adding Grouping and Rounding

Since we are aggregating metrics per product, we must use GROUP BY p.product_id, and wrap our division in the ROUND(..., 2) function.

SELECT 
    p.product_id,
    ROUND(SUM(p.price * u.units) / SUM(u.units), 2) AS average_price
FROM Prices p
LEFT JOIN UnitsSold u 
    ON p.product_id = u.product_id
    AND u.purchase_date BETWEEN p.start_date AND p.end_date
GROUP BY p.product_id;

6. Handling Edge Cases: The NULL Trap

The Problem with Unsold Products

If a product has zero units sold, the SUM(u.units) evaluates to NULL. When SQL divides by NULL, or when an entire row has no matching purchase, the division yields NULL instead of 0.

The Solution: IFNULL / COALESCE

To ensure robust production behavior, we wrap our calculation in an IFNULL (or COALESCE) function to default uncalculated prices to 0:

IFNULL(ROUND(SUM(p.price * u.units) / SUM(u.units), 2), 0)

7. Final Complete SQL Solution

Here is the clean, final, production-ready SQL query accepted by LeetCode:

SELECT 
    p.product_id,
    IFNULL(
        ROUND(SUM(p.price * u.units) / SUM(u.units), 2), 
        0
    ) AS average_price
FROM Prices p
LEFT JOIN UnitsSold u 
    ON p.product_id = u.product_id
    AND u.purchase_date >= p.start_date 
    AND u.purchase_date <= p.end_date
GROUP BY p.product_id;

8. Summary of Functions Used

9. Summary and Key Takeaways

• Interval Matching: Always join time-series or range-based tables using inequality operators (>= and <=) or BETWEEN alongside equality keys.
• Preserve All Entities: Use LEFT JOIN when reporting master data (like products) to ensure items without transactional history are not silently dropped.
• Weighted Averages in SQL: Remember that average selling price is total revenue divided by total volume, not a simple average of price tags (AVG(price)).
• Null Safety: Always anticipate division-by-zero or missing join matches by wrapping aggregate calculations in IFNULL() or COALESCE().
• Formatting Precision: Use ROUND(val, 2) to satisfy strict decimal formatting requirements in reporting interviews.