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 columnsproduct_id(integer) andprice(float).UnitsSold: A table with columnsproduct_id(integer) andunits(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
- Join Tables: First, join the
Pricestable with theUnitsSoldtable onproduct_id. This will allow us to access both the price and units sold for each product. - Calculate Total Revenue: For each product, calculate the total revenue by multiplying the price by the number of units sold.
- Aggregate Data: Use SQL window functions or subqueries to aggregate the total revenue and total units sold for each product.
- 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
Pricestable and m is the number of rows in theUnitsSoldtable. 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
pricestable 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.