Domain 1EasyCORE

Average Selling Price

The problem requires calculating the average selling price for each product based on given sales data. The inputs are two tables: `Prices` and `UnitsSold`. The `Prices` table contains the product ID and its corresponding price, while the `UnitsSold` table contains the product ID and the number of units sold. The output should be a list of products with their average selling prices. If a product has no sales data, it should still appear in the result with an average price of 0.

Domain: Easy•Updated: 2026-08-31
#Algorithms#LeetCode#qwen2.5-coder:14b#**FAANG asked:** 1

1251. Average Selling Price

Model: qwen2.5-coder:14b
Topics:
FAANG asked: 1
Acceptance rate: 82.7%


Problem Breakdown

The problem requires calculating the average selling price for each product based on given sales data. The inputs are two tables: Prices and UnitsSold. The Prices table contains the product ID and its corresponding price, while the UnitsSold table contains the product ID and the number of units sold. The output should be a list of products with their average selling prices. If a product has no sales data, it should still appear in the result with an average price of 0.

Inputs:

  • Prices: A table with columns product_id (integer) and price (float).
  • UnitsSold: A table with columns product_id (integer) and units (integer).

Outputs:

  • A list of products with their average selling prices, including those with no sales data.

Implicit Constraints/Edge Cases:

  • Products may have no sales data.
  • The number of units sold can be zero.
  • There might be duplicate entries in the tables.

Intuition

The brute-force approach would involve joining the two tables on product_id and then calculating the average price for each product. However, this approach could lead to inefficient queries if the datasets are large, as it requires a full table scan and multiple aggregations.

A better approach is to use SQL window functions or subqueries to efficiently calculate the total revenue and total units sold for each product, and then compute the average selling price. This method reduces the complexity by avoiding unnecessary joins and aggregations.

Approach

  1. Join Tables: First, join the Prices table with the UnitsSold table on product_id. This will allow us to access both the price and units sold for each product.
  2. Calculate Total Revenue: For each product, calculate the total revenue by multiplying the price by the number of units sold.
  3. Aggregate Data: Use SQL window functions or subqueries to aggregate the total revenue and total units sold for each product.
  4. Compute Average Selling Price: Divide the total revenue by the total units sold to get the average selling price for each product. If a product has no sales data, ensure that the result is 0.

Complexity Analysis

  • Time Complexity: The time complexity is O(n + m), where n is the number of rows in the Prices table and m is the number of rows in the UnitsSold table. This is because we are performing a join operation followed by aggregation.
  • Space Complexity: The space complexity is O(n + m) for storing the intermediate results of the join and aggregation.

Solution

import pandas as pd

def average_selling_price(prices: pd.DataFrame, units_sold: pd.DataFrame) -> pd.DataFrame:
    # Merge the Prices and UnitsSold tables on product_id
    merged_df = prices.merge(units_sold, on='product_id', how='left')
    
    # Calculate total revenue for each product
    merged_df['total_revenue'] = merged_df['price'] * merged_df['units']
    
    # Group by product_id to calculate total revenue and total units sold
    aggregated_df = merged_df.groupby('product_id').agg(
        total_revenue=('total_revenue', 'sum'),
        total_units=('units', 'sum')
    ).reset_index()
    
    # Calculate average selling price, handling division by zero
    aggregated_df['average_price'] = aggregated_df.apply(lambda row: 
                                                        row['total_revenue'] / row['total_units'] if row['total_units'] > 0 else 0, axis=1)
    
    # Ensure all products are included in the result, even those with no sales data
    all_products = prices[['product_id']].drop_duplicates()
    final_df = all_products.merge(aggregated_df[['product_id', 'average_price']], on='product_id', how='left').fillna(0)
    
    return final_df[['product_id', 'average_price']]

Edge Cases Handled

  • Products with No Sales Data: The code ensures that products with no sales data are included in the result by merging with the prices table and filling missing values with 0.
  • Zero Units Sold: The code handles cases where the total units sold is zero to avoid division by zero errors, setting the average price to 0 for such products.