QCC 1회차: WINDOW 함수 | RANK | 상관 서브쿼리 | 2번 JOIN하기 | 우선순위 부여

전년도 GNP가 없거나 전년 대비 GNP가 감소한 국가 중 인구가 10,000,000명 이상인 국가의 수

-- 내가 작성한 쿼리
-- 문제를 잘 읽자..!
SELECT GNP, GNPOld, Population 
FROM (
SELECT GNP, GNPOld, Population
FROM country
WHERE GNPOld IS NULL
	OR GNP < GNPOld  -- AND 가 먼저 실행되지 않도록 OR에 괄
	) a
WHERE Population >= 10000000;

-- 정답
-- 숫자에 , 적으면 안됨....!
-- or 구문이 먼저 실행될 수 있도록 ()를 삽입해주면 위 쿼리처럼 서브쿼리를 쓸 필요x
SELECT COUNT(DISTINCT code) AS country_count
FROM country
WHERE (GNP - GNPOld < 0 or GNPOld is null) 
and population >= 10000000; -- 34

 

각 대륙에서 가장 인구가 많은 도시의 CityName, CountryName, Continent, Population

-- 각 대륙에서 가장 인구가 많은 도시의 CityName, CountryName, Continent, Population
-- 인구 기준 내림차순 정렬 
-- 내가 작성한 쿼리
-- 대륙별x, 국가별 인구가 가장 많은 도시까지만 구함
SELECT c3.Name CityName, 
	c2.Name CountryName, 
	c2.Continent, 
	c.Population
FROM (
	SELECT CountryCode, MAX(Population) Population 
	FROM city
	GROUP BY CountryCode 
	) c
JOIN country c2 ON c.CountryCode = c2.Code 
JOIN city c3 ON c.Population = c3.Population
ORDER BY 4 DESC;

-- 정답
---- 방법 1. WINDOW 함수 사용
SELECT 
    CityName,
    CountryName,
    Continent,
    Population
FROM (
    SELECT 
        c.Name AS CityName,
        co.Name AS CountryName,
        co.Continent,
        c.Population,
        RANK() OVER(PARTITION BY co.Continent ORDER BY c.Population DESC) AS PopulationRank
    FROM -- 각 대륙별 인구수 내림차순 정렬 및 RANK 생성
        city c
    JOIN 
        country co ON c.CountryCode = co.Code
) ranked_cities
WHERE 
    PopulationRank = 1 -- RANK가 1인 도시만 SELECT
ORDER BY 
    Population DESC;
   
   
---- 방법 2. 상관 서브쿼리: Population을 하나씩 대입하는 방법 (가장 시간이 오래 걸리는 방법)
SELECT c.Name AS CityName,
	co.Name AS CountryName,
    co.Continent,
    c.Population
FROM city c
JOIN country co ON c.CountryCode = co.Code
WHERE c.Population = ( -- 인구수가 대륙별 인구수 MAX값과 일치하는지 확인
			SELECT MAX(c2.Population) -- 대륙별 인구수 MAX값 구하기
			FROM city c2
			JOIN country co2 ON c2.CountryCode = co2.Code
			WHERE co2.Continent = co.Continent
            )
ORDER BY 4 DESC;


---- 방법 3. 내가 풀려고 했던 방법
SELECT c.Name CityName, 
	c2.Name CountryName, 
	c2.Continent, 
	c.Population
FROM city c 
JOIN country c2 ON c.CountryCode = c2.Code 
JOIN ( -- city와 country 테이블을 조인한 테이블에 MAX값을 추출한 테이블 조인 
	SELECT c2.Continent,
		MAX(c.Population) MaxPopulation
	FROM city c 
	JOIN country c2  ON c.CountryCode = c2.Code
	GROUP BY 1
    ) max_pop ON c2.Continent = max_pop.Continent -- 대륙과 인구 수를 기준으로 조인 -> MAX값이 아닌 인구수 사라짐
    AND c.Population = max_pop.MaxPopulation -- 이 자리에 and를 사용할 수 있구나...?
ORDER BY 4 DESC;