EXL Analytics SQL Interview Question | 2 Different Ways of using Window Functions

In this video we will solve a SQL interview question asked in EXL Analytics Company.
Here are the timestamps :
00:00 Understand the problem
00:53 Solution 1
06:20 Solution 2

script:
CREATE TABLE city_population (
state VARCHAR(50),
city VARCHAR(50),
population INT
);

-- Insert the data
INSERT INTO city_population (state, city, population) VALUES ('haryana', 'ambala', 100);
INSERT INTO city_population (state, city, population) VALUES ('haryana', 'panipat', 200);
INSERT INTO city_population (state, city, population) VALUES ('haryana', 'gurgaon', 300);
INSERT INTO city_population (state, city, population) VALUES ('punjab', 'amritsar', 150);
INSERT INTO city_population (state, city, population) VALUES ('punjab', 'ludhiana', 400);
INSERT INTO city_population (state, city, population) VALUES ('punjab', 'jalandhar', 250);
INSERT INTO city_population (state, city, population) VALUES ('maharashtra', 'mumbai', 1000);
INSERT INTO city_population (state, city, population) VALUES ('maharashtra', 'pune', 600);
INSERT INTO city_population (state, city, population) VALUES ('maharashtra', 'nagpur', 300);
INSERT INTO city_population (state, city, population) VALUES ('karnataka', 'bangalore', 900);
INSERT INTO city_population (state, city, population) VALUES ('karnataka', 'mysore', 400);
INSERT INTO city_population (state, city, population) VALUES ('karnataka', 'mangalore', 200);

Zero to hero(Advance) SQL Aggregation:

Most Asked Join Based Interview Question:

Solving 4 Trick SQL problems:

Data Analyst Spotify Case Study:

Top 10 SQL interview Questions:

Interview Question based on FULL OUTER JOIN:

Playlist to master SQL :

Rank, Dense_Rank and Row_Number:

#sql #dataengineer

[ferdinandroshan]

I managed to solve it using aggregate window functions without seeing your solution first. Here's my approach:

with cte as (
select
*,
max(population) over(partition by state) as max_pop,
min(population) over(partition by state) as min_pop
from city_population
)
select
state,
max(case when population = max_pop then city else null end) as max_pop_city,
max(case when population = min_pop then city else null end) as min_pop_city
from cte
group by state;

Cross checked your solution sir, looks like I used the same ditto approach that you've used before I even saw your approach. All thanks to watching your videos daily, I am able to think and write the codes like you do :)

[harshkhannashorts]

Both options are perfect. You are doing good work for us.
Thanks a lot brother.. I have 13+ years experience in SQL.

[deepeshmatkati]

We eagerly wait for your "I'm back with another interesting question" ... Keep posting more!

[kabiraggarwal]

Hey @ankit, Thank you for building my skills in SQL by always helping in solving the problem with different approaches. I by default solved the problem with the second approach at first place.😇

[devrajpatidar]

Hello sir,
Nice video !
Here is my solution with first_value window function

with cte as (
select *,
FIRST_VALUE(city) over(partition by state order by population desc) as max_popln_city,
FIRST_VALUE(city) over(partition by state order by population asc) as min_popln_city
from city_population
)
select state, min(max_popln_city) as highest_populated_city, min(min_popln_city) as lowest_populated_city
from cte
group by state;

[sonalijain]

my approach of solving this question
select distinct state,
FIRST_VALUE(city) over(PARTITION by state order by population desc) highest,
FIRST_VALUE(city) over(PARTITION by state order by population ) lowest
from city_population;

[bankimdas]

Thanks for making this sir.🙏🙏
This type of question you made us understand earlier in one of the video.

[SuperMohit]

WITH cte AS(
SELECT
*,
RANK() OVER(PARTITION BY state ORDER BY population ASC) AS rank_asc,
RANK() OVER(PARTITION BY state ORDER BY population DESC) AS rank_desc
FROM
city_population
)
SELECT
state,
MAX(CASE WHEN rank_asc = 1 THEN city END) AS min_pop,
MAX(CASE WHEN rank_desc = 1 THEN city END) AS max_pop
FROM
cte
GROUP BY
1

[Kirankumar-mlro]

