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.
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.
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.
C++
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.
| 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. |
Product Sales Analysis I | Leetcode 1068 | Crack SQL Interviews in 50 Qs #mysql #leetcode • Learn With Chirag • 6,000 views views
Watch 9 more video solutions →Practice Product Sales Analysis I with our built-in code editor and test cases.
Practice on FleetCode