https://www.youtube.com/watch?v=WM2jN1gOs_8&list=PLBTZqjSKn0IfuIqbMIqzS-waofsPHMS0E&index=26

-- Create the city_population table
CREATE TABLE city_population (
    state VARCHAR(50),
    city VARCHAR(50),
    population INT
);

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

WITH

-- Approach 1
WITH CTE AS (
SELECT *,
MAX(population) OVER (PARTITION BY state) AS Max_population, 
MIN(population) OVER (PARTITION BY state) AS Min_population
FROM cipo
)
SELECT state,
MAX(CASE WHEN population = Max_population THEN city END) AS Max_Pop_City,
MIN(CASE WHEN population = Min_population THEN city END) AS Min_Pop_City
FROM CTE
GROUP BY state;

ROW_NUMBER()

-- Approach 2
WITH CTE AS (
SELECT *, 
ROW_NUMBER() OVER (PARTITION BY state ORDER BY population DESC) AS rn_desc_fr_max,
ROW_NUMBER() OVER (PARTITION BY state ORDER BY population) AS rn_asc_fr_min
FROM cipo
)
SELECT state,
MAX(CASE WHEN rn_desc_fr_max = 1 THEN city END )AS Max_pop_city,
MIN(CASE WHEN rn_asc_fr_min = 1 THEN city END )AS Min_pop_city
FROM CTE
GROUP BY state;