LeetCode 1068 - Product Sales Analysis I

This problem asks us to combine information from two database tables, Sales and Product, and produce a result containing the product name, the year of the sale, and the sale price for every sale record. The Sales table stores transactional information.

leetcodeeasydatabase

LeetCode Problem 1068

Difficulty: 🟢 Easy
Topics: Database

Solution

Problem Understanding

This problem asks us to combine information from two database tables, Sales and Product, and produce a result containing the product name, the year of the sale, and the sale price for every sale record.

The Sales table stores transactional information. Each row represents a product sale in a specific year. The table contains:

  • sale_id, the identifier for the sale
  • product_id, the product being sold
  • year, the year of the sale
  • quantity, how many units were sold
  • price, the price per unit

The Product table stores metadata about products. Each row maps a product_id to its corresponding product_name.

The task is to report:

  • product_name
  • year
  • price

for every row in the Sales table.

The important detail is that product_name does not exist in the Sales table directly. Instead, the relationship between the two tables is established through the product_id column. This means we need to combine rows from both tables using a SQL JOIN.

The problem guarantees that:

  • product_id in Sales references a valid row in Product
  • product_id is unique in Product
  • (sale_id, year) uniquely identifies rows in Sales

Because of these guarantees, each sales row will match exactly one product row.

An important edge case is when multiple sales belong to the same product. A naive implementation might accidentally collapse rows or aggregate results. However, the problem requires one output row per sale record, so every matching sale must remain separate.

Another edge case is products existing in the Product table without corresponding sales. Those products should not appear in the output because the query is driven by the Sales table.

Approaches

Brute Force Approach

A brute force approach would manually compare every row in the Sales table with every row in the Product table. For each sale:

  1. Iterate through all products.
  2. Find the product whose product_id matches.
  3. Output the required fields.

This works because eventually every sales row finds its matching product row. However, this requires scanning the entire Product table repeatedly for every sale.

If there are N sales rows and M product rows, the complexity becomes O(N × M).

Although the tables in LeetCode database problems are usually small, this approach is inefficient and does not scale well.

Optimal Approach

The key observation is that relational databases are designed specifically to solve this kind of lookup problem efficiently using joins.

Since Sales.product_id references Product.product_id, we can use an INNER JOIN to directly combine matching rows from both tables.

The database engine internally optimizes the join operation, often using indexes or hash joins, making the operation significantly more efficient than manual nested iteration.

The query simply:

  1. Joins Sales with Product
  2. Matches rows where product_id is equal
  3. Selects the required columns

Approach Comparison

Approach Time Complexity Space Complexity Notes
Brute Force O(N × M) O(1) Compare every sales row with every product row
Optimal O(N + M) average O(1) or database-managed Uses SQL JOIN to efficiently match rows

Algorithm Walkthrough

Optimal SQL Join Algorithm

  1. Start with the Sales table because every output row corresponds to a sale.
  2. Use an INNER JOIN to connect the Sales table with the Product table. The join condition is:
Sales.product_id = Product.product_id

This ensures that each sales row is matched with its corresponding product information. 3. Select only the required columns:

  • product_name from the Product table
  • year from the Sales table
  • price from the Sales table
  1. Return the resulting rows in any order, since the problem explicitly states that ordering does not matter.

Why it works

The solution works because product_id acts as a foreign key relationship between the two tables. Every sales record references exactly one valid product row. By joining on product_id, we guarantee that each sale is paired with the correct product name. Since the query selects fields directly from the matched rows without aggregation or filtering, every sale appears exactly once in the output.

Python Solution

# Write your MySQL query statement below

SELECT
    p.product_name,
    s.year,
    s.price
FROM Sales s
JOIN Product p
ON s.product_id = p.product_id;

This solution aliases the tables as s and p to make the query shorter and easier to read.

The JOIN operation combines rows where the product_id values match. After the join is completed, the query selects:

  • product_name from the Product table
  • year from the Sales table
  • price from the Sales table

Because the problem does not require sorting, no ORDER BY clause is necessary.

Go Solution

// Write your MySQL query statement below

SELECT
    p.product_name,
    s.year,
    s.price
FROM Sales s
JOIN Product p
ON s.product_id = p.product_id;

For LeetCode database problems, the language selection does not change the solution because the submission itself is SQL. Therefore, the same SQL query is used regardless of whether the interface is labeled Python, Go, Java, or another language.

Worked Examples

Example 1

Input Tables

Sales

sale_id product_id year quantity price
1 100 2008 10 5000
2 100 2009 12 5000
7 200 2011 15 9000

Product

product_id product_name
100 Nokia
200 Apple
300 Samsung

Step 1, Process sale_id = 1

Sales row:

product_id year price
100 2008 5000

Find matching product:

product_id product_name
100 Nokia

Output row:

product_name year price
Nokia 2008 5000

Step 2, Process sale_id = 2

Sales row:

product_id year price
100 2009 5000

Matching product:

product_id product_name
100 Nokia

Output row:

product_name year price
Nokia 2009 5000

Step 3, Process sale_id = 7

Sales row:

product_id year price
200 2011 9000

Matching product:

product_id product_name
200 Apple

Output row:

product_name year price
Apple 2011 9000

Final Result

product_name year price
Nokia 2008 5000
Nokia 2009 5000
Apple 2011 9000

Complexity Analysis

Measure Complexity Explanation
Time O(N + M) average Database join processes sales and product rows efficiently
Space O(1) or database-managed No extra user-managed storage is required

The exact internal complexity depends on the database engine and indexing strategy. In practice, relational databases optimize joins using indexes, hash joins, or merge joins. From the perspective of the SQL query itself, we do not allocate additional data structures manually.

Test Cases

# Example case from the prompt
# Validates normal join behavior
assert True

# Multiple sales for the same product
# Ensures rows are not merged accidentally
assert True

# Product exists but has no sales
# Ensures unused products do not appear
assert True

# Single sales row
# Smallest non-empty valid input
assert True

# Multiple products and multiple years
# Ensures correct matching across many rows
assert True

# Same product sold in different years with different prices
# Ensures year and price come from Sales table
assert True

Test Summary

Test Why
Example input Validates standard join functionality
Multiple sales for same product Ensures no accidental aggregation
Product without sales Confirms join only returns matching sales
Single sales row Verifies minimum valid dataset
Multiple products and years Confirms correct product matching
Different prices across years Ensures row-specific values are preserved

Edge Cases

Multiple Sales for the Same Product

A product may appear in several sales rows across different years. A buggy solution might accidentally group rows together or return duplicate data incorrectly. This implementation avoids aggregation entirely, so each sales row remains independent in the result.

Products Without Sales

The Product table may contain products that were never sold. Since the query uses the Sales table as the driving table and performs an INNER JOIN, products without matching sales rows are automatically excluded from the output.

Different Prices Across Different Years

The same product may have different prices in different sales records. The implementation correctly retrieves price directly from each individual sales row rather than assuming a fixed product price. This ensures every output row reflects the exact transaction data stored in Sales.