1070 Product Sales Analysis III

Description

Table: Sales

• (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

• 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.

Problem Statement

Write a solution to select the product_id, year, quantity, and price for the first year of every product sold.

Return the resulting table in any order. The result format is in the following example.

Example 1:

Input:

• Sales table:

• Product table:

Output:

Solution

This problem can be solved using multiple approaches.

Approach 1: Using Window Functions

In this problem, we can partition the data per product_id ordering the partitions by year column. This way, the first row will have the record for the first year of each product. So, we will have to filter the results by the row_num=1 condition.

The problem also states that if there are more than one sale for the same product in the same year, then we need to include all of them. If you use, ROW_NUMBER() function, you will get only one row even if there are more than one sale for the same product in the same year. Instead of ROW_NUMBER() function, you can find the row_num using RANK() or DENSE_RANK() window function. This will assign the same rank to all sales as long as they occur in the same year.

1WITH product_partitions AS (
2    SELECT product_id, year AS first_year, quantity, price,
3        DENSE_RANK() OVER (PARTITION BY product_id ORDER BY year) row_num
4        FROM Sales
5) SELECT product_id, first_year, quantity, price
6    FROM product_partitions
7    WHERE row_num = 1;

Approach 2: Using Subquery

1. Find the first year for each product. This you can find using MIN() function on year column.

1SELECT product_id, MIN(year) year
2    FROM Sales
3    GROUP BY product_id

2. Next, you need to find all the sales for the first year for each product. For this, you can use (product_id, year) combination which occurs for the first year for each product. This you can find using WHERE clause ensuring that product_id and year combinations are for the first year (i.e. the result of the subquery).

1WHERE (product_id, year) IN (
2    SELECT product_id, MIN(year) year
3        FROM Sales
4        GROUP BY product_id
5);

The final solution will be as below.

1SELECT product_id, year AS first_year, quantity, price
2    FROM Sales
3    WHERE (product_id, year) IN (
4        SELECT product_id, MIN(year) year
5            FROM Sales
6            GROUP BY product_id
7    );