1083. Sales Analysis II
Model: qwen2.5-coder:14b
Topics:
FAANG asked: 1
Acceptance rate: 50.8%
Problem Breakdown
The problem “Sales Analysis II” requires us to analyze sales data from two tables: Product and Sales. The goal is to find the names of products that have never been sold.
Inputs:
- Two tables:
Product: Contains columnsproduct_id(integer) andproduct_name(string).Sales: Contains columnssale_id(integer),product_id(integer), andquantity(integer).
Outputs:
- A list of product names from the
Producttable that do not have any corresponding entries in theSalestable.
Implicit Constraints/Edge Cases:
- The
Producttable may be empty. - The
Salestable may be empty. - There might be duplicate entries in either table, but we are only interested in unique product names.
- The
product_idin both tables is a foreign key relationship.
Intuition
To solve this problem, we need to identify products that have no sales records. A brute-force approach would involve iterating through each product and checking if it exists in the Sales table. This would be inefficient with high time complexity (O(n^2)) because for each product, we might need to scan the entire Sales table.
A better approach is to use a set difference operation. We can extract all unique product_ids from the Product table and then subtract those found in the Sales table. This will leave us with the product_ids of products that have never been sold. Finally, we map these product_ids back to their corresponding names.
Approach
- Extract Product IDs: Retrieve all unique
product_ids from theProducttable. - Extract Sold Product IDs: Retrieve all unique
product_ids from theSalestable. - Find Unsold Products: Compute the set difference between the product IDs from step 1 and step 2 to get the
product_ids of unsold products. - Map IDs to Names: Use the resulting
product_ids to fetch the correspondingproduct_names from theProducttable.
Data Structures:
- Use sets for efficient lookups and differences.
- Use a list comprehension or similar construct to map product IDs to names.
Complexity Analysis
- Time Complexity: O(n + m), where n is the number of products and m is the number of sales. Extracting unique IDs from both tables takes linear time, and computing the set difference also takes linear time.
- Space Complexity: O(n + m) for storing the sets of product IDs.
Solution
def find_unsold_products(product_table, sales_table):
# Step 1: Extract all unique product IDs from the Product table
all_product_ids = {product['product_id'] for product in product_table}
# Step 2: Extract all unique product IDs from the Sales table
sold_product_ids = {sale['product_id'] for sale in sales_table}
# Step 3: Find the difference to get unsold product IDs
unsold_product_ids = all_product_ids - sold_product_ids
# Step 4: Map these IDs back to product names
unsold_products = [product['product_name'] for product in product_table if product['product_id'] in unsold_product_ids]
return unsold_products
# Example usage:
product_table = [
{'product_id': 1, 'product_name': 'Laptop'},
{'product_id': 2, 'product_name': 'Smartphone'},
{'product_id': 3, 'product_name': 'Tablet'}
]
sales_table = [
{'sale_id': 101, 'product_id': 1, 'quantity': 5},
{'sale_id': 102, 'product_id': 2, 'quantity': 3}
]
print(find_unsold_products(product_table, sales_table)) # Output: ['Tablet']
Edge Cases Handled
- Empty Product Table: If the
Producttable is empty, the function will return an empty list. - Empty Sales Table: If the
Salestable is empty, all products from theProducttable are considered unsold. - Duplicate Entries: The use of sets automatically handles duplicate entries by ensuring uniqueness.