SQL | find max, min population city names by state | SQL Interview Question |

[@nikhilshinde8067]

select * from (
select p1.state,p1.city as maxPopulation ,p2.city as minPopulation,
row_number() over (partition by p1.state order by p1.state, p1.population-p2.population desc)as rn
from CityPopulation p1
inner join CityPopulation p2
on p1.state = p2.state
and p1.population > p2.population
) where rn=1 ;
----
SELECT DISTINCT
p1.state,
FIRST_VALUE(p1.city) OVER (PARTITION BY p1.state ORDER BY p1.population DESC) AS MaxPopulation,
FIRST_VALUE(p2.city) OVER (PARTITION BY p1.state ORDER BY p2.population ASC) AS minPopulation
FROM
CityPopulation p1
INNER JOIN
CityPopulation p2 ON p1.state = p2.state AND p1.population > p2.population;

[@CodeSQL]

Many thanks for contributing this solution! 🙌