[SQL] 12. 연습문제 풀이

 

문제1

각 트레이너별로 가진 포켓몬의 평균 레벨을 계산하고, 그 중 평균 레벨이 높은 TOP 3 트레이너의 이름과 보유한 포켓몬의 수, 평균 레벨을 출력해주세요.  

 

❎내 풀이 과정

- status 조건을 빼먹음..!

1) 데이터 확인

 

2) 일단 trainer_pokemon 테이블로 평균 레벨, 포켓몬 수를 계산해봄

• 쿼리 결과 검증을 위해 trainer_id = 5인 경우로 WHERE 걸어서 먼저 실행 > 일치함!

• WHERE 빼고, ORDER BY 추가해서 다시 출력

3) 여기서 트레이너 이름(trainer_name)만 출력하면 됨 = trainer 테이블 JOIN하자!

• 2번의 결과를 WITH temp_table AS로 지정한 뒤, LEFT JOIN해서 출력 (ORDER BY는 맨 마지막으로 뺌)

• 잘 나오는 걸 확인했으니, 최종 정답에 맞게 LIMIT 추가하여 답변 출력!

# 전체 코드

-- 쿼리 작성하는 목표, 확인할 지표 : 트레이너별 포켓몬 레벨 평균
-- 쿼리 계산 방법 : join > trainer_id 그룹화, level 평균 계산, 포켓몬 개수 count > limit 3으로 출력
-- 데이터의 기간 : 
-- 사용할 테이블 : trainer_pokemon, trainer
-- JOIN KEY : trainer_pokemon.trainer_id = trainer.id
-- 데이터의 특징 :
  -- 근데 trainer_pokemon 테이블만으로 계산은 가능! 트레이너 이름을 모를 뿐 = 계산 먼저 한 뒤 join해보자

WITH temp_table AS(
  SELECT
    trainer_id,
    COUNT(pokemon_id) AS pokemon_cnt,
    COUNT(DISTINCT(pokemon_id)) AS pokemon_cnt_unique,
    AVG(level) AS avg_level
  FROM basic.trainer_pokemon
  GROUP BY trainer_id -- WHERE 빼고 다시 출력
)

SELECT
  --tp.*, 필요한 컬럼들만 최종 출력
  t.name AS trainer_name,
  tp.pokemon_cnt,
  tp.avg_level
FROM temp_table AS tp
LEFT JOIN basic.trainer AS t
ON tp.trainer_id = t.id
ORDER BY avg_level DESC -- 정렬 추가
LIMIT 3; -- Top3만 출력


-- SELECT
--   id,
--   trainer_id,
--   pokemon_id,
--   level
-- FROM basic.trainer_pokemon
-- WHERE trainer_id = 5
   -- 결과 검증을 위해 특정 행으로 제한해서 출력해봄
   -- 포켓몬 개수 8개, 평균 레벨 27.75임을 확인함

✅강사님 풀이 과정

1) 트레이너가 보유한 포켓몬 수, 평균 레벨을 먼저 출력해봄

• 이때, WHERE문에 status 조건 걸어줘야 함!

2) 방금 구한 테이블을 WITH문으로 지정한 뒤, trainer 테이블과 JOIN !

• (내 풀이와 달리) trainer 테이블을 왼쪽에 두고 JOIN 수행하심

3) 트레이너 이름(t.name)까지 함께 출력하면 끝!

• 결과는 status 조건 빼먹은 내 답과 한 행만 다름 (Blue가 1위였는데, 아마 방출한 포켓몬이 있나보네,,)
• ➕중복 걱정될 땐 DISTINCT 추가로 사용 가능

 

# 전체 코드

-- 쿼리 작성하는 목표, 확인할 지표 : 트레이너 이름, 보유 포켓몬 수, 평균 레
-- 쿼리 계산 방법 : 보유 포켓몬의 평균 레벨 계산 + trainer 테이블과 연결해서 트레이너 이름 출
-- 데이터의 기간 : 
-- 사용할 테이블 : trainer_pokemon, trainer
-- JOIN KEY : trainer_pokemon.trainer_id = trainer.id
-- 데이터의 특징 :
  -- trainer_pokemon 테이블의 status="Released"는 제외해야 함

