Average Selling Price - Solution & Explanation
Problem Statement
Table: Prices
+---------------+---------+ | Column Name | Type | +---------------+---------+ | product_id | int | | start_date | date | | end_date | date | | price | int | +---------------+---------+ (product_id, start_date, end_date) is the primary key (combination of columns with unique values) for this table. Each row of this table indicates the price of the product_id in the period from start_date to end_date. For each product_id there will be no two overlapping periods. That means there will be no two intersecting periods for the same product_id.
Table: UnitsSold
+---------------+---------+ | Column Name | Type | +---------------+---------+ | product_id | int | | purchase_date | date | | units | int | +---------------+---------+ This table may contain duplicate rows. Each row of this table indicates the date, units, and product_id of each product sold.
Write a solution to find the average selling price for each product. average_price should be rounded to 2 decimal places. If a product does not have any sold units, its average selling price is assumed to be 0.
Return the result table in any order.
The result format is in the following example.
Example 1:
Input: Prices table: +------------+------------+------------+--------+ | 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 table: +------------+---------------+-------+ | 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 | +------------+---------------+ Explanation: Average selling price = Total Price of Product / Number of products sold. Average selling price for product 1 = ((100 * 5) + (15 * 20)) / 115 = 6.96 Average selling price for product 2 = ((200 * 15) + (30 * 30)) / 230 = 16.96
Approach Overview
Problem Overview: Each product has price intervals in the Prices table and sales records in UnitsSold. For every product, calculate the average selling price by weighting the price with the number of units sold during each valid price period.
The key detail: a sale only uses the price that was active on the purchase date. After matching sales to the correct price range, compute SUM(price * units) / SUM(units) for each product. If a product has no sales, the result should be 0.
Approach 1: Using SQL for Data Retrieval and Calculation (O(n))
This approach relies on relational operations directly in SQL. Join Prices and UnitsSold on product_id and filter rows where purchase_date falls between start_date and end_date. After the join, compute the weighted average using SUM(price * units) divided by SUM(units), grouped by product_id. A LEFT JOIN ensures products without sales still appear, and COALESCE or conditional aggregation handles division when no units exist.
This is the natural solution for database problems. The database engine handles filtering, joins, and aggregation efficiently. Time complexity is O(n) relative to the number of rows processed in the join, and space complexity is O(1) beyond query execution buffers.
Approach 2: Using In-Memory Data Structures (O(p + s))
If the data is loaded into application code, replicate the join logic manually. Store price intervals for each product using a hash map keyed by product_id. For every sale in UnitsSold, iterate through that product’s price intervals and find the range containing the purchase_date. Multiply the matched price by the number of units and accumulate totals for revenue and units.
After processing all sales, compute the weighted average for each product: total_revenue / total_units. This approach uses a hash map to group product data and simple iteration to locate valid price ranges. Time complexity is O(p + s) where p is price intervals and s is sales records, with O(p) space for storing intervals.
Recommended for interviews: Interviewers expect the SQL aggregation approach. It shows you understand joins, date filtering, and weighted averages in relational queries. The in-memory solution demonstrates the underlying logic and is useful when solving the problem in languages like Python or JavaScript outside a database environment.
Approach 1: Using SQL for Data Retrieval and Calculation
This approach leverages SQL queries to join the Prices and UnitsSold tables on the product_id column. We filter units sold within the specified price start_date and end_date periods. For each matching row, we calculate the total price – the product of the unit price and units sold. Finally, for each product_id, we compute the average selling price by dividing the aggregated total price by the total units. The price is rounded to two decimal places as needed.
The SQL solution combines the Prices and UnitsSold tables on product_id and matches records where purchase_date is between start_date and end_date. The sum of the product of price and units is divided by the sum of units for computing average_price, ensuring rows are grouped by product_id.
Code
SQL
Complexity
Time Complexity: O(N * M), where N and M are the number of rows in Prices and UnitsSold tables respectively.
Space Complexity: O(N) for the resulting dataset.
Approach 2: Using In-Memory Data Structures
This approach entails loading data into dictionaries or maps to efficiently query and calculate required values. First, the Prices data is structured into a nested dictionary with product_id mappings to date-range-price tuples. Next, iterate over UnitsSold, and for each record, check applicable price entries based on purchase_date. Aggregate the results in terms of total price and total units, and eventually calculate the average price for each product.
This Python solution reads unit sale records and matches them against price periods using a nested for loop. For each sale, we compute the contribution to the total price and update the units count. At the end, we iterate over accumulated totals to compute average prices, handling no sales as a special case.
Code
Python
JavaScript
Complexity
Time Complexity: O(n * m), where n is the number of saless, each potentially being matched against m price periods.
Space Complexity: O(p), where p is the number of unique products due to resultant data storage.
Approach 3: Left Join + Grouping
We can use a left join to join the Prices table and the UnitsSold table on product_id, and the condition that purchase_date is between start_date and end_date. Then, we can use GROUP BY to group by product_id for aggregation, and use the AVG function to calculate the average price. Note that if a product has no sales records, the AVG function will return NULL, so we can use the IFNULL function to convert it to 0.
Code
MySQL
Complexity Comparison
| Approach | Complexity |
|---|---|
| Using SQL for Data Retrieval and Calculation | Time Complexity: O(N * M), where N and M are the number of rows in Prices and UnitsSold tables respectively. |
| Using In-Memory Data Structures | Time Complexity: O(n * m), where n is the number of saless, each potentially being matched against m price periods. |
| Left Join + Grouping | — |
Detailed Complexity Analysis
| Approach | Time | Space | When to Use |
|---|---|---|---|
| SQL Join + Aggregation | O(n) | O(1) | Best when solving directly in SQL or database interview questions |
| In-Memory Hash Map + Interval Check | O(p + s) | O(p) | When processing database rows in application code (Python/JavaScript) |
Video Solution
Average Selling Price | Leetcode 1251 | Crack SQL Interviews in 50 Qs #mysql #leetcode • Learn With Chirag • 20,764 views views
Watch 9 more video solutions →Frequently Asked Questions
Is Average Selling Price easy or hard?
How to solve Average Selling Price in O(n)?
Average Selling Price Python or Java solution
What is the best approach for Average Selling Price?
What data structure is used in Average Selling Price?
What is the time complexity of Average Selling Price?
Is Average Selling Price asked at Google, Amazon, or Meta?
Ready to solve this problem?
Practice Average Selling Price with our built-in code editor and test cases.
Practice on FleetCode