Domain 2MediumMEDIUM

Product Sales Analysis III

Identify categories with at least 10 distinct products sold by joining Product and Sales tables, aggregating unique product counts per category, and filtering results.

Domain: medium•Updated: 2026-09-08
#pandas#merge#groupby#filtering

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 (O(N2)O(N^2) or O(N⋅M)O(N \cdot M) depending on implementation) due to repeated lookups. The optimal strategy leverages vectorized operations:

  1. Join: Associate sales with categories via product_id.
  2. Aggregate: Use built-in aggregation functions (like .nunique()) which are implemented in C/C++ under the hood, making them significantly faster than Python loops (O(N)O(N)).
  3. Filter: Apply a boolean mask to keep only groups meeting the threshold.

Approach

  1. Merge Tables: Perform an inner join between Product and Sales. This ensures we only analyze categories that have actual sales records, as unsold products do not contribute to “distinct products sold”.
  2. Group By Category: Group the merged data by the category column.
  3. Count Distinct Products: Use .nunique() on the product_id within each group. This handles duplicate sale entries for the same product automatically (counting them as one).
  4. Filter Thresholds: Select groups where the count is greater than or equal to 10.
  5. Format Output: Return a DataFrame with only the required columns (category).

Complexity Analysis

  • Time Complexity: O(N+M)O(N + M), where NN and MM are row counts of Sales and Product. The join operation is typically linear (O(N+M)O(N+M)) using hash joins in Pandas. Grouping and aggregation iterate through the joined result once. Filtering is also linear relative to group size.
  • Space Complexity: O(N+M)O(N + M) 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_df is 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 >= 10 strictly 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’ merge and groupby handle 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.