WITH trainer_avg_level AS(
  SELECT -- (1) 트레이너가 보유한 포켓몬의 평균 레벨, 포켓몬 수
    trainer_id,
    AVG(level) AS avg_level,
    COUNT(id) AS pokemon_cnt,
  FROM basic.trainer_pokemon
  WHERE
    status != "Released"
  GROUP BY
    trainer_id
)

-- (2) 테이블 (1)과 trainer를 합쳐서 name을 출력
SELECT
  DISTINCT -- 혹시 trainer 중복 의심되면 이거 추가하고 출력
    t.name AS trainer_name, -- (지금은 중복 없음)
    tal.pokemon_cnt,
    tal.avg_level
FROM basic.trainer AS t
LEFT JOIN trainer_avg_level AS tal
ON t.id = tal.trainer_id
ORDER BY avg_level DESC -- 정렬 추가
LIMIT 3; -- Top3만 출력

 

문제2

각 포켓몬 타입1을 기준으로 가장 많이 포획된(방출 여부 상관없음) 포켓몬의 타입1, 포켓몬의 이름, 포획 횟수를 출력해주세요.

 

❎내 풀이 과정

- 나는 '타입마다' 가장 많이 포획된 포켓몬을 찾으라는 것으로 해석하고 풀었음

1) 데이터 확인

 

2) LEFT JOIN으로 pokemon과 trainer_pokemon 연결

• 출력해봤는데 행 개수가 464개로 나옴. trainer_pokemon의 행 개수 379개와 왜 다르지?
• 결과 살펴보니 tp.id가 NULL인 행들 발견 ⇒ 한번도 포획되지 않은 포켓몬들도 있어서 그렇구나!

• WHERE문으로 IS NOT NULL 조건 추가! ⇒ 이제 행 개수 맞음👍

3) 문제 표현이 모호해서 일단 내가 해석한 대로 풀어봄 (=타입마다 제일 많이 잡힌 포켓몬이 뭔지 말해라. 불 타입에선 뭐가 제일 많이 잡혔고, 물 타입은 뭐가 제일 많이 잡혔고...)

• 일단 type1 = "Fire"로 한정해서 tp.id 개수를 COUNT & 그걸 내림차순 정렬
• 불 타입에선 '식스테일'이 '7번'으로 가장 많이 잡혔음 ⇒ 이걸 type1마다 바꿔가면서 구하면 됨,,

# 전체 코드

-- 각 포켓몬 타입1을 기준으로 가장 많이 포획된(방출 여부 상관없음) 포켓몬의 타입1, 포켓몬의 이름과 포획 횟수를 출력해주세요.
-- 쿼리 작성 목표/확인할 지표 : 타입별 포획 횟수(tp 테이블의 id) 
-- 쿼리 계산 방법 : join > type1 그룹화, id 개수 count > 출력
-- 데이터의 기간 : 
-- 사용할 테이블 : trainer_pokemon, pokemon
-- JOIN KEY : trainer_pokemon.pokemon_id = pokemon.id
-- 데이터의 특징 :
  -- type1을 기준으로 trainer_pokemon.id를 count해야하므로 JOIN부터 해야 함

SELECT
  -- p.id,
  -- p.type1,
  p.kor_name,
  COUNT(tp.id) AS caught_cnt,
  COUNT(DISTINCT(tp.id)) AS caught_cnt_unique -- 이건 혹시나 같이 출력.
  -- tp.pokemon_id,
  -- tp.id
FROM basic.pokemon AS p
LEFT JOIN basic.trainer_pokemon AS tp
ON p.id = tp.pokemon_id
WHERE tp.pokemon_id IS NOT NULL AND type1="Fire"
GROUP BY 1
ORDER BY caught_cnt DESC

