Query to find the product name which has a continuous increase in sales every year

Question

Product table with columns - PRODUCT_ID PRODUCT_NAME

PRODUCT_ID	PRODUCT_NAME
100	NOKIA
200	IPHONE
300	SAMSUNG
400	OPPO

Sales table with columns - SALE_ID PRODUCT_ID YEAR QUANTITY Price

SALE_ID	PRODUCT_ID	YEAR	QUANTITY	PRICE
1	100	2010	25	5000
2	100	2011	16	5000
3	100	2012	8	5000
4	200	2010	10	9000
5	200	2011	15	9000
6	200	2012	20	9000
7	300	2010	20	7000
8	300	2011	18	7000
9	300	2012	20	7000
10	400	2010	15	7000
11	400	2011	18	7000
12	400	2012	22	7000
13	400	2013	23	7000

Here Quantity is the number of products sold each year. Price is the sale price of each product. Write a SQL query to find the product name which has a continuous increase in sales every year?

Output: PRODUCT_NAME

IPHONE

OPPO

I tried the below query, it can solve if we have only one product_id with increasing value, but it fails when we have multiple product_ids with increasing sales values.

WITH cte1 AS
(
    SELECT a.*,
        (a.QUANTITY * a.PRICE) AS total_sales,
        LAG(a.QUANTITY * a.PRICE, 1, a.QUANTITY * a.PRICE) 
          OVER(PARTITION BY a.product_id ORDER BY year) as next_sales,
        B.PRODUCT_NAME
    FROM Sales AS a
    INNER JOIN Products AS b
       ON a.PRODUCT_ID = b.PRODUCT_ID
), 
cte2 AS
(
    SELECT *,
        (total_sales - next_sales) AS diff,
        CASE WHEN (total_sales - next_sales) > 0 THEN 1
             WHEN (total_sales - next_sales) < 0 THEN 0
             ELSE 0
        END AS val
    FROM cte1
),
cte3 AS
(
    SELECT
        PRODUCT_ID,
        SUM(val) AS max_val
    FROM cte2
    GROUP BY
        PRODUCT_ID
)
SELECT
    PRODUCT_ID
FROM cte3
WHERE max_val = (SELECT MAX(max_val) FROM cte3);

Answer 1

Here is yet another approach using a gaps and islands method. Here we consider years of increasing sales a block, and we only keep products that have a single such block. Products with two blocks for example have one increasing period, then a gap (a decrease), then another increasing period. (Blockks can also be one-liners, if we have decrease after decrease.) We get the block numbers by subtracting a product's row rank by sales from the years.

Illustration:

SALE_ID	PRODUCT_ID	YEAR	QUANTITY	PRICE	RANK	BLOCK
1	100	2010	25	5000	3	2007
2	100	2011	16	5000	2	2009
3	100	2012	8	5000	1	2011
4	200	2010	10	9000	1	2009
5	200	2011	15	9000	2	2009
6	200	2012	20	9000	3	2009
7	300	2010	20	7000	2	2008
8	300	2011	18	7000	1	2010
9	300	2012	20	7000	2	2010
10	400	2010	15	7000	1	2009
11	400	2011	18	7000	2	2009
12	400	2012	22	7000	3	2009
13	400	2013	23	7000	4	2009

Query:

select *
from products
where product_id in
(
  select product_id
  from
  (
    select
      product_id,
      year - rank() over (partition by product_id order by quantity * price) as block
    from sales
  ) blocks
  group by product_id
  having min(block) = max(block)
);

In this approach each table gets only read once which is generally regarded an advantage. You may still want to compare different methods, if you should run into performance issues.

Answer 2

You should keep it simple:

select *
from products p
  where not exists (select * 
  from sales s1
  inner join sales s2 on s1.Product_id = s2.Product_Id 
                  and s1.Year = s2.Year - 1
                  and s1.Quantity > s2.Quantity
                  where s1.Product_id = p.PRODUCT_ID);

Note that this SQL is compatible even with old versions where windowing functions were not supported.

Here is DBFiddle demo

PS: If your requirement is to check always for an increase (equal amount would be considered there wasn't an increase) then make that

s1.Quantity > s2.Quantity

s1.Quantity >= s2.Quantity

Here, we are checking per product if there is any year with less Quantity sold than the previous year (Year - 1). If there is such an entry than that product is eliminated with (where not exists) check.