4회차 인프런 스터디

BigQuery 입문 연습 문제

 

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

WITH trainer_avg_level AS (
  -- 각 트레이너별 평균 레벨과 포켓몬 개수를 계산하기 위한 쿼리
  SELECT
    trainer_id,  -- 각 트레이너의 고유 ID
    ROUND(AVG(level), 2) AS avg_level,  -- 트레이너가 보유한 포켓몬들의 평균 레벨 (소수점 2자리로 반올림)
    COUNT(id) AS pokemon_cnt  -- 트레이너가 보유한 포켓몬의 개수 (단, "Released" 상태가 아닌 포켓몬만 포함)
  
  FROM basic.trainer_pokemon  -- 기본 스키마의 trainer_pokemon 테이블에서 데이터 조회
  
  WHERE
    status != "Released"  -- "Released" 상태가 아닌 포켓몬만 포함 (즉, 현재 보유 중인 포켓몬만)
  
  GROUP BY
    trainer_id  -- 각 트레이너별로 그룹화하여 계산
)

 

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

SELECT
  kor_name,  -- 포켓몬의 한국어 이름
  
  COUNT(tp.id) AS cnt  -- 해당 포켓몬의 등장 횟수 (트레이너가 보유한 포켓몬 수)

FROM basic.trainer_pokemon AS tp  -- 트레이너와 포켓몬 관계를 나타내는 테이블 (trainer_pokemon)
  
LEFT JOIN basic.pokemon AS p  -- pokemon 테이블과 LEFT JOIN하여 포켓몬의 세부 정보를 가져옴
ON tp.pokemon_id = p.id  -- trainer_pokemon 테이블의 pokemon_id와 pokemon 테이블의 id가 일치하는 데이터를 연결

GROUP BY
  type1,  -- 포켓몬의 타입 1 (예: 불, 물, 풀 등)으로 그룹화
  kor_name  -- 포켓몬의 한국어 이름으로 그룹화

ORDER BY
  cnt DESC  -- 등장 횟수를 기준으로 내림차순 정렬 (가장 많이 등장한 포켓몬부터)

LIMIT 3  -- 등장 횟수가 많은 상위 3개 포켓몬만 출력

 

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

WITH legendary_cnts AS (

  SELECT
    tp.trainer_id,  -- 트레이너 ID
    SUM(CASE WHEN p.is_legendary IS TRUE THEN 1 ELSE 0 END) AS legendary_cnt,  -- 전설의 포켓몬 수
    SUM(CASE WHEN p.is_legendary IS NOT TRUE THEN 1 ELSE 0 END) AS nomal_cnt  -- 일반 포켓몬 수

  FROM basic.trainer_pokemon AS tp  -- 트레이너와 포켓몬의 관계 테이블
  LEFT JOIN basic.pokemon AS p  -- 포켓몬 테이블 (포켓몬 정보를 얻기 위해 LEFT JOIN 사용)
  ON tp.pokemon_id = p.id  -- 트레이너와 포켓몬을 연결하는 조건

  WHERE tp.status IN ("Active", "Training")  -- 트레이너 상태가 "Active" 또는 "Training"인 경우만 필터링

  GROUP BY
    tp.trainer_id,  -- 트레이너별로 그룹화
    tp.pokemon_id,  -- 포켓몬별로 그룹화 (이 부분은 총합을 원한다면 제거해도 좋음)
    p.kor_name  -- 포켓몬의 이름 (이 부분도 결과에서 필요 없다면 제거 가능)

)

 

4. 가장 승리가 많은 트레이너 ID, 트레이너 이름, 승리한 횟수, 보유한 포켓몬의 수, 평균 포켓몬의 레벨을 출력해주세요. 단, 포켓몬의 레벨은 수수점 2째 자리에서 반올림해주세요.

SELECT
  tw.trainer_id,  -- 트레이너 ID
  tw.trainer_name,  -- 트레이너 이름
  tw.win_counts,  -- 트레이너의 승리 횟수
  COUNT(tp.pokemon_id) AS pokemon_cnt,  -- 트레이너가 보유한 포켓몬 수
  ROUND(AVG(tp.level), 2) AS avg_level  -- 트레이너가 보유한 포켓몬의 평균 레벨 (소수점 2자리까지 반올림)

FROM top_winner AS tw  -- 승리한 트레이너 테이블
LEFT JOIN baisc.trainer_pokemon AS tp  -- 트레이너의 포켓몬 정보 테이블
ON tw.trainer_id = tp.trainer_id  -- 트레이너 ID로 두 테이블을 조인

WHERE
  tp.status IN ("Active", "Training")  -- 트레이너 상태가 "Active" 또는 "Training"인 경우만 필터링

GROUP BY
  tw.trainer_id,  -- 트레이너별로 그룹화
  tw.trainer_name,  -- 트레이너 이름으로 그룹화
  tw.win_counts  -- 승리 횟수로 그룹화

 