✅강사님 풀이 과정

- 문제 의도는 그냥 타입 상관없이 '제일 많이 잡힌 포켓몬'에 대한 타입, 이름, 횟수였음..

 1) tp 테이블과 p 테이블 연결하고, 그냥 cnt 기준으로 내림차순 출력하면 끝..

• 최다 횟수가 9회인데 동점자가 3명임. 이런 경우에 어떻게 처리할지는 요청자에게 추가로 확인 필요!

 

문제3

전설의 포켓몬을 보유한 트레이너들은 전설의 포켓몬과 일반 포켓몬을 얼마나 보유하고 있을까요? (트레이너의 이름을 같이 출력해주세요) 

 

✅내 풀이 과정

- 더미 컬럼 추가한 건 잘했음! (나는 IF문, 강사님은 CASE문을 사용)

- COUNTIF를 사용해봤으면 좋았을 듯..!

1) 데이터 확인

• trainer 테이블은 이름 출력할 때만 필요하니까, 다 계산하고 마지막에 연결하자

2) 일단 LEFT JOIN으로 trainer_pokemon과  pokemon 연결

• 일단 WHERE 조건으로 전설 포켓몬 보유한 트레이너가 누군지 확인 ⇒ 7,8,22,33,60,70,88 총 7명이네

3) 전설 포켓몬인 경우와 그렇지 않은 경우 카운트할 수 있도록, 각각 1로 표시한 컬럼(더미 변수 느낌)을 추가함

• 이러면 SUM으로 계산할 수 있지 않을까..?

4) trainer_id로 그룹화해서 legendary_cnt를 합산해보자, not_legendary_cnt도 마찬가지!

• 개수 잘 나옴! 이제 여기다가 trainer의 이름만 붙여주면 됨!

5) LEFT JOIN으로 trainer 테이블 연결 후, t.name도 함께 출력

• ⚠️이때, 그냥 GROUP BY에 t.name도 넣어주면 에러 없이 같이 출력 가능하다고 gpt가 알려줌 (이게 제일 일반적인 방식인가? 질문해봐야겠다)
• 어쨌든 답변 도출해냄..!

# 전체 코드

-- 쿼리 작성 목표/확인할 지표 : 트레이너별 포켓몬 개수 (단, 전설/비전설로 나눠서 count) 
-- 쿼리 계산 방법 :
-- 데이터의 기간 : 
-- 사용할 테이블 : trainer_pokemon, pokemon
-- JOIN KEY : tp.pokemon_id = p.id
-- 데이터의 특징 :
  -- 역시 트레이너 이름은 마지막에 연결해줘도 되니까 trainer_pokemon이랑 pokemon만 연결해서 먼저 작업하자
  -- 7,8,22,33,60,70,88 총 7명의 트레이너만 전설포켓몬 갖고 있음

SELECT
  trainer_id,
  t.name AS trainer_name,
  SUM(legendary_cnt) AS legendary_pokemon_cnt,
  SUM(not_legendary_cnt) AS not_legendary_pokemon_cnt
FROM(
  SELECT
    tp.id,
    tp.trainer_id,
    tp.pokemon_id,
    p.is_legendary,
    IF(is_legendary = True, 1, 0) AS legendary_cnt,
    IF(is_legendary = False, 1, 0) AS not_legendary_cnt
  FROM basic.trainer_pokemon AS tp
  LEFT JOIN basic.pokemon AS p
  ON p.id = tp.pokemon_id
) AS temp_table
LEFT JOIN `basic.trainer` AS t
ON temp_table.trainer_id = t.id
GROUP BY 1,2
ORDER BY trainer_id

✅강사님 풀이 과정

- 문자열 쓸 때 따옴표 잘 보자😢 별 거 아닌 걸로 1시간을 헤맸네... ("Active", "Training")

1) 일단 전설의 포켓몬 얼마나 있는지 확인

• CASE WHEN ~으로 더미 컬럼 추가!

