문제 1번. 글로벌 확장 기회 발굴
전년 대비 GNP가 감소한 국가 중 인구가 1천만 명 이상인 국가의 수를 조회해야 한다.
- 이전 연도의 GNP 값이 0이거나 NULL인 경우는 제외해주세요.
-제출한 답
-- 전년 대비 GNP 감소, 인구 1천명 이상, 국가의 수 (count)
-- 이전 연도 GNP 0 or NULL 제외
select count(code)
from country
where gnp < gnpold and population >= 10000000 and gnpold != 0 and gnpold is not null- 정답
SELECT COUNT(DISTINCT code) AS country_count
FROM qcc.country
WHERE GNPOld <> 0
and GNP - GNPOld < 0
and population >= 10000000'GNP의 값이 0 이거나 NULL인 경우는 제외'라는 문장이 헷갈려서 처음에 시간을 꽤 많이 썼다. 0도 제외이고 NULL도 제외이기 때문에 '~이거나'와 상관없이 AND를 사용하는 게 정답이다.
문제 2번. 도시개발구역 인구 분석
각 행정 구역(District)내 도시들의 평균 인구 수를 분석하고자 한다.
각 District별 평균 인구(population)를 반올림하여 정수로 출력한다.
도시가 3개 이상 존재하는 District만 포함해야 한다.
결과는 평균 인구 수를 기준으로 내림차순 정렬해야 한다.
-- district 내 도시 평균 인구 수
-- district별 (group by district)
-- round(avg(population))
-- id >= 3인 district만 포함
-- order by round(avg(population)) desc
select c2.district, round(avg(population)) as average_population
from city c
join
(select district, count(id)
from city
where district != '–'
group by district
having count(id) >= 3) as c2 -- 도시 3개 이상 존재하는 district
on c.district = c2.district
group by c2.district
order by 2 desc문제에 명시되어있지 않는 한, '-'를 제거하지 않는 게 정답이다.
도시가 3개 이상 존재한다는 뜻은 district가 3번 이상 나온다는 뜻이기 때문에 count(district)를 having에 넣어주는 게 맞다.
문제를 너무 어렵게 접근했다. 생각보다 정답은 간단하다.
- 정답
select district, round(avg(population)) as average_population
from city
group by district
having count(1) >= 3
order by 2 desc문제 3번. 인기도시 타겟 마케팅
각 대륙에서 인구가 가장 많은 도시를 분석하여 주요 타겟 시장을 선정하고자 한다.
대륙 별 가장 인구가 많은 도시를 찾아야 한다.
각 대륙별로 인구가 가장 많은 도시를 찾고, 해당 도시만 조회해야 한다.
도시 정보가 없는 대륙은 제외.
결과는 인구 기준으로 내림차순 정렬
-- 각 대륙에서 인구가 가장 많은 도시
-- 도시 정보가 없는 대륙 제외
-- 인구 기준 내림차순 정렬
select c.name as city_name, ctr.name as country_name, ctr.continent, c.population
from country ctr
join city c
on ctr.code = c.countrycode
where c.population = (
select max(c2.population)
from country ctr2
join city c2
on ctr2.code = c2.countrycode
where ctr.continent = ctr2.continent) -- 대륙 별 인구가 가장 많은 도시
order by c.population desc정답과 결과는 동일하지만 이 문제 역시 너무 어렵게 접근했다.
max()보다는 rank() over()를 사용하는 문제였다.
그 후에 rank = 1인걸로 하면 나온다.
이런 식의 최대값, 최솟값 등 특정 조건에서의 결과를 찾는 문제의 경우,
결과를 미리 찾아서, 첫번째 행 정도를 주석으로 적어놓고 그 값을 기준으로 쿼리를 작성해나가면 도움이 된다.
- 다시 작성해본 답
select
city_name, country_name, continent, population
from (
select
c.name as city_name,
ctr.name as country_name,
ctr.continent,
c.population,
rank() over(partition by ctr.continent order by c.population desc) as rnk
from country ctr
join city c
on ctr.code = c.countrycode) as a
where rnk = 1
order by 4 desc- 정답
SELECT
CityName AS city_name,
CountryName AS country_name,
Continent AS continent_name,
Population AS population
FROM (
SELECT
c.Name AS CityName,
co.Name AS CountryName,
co.Continent,
c.Population,
ROW_NUMBER() OVER (PARTITION BY co.Continent ORDER BY c.Population DESC) AS PopulationRank
FROM qcc.country co
JOIN qcc.city c ON c.CountryCode = co.Code
) ranked_cities
WHERE
PopulationRank = 1
ORDER BY
Population DESC