Cracking LeetCode 1251: Average Selling Price SQL

Unlocking Database Puzzles: LeetCode 1251 Explained

Unlocking Database Puzzles: LeetCode 1251 Explained
Further Reading & Resources

Welcome to another deep dive into the world of SQL challenges! LeetCode problems aren't just for coding interviews; they're fantastic for honing your database skills. Today, we're tackling LeetCode problem 1251: "Average Selling Price."

This problem is a quintessential example of how real-world business logic translates into SQL queries, requiring a solid understanding of JOIN operations, date range comparisons, and aggregate functions. Let's break it down!

The Challenge: Average Selling Price

The goal of this problem is to calculate the average selling price for each product. Sounds simple, right? The twist lies in how product prices can change over time. Each unit of a product might be sold at a different price depending on the date of purchase.

You are provided with two tables:

Prices:
- product_id (INT)
- start_date (DATE)
- end_date (DATE)
- price (INT)
This table specifies the price of a product during a particular period. Each product_id can have multiple overlapping or non-overlapping price ranges.
UnitsSold:
- product_id (INT)
- purchase_date (DATE)
- units (INT)
This table records sales transactions, indicating how many units of a product_id were sold on a specific purchase_date.

Your task is to return a table with product_id and average_price for each product. The average_price should be rounded to two decimal places.

Decoding the Logic: Strategy Breakdown

To solve this, we need to correctly link each sale (from UnitsSold) to its corresponding price (from Prices) based on the sale date. Then, we can calculate the total revenue and total units sold for each product to find the average.

Here's the step-by-step strategy:

1. Joining Tables: The Crucial Link

The first step is to combine information from UnitsSold and Prices. We need to join them based on product_id. However, a simple JOIN on product_id isn't enough. We also need to ensure that the purchase_date from UnitsSold falls within the start_date and end_date range defined in the Prices table for that specific product.

We'll use an INNER JOIN because we only care about sales for which a valid price exists within the given date ranges.

SELECT
    us.product_id,
    us.units,
    p.price,
    us.purchase_date,
    p.start_date,
    p.end_date
FROM
    UnitsSold us
INNER JOIN
    Prices p ON us.product_id = p.product_id
             AND us.purchase_date BETWEEN p.start_date AND p.end_date;

This query will give us a combined view, showing each sale transaction with its matching price at the time of purchase.

2. Calculating Total Revenue and Units

Once we have the price for each individual sale, we can calculate the revenue generated by that sale (price * units). To find the average selling price for a product, we need:

Total Revenue for a Product: SUM(price * units) for all its sales.
Total Units Sold for a Product: SUM(units) for all its sales.

The average selling price is then (Total Revenue) / (Total Units Sold).

3. Grouping by Product

Since we need the average selling price for each product, we'll use the GROUP BY product_id clause. This aggregates all sales data for a particular product, allowing us to apply our SUM calculations correctly.

The Complete SQL Solution

Combining these steps, here's the final SQL query:

SELECT
    p.product_id,
    -- Calculate total revenue (price * units) and total units sold.
    -- Ensure floating-point division by multiplying by 1.0.
    -- Round the final average price to two decimal places.
    ROUND(SUM(p.price * us.units) * 1.0 / SUM(us.units), 2) AS average_price
FROM
    Prices p
INNER JOIN
    UnitsSold us ON p.product_id = us.product_id
                AND us.purchase_date BETWEEN p.start_date AND p.end_date
GROUP BY
    p.product_id;

Code Explanation:

FROM Prices p INNER JOIN UnitsSold us: We start by joining Prices and UnitsSold tables. We use aliases p and us for brevity.
ON p.product_id = us.product_id AND us.purchase_date BETWEEN p.start_date AND p.end_date: This is the core of our join condition. It matches products by their product_id AND ensures that the purchase_date of a unit sold falls within the valid start_date and end_date for that specific product's price.
GROUP BY p.product_id: This clause aggregates all rows that have the same product_id into a single group, so our SUM functions work per product.
SUM(p.price * us.units): This calculates the total revenue for all units sold within the valid price ranges for each product.
SUM(us.units): This calculates the total number of units sold within the valid price ranges for each product.
* 1.0: This is a common trick in many SQL dialects to ensure that the division performs floating-point arithmetic rather than integer division, preventing truncation of decimal values.
ROUND(..., 2) AS average_price: Finally, we divide the total revenue by the total units to get the average price and ROUND it to two decimal places as required by the problem, aliasing the result as average_price.

Why This Matters: Real-World Applications

Solving problems like LeetCode 1251 isn't just an academic exercise. This exact logic is used in various real-world scenarios:

Inventory Valuation: Calculating the average cost or selling price of inventory over time.
Sales Performance Analysis: Understanding product profitability when pricing is dynamic.
Financial Reporting: Aggregating sales data for revenue recognition.
Dynamic Pricing Models: Feeding historical average prices into algorithms that predict future pricing strategies.

Conclusion: Master Your SQL Joins!

LeetCode 1251 is a fantastic problem for reinforcing your understanding of INNER JOIN with multiple conditions, date range comparisons, and aggregate functions. The ability to accurately combine and summarize data from different tables based on specific criteria is a fundamental skill for any data professional.

Keep practicing these types of problems, and you'll build a strong foundation for tackling complex database challenges in any environment! Happy coding!