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 ;