2) 여기서 그대로 SUM을 추가할 수 있음! (집계함수니까 GROUP BY 함께 사용)

• 그러면 더미 컬럼의 숫자들이 다 합쳐져서 나옴! = 트레이너마다 전설/비전설 포켓몬 개수를 알 수 있음
• legendary_true 컬럼 옆의 ▼누르고 '내림차순 정렬'

3) 이제 이 결과를 WITH로 감싸고, trainer 테이블 연결해서 트레이너 이름 출력하면 끝!

• legendary_counts 테이블 만들 때 WHERE문으로 '보유' 조건도 추가해줌

# 전체 코드

-- 쿼리 작성 목표/확인할 지표 : 트레이너별 전설/ 비전설 포켓몬 개수 
-- 쿼리 계산 방법 : trainer_pokemon, pokemon 조인 -> 전설 여부에 따라 count -> trainer 조인해서 이름 출력
-- 데이터의 기간 : 
-- 사용할 테이블 : trainer_pokemon, pokemon, trainer
-- JOIN KEY : tp.pokemon_id = p.id, (결과 테이블).trainer_id = t.id
-- 데이터의 특징 :
  -- 전설 여부에 따라 count = COUNTIF도 가능하지만 SUM(CASE WHEN~)으로 풀어보겠음

WITH legendary_counts AS(
  SELECT
    tp.trainer_id,
  --  tp.pokemon_id,
  --  p.kor_name,
    SUM(CASE -- 전설 포켓몬일 때 1로 입력
      WHEN p.is_legendary IS True THEN 1 ELSE 0 END) AS legendary_true,
    SUM(CASE -- 일반 포켓몬일 때 1로 입력
      WHEN p.is_legendary IS NOT True THEN 1 ELSE 0 END) AS normal_true
  FROM basic.trainer_pokemon AS tp
  LEFT JOIN basic.pokemon AS p
  ON tp.pokemon_id = p.id
  WHERE tp.status IN ("Active", "Training") -- '보유' 조건도 추가해줌
  GROUP BY
    tp.trainer_id
)

SELECT
  t.name AS trainer_name,
  lc.legendary_true, --전설 개수
  lc.normal_true -- 일반 개수
FROM basic.trainer AS t
LEFT JOIN legendary_counts AS lc
ON t.id = lc.trainer_id
WHERE -- 전설 포켓몬 있는 경우만 궁금하니까 조건 추가
  lc.legendary_true >= 1

 

문제4

가장 승리가 많은 트레이너 ID, 트레이너의 이름, 승리한 횟수, 보유한 포켓몬의 수, 평균 포켓몬의 레벨을 출력해주세요.

단, 포켓몬의 레벨은 소수점 2째 자리에서 반올림해주세요. (반올림 함수는 ROUND)

 

✅내 풀이 과정

1) 데이터 확인

승리 횟수는 battle, 레벨은 trainer_pokemon,  트레이너 이름은 trainer 써야 함 (trainer는 마지막에 추가하자)

 

2) 일단 '승리 횟수'를 위해 지난 시간에 쓴 쿼리 가져옴..

• UNION ALL로 player1과 player2를 모두 trainer_id 컬럼으로 통합해버렸던 쿼리 (WITH 쿼리 내용은 글 '11. 쿼리 가독성, 데이터 결과 검증' 참고)

3) COUNTIF를 사용해 승리 횟수 & 패배 횟수 계산 ⇒ 각각 컬럼으로 추가 (역시 이전 쿼리와 동일)

• 승리 횟수(win_count) 기준으로 내림차순 정렬 ⇒ 1번 트레이너가 제일 많이 승리했네!
• 이제 트레이너 이름, 보유 포켓몬 수, 평균 포켓몬 레벨만 출력하면 됨!

4) 일단 '보유 포켓몬 수', '평균 포켓몬 레벨' 먼저 계산하기 위해 trainer_pokemon 조인!

