Skip to main content
Talentd

Product Sales Analysis I - Solution & Explanation

EasyDatabase6 min readAsked at: Amazon, Microsoft, Meta +4
Practice this problem

Problem Statement

Table: Sales

+-------------+-------+
| Column Name | Type  |
+-------------+-------+
| sale_id     | int   |
| product_id  | int   |
| year        | int   |
| quantity    | int   |
| price       | int   |
+-------------+-------+
(sale_id, year) is the primary key (combination of columns with unique values) of this table.
product_id is a foreign key (reference column) to Product table.
Each row of this table shows a sale on the product product_id in a certain year.
Note that the price is per unit.

 

Table: Product

+--------------+---------+
| Column Name  | Type    |
+--------------+---------+
| product_id   | int     |
| product_name | varchar |
+--------------+---------+
product_id is the primary key (column with unique values) of this table.
Each row of this table indicates the product name of each product.

 

Write a solution to report the product_name, year, and price for each sale_id in the Sales table.

Return the resulting table in any order.

The result format is in the following example.

 

Example 1:

Input: 
Sales table:
+---------+------------+------+----------+-------+
| sale_id | product_id | year | quantity | price |
+---------+------------+------+----------+-------+ 
| 1       | 100        | 2008 | 10       | 5000  |
| 2       | 100        | 2009 | 12       | 5000  |
| 7       | 200        | 2011 | 15       | 9000  |
+---------+------------+------+----------+-------+
Product table:
+------------+--------------+
| product_id | product_name |
+------------+--------------+
| 100        | Nokia        |
| 200        | Apple        |
| 300        | Samsung      |
+------------+--------------+
Output: 
+--------------+-------+-------+
| product_name | year  | price |
+--------------+-------+-------+
| Nokia        | 2008  | 5000  |
| Nokia        | 2009  | 5000  |
| Apple        | 2011  | 9000  |
+--------------+-------+-------+
Explanation: 
From sale_id = 1, we can conclude that Nokia was sold for 5000 in the year 2008.
From sale_id = 2, we can conclude that Nokia was sold for 5000 in the year 2009.
From sale_id = 7, we can conclude that Apple was sold for 9000 in the year 2011.

Approach Overview

Problem Overview: You are given two tables: Sales and Product. Each sale references a product_id, while the product table stores the corresponding product_name. The task is to return product_name, year, and price for every sale by matching records between the two tables.

Approach 1: Using SQL Join (O(n + m) time, O(1) extra space)

The direct solution is a relational join between the Sales and Product tables using product_id. The database engine scans both tables and matches rows where the keys are equal. In practice, modern SQL engines use optimized strategies such as hash join or indexed lookup, making the operation roughly O(n + m), where n is the number of rows in Sales and m is the number of rows in Product. You select only the required columns: product_name, year, and price. This approach is concise, readable, and exactly what SQL was designed for. It’s the expected solution for database interview questions involving table relationships. If you're practicing relational data problems, review concepts under SQL and Database.

Approach 2: Using Multiple Data Structures in Code (O(n + m) time, O(m) space)

If the same logic is implemented in a programming language instead of SQL, you simulate a join using a hash map. First iterate through the Product list and build a map from product_id → product_name. This preprocessing step takes O(m) time and space. Then iterate through each record in Sales, perform a constant-time hash lookup to retrieve the product name, and construct the result row containing product_name, year, and price. The second pass takes O(n) time. Hash-based lookups make the join efficient even for large datasets. This pattern appears frequently in system design tasks where relational joins must be implemented in application code. The key structure here is a hash table for constant-time lookups.

Recommended for interviews: For database-focused problems, interviewers expect the SQL JOIN solution because it demonstrates understanding of relational data modeling and query composition. The hash map simulation is useful when the data is already loaded in memory and SQL is not available. Showing both approaches proves you understand the underlying join operation, not just the syntax.

Approach 1: Using SQL Join

We will use an SQL query with an INNER JOIN to combine the Sales and Product tables based on the product_id. INNER JOIN will ensure we fetch records that have corresponding IDs in both tables. After joining, we will project the columns product_name, year, and price.

This SQL query selects the product_name from the Product table and year and price from the Sales table. We use INNER JOIN to join these two tables based on product_id.

Code

SQL

SQL

Complexity

Time Complexity: O(N) where N is the number of rows in Sales table.
Space Complexity: O(1) as we use a fixed amount of extra space for the query execution.

Try this approach in the editor →

Approach 2: Using Multiple Data Structure in Code

This approach involves using programming constructs to emulate a join operation typically done in SQL. We'll utilize hash maps (or dictionaries) in different languages to store data from one table and then iterate through the other to join and construct the result.

We create a dictionary mapping product_id to product_name. Then, we iterate through the Sales table, using the mapped dictionary to find the relevant product name and appending this data to a result list.

Code

PythonC++

Python

C++

Complexity

Time Complexity: O(N + M) where N is the number of products and M is the number of sales.
Space Complexity: O(N) to store the dictionary mapping product_id to product_name.

Try this approach in the editor →

Approach 3: Default Approach

Code

MySQL

MySQL

Try this approach in the editor →

Complexity Comparison

ApproachComplexity
Using SQL Join

Time Complexity: O(N) where N is the number of rows in Sales table.
Space Complexity: O(1) as we use a fixed amount of extra space for the query execution.

Using Multiple Data Structure in Code

Time Complexity: O(N + M) where N is the number of products and M is the number of sales.
Space Complexity: O(N) to store the dictionary mapping product_id to product_name.

Default Approach—

Detailed Complexity Analysis

ApproachTimeSpaceWhen to Use
SQL JOINO(n + m)O(1)Best for relational database queries where tables must be combined using keys
Hash Map Join in CodeO(n + m)O(m)When data is already loaded in memory and SQL is not available

Video Solution

Product Sales Analysis I | Leetcode 1068 | Crack SQL Interviews in 50 Qs #mysql #leetcode • Learn With Chirag • 13,701 views views

Watch 9 more video solutions →

Frequently Asked Questions

Product Sales Analysis I Python solution
In Python, first create a dictionary mapping product_id to product_name from the Product list. Then iterate through the Sales list and append product_name, year, and price to the result using dictionary lookups. This approach runs in O(n + m) time and O(m) space.
Is Product Sales Analysis I easy or hard?
Product Sales Analysis I is classified as an Easy problem with a high acceptance rate around 85%. The task mainly tests basic SQL join knowledge or simple hash map usage to combine two datasets.
How to solve Product Sales Analysis I in O(n)?
Build a hash map from product_id to product_name using the Product dataset, which takes O(m) time. Then iterate through the Sales dataset and use constant-time hash lookups to attach the product name to each sale. The total complexity becomes O(n + m) with O(m) extra space.
What is the best approach for Product Sales Analysis I?
The best approach is using an SQL INNER JOIN between the Sales and Product tables on product_id. This directly retrieves product_name, year, and price in one query. Database engines optimize joins internally, giving near O(n + m) performance depending on indexing and execution strategy.
What data structure is used in Product Sales Analysis I?
The SQL solution relies on relational joins implemented internally by the database engine. When implemented in application code, a hash table (dictionary or unordered_map) is used to map product_id to product_name for constant-time lookups.
What is the time complexity of Product Sales Analysis I?
The typical complexity is O(n + m), where n is the number of rows in the Sales table and m is the number of rows in the Product table. SQL engines usually perform a hash join or indexed lookup to match product_id values efficiently.
Is Product Sales Analysis I asked at Google, Amazon, or Meta?
Database join problems similar to Product Sales Analysis I appear frequently in interviews at large tech companies. They test understanding of relational schemas, foreign keys, and how to combine datasets using joins or hash maps.

Ready to solve this problem?

Practice Product Sales Analysis I with our built-in code editor and test cases.

Practice on FleetCode
Previous

Product Sales Analysis II

Next

Product Sales Analysis IV