[SQL/QCC] 3회차 코딩 테스트_해설

https://query-test-mu.vercel.app/?set=3&problem=48

https://query-test-mu.vercel.app/?problem=48&set=3

---

1번 풀이(정답)

-- 생존 대비 GNP가 감소한 국가
-- 인구가 1억 명 이상의 국가
-- 이전 연도 GNP 값이 0이면 null인 경우는 제외

SELECT count(name) as country_count
FROM (
  select *
  from country as c
  where c.GNP < c.GNPOLD
) a
WHERE population >= 10000000

문제점

조금 어렵게 생객했었나, 어쩌다 서브쿼리를 쓰게 되었나 ?

운이 좋게 정답이긴 했지만, 0 과 null 값 인 것은 제외를 해주는 부분에서는 미흡했다.

 

해설

-- 해설
select count(distinct code) as country_count  -- count(1)
from country
where population >= 10000000
and gnp - gnpold < 0
and gnpold <> 0        -- 0 아닌거
and gnpold is not null -- null제거

▼ 결과

---

2번 풀이(오답)

-- 각 행정 구역(district) 내 도시들의 평균 인구 수 분석
-- 각 district 별 평균 인구를 계산, 반올림해서 정수로
-- 도시가 3개 이상 존재하는 district만
-- 결과 : 평균 인구수를 기준으로 내림차순 정렬

SELECT d.district, ROUND(SUM(population)/d.cnt, 0) as average_population
FROM (
    select district, population, count(district) as cnt
    from city
    group by district
    having count(district) >= 3
  ) d
WHERE cnt >= 3
GROUP BY d.district 
ORDER BY average_population DESC

▼ 오답 결과

문제점

정답 결과와 비교했을 때 "평균 인구수"가 다르다. 

음,,, 내 쿼리에서 서브쿼리 내 HAVING 절에서 district 대신 id를 넣었다면 정답이었다!!

having count(id) >= 3

SQL은 정말 안하다보면 감이 없어지는 게 느껴진다. 

 

해설

-- 해설
SELECT district,
      round(avg(population)) as average_population
      -- 동일: sum(population) / count(1) as average_population_v2,
      -- count(id)도 select절에 넣어서 한번 확인해주고 -- count(distinct id)  사실 중복은 없음
FROM city
GROUP BY district
HAVING COUNT(id) >= 3
ORDER BY average_population DESC

▼ 결과

---

3번 풀이(오답)

-- 각 대륙에서 인구가 가장 많은 도시 분석
-- 대륙별 가장 인구가 많은 도시, 해당 도시만 조회   : continent, population, district
-- 도시 정보가 없는 대륙은 제외
-- 결과 : 인구 기준으로 내림차순 정렬

SELECT c.district as CITY_NAME,
       t.NAME     as COUNTRY_NAME,
       t.continent,
       max(c.population) as population
FROM country   as t
LEFT JOIN city as c ON t.code = c.countrycode
WHERE c.district is NOT NULL
GROUP BY t.continent
ORDER BY c.population DESC

문제점

해설을 보니, 윈도우 함수를 사용했다... 어쩐지 테스트 전에 한번 보고 들어갈까 싶더라니..ㅎ.ㅎ

⭐️ 중요했던 문장 "각 대륙별로 인구가 가장 많은 도시" ➡️ 윈도우 함수 생각해야지?

- 대륙별로 라고 해서 GROUP BY continent 를 해줘야만 한다고 생각했음.

- 도시 이름도 c.district가 아니라 c.name으로 불러왔어야 함.

- MAX() 함수도 잘못 사용한 것 같다. => 어떤 결과가 나오는거지?

 

해설

-- 해설
SELECT city_name, country_name, continent, population
FROM (
  SELECT ci.name as city_name,
       co.name as country_name,
       co.continent,
       ci.population,
       ROW_NUMBER() OVER(partition by continent order by ci.population desc) as rn
  FROM city    as ci
  JOIN country as co ON ci.countrycode = co.code
  WHERE ci.district is not null
) a
WHERE rn = 1
ORDER BY population DESC

-- WHERE continent = 'Asia'
-- ORDER BY 4 desc
-- Asia = Mumbai = 10500000 가 가장 인구 많음 (최종 결과에 이게 없으면 오답이겠지?)

💡 중간중간 결과를 확인해가며 쿼리를 이어나가면 실수를 줄일 수 있다. 디버깅 디버깅...

ROW_NUMBER() OVER (partition by ** (order by -- ))
-- 중복없이 순번 정하기

▼ 정답 결과

끝.