Product with sales increasing every year

A multinational Company is going through very bad timings and now want to focus on products which has shown increased sales and profit every year. They are looking for your help in identifying such products so that they can plan marketing campaign accordingly.


Input Table – Product_sales

sale_year product_id units_sold
2016 1 1000
2016 2 2000
2016 3 3000
2016 4 4000
2017 1 900
2017 2 4000
2017 3 2100
2017 4 3100
2018 1 800
2018 2 9000
2018 3 100
2018 4 50

 

Output Table – Profitable_products

product_id
2

 


Explanation:

In input table, only product_id = 2 has shown increased sales every consecutive year. In year 2016, 2017 and 2018 , sales has increased as 2000, 4000 and 9000 respectively. No other product in input table, has shown increased in sales every year consecutively.


DDLs:

CREATE TABLE product_sales ( sale_year INT , product_id INT , units_sold INT);
INSERT INTO product_sales VALUES (2016,1,1000);
INSERT INTO product_sales VALUES (2016,2,2000);
INSERT INTO product_sales VALUES (2016,3,3000);
INSERT INTO product_sales VALUES (2016,4,4000);
INSERT INTO product_sales VALUES (2017,1,900);
INSERT INTO product_sales VALUES (2017,2,4000);
INSERT INTO product_sales VALUES (2017,3,2100);
INSERT INTO product_sales VALUES (2017,4,3100);
INSERT INTO product_sales VALUES (2018,1,800);
INSERT INTO product_sales VALUES (2018,2,9000);
INSERT INTO product_sales VALUES (2018,3,100);
INSERT INTO product_sales VALUES (2018,4,50);

Solution:

SELECT 
A.product_id
FROM
( 
SELECT
product_id, 
CASE
WHEN units_sold > LAG(units_sold,1,1) OVER(PARTITION BY product_id ORDER BY sale_year ASC) THEN 1 
ELSE 0 
END AS flag 
FROM product_sales 
) A
GROUP BY 
A.product_id 
HAVING SUM(A.flag) = COUNT(A.product_id)
;

 

Leave a Comment