Problem Breakdown
The objective is to filter Product categories based on sales volume metrics. We utilize two relational inputs (Product, Sales) linked by product_id. The output must list only those categories where the number of distinct products sold meets a threshold (>= 10).
Inputs:
product_df: Contains product metadata including category and ID.sales_df: Contains transaction records with sale dates and product IDs.
Outputs:
A DataFrame containing the column category for all categories satisfying the sales volume condition. The count of products is used internally for filtering but typically not returned in standard LC 1070 output schemas unless explicitly requested (here we align with common expectations returning only category names).
Intuition
Iterating through rows manually to tally distinct items per category would be inefficient ( or depending on implementation) due to repeated lookups. The optimal strategy leverages vectorized operations:
- Join: Associate sales with categories via
product_id. - Aggregate: Use built-in aggregation functions (like
.nunique()) which are implemented in C/C++ under the hood, making them significantly faster than Python loops (). - Filter: Apply a boolean mask to keep only groups meeting the threshold.
Approach
- Merge Tables: Perform an inner join between
ProductandSales. This ensures we only analyze categories that have actual sales records, as unsold products do not contribute to “distinct products sold”. - Group By Category: Group the merged data by the
categorycolumn. - Count Distinct Products: Use
.nunique()on theproduct_idwithin each group. This handles duplicate sale entries for the same product automatically (counting them as one). - Filter Thresholds: Select groups where the count is greater than or equal to 10.
- Format Output: Return a DataFrame with only the required columns (
category).
Complexity Analysis
- Time Complexity: , where and are row counts of
SalesandProduct. The join operation is typically linear () using hash joins in Pandas. Grouping and aggregation iterate through the joined result once. Filtering is also linear relative to group size. - Space Complexity: for storing the intermediate merged DataFrame before grouping, plus space proportional to unique categories during aggregation.
Solution
import pandas as pd
def product_sales_analysis(product_df: pd.DataFrame, sales_df: pd.DataFrame) -> pd.DataFrame:
"""
Identifies categories with at least 10 distinct products sold.
Args:
product_df (pd.DataFrame): Columns ['product_id', 'category']
sales_df (pd.DataFrame): Columns ['sale_date', 'product_id']
Returns:
pd.DataFrame: DataFrame containing the category column for qualifying groups.
"""
# Step 1: Join Product table with Sales to associate product categories with transactions.
# We use inner join because we only care about sales that actually occurred (standard LC requirement).
merged_df = product_df.merge(
sales_df,
on='product_id',
how='inner'
)
if merged_df.empty:
return pd.DataFrame(columns=['category'])
# Step 2 & 3: Group by category and count distinct products sold.
# .nunique() counts unique product_ids per group, handling duplicates automatically.
grouped = merged_df.groupby('category', as_index=False).agg(
num_products=('product_id', 'nunique')
)
# Step 4: Filter for categories meeting the threshold of at least 10 distinct products.
result = grouped[grouped['num_products'] >= 10]
return result[['category']]
Edge Cases Handled
- Zero Sales: If
merged_dfis empty (no sales exist), returns an empty DataFrame with the correct column structure ('category') to prevent crashes on downstream processing. - Count Boundary (< 10): The filter condition
>= 10strictly excludes categories selling fewer than 10 distinct products, ensuring exact boundary compliance (9 excluded, 10 included). - Duplicate Sales: Using
.nunique()ensures that multiple sale records for the same product in a category count as only one “distinct product”. - Null Values: Pandas’
mergeandgroupbyhandle nulls gracefully; however, standard LC inputs guarantee non-null keys. If present, they are excluded from grouping logic naturally by default behavior unless specified otherwise.