Average Selling Price

Difficulty: Easy | Acceptance: 37.30% | Paid: No Topics: Database

Table: Prices

(product_id, start_date, end_date) is the primary key (combination of columns with unique values) of this table. Table: UnitsSold

There is no primary key (column with unique values) for this table. It may contain duplicates. Each row of this table indicates that the product with the given product_id was sold in units units on the given purchase_date.

Write a solution to find the average selling price for each product. The average selling price is equal to the total price of all sales of the product divided by the total number of units sold.

The results should be rounded to two decimal places. Return the result table in any order.

The result format is in the following example.

• Examples
• Constraints
• Approach 1: Hash Map Aggregation
• Approach 2: Brute Force Iteration

Examples

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 for product 1 is ((5 * 100) + (20 * 15)) / (100 + 15) = 500 + 300 / 115 = 800 / 115 = 6.96
Average selling price for product 2 is ((15 * 200) + (30 * 30)) / (200 + 30) = 3000 + 900 / 230 = 3900 / 230 = 16.96

Constraints

Prices table:
- 1 <= product_id <= 1000
- start_date and end_date are between 2000-01-01 and 2099-12-31 inclusive.
- price is between 1 and 1000 inclusive.

UnitsSold table:
- 1 <= product_id <= 1000
- purchase_date is between 2000-01-01 and 2099-12-31 inclusive.
- units is between 1 and 1000 inclusive.

Approach 1: Hash Map Aggregation

Intuition We can simulate the SQL GROUP BY and JOIN operations using a hash map. First, we organize the price ranges by product_id for quick lookup. Then, we iterate through the sales records, find the applicable price for each sale, and accumulate the total revenue and total units per product.

Steps

• Create a map where the key is product_id and the value is a list of price ranges (start_date, end_date, price).
• Initialize a result map to store total_revenue and total_units for each product.
• Iterate through the UnitsSold data. For each record:
  • Retrieve the list of price ranges for the corresponding product_id.
  • Find the range where purchase_date falls between start_date and end_date.
  • If a valid price is found, update the result map: total_revenue += price * units and total_units += units.
• Finally, calculate the average price for each product (total_revenue / total_units), round to 2 decimal places, and format the output.

from collections import defaultdict

class Solution:
    def averageSellingPrice(self, prices: list[list], unitsSold: list[list]) -> list[list]:
        # Map product_id -> list of (start_date, end_date, price)
        price_map = defaultdict(list)
        for p in prices:
            pid, start, end, price = p
            price_map[pid].append((start, end, price))
        
        # Map product_id -> [total_revenue, total_units]
        stats = defaultdict(lambda: [0, 0])
        
        for u in unitsSold:
            pid, purchase_date, units = u
            if pid not in price_map:
                continue
            
            # Find matching price range
            # Assuming dates are strings in YYYY-MM-DD format, they are lexicographically comparable
            for start, end, price in price_map[pid]:
                if start <= purchase_date <= end:
                    stats[pid][0] += price * units
                    stats[pid][1] += units
                    break
        
        result = []
        for pid in sorted(stats.keys()):
            revenue, total_units = stats[pid]
            if total_units > 0:
                avg = round(revenue / total_units, 2)
                result.append([pid, avg])
        
        return result

Complexity

• Time: O(N + M * K), where N is the number of price entries, M is the number of sales, and K is the average number of price ranges per product.
• Space: O(N + M) to store the maps.
• Notes: This approach efficiently handles the join logic by grouping data first, reducing the need for nested loops over the entire dataset.

Approach 2: Brute Force Iteration

Intuition For every sale record, iterate through all price records to find a match. This is the most straightforward implementation of the logic but is less efficient than using a hash map for lookups.

Steps

• Initialize a map to store total_revenue and total_units for each product.
• Iterate through each record in UnitsSold.
• For each sale, iterate through every record in Prices.
• Check if product_id matches and if purchase_date falls within the date range.
• If a match is found, update the totals and break the inner loop.
• Calculate averages and format the result.

from collections import defaultdict

class Solution:
    def averageSellingPrice(self, prices: list[list], unitsSold: list[list]) -> list[list]:
        stats = defaultdict(lambda: [0, 0])
        
        for u in unitsSold:
            u_pid, u_date, u_units = u
            found = False
            for p in prices:
                p_pid, p_start, p_end, p_price = p
                if p_pid == u_pid and p_start <= u_date <= p_end:
                    stats[u_pid][0] += p_price * u_units
                    stats[u_pid][1] += u_units
                    found = True
                    break
        
        result = []
        for pid in sorted(stats.keys()):
            revenue, total_units = stats[pid]
            if total_units > 0:
                avg = round(revenue / total_units, 2)
                result.append([pid, avg])
        
        return result

Complexity

• Time: O(M * N), where M is the number of sales and N is the number of price entries. This is because for every sale, we scan the entire price list.
• Space: O(M) to store the result statistics.
• Notes: This approach is simple to implement but inefficient for large datasets compared to the Hash Map approach.