• COUNTIF와 AVG 함수로 각각 계산 완료 (ROUND로 반올림)

5) 이제 마지막으로 '트레이너 이름'만 출력하면 됨! = trainer 테이블 조인!

• 가장 승리가 많은 트레이너는 Ash네요!

# 전체 코드

-- 가장 승리가 많은 트레이너 ID, 트레이너의 이름, 승리한 횟수, 보유한 포켓몬의 수, 평균 포켓몬의 레벨을 출력
-- 쿼리 작성 목표/확인할 지표 : 트레이너별 승리횟수 > 그 중에 MAX 
-- 사용할 테이블 : trainer_pokemon, pokemon
-- JOIN KEY : tp.pokemon_id = p.id
-- 데이터의 특징 : 역시 트레이너 이름은 마지막에 연결하자

WITH battle_basic AS (  
  SELECT  
    id AS battle_id,  
    player1_id AS trainer_id,  
    winner_id  
  FROM `basic.battle`  
  UNION ALL
  SELECT  
    id AS battle_id,  
    player2_id AS trainer_id,  
    winner_id  
  FROM `basic.battle` 
), battle_with_result AS( --battle_with_result로 추가 테이블 지정
  SELECT
    *,  
    CASE  --winner_id 기준으로
      WHEN trainer_id = winner_id THEN "WIN" --trainer_id와 같으면 승리
      WHEN winner_id IS NULL THEN "DRAW" --NULL이면 무승부
      ELSE "LOSE" --trainer_id와 다르면 패배
    END AS battle_result -- 그걸 하나의 컬럼으로 저장
  FROM battle_basic  
  -- WHERE trainer_id = 7 (이제 주석 처리해서 전체 트레이너로 확장!)
) 

SELECT
  bwr.trainer_id,
  t.name AS trainer_name, -- 트레이너 이름 추가!
  COUNTIF(battle_result="WIN") AS win_count,
  COUNTIF(battle_result="LOSE") AS lose_count,
  COUNTIF(status IN ("Active", "Training")) AS pokemon_cnt, -- 보유 포켓몬 수
  ROUND(AVG(level), 2) AS avg_level -- 평균 포켓몬 레벨 (둘째자리까지 반올림)
FROM battle_with_result AS bwr
LEFT JOIN basic.trainer_pokemon AS tp
ON bwr.trainer_id = tp.trainer_id
LEFT JOIN basic.trainer AS t -- trainer 테이블 추가로 JOIN
ON bwr.trainer_id = t.id
GROUP BY 1,2
ORDER BY win_count DESC -- 승리 횟수 많은 것부터 정렬
-- LIMIT 1 -- 이거까지 하면 딱 답만 출력됨

 

✅강사님 풀이 과정

- winner_id만 있어도 됨..

1) winner_id 기준으로 승리 횟수 카운트

• NULL은 비긴 경우니까, WHERE 조건 추가해서 제외시킴

 

2) 방금 구한 쿼리를 WITH로 지정하고, trainer 테이블 JOIN해서 이름 추가

• 일단 출력해봄 / 가장 승리가 많은 경우니까, ORDER BY & LIMIT으로 일단 행 수 줄이고 가자

3) 1위만 뽑은 쿼리를 다시 WITH로 지정하고, 평균 포켓몬 레벨, 포켓몬 수 추가

• 그냥 출력해보면, 1위(Ash)가 가진 포켓몬만 나옴

• COUNT와 AVG로 평균 레벨, 포켓몬 수 계산하면 끝! + status 조건도 추가

# 전체 코드

WITH winner_counts AS(
  SELECT
    winner_id,
    COUNT(winner_id) AS win_count
  FROM basic.battle
  WHERE
    winner_id IS NOT NULL
  GROUP BY
    winner_id
), top_winner AS( -- t.name 추가, 1위만 필터링
  SELECT
    wc.winner_id AS trainer_id,
    wc.win_count,
    t.name AS trainer_name
  FROM winner_counts AS wc
  LEFT JOIN basic.trainer AS t
  ON wc.winner_id = t.id
  ORDER BY
    win_count DESC
  LIMIT 1
)

