Beauty of SQL RANK Function | SQL Interview Question and Answers | Covid Cases

with cte as (
select
*,
case when
lag(cases,1,1) over(partition by city order by days) < cases
then 1 else 0 end as rn
from covid
)
select city
from cte
group by city
having count(1) = sum(rn)

My Approach -
with cte_rn as (
select
city, days, cases,
rank() over (partition by city order by days asc) as days_rn,
rank() over (partition by city order by cases asc) as cases_rn,
abs(days_rn - cases_rn) as diff
from covid )
select city from cte_rn
group by city having sum(diff) = 0;

My Solution using LEAD and group by

select city
from(
select *,
lead(cases) over(partition by city order by days) as next_day_cases
from covid) a
group by city
having count(*) = sum(case when (next_day_cases is null or next_day_cases > cases) then 1 else 0 end)

Hi Ankit, Thanks for the video.
Below is my approach:

WITH CTE AS(SELECT *,
CASE WHEN CASES - LAG(CASES,1,0) OVER(PARTITION BY CITY ORDER BY DAYS ) > 0 THEN 1 ELSE 0 END AS CHK
FROM COVID)

SELECT CITY
FROM CTE
GROUP BY 1
HAVING COUNT(DISTINCT CHK) = 1 AND SUM(DISTINCT CHK) <> 0

My solution On Mysql

with cte as (
select city
from
(select *,
if(v=t , "Yes","NO") as logic
from
(
select *,cast(casess as signed) as v,cast(dayss as signed) as t
from
(
select *,
rank() over(partition by city order by cases ) as casess,
rank() over(partition by city order by days) as dayss

from covid
) as table1) t2) t3
where logic = "no"
group by city)
select distinct city
from covid
where city not in (select * from cte)

with cte as (select *, ROW_NUMBER() OVER (partition by city order by cases) as row
from covid order by city, days),

cte2 as (select *,
CASE WHEN LAG(row) OVER (partition by city) = row-1 then TRUE
ELSE FALSE END as lag
from cte)

select city from cte2 where lag = TRUE group by city having count(lag) >= 2;

my approach
MS SQL:

with cte as (
select city, (case when cases>lag(cases, 1, 0) over(partition by city order by days) then 1 else 0 end) as flag from covid
)
select distinct city from cte where city not in (select city from cte where flag=0)

Ankit thanks for the rank method. I tried using lag method below:
with cte as(select *
,lag(cases,1,0) over(partition by city order by days) as prev_cases
,cases-lag(cases,1,0) over(partition by city order by days) as cases_diff
from covid)
select city
from cte
group by city
having count(case when cases_diff<=0 then cases_diff end)=0

with cte as(
select [city], RANK() over(partition by city order by cases) as rnk_cases,
RANK() over(partition by city order by [days]) as rnk_days,
case when RANK() over(partition by city order by cases) = RANK() over(partition by city order by [days]) then 1
else 0 end as inc_order
from [dbo].[covid]
),
cte2 as (
select city, COUNT(distinct inc_order) as cnt from cte
group by [city]
)
select city from cte2 where cnt=1

WITH CTE AS(
SELECT *,
ABS(RANK() OVER(PARTITION BY city ORDER BY days)-RANK() OVER(PARTITION BY city ORDER BY cases)) AS diff
FROM covid)

SELECT city FROM CTE
GROUP BY city HAVING SUM(DIFF)=0

with cte as (select city,DAY(days) as dt,rank() over(partition by city order by cases) as rn
from covid order by city,days)

select city from(select city,dt-rn*1.0 as diff from cte) as t group by city having count( distinct diff)=1

SELECT city
FROM (
SELECT city,
LAG(cases, 1) OVER (PARTITION BY city ORDER BY days) as prev_day_cases,
cases
FROM CovidData
) as SubQuery
GROUP BY city
HAVING COUNT(CASE WHEN cases > prev_day_cases THEN 1 END) = COUNT(*) - 1

select city from (select * from (select *,
lead(cases,1) over (partition by city order by days) as next_1,
lead(cases,2) over (partition by city order by days) as next_2
from covid) a where (a.next_1 is not null) and (a.next_2 is not null)) b where b.cases<b.next_1 and b.next_1<b.next_2

WITh get_cases_info AS(
SELECT
*,
LEAD(cases, 1) OVER(PARTITION BY city ORDER BY days) AS next_cases
FROM covid),
get_case_diff AS(
SELECT
*,
next_cases - cases AS diff_case
FROM get_cases_info),
identify_city AS(
SELECT
city,
COUNT(CASE WHEN diff_case <= 0 THEN 1 END) AS cnt
FROM get_case_diff
GROUP BY city)
SELECT
city FROM identify_city
WHERE cnt=0;

