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) ;