-- 평균 포켓몬 레벨, 포켓몬 수 추가
SELECT
  tw.trainer_id,
  tw.trainer_name,
  tw.win_count,
  COUNT(tp.pokemon_id) AS pokemon_cnt,
  ROUND(AVG(tp.level), 2) AS avg_level
FROM top_winner AS tw
LEFT JOIN `basic.trainer_pokemon` AS tp
ON tw.trainer_id = tp.trainer_id
WHERE tp.status != "Released"
GROUP BY ALL

 

문제5

트레이너가 잡았던 포켓몬의 총 공격력(attack)과 방어력(defense)의 합을 계산하고, 이 합이 가장 높은 트레이너를 찾으세요.  

 

✅내 풀이 과정

1) 데이터 확인

• pokemon 테이블에서 attack + defense = att_def 컬럼 만들기 > trainer_pokemon에 조인하고, trainer_id 그룹화해서 att_def를 SUM > 내림차순 정렬 & (필요시) trainer 테이블 연결해서 이름도 출력

2) 일단 pokemon 테이블에서 att_def 컬럼 만들어봄

3) WITH문 써서 pokemon2라는 테이블로 지정 & trainer_pokemon 테이블 오른쪽에 붙임

• att_def 컬럼까지 같이 출력해보면 잘 나옴 (9번 거북왕 체크해보니 공격+방어=183 맞음)

4) trainer_id 기준으로 att_def 컬럼을 SUM & 내림차순 출력

5) trainer 테이블 붙여서 name 출력하면 끝

• 1위는 총합이 1,951인 Kyle이네!
• 근데 이런 경우처럼 그룹화 말고 그냥 확인차 출력하는 컬럼이 있을 경우, 그룹화를 어떻게 처리해야 하나요❓예를 들어, trainer_name에 동명이인이 있을 경우 trainer_name 기준으로 그룹화한 결과와, trainer_id 기준으로 그룹화한 결과는 다를 것입니다. 이런 데이터에서 trainer_id와 trainer_name을 둘 다 select & groupby하면 어떻게 되나요?

# 전체 코드

-- 트레이너가 잡았던 포켓몬의 총 공격력(attack)과 방어력(defense)의 합을 계산하고, 이 합이 가장 높은 트레이너를 찾으세요.
-- 쿼리 작성하는 목표, 확인할 지표 : 트레이너별로, 잡은 포켓몬의 공격력, 방어력 합
-- 쿼리 계산 방법 : 
  -- 1) pokemon 테이블에서attack + defense = att_def 컬럼 만들기
  -- 2) trainer_pokemon에 조인하고, trainer_id 그룹화해서 att_def를 SUM
  -- 3) 내림차순 정렬 & (필요시) trainer 테이블 연결해서 이름도 출력
-- 사용할 테이블 : pokemon, trainer_pokemon, trainer

WITH pokemon2 AS(
  SELECT
    id,
    kor_name,
    attack,
    defense,
    (attack+defense) AS att_def
  FROM `basic.pokemon`
)

SELECT
  -- tp.id,
  tp.trainer_id,
  t.name AS trainer_name,
  SUM(p2.att_def) AS sum_att_def
FROM basic.trainer_pokemon AS tp
LEFT JOIN pokemon2 as p2
ON tp.pokemon_id = p2.id
LEFT JOIN basic.trainer as t
ON tp.trainer_id = t.id
GROUP BY 1,2
ORDER BY sum_att_def DESC

 

 

✅강사님 풀이 과정

1) tp와 p 합치고, attack + defense 더해서 stat 컬럼 생성

• p에서 합친 뒤에 JOIN했던 내 풀이와 달리, 아예 JOIN 하시고 시작하심 

 

2) 그리고 여기서 바로 SUM을 적용! trainer_id 기준으로!

