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.


 

Input Table : sales

sale_id product_id sale_year quantity price
20 80 2017 21 300
30 80 2018 23 400
40 13 2019 56 600

 

Output Table:

product_id first_year quantity price
80 2017 21 300
13 2019 56 600

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
;

 

Leave a Comment