https://www.youtube.com/watch?v=KH6UCUiLHDE

CREATE TABLE city_info (
    id INT AUTO_INCREMENT PRIMARY KEY,
    state VARCHAR(15),
    city VARCHAR(10),
    population INT
);

INSERT INTO city_info (state, city, population) VALUES
('Maharashtra', 'Mumbai', 1000),
('Maharashtra', 'Pune', 500),
('Maharashtra', 'Nagpur', 400),
('Punjab', 'Amritsar', 800),
('Punjab', 'Ludhiana', 350),
('Punjab', 'Patiala', 200),
('TamilNadu', 'Chennai', 700),
('TamilNadu', 'Vellore', 400);

WITH
CASE WHEN
ROW_NUMBER()

WITH CTE AS (
SELECT 
	*,
    ROW_NUMBER() OVER (PARTITION BY State ORDER BY population ASC) AS rn1,
    ROW_NUMBER() OVER (PARTITION BY State ORDER BY population DESC) AS rn2
FROM city_info
)

SELECT 
	State,
	MAX(CASE WHEN rn1 = 1 THEN city END) AS Minimum_population,
    MAX(CASE WHEN rn2 = 1 THEN city END) AS Maximum_population
FROM CTE WHERE rn1 = 1 OR rn2 = 1
GROUP BY state