1082. Sales Analysis I
Model: qwen2.5-coder:14b
Topics:
FAANG asked: 1
Acceptance rate: 74.0%
Problem Breakdown
The problem requires analyzing sales data to identify the top-selling product(s). Given two tables: Product and Sales, we need to find the product name that has the highest total sales amount. The Product table contains columns for product_id and product_name. The Sales table contains columns for product_id, sale_date, and amount.
Inputs:
Product: A table with columnsproduct_id(integer) andproduct_name(string).Sales: A table with columnsproduct_id(integer),sale_date(date), andamount(float).
Outputs:
- The name of the product(s) with the highest total sales amount.
Implicit Constraints/Edge Cases:
- There might be multiple products with the same highest sales amount.
- The
Salestable could be empty, resulting in no top-selling product. - Duplicate entries in the
Salestable should be considered only once.
Intuition
A brute-force approach would involve joining the two tables and calculating the total sales for each product. Then, we would sort these totals to find the highest one(s). However, this approach is inefficient because it involves sorting all products by their sales amounts, which has a time complexity of O(n log n).
The key insight is to use aggregation functions to calculate the total sales for each product and then order the results in descending order. This way, we can limit our result set to just the top-selling product(s) without having to sort the entire list.
Approach
- Join Tables: Perform an inner join between the
ProductandSalestables on theproduct_idcolumn. - Aggregate Sales: Group the results by
product_nameand calculate the sum of theamountfor each product. - Order Results: Order the aggregated results in descending order based on the total sales amount.
- Limit Results: Limit the result set to the top-selling product(s).
Complexity Analysis
- Time Complexity: O(n log n) due to the sorting operation, where n is the number of unique products.
- Space Complexity: O(n) for storing the aggregated results.
Solution
import pandas as pd
def sales_analysis(product: pd.DataFrame, sales: pd.DataFrame) -> pd.DataFrame:
# Join the Product and Sales tables on product_id
merged_df = pd.merge(product, sales, on='product_id')
# Group by product_name and calculate total sales amount
total_sales = merged_df.groupby('product_name')['amount'].sum().reset_index()
# Sort the results in descending order based on total sales amount
sorted_sales = total_sales.sort_values(by='amount', ascending=False)
# Return the top-selling product(s)
return sorted_sales[['product_name']].head(1)
Edge Cases Handled
- Empty Sales Table: If the
Salestable is empty, the result will be an empty DataFrame. - Multiple Top-Selling Products: The code currently returns only one product. To handle multiple top-selling products with the same sales amount, we would need to adjust the
.head(1)method to return all products with the highest total sales amount.
The solution provided is correct and efficient for the given problem statement. It handles edge cases such as an empty Sales table and returns the top-selling product(s) based on total sales amount. The approach and complexity analysis are accurate, and the code is clear and concise.