Product Sales Analysis III

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 select the product id, year, quantity, and price for the first year of every product sold.

Return the resulting table in any order.

Example:

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_id | first_year | quantity | price |
+------------+------------+----------+-------+
| 100 | 2008 | 10 | 5000 |
| 200 | 2011 | 15 | 9000 |

SELECT 
    product_id,
    year AS first_year,
    quantity,
    price
FROM (
    SELECT 
        product_id,
        year,
        quantity,
        price,
        ROW_NUMBER() OVER(PARTITION BY product_id ORDER BY year ASC) as rn
    FROM Sales
) AS subquery
WHERE rn = 1;

Explanation:

1. Inner Subquery:

   • ROW_NUMBER() OVER(PARTITION BY product_id ORDER BY year ASC): This assigns a unique rank to each sale record within each product group, based on the year. The earliest year gets a rank of 1.
   • PARTITION BY product_id: This divides the sales data into partitions based on product_id. The ranking is done separately for each product.
   • ORDER BY year ASC: Within each product's partition, the sales records are ordered by year in ascending order. This ensures the earliest year gets the rank of 1.

2. Outer Query:

   • FROM (...) AS subquery: This selects from the results of the inner subquery.
   • WHERE rn = 1: This filters the results to only include records where the rank (rn) is 1, which corresponds to the first year of sales for each product.
   • SELECT product_id, year AS first_year, quantity, price: Finally, it selects the required columns: product_id, the year (aliased as first_year), quantity, and price for the earliest sale year of each product.