Product Sales Analysis I

Difficulty: Easy | Acceptance: 85.70% | Paid: No Topics: Database

Table: Sales +-------------+-------+ | Column Name | Type | +-------------+-------+ | sale_id | int | | product_id | int | | year | int | | quantity | int | +-------------+-------+ sale_id is the primary key of this table. product_id is a foreign key to Product table. Note: There are no duplicate rows in this table.

Table: Product +--------------+---------+ | Column Name | Type | +--------------+---------+ | product_id | int | | product_name | varchar | +--------------+---------+ product_id is the primary key of this table. Note: There are no duplicate rows in this table.

Write a solution to report the product_id, year, and quantity for each sale.

The result can be returned in any order.

• Examples
• Constraints
• Direct Selection
• Inner Join

Examples

Example 1:

Input: Sales table: +---------+------------+------+----------+ | sale_id | product_id | year | quantity | +---------+------------+------+----------+ | 1 | 100 | 2008 | 10 | | 2 | 100 | 2009 | 12 | | 7 | 200 | 2011 | 15 | +---------+------------+------+----------+ Product table: +------------+--------------+ | product_id | product_name | +------------+--------------+ | 100 | Nokia | | 200 | Apple | | 300 | Samsung | +------------+--------------+

Output: +------------+------+----------+ | product_id | year | quantity | +------------+------+----------+ | 100 | 2008 | 10 | | 100 | 2009 | 12 | | 200 | 2011 | 15 | +------------+------+----------+

Constraints

The Sales table may have up to 10^5 rows.
The Product table may have up to 10^5 rows.

Direct Selection

Intuition Since all the required columns (product_id, year, quantity) are present in the Sales table, we can simply select them directly without needing to join with the Product table.

Steps

• Select the product_id, year, and quantity columns from the Sales table.

SELECT product_id, year, quantity FROM Sales

Complexity

• Time: O(N) where N is the number of rows in the Sales table.
• Space: O(1) (excluding the space for the result set).
• Notes: This is the most efficient approach as it avoids the overhead of a join operation.

Inner Join

Intuition Although the columns exist in the Sales table, joining with the Product table ensures that we only report sales for products that actually exist in the Product catalog (referential integrity).

Steps

• Perform an inner join between the Sales table (aliased as s) and the Product table (aliased as p) on the product_id.
• Select the product_id, year, and quantity from the Sales table.

SELECT s.product_id, s.year, s.quantity FROM Sales s INNER JOIN Product p ON s.product_id = p.product_id

Complexity

• Time: O(N + M) or O(N log M) depending on the database optimizer’s join strategy, where N is rows in Sales and M is rows in Product.
• Space: O(1) (excluding the space for the result set).
• Notes: Slightly less performant than direct selection due to the join operation, but useful if filtering by product attributes is required.