Watch 10 video solutions for Product Sales Analysis I, a easy level problem involving Database. This walkthrough by Learn With Chirag has 12,249 views views. Want to try solving it yourself? Practice on FleetCode or read the detailed text solution.
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.
| 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 |