• 이렇게 간결하게도 되는구나,,,❗

3) 위 쿼리를 WITH문으로 지정해주고, trainer 테이블 JOIN해서 이름 출력하면 끝!

• ORDER BY로 total_stat (공격+방어) 가장 높은 것만 출력

# 전체 코드

WITH total_stats AS(
  SELECT
    tp.trainer_id,
    -- p.attack,
    -- p.defense,
    SUM(p.attack + p.defense) AS total_stat
  FROM basic.trainer_pokemon AS tp
  LEFT JOIN basic.pokemon as p
  ON tp.pokemon_id = p.id
  GROUP BY tp.trainer_id
)

SELECT
 t.name,
 ts.trainer_id,
 ts.total_stat
FROM total_stats AS ts
LEFT JOIN `basic.trainer` AS t
ON ts.trainer_id = t.id
ORDER BY 3 DESC
LIMIT 1

 

문제6

각 포켓몬의 최고 레벨과 최저 레벨을 계산하고, 레벨 차이가 가장 큰 포켓몬의 이름을 출력하세요. 

 

✅내 풀이 과정

1) pokemon_id 기준으로 level의 MAX, MIN을 계산 > 각각 컬럼으로 추가

• 데이터 검증을 위해 WHERE로 출력해서 맞춰봄 (ok)

2) max_level과 min_level의 차이를 계산한 컬럼을 추가 & 내림차순 정렬해서 출력

• 가장 차이가 큰 포켓몬은 147번 포켓몬이네!

3) 여기다 이제 pokemon 테이블 붙여서 이름만 같이 출력하면 끝

• 정답은 '미뇽'

# 전체 코드

-- 쿼리 작성하는 목표, 확인할 지표 : 포켓몬별 최고레벨-최저레벨

SELECT
  pokemon_id,
  p.kor_name,
  MAX(level) AS max_level,
  MIN(level) AS min_level,
  (MAX(level)-MIN(level)) AS gap_max_min --차이 컬럼을 추가
FROM basic.trainer_pokemon AS tp
LEFT JOIN basic.pokemon AS p
ON tp.pokemon_id = p.id
GROUP BY
  1,2
ORDER BY 
  gap_max_min DESC -- 차이 큰 것부터

-- SELECT -- 검증용 쿼리
--   pokemon_id,
--   level
-- FROM basic.trainer_pokemon
-- WHERE pokemon_id = 12
-- ORDER BY 2 DESC

 

✅강사님 풀이 과정

1) 데이터 확인. pokemon_id 하나 잡고 min, max값 확인해봄

• 12번 포켓몬은 최소 레벨 6, 최고 레벨 22 라는 것을 체크 => 차이는 16

2) MIN, MAX 함수로 최소, 최대 레벨 출력해보기 + 뺄셈 연산으로 차이값도 출력

• 내 풀이와 동일! 

3) 한 번에 출력해도 되지만, 가독성을 위해 따로 WITH 지정 후에 출력

• 👨‍🏫제일 마지막 SELECT문은 최대한 간소하게 작성하는 편! 그래야 처음 봤을 때 이 출력값이 어떻게 나왔는지 이해하기 편함!

# 전체 코드

WITH level_difference AS(
  SELECT
    tp.pokemon_id,
    p.kor_name,
    MAX(level) AS max_level,
    MIN(level) AS min_level,
    (MAX(level) - MIN(level)) AS level_diff
  FROM `basic.trainer_pokemon` AS tp
  LEFT JOIN basic.pokemon AS p
  ON tp.pokemon_id = p.id
  -- WHERE
  --   pokemon_id = 12 -- 예시로 출력해봄
  GROUP BY
    pokemon_id,
    kor_name
)

SELECT --👨‍🏫마지막 쿼리는 간소하게!
  kor_name,
  level_diff
FROM level_difference
ORDER BY
  level_diff DESC
LIMIT 1

 

문제7