with cte1 as(
select *,lead(cases,1)over(partition by city order by days) nextm
from covid),
cte2 as(
select *,(nextm-cases) as [diff] from cte1
where nextm-cases<=0)
select distinct(city) from cte1 where city not in (select city from cte2)
SIR kindly comment on this approach

with cte as (
select city, days, cases,
lag(cases, 1, 0) over (partition by city order by days) as pre_cases
from covid
)
select distinct city
from cte
where cases > pre_cases
order by city, days;

select city from(
select * ,
rank() over(partition by city order by cases) as rn,
row_number() over(partition by city order by days,cases) as row_no,
case when rank() over(partition by city order by cases) <> row_number() over(partition by city order by days,cases) then 1 else 0 end as flag
from covid) as a
group by city
having count(distinct flag) =1

slightly different approach

SELECT city, sum(disc) ,count(*)-1
from (
SELECT *,
lead(cases,1) over (partition by city order by days) as lead_cases ,
case when lead(cases,1) over (partition by city order by days)> cases then 1 else 0 end as disc
from covid)A
group by city
having sum(disc) = count(*)-1

create temp table uj as
(
select city , (lead(cases) over(partition by city order by days ) - cases) as ld from covid );

select distinct city from covid where city not in
( select distinct city from uj where ld < 0 or ld = 0)

Hey ankit, thanks for posting this. Below is my solution in mysql workbench :
with cte1 as(
select *,
lag(cases,1,0) over(partition by city order by days) as prev
from covid)
select city
from cte1
group by city
having SUM(case when cases>prev then 0 else 1 end)=0

My solution
with cte as
(
select *,
lead(cases) over(partition by city order by days) as next_day,
lead(cases) over(partition by city order by days) - cases as diff
from covid
),
cte2 as
(
select distinct city
from cte
where diff <= 0
)
select distinct city from covid
where city not in (select city from cte2)

Hello sir, here avg(diff) =0 should be satisfied by bangalore and chennai right

with cte1 as
(
select city,
rank()over(partition by city order by days asc) as rn_days,
rank()over(partition by city order by cases asc) as rn_cases,
rank()over(partition by city order by days asc)-rank()over(partition by city order by cases asc) as diff
from covid
)
select city from cte1
group by city
having count(distinct diff)=1 and max(diff)=0 and min(diff)=0

I have taken absolute difference then aggregated(sum)

with cte1 as
(
select city,
rank()over(partition by city order by days asc) as rn_days,
rank()over(partition by city order by cases asc) as rn_cases,
abs(rank()over(partition by city order by days asc)-rank()over(partition by city order by cases asc)) as diff
from covid
)
select city from cte1
group by city
having sum(diff)=0

create temp table t1 as
(
select distinct city from
(
select city,(row_number()over (partition by city order by cases) - rank()over (partition by city order by cases)) as rk from covid
)
where rk=1 );
select distinct city from covid where city not in (select * from t1)

select city,sum(flag) from (
select *,lag(cases,1,0) over(partition by city order by days) as prev,
case when lag(cases,1,0) over(partition by city order by days) < cases then 0 else 1 end as flag
from covid) a
group by city
having sum(flag)= 0;

My approach though I had to take help from the video once:

SELECT CITY FROM (SELECT *, RANK() OVER(PARTITION BY CITY ORDER BY DAYS) AS RN_D,RANK() OVER(PARTITION BY CITY ORDER BY CASES) AS RN_CASES,
CAST(RANK() OVER(PARTITION BY CITY ORDER BY DAYS) AS SIGNED)-CAST(RANK() OVER(PARTITION BY CITY ORDER BY CASES) AS SIGNED) AS DIFF
FROM COVID) T1 GROUP BY CITY HAVING MAX(DIFF)=MIN(DIFF);

With cte as
(
Select *,lag(cases,1,0) over(partition by city order by days asc) as previous_cases from covid
)
Select city from cte
group by city
having sum(case when cases>previous_cases then 0 else 1 end)=0;
Hi Ankit,solve the same using lag function above.

When I'm doing rank1 - rank2 it is giving me error that
Error 1690 begint unsigned value out of range in

How to solve it ?

with cte1 as
(select *,lag(cases,1,0) over(partition by city order by days) cases2,
count(*) over(partition by city) cnt from covid)
,cte2 as
(select city,cnt,count(*) cnt2 from cte1 where cases>cases2 group by city)
select city from cte2 where cnt=cnt2
(easy logical answer)

with cities as (select *, lag(cases,1,1) over(partition by city order by days) as prev_day_case from covid),
filter_city as (select *, case when cases-prev_day_case <= 0 then 'Decreasing' else 'Increasing' end as trend from cities order by city,days)
select distinct city from cities where city not in (select distinct city from filter_city where trend = 'Decreasing');