5. 트레이너가 보유한 포켓몬들의 총 공격력(attack)과 방어력(defence)의 합을 계산하고, 가장 높은 합을 가진 트레이너를 찾는 쿼리

-- (1) 트레이너가 보유한 포켓몬들의 attack과 defence 합계 계산
WITH total_stats AS (

  SELECT
    tp.trainer_id,  -- 트레이너 ID
    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  -- 포켓몬 ID로 조인

  GROUP BY
    tp.trainer_id  -- 트레이너별로 그룹화
)

-- (2) 트레이너의 이름과 총 스탯 합계를 출력
SELECT
  t.name,  -- 트레이너 이름
  ts.trainer_id,  -- 트레이너 ID
  ts.total_stat  -- 트레이너의 포켓몬들의 총 공격력 + 방어력 합계

FROM total_stats AS ts  -- 총 스탯 합계 테이블
LEFT JOIN basic.trainer AS t  -- 트레이너 정보 테이블
ON ts.trainer_id = t.id  -- 트레이너 ID로 조인

ORDER BY
  total_stat DESC  -- 총 스탯 합계가 높은 순으로 정렬
LIMIT 1  -- 가장 높은 총 스탯을 가진 트레이너 1명만 출력

 

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

-- (1) 각 포켓몬의 최저 레벨과 최고 레벨 계산, 레벨 차이 구하기
WITH level_diff AS (

  SELECT
    tp.pokemon_id,  -- 포켓몬 ID
    p.kor_name,  -- 포켓몬 이름
    MIN(tp.level) AS min_level,  -- 포켓몬의 최저 레벨
    MAX(tp.level) AS max_level,  -- 포켓몬의 최고 레벨
    MAX(tp.level) - MIN(tp.level) AS level_difference  -- 레벨 차이 계산

  FROM basic.trainer_pokemon AS tp  -- 트레이너 포켓몬 정보 테이블
  LEFT JOIN basic.pokemon AS p  -- 포켓몬 테이블
  ON tp.pokemon_id = p.id  -- 포켓몬 ID로 조인

  GROUP BY
    tp.pokemon_id,  -- 포켓몬별로 그룹화
    p.kor_name  -- 포켓몬 이름으로 그룹화
)

-- (2) 레벨 차이가 가장 큰 포켓몬 출력
SELECT
  kor_name,  -- 포켓몬 이름
  min_level,  -- 최저 레벨
  max_level,  -- 최고 레벨
  level_difference  -- 레벨 차이

FROM level_diff  -- 레벨 차이 계산 테이블

ORDER BY
  level_difference DESC  -- 레벨 차이가 큰 순으로 정렬
LIMIT 1  -- 레벨 차이가 가장 큰 포켓몬 1개만 출력

 

7. 각 트레이너가 보유한 포켓몬 중에서 공격력(attack)이 100 이상인 포켓몬과 100 미만인 포켓몬의 수를 각각 계산하는 쿼리

-- (1) 트레이너의 포켓몬 중 상태가 "Active" 또는 "Training"인 포켓몬만 필터링
WITH active_and_training_pokomon AS (

  SELECT
    *  -- 트레이너와 포켓몬 정보 선택
  FROM basic.trainer_pokemon  -- 트레이너 포켓몬 정보 테이블
  WHERE
    status IN ("Active", "Training")  -- 상태가 "Active" 또는 "Training"인 포켓몬만 선택

),

-- (2) 각 트레이너별로 공격력 100 이상인 포켓몬과 100 미만인 포켓몬의 수 계산
trainer_high_and_low_attack_cnt AS (

  SELECT
    atp.trainer_id,  -- 트레이너 ID
    COUNTIF(p.attack >= 100) AS high_attack_cnt,  -- 공격력 100 이상인 포켓몬 수
    COUNTIF(p.attack < 100) AS low_attack_cnt  -- 공격력 100 미만인 포켓몬 수

  FROM active_and_training_pokomon AS atp  -- 상태가 "Active" 또는 "Training"인 포켓몬
  LEFT JOIN basic.pokemon AS p  -- 포켓몬 테이블
  ON atp.pokemon_id = p.id  -- 포켓몬 ID로 조인

  GROUP BY
    atp.trainer_id  -- 트레이너별로 그룹화
)

-- (3) 각 트레이너의 이름과 포켓몬 수 출력
SELECT
  t.name,  -- 트레이너 이름
  thala.*  -- 트레이너의 공격력 100 이상 및 미만인 포켓몬 수

FROM trainer_high_and_low_attack_cnt AS thala  -- 공격력 기준 포켓몬 수 계산 테이블
LEFT JOIN basic.trainer AS t  -- 트레이너 정보 테이블
ON thala.trainer_id = t.id  -- 트레이너 ID로 조인