{x}
blog image

Product Sales Analysis I

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.

Solution Explanation for LeetCode Problem 1068: Product Sales Analysis I

This problem requires querying two tables, Sales and Product, to generate a report showing product name, year, and price for each sale. The solution leverages a JOIN operation to combine relevant information from both tables.

Approach

The core idea is to join the Sales and Product tables based on the product_id column, which serves as a foreign key in Sales referencing the primary key in Product. This join combines each sale record with its corresponding product name. The query then selects the desired columns (product_name, year, and price) from the combined result.

SQL Query (MySQL)

SELECT product_name, year, price
FROM
    Sales
    JOIN Product USING (product_id);

This query uses an implicit JOIN using USING (product_id). This syntax simplifies the query when the join condition involves columns with the same name in both tables. It's equivalent to the more explicit INNER JOIN syntax:

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

Both achieve the same result. The INNER JOIN ensures that only sales with matching product IDs in both tables are included in the output. Rows in Sales where product_id doesn't exist in Product (and vice-versa) are excluded.

Time and Space Complexity Analysis

The time complexity of this query is dominated by the JOIN operation. In the worst case, the time complexity is O(N*M), where N is the number of rows in Sales and M is the number of rows in Product. However, database systems employ highly optimized algorithms for joins (like hash joins or merge joins), making the actual performance significantly better than a naive nested-loop implementation. The time complexity would be closer to O(N + M) in optimized scenarios.

The space complexity is O(K), where K is the number of rows in the result set. This depends on the size of the input tables and the number of sales entries with matching product IDs. The space used by the database system to store intermediate results during the join operation is also a factor but is usually handled efficiently by the database system's query optimizer. Generally the space is linear to the size of the output.