with cte as (
select *, row_number() over(partition by state order by population desc) as rn1,
row_number() over(partition by state order by population) as rn2
from city_population
)
select state, max(case when rn1=1 then city end) as highest_population, max(case when rn2=1 then city end) as lowest_population
from cte
where rn1=1 or rn2=1
group by state

[akashgoel]

Thanks for this, posting my solution as well

query:
with cte as (
select state, city, population, ROW_NUMBER() over(partition by state order by population desc) as rn
from city_population
group by state, city, population
)
select a.state, a.city, b.city
from cte a join cte b on a.state=b.state
where
a.rn in (select min(rn) from cte where state='haryana')
and b.rn in (select max(rn) from cte where state='haryana')
order by a.state

[PowerBiWithVivek]

I used the cte with join:
with cte as
(select state, min(population), max(population)
from city_population
group by state)

select
cp.state,
min(case when cp.population = c.max then city end) as max_population_city, --- max() can be used
min(case when cp.population = c.min then city end) as min_population_city --- max() can be used
from city_population cp
join cte c on c.state = cp.state
group by cp.state;

[MuskanGoyal-dbcs]

with cte as(select state, city, sum(population) as pop
from city_population
group by state, city),
cte2 as(
select state, city, dense_rank()over(partition by state order by pop desc) as maxi, dense_rank()over(partition by state order by pop ) as mini, pop from cte)
select state, max(case when maxi=1 then city end) as maxpop,
min(case when mini=1 then city end )as minpop from cte2
group by state

[srinivasulum]

with cte as (
select *, rank()over(partition by state order by population desc)as rno_desc,
rank()over(partition by state order by population )as rno_asc from city_population
)
select state,
max(case when rno_desc=1 then city else null end) as max_population,
min(case when rno_asc=1 then city else null end) as min_population
from cte
where rno_asc=1 or rno_desc=1
group by state

[lloydfernandes]

select distinct(state),
first(city) over(partition by state order by population rows between unbounded preceding and unbounded following) as min_pop,
last(city) over(partition by state order by population rows between unbounded preceding and unbounded following) as max_pop
from city_population

[diptikar-sf]

1st solution:-

with T as(select distinct state, max(population) as p, min(population) as q
from a
group by state)
select T.state, min(case when T.p = a.population then a.city end) as maxo,
min(case when T.q = a.population then a.city end)as mino
from T join a on T.state = a.state
group by T.state

2nd solution :-

select distinct state,
first_value(city) over(partition by state order by population desc) as first_value,
first_value(city) over(partition by state order by population) as last_value
from a

[mathavansg]

with cte as (SELECT *, rank() over (partition by state order by population desc ) as rn from city_population )

SELECT *, max(case when rn = 1 then city end) as 'max_population',
min(case when rn=3 then city end) as 'min_population' from cte GROUP by state

[dasubabuch]

1st method:

select distinct state,
by state order by population) as city_mins,
by state order by population rows between unbounded preceding and unbounded following) as city_maxs
from city_population;


2nd method:

with t as
(
select state, city,
dense_Rank()over(partition by state order by population) as r,
dense_Rank()over(partition by state order by population desc) as r1
from city_population
),
t1 as
(
select state,
case when r=1 then city end as lowest_population_city,
case when r1=1 then city end as highest_population_city
from t
)
select state, max(lowest_population_city) as min_population_city,
max(highest_population_city) as max_population_city
from t1
group by state;

[ManjeetRaj-my]

3rd approach

SELECT A.state, A.city as min_population, B.city as max_population from (select state, city from city_population group by state having min(population) = population ) A
JOIN
(select state, city from city_population group by state having max(population) = population ) B
where A.state = B.state

[nangedlapraveen]

with cte as (
select state as stat,
min(population) mini,
max(population) maxi from city_population group by state )
select a.stat,
case when a.maxi in (select distinct population from city_population where state=a.stat) then (select city from city_population where population=a.maxi and state=a.stat) else null end as maxi,
case when a.mini in (select distinct population from city_population where state=a.stat) then (select city from city_population where population=a.mini and state=a.stat) else null end as mini
from cte a

[sridharandavarapu]

with temp as(select *, dense_rank() over(partition by state order by population asc) as rk, dense_rank() over(partition by state order by population desc) as rk_1
from city_population)
select state, max(case when rk=1 then city end) as min_pop,
max(case when rk_1=1 then city end) as max_pop from temp
group by state;