Query to find out the cities where covid cases are increasing continuously.

---Write a query to find out the cities where covid cases are increasing continuously. 

create table covid(city varchar(50),days date,cases int);

delete from covid;

insert into covid values('DELHI','2022-01-01',100);

insert into covid values('DELHI','2022-01-02',200);

insert into covid values('DELHI','2022-01-03',300);

insert into covid values('MUMBAI','2022-01-01',100);

insert into covid values('MUMBAI','2022-01-02',100);

insert into covid values('MUMBAI','2022-01-03',300);

insert into covid values('CHENNAI','2022-01-01',100);

insert into covid values('CHENNAI','2022-01-02',200);

insert into covid values('CHENNAI','2022-01-03',150);

insert into covid values('BANGALORE','2022-01-01',100);

insert into covid values('BANGALORE','2022-01-02',300);

insert into covid values('BANGALORE','2022-01-03',200);

insert into covid values('BANGALORE','2022-01-04',400);

SELECT * FROM covid

---Write a query to find out the cities whare covid cases are increasing continously.

--Solution----

--Approach

--1-finding the that each date wise covid cases are increasing 

--2-By using Rank function we can find that cases are increasing or not (By date wise also)

--3- Now when the both the rank is same (Means case wise and days wise its increasing )

--4--Want to reflect the city only so group by & taken the difference between both rank so it will be taken only ZEROS (No difference between rank

--5--Find the diff is 0 & count of all these diff should be 1.

WITH CTE_1 AS 

(

SELECT *, RANK() OVER (PARTITION BY city order by days) rn_days,

RANK() OVER (PARTITION BY city order by cases) rn_cases,RANK() OVER (PARTITION BY city order by days) - RANK() OVER (PARTITION BY city order by cases) AS DIFF

FROM covid

) 

SELECT city from CTE_1

GROUP BY city

having count(distinct (DIFF))=1 AND MAX(DIFF)=0 ---

---solution end---