Product Sales Analysis – 3

Write a SQL query that selects the product id, sale year, quantity, and price for the first year of every product sold.

---

 

---

Explanation:

• product_id is Foreign Key and (sale_id, year) is primary key of “sales” table.

Fetch product id, sale year, quantity, and price for the first year of every product sold.

---

DDLs:

CREATE TABLE sales ( sale_id INT, product_id INT, sale_year INT, quantity INT, price INT );
INSERT INTO sales VALUES (20, 80, 2017, 21, 300);
INSERT INTO sales VALUES (30, 80, 2018, 23, 400);
INSERT INTO sales VALUES (40, 13, 2019, 56, 600);

---

Solution:

SELECT
 A.product_id,
 A.sale_year as first_year,
 A.quantity,
 A.price
FROM
 sales A
JOIN
 (SELECT product_id , min(sale_year) as sale_year from sales group by product_id) B
ON A.product_id = B.product_id
AND A.sale_year = B.sale_year
;