Domain 1EasyCORE

Product Sales Analysis II

The problem "Product Sales Analysis II" requires us to analyze sales data from two tables: `Sales` and `Product`. The goal is to find the total sales amount for each product. The `Sales` table contains records of individual sales with columns for sale_id, product_id, quantity, and price. The `Product` table contains product details including product_id and product_name.

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

1069. Product Sales Analysis II

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


Problem Breakdown

The problem “Product Sales Analysis II” requires us to analyze sales data from two tables: Sales and Product. The goal is to find the total sales amount for each product. The Sales table contains records of individual sales with columns for sale_id, product_id, quantity, and price. The Product table contains product details including product_id and product_name.

Inputs:

  • Sales table with columns: sale_id (int), product_id (int), quantity (int), price (float)
  • Product table with columns: product_id (int), product_name (varchar)

Outputs:

  • A result set with two columns: product_name (varchar) and total_sales (float), representing the total sales amount for each product.

Implicit Constraints or Tricky Edge Cases:

  • The Sales table might be empty, resulting in no output.
  • There could be duplicate entries in the Sales table that need to be aggregated.
  • Negative values for quantity or price are not expected and should be handled gracefully (though typically such data would be validated before insertion).

Intuition

A brute-force approach would involve iterating through each row in the Sales table, calculating the total sales for each product, and then joining this result with the Product table to get the product names. However, this approach is inefficient as it requires multiple passes over the data and does not leverage SQL’s strengths.

The key insight is to use SQL’s aggregation capabilities to sum up the sales amounts directly in a single query. This leverages the database engine’s optimized execution plans for such operations, making it both time-efficient and concise.

Approach

  1. Join Tables: Perform an inner join between the Sales table (aliased as T1) and the Product table (aliased as T2) on the product_id column to combine sales data with product names.
  2. Aggregate Data: Use the SUM function to calculate the total sales amount for each product by multiplying the quantity and price columns.
  3. Group Results: Group the results by product_name to ensure that the total sales are calculated per product.

Complexity Analysis

  • Time Complexity: O(n), where n is the number of rows in the Sales table. This is because each row needs to be processed once during the aggregation.
  • Space Complexity: O(m), where m is the number of unique products. This is due to the storage required for grouping results by product name.

Solution

SELECT 
    T2.product_name, 
    SUM(T1.quantity * T1.price) AS total_sales
FROM 
    Sales AS T1
JOIN 
    Product AS T2 ON T1.product_id = T2.product_id
GROUP BY 
    T2.product_name;

Edge Cases Handled

  • Empty Sales Table: The query will return an empty result set as there are no sales to aggregate.
  • Duplicate Sales Records: The use of SUM ensures that duplicate records are aggregated correctly, summing up their contributions to the total sales for each product.