My solution is :
with cte as (
select *
, lead(cases) over (partition by city order by days,cases) nxt
from covid)
, cte2 as (select city from cte where cases-nxt>=0)
select distinct city from cte where city not in (select city from cte2)

with cte1 as (select *,row_number() over(partition by city order by days) as row_number,
dense_rank() over(partition by city order by cases) as dense_rank
from covid),

cte2 as (select *,case when row_number=dense_rank then 'true' else 'false' end as test from cte1)

select * from cte2 where city not in (select distinct city from cte2 where test='false')

with cte as (
select city, rank() over(partition by city order by days) as days_rank, rank() over(partition by city order by cases) as cases_rank, days_rank - cases_rank as diff
from covid)
select city from cte
group by city having count(distinct(diff)) = 1

My Submission using Lag function


with cte as (
select *,
case
when lag(cases) over(Partition by city order by days) < cases then 'yes'
when lag(cases) over(Partition by city order by days) is null then 'yes'
else 'no'
end as increasing_day
from covid
)

select City
from cte
group by city
having count(distinct increasing_day) = 1

COUNT(DISTTINCT(diff))=1 should be good enough, but what happens when there is a new city with one date record , we should add min(days)!=max(days) to the having condition

SELECT city
FROM
(SELECT *,
lag(cases, 1) over(PARTITION BY city
ORDER BY days) AS prev_cases,
(cases - coalesce(lag(cases, 1) over(PARTITION BY city
ORDER BY days), 0)) AS diff
FROM covid)a
GROUP BY city
HAVING min(diff) > 0;

Solution that I tried,
with tab1 as(select *,lead(cases,1) over(partition by city order by days ) as prev_month,
lead(cases,1) over(partition by city order by days ) - cases as sumcount
from covid),
tab2 as (select distinct city from tab1
where sumcount <= 0)
select distinct city from covid where city not in (select city from tab2)

my solution - kinda same but differs on where/having conditions

with cte as(
select *, rank()over(partition by city order by cases asc) as rnk, row_number()over(partition by city order by days asc) as rn
from covid),
cte1 as(
select *, case when rnk=rn then 1 else 0 end as flag
from cte)

select city, count(1) as galf, sum(flag)
from cte1
group by city
having count(1) = sum(flag)

WITH CTE AS ( SELECT * ,
RANK() over(partition by city order by days ) rk1 ,
rank() over(partition by city order by cases asc ) rk2 ,
RANK() over(partition by city order by days ) - rank() over(partition by city order by cases asc ) rk3
from covid )

SELECT city
from cte
group by city
having count(distinct rk3) = 1

with cte1 as(
select city,days,cases,rank() over(partition by city order by cases asc) as rnk from covid
),cte2 as(
select *,ROW_NUMBER() over(partition by city order by days) rm from cte1 --order by city,days
)select city as diff from cte2 group by city having count(distinct rnk-rm)=1

my approach -

with cte1 as (
with cte as (
select * , Rank() over(partition by city order by cases asc ) as rank1 ,
Rank() over(partition by city order by days asc ) as rank2 from covid )

select * , case when rank1 > rank2 then 1 when rank1<rank2 then 0 else 1 end as status1 from cte )

select city from cte1 group by city having sum(status1) = count(status1) ;

hello Ankit ,
My solution is as below -


-- this will work in snowflake , used qualify clause just to simplify.

select city, diff , count(*) over ( partition by city) cnt from (
select city , diff from (
select * , row_number() over ( partition by city order by days ) row_num
,rank() over ( partition by city order by cases ) rnk , rnk - row_num diff from covid )
group by city , diff ) qualify cnt = 1 ;

with covidAnalysis as
(
select city, days, cases, (case when cases - lag(cases) over (partition by city order by days) <=0 then 0 else 1 end) flag
from covid
)

select city
from covidAnalysis
group by city
having count(flag)=sum(flag);

SET sql_mode='NO_UNSIGNED_SUBTRACTION';
select * from covid;
with cte as (
select *, rank() over(partition by city order by cases) as ranked
from covid
order by city,days ),
cte2 as(
select *, case when day(days) = ranked then 1 else 0 end as sts
from cte)
select city from cte2
group by 1
having sum(sts) = 3;

with t as (
select *,
cast(row_number() over(partition by city) as signed) rn,
cast(dense_rank() over(partition by city order by cases) as signed) rnk,
cast(row_number() over(partition by city) as signed)-cast(dense_rank() over(partition by city order by cases) as signed) diff
from covid)


select distinct city from covid where city not in (select distinct city from t where diff <> 0)

I was thinking, instead of count(disitnct diff) = 1 and avg(diff) = 0, we can use max(diff) = 0 alone?