각 트레이너가 가진 포켓몬 중에서 공격력(attack)이 100 이상인 포켓몬과 100 미만인 포켓몬의 수를 각각 계산해주세요.

(트레이너의 이름과 두 조건에 해당하는 포켓몬의 수를 출력해주세요.)

 

✅내 풀이 과정

- countif로 풀 수 있는 건가..? >>> 강사님 풀이 확인!

1) 데이터 확인

 

2) trainer_pokemon에서 attack 컬럼 볼 수 있게 pokemon LEFT JOIN & 일단 출력해봄

• 보유 중인 포켓몬이니까 status 조건도 걸어줌!

 

3) IF 조건문으로 over_100, under_100 컬럼을 추가 (합산할 수 있게 1로 입력)

 4) IF문에다 그냥 SUM 씌우니까 계산됨! 이전에 봤던 SUM(CASE WHEN~)과 같은 맥락인 듯!

5) 이제 trainer 테이블 옆에 붙여서 name만 출력하면 끝!

# 전체 코드

-- 각 트레이너가 가진 포켓몬 중에서 공격력(attack)이 100 이상인 포켓몬과 100 미만인 포켓몬의 수를 각각 계산해주세요.
-- 쿼리 작성하는 목표, 확인할 지표 : trainer별 조건에 맞는 포켓몬 수
-- 쿼리 계산 방법 : 
  -- 1) trainer_pokemon + pokemon 조인한 뒤, over_100, under_100 컬럼을 추가
  -- 2) trainer_id 기준으로, SUM(over_100) & SUM(under_100)
-- 데이터의 특징 : status!="Released" 조건 필요!

SELECT
  -- tp.id,
  -- pokemon_id,
  trainer_id,
  t.name AS trainer_name,
  -- p.attack,
  SUM(IF(attack>=100, 1, 0)) AS over_100,
  SUM(IF(attack<100, 1, 0)) AS under_100,
FROM basic.trainer_pokemon AS tp
LEFT JOIN basic.pokemon AS p
ON tp.pokemon_id = p.id
LEFT JOIN basic.trainer as t
ON tp.trainer_id = t.id
WHERE
  status != "Released"
GROUP BY 1,2

 

✅강사님 풀이 과정

1) 일단 '보유' 포켓몬 조건을 충족하는 테이블을 만들고 시작 (WITH문)

2) pokemon 테이블 JOIN 후, COUNTIF로 개수 세고 컬럼으로 추가!

• 결과 검증을 위해 5번 트레이너(high 0개 low 7개) 기준으로 체크 ⇒ trainer_id, pokemon_id, attack 만 SELECT로 출력해서 직접 세어보니 ok!

3) 위 결과도 WITH문으로 테이블 지정하고, trainer 테이블 추가로 JOIN 해서 name 출력하면 끝! 

# 전체 코드

WITH active_n_training_pokemon AS(
  SELECT
    *
  FROM basic.trainer_pokemon
  WHERE
    status IN ("Active", "Training")
), trainer_attack_cnt AS(
  SELECT
    atp.trainer_id,
    COUNTIF(p.attack >= 100) AS high_attack_cnt,
    COUNTIF(p.attack < 100) AS low_attack_cnt
  FROM active_n_training_pokemon AS atp
  LEFT JOIN basic.pokemon AS p
  ON atp.pokemon_id = p.id
  GROUP BY
    atp.trainer_id
)

SELECT
  -- * -- 최초 확인
  t.name,
  tac.*
FROM trainer_attack_cnt AS tac
LEFT JOIN basic.trainer as t
ON tac.trainer_id = t.id

 

---

🙏References

• 카일스쿨 님의 인프런 강의 (https://www.inflearn.com/course/%EC%B4%88%EB%B3%B4%EC%9E%90%EB%A5%BC-%EC%9C%84%ED%95%9C-%EB%B9%85%EC%BF%BC%EB%A6%AC-sql-%EC%9E%85%EB%AC%B8/dashboard) 를 듣고 정리한 내용입니다.