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.
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.
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.
SQL
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.
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.
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.
MySQL
| Approach | Complexity |
|---|---|
| Using SQL Join | Time Complexity: O(N) where N is the number of rows in Sales table. |
| 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. |
| Default Approach | — |
| Approach | Time | Space | When to Use |
|---|---|---|---|
| SQL JOIN | O(n + m) | O(1) | Best for relational database queries where tables must be combined using keys |
| Hash Map Join in Code | O(n + m) | O(m) | When data is already loaded in memory and SQL is not available |
Product Sales Analysis I | Leetcode 1068 | Crack SQL Interviews in 50 Qs #mysql #leetcode • Learn With Chirag • 12,249 views views
Watch 9 more video solutions →Practice Product Sales Analysis I with our built-in code editor and test cases.
Practice on FleetCode