Domain 1EasyCORE

Sales Analysis III

The problem requires analyzing sales data to determine which products have been sold in every store. The inputs are two tables: `Sales` and `Product`. The `Sales` table contains records of product sales with columns for sale_id, product_id, store_id, and the date of the sale. The `Product` table contains a list of all products with their respective product_id and product_name. The output should be a list of product names that have been sold in every store.

Domain: Easy•Updated: 2026-08-31
#Algorithms#LeetCode#qwen2.5-coder:14b#**FAANG asked:** 1

1084. Sales Analysis III

Model: qwen2.5-coder:14b
Topics:
FAANG asked: 1
Acceptance rate: 54.7%


Problem Breakdown

The problem requires analyzing sales data to determine which products have been sold in every store. The inputs are two tables: Sales and Product. The Sales table contains records of product sales with columns for sale_id, product_id, store_id, and the date of the sale. The Product table contains a list of all products with their respective product_id and product_name. The output should be a list of product names that have been sold in every store.

Inputs:

  • Sales: A table with columns (sale_id, product_id, store_id, sale_date).
  • Product: A table with columns (product_id, product_name).

Outputs:

  • A list of product names from the Product table where each product has a corresponding entry in the Sales table for every unique store_id.

Implicit Constraints/Edge Cases:

  • The Sales table might be empty.
  • There could be duplicate entries in the Sales table.
  • The Product table might have products that are not sold in any store.
  • The number of stores can vary, and we need to ensure a product is sold in all of them.

Intuition

A brute-force approach would involve checking each product against every store to see if it has been sold. This approach would be inefficient with high time complexity due to the nested loops required for each product and store combination.

The key insight is to use SQL aggregation functions to count the number of unique stores where each product has been sold. By comparing this count to the total number of unique stores, we can determine if a product has been sold in every store.

Approach

  1. Identify Unique Stores: First, determine the total number of unique stores by querying the Sales table.
  2. Count Sales per Product per Store: Use a GROUP BY clause on product_id and store_id to count how many times each product has been sold in each store.
  3. Filter Products Sold in All Stores: Use HAVING COUNT(store_id) equal to the total number of unique stores to filter out products that have not been sold in every store.
  4. Join with Product Table: Finally, join the filtered results with the Product table to get the product names.

Complexity Analysis

  • Time Complexity: O(n), where n is the number of rows in the Sales table. The GROUP BY operation and subsequent filtering are linear operations over the sales data.
  • Space Complexity: O(m), where m is the number of unique products sold across all stores. This space is used to store intermediate results during the aggregation process.

Solution

# SQL query to solve the problem
SELECT p.product_name 
FROM Product p
JOIN (
    SELECT product_id 
    FROM Sales
    GROUP BY product_id, store_id
    HAVING COUNT(store_id) = (SELECT COUNT(DISTINCT store_id) FROM Sales)
) s ON p.product_id = s.product_id;

Edge Cases Handled

  • Empty Sales Table: If the Sales table is empty, no products will be sold in any store, and thus the query will return an empty result set.
  • Duplicate Entries in Sales: The GROUP BY operation inherently handles duplicates by counting each unique combination of product_id and store_id only once.
  • Products Not Sold in Any Store: Products that do not appear in the Sales table are excluded from the final result as they will not be part of the subquery results.
  • Varying Number of Stores: The query dynamically calculates the number of unique stores and ensures products are sold across all of them.