[강의 수강 15일차] 최종 연습 문제(수강 완료-)

기본적인 SQL 강의를 듣고 마무리로 최종 연습 문제를 풀어 보는 시간!

이미 현업에서 SQL을 활용하고 있기에 점검하는 정도로 가볍게 해보고자 한다.

 

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

# 쿼리를 작성하는 목표, 확인할 지표 : 트레이너의 이름, 보유한 포켓몬 수, 평균 레벨
# 쿼리 계산 방법 : 트레이너가 보유한 포켓몬의 포켓몬 수, 평균 레벨을 계산 + 트레이너 테이블과 연결해서 트레이너의 이름 출력
# 데이터 기간 : X
# 사용할 테이블 : trainer_pokemon, trainer
# Join Key : trainer_pokemon.trainer_id = trainer.id
# 데이터 특징 : trainer_pokemon 의 status => Realeased 는 방출

# STEP 1. 트레이너가 보유한 포켓몬의 평균 레벨, 포켓몬 수
# 단계별로 값을 확인할 때는 DISTINCT를 통해 확인할 수 있음 
WITH trainer_avg_level AS (
    SELECT
        trainer_id,
        ROUND(AVG(level),2) AS avg_level,
        COUNT(id) AS pokemon_cnt
    FROM basic.trainer_pokemon
    WHERE 
        status != 'Released'
    GROUP BY
        trainer_id
)

# STEP 2. 1에서 만든 테이블 + trainer 테이블 합쳐 trainer의 name 출력
SELECT
	t.name
    tal.avg_level,
    tal.pokemon_cnt
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

✔️ 중복을 제거할 때는 DISTINCT를 사용해도 되고, GROUP BY도 사용할 수 있다. 

 

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

# 쿼리를 작성하는 목표, 확인할 지표 : 포켓몬 타입1, 포켓몬 이름, 포켓몬의 포획 횟수 
# 쿼리 계산 방법 : trainer_pokemon + pokemon type1, kor_name
# 데이터 기간 : X
# 사용할 테이블 : trainer_pokemon, trainer
# Join Key : trainer_pokemon.trainer_id = trainer.id
# 데이터 특징 : X

SELECT
	p.type1,
    p.kor_name,
    COUNT(tp.id) AS cnt
    tal.avg_level,
    tal.pokemon_cnt
FROM basic.trainer_pokemon AS tp
LEFT JOIN basic.pokemon AS p
ON tp.id = p.trainer_id 
GROUP BY
	type1, kor_name
ORDER BY 
	cnt DESC

✔️ 여기서 결과값은 3마리, 조건에 따라 어느 정도를 볼 것인 지 파악하는 게 필요하다. 

 

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

# 쿼리를 작성하는 목표, 확인할 지표 : 트레이너마다 전설의 포켓몬을 얼마나 보유? 일반 포켓몬 수는?
# 쿼리 계산 방법 : trainer_pokemon + pokemon > 전설 T/F > 전설 여부 따라 얼마 있는 지 COUNT > trainer JOIN 이름 출력
# 데이터 기간 : X
# 사용할 테이블 : trainer_pokemon, pokemon, trainer
# Join Key : trainer_pokemon.pokemon_id = pokemon.id, lc.trainer_id = trainer_id
# 데이터 특징 : 전설 여부에 따라 COUNT = COUNTIF, SUM(CASE WHEN ~) 

WITH lengendary_cnt AS (
    SELECT
        tp.trainer_id,
        --tp.pokemon_id, 초반 확인용
        --p.kor_name, 초반 확인용 
        SUM(CASE WHEN p.is_legendary IS TRUE THEN 1 ELSE 0 END) AS legendary_cnt, # IS TRUE 생략 가능 
        SUM(CASE WHEN p.is_legendary IS NOT TRUE THEN 1 ELSE 0 END) AS normal_cnt #일반 포켓몬
        -- SUM(CASE WHEN NOT p.is_legendary TRUE THEN 1 ELSE 0 END) AS normal_cnt 위와 동일한 결과 #일반 포켓몬
    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,
        tp.pokemon_id,
        p.kor_name
)

# legendary_cnts+trainer
SELECT
	t.name AS trainer_name,
    lc.legendary_cnt,
    lc.normal_cnt
FROM basic.trainer AS t
LEFT JOIN legendary_cnts AS lc
ON t.id = lc.trainer_id
WHRER
	lc.legendary_cnt >= 1

 

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

# 쿼리를 작성하는 목표, 확인할 지표 : 가장 승리가 많은 트레이너, 트레이너 이름, 승리 횟수, 보유한 포켓몬 수, 평균 포켓몬 레벨 
# 쿼리 계산 방법 : battle 테이블 > winner_id 승리 횟수 COUNT + 트레이너 이름 + trainer_pokemon 포켓몬 수, 레벨 
# 데이터 기간 : X
# 사용할 테이블 : battle, trainer_pokemon, trainer
# Join Key : battle.winner_id = trainer.id => lc.trainer_id = trainer_pokemon.trainer_id
# 데이터 특징 : battle 테이블 확인 필요 

# STEP 1. winner_id, COUNT(승리 횟수)
    WITH winner_counts AS (
    SELECT
        winner_id,
        COUNT(winner_id) AS win_counts
    FROM basic.battle
    WHERE
        winner_id IS NOT NULL
    GROUP BY
        winner_id 
),

# STEP 2. 이름 추가
top_winner AS(
    SELECT
        wc.winner_id AS trainer_id,
        wc.win_counts,
        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_counts DESC -- ㄱ 방법 채택 
    LIMIT 1
    # 가장 승리가 많은
        # ㄱ) 이름을 추가한 결과에서 필터링해서 가장 승리가 많은 trainer_id 1개만 뽑을 수 있음
        # ㄴ) 평균 포켓몬 레벨, 포켓몬 수 추가한 후에 trainer_id 1개만 뽑을 수 있음
        # ORDER BY + LIMIT 1
        # 둘 다 가능, 데이터가 많고 1개만 뽑는 게 확실하다면 ㄱ 방법 추천
        # 결과 어떻게 사용할까에 따라 계속 바꾸거나 TOP 10 보고 싶다 등이면 ㄴ 선택 
)

# STEP 3. 평균 포켓몬 레벨, 포켓몬 수 추가
SELECT
	tw.trainer_id,
    tw.trainer_name,
    tw.win_counts,
    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 IN ("Active", "Training) 
GROUP BY
	tw.trainer_id,
    tw.trainer_name,
    tw.win_counts

✔️ WHERE에 "Active", "Training" 이라고 한 만큼 현업에서도 조건을 걸어 주어야 하는 경우가 있으니 유의하기!

실제로 비회원/회원을 보고자 할 때 필터링하는 단계 중요! 

 

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

# 쿼리를 작성하는 목표, 확인할 지표 : 트레이너 포켓몬 총 공격+방어 
# 쿼리 계산 방법 : trainer_pokemon 테이블 > pokemon, attack+defense 
# 데이터 기간 : X
# 사용할 테이블 : pokemon, trainer_pokemon, trainer
# Join Key : pokemon.id = trainer_pokemon.pokemon_id
# 데이터 특징 : 

# STEP 1. 트레이너 보유한 포켓몬들의 attack, defense
WITH total_stat 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
),

# STEP 2. 트레이너 이름에 따른 출력 
SELECT
	t.name,
    ts.trainer_id,
    ts.total_stat
FROM total_stat AS ts
LEFT JOIN basic.trainer AS t
ON ts.trainer_id = t.id
ORDER BY 
	total_stat DESC

 

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

# 쿼리를 작성하는 목표, 확인할 지표 : 포켓몬의 레벨 차이(최고-최저 레벨)  
# 쿼리 계산 방법 : trainer_pokemon에서 포켓몬의 최고 레벨, 최저 레벨 계산 > 차이 구하기 > 차이 큰 순 정렬  
# 데이터 기간 : X
# 사용할 테이블 : pokemon, trainer_pokemon
# Join Key : pokemon.id = trainer_pokemon.pokemon_id
# 데이터 특징 : X 

# STEP 1. 레벨 차이 구하기 
WITH level_diff AS (
    SELECT
       	tp.pokemon_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 
    -- WHERE pokemon_id = 12 > 실제 값 확인할 때 활용하기 
    GROUP BY
    	tp.pokemin_id
),

# STEP 2. 가장 값 차이 많이 나는 포켓몬 추출 
-- 추출 지표를 요약해서 볼 수 있으므로 with 문을 써서 가독성 높이는 것도 방법 
SELECT
	kor_name,
    level_differencs
FROM level_diff
ORDER BY
	level_difference DESC
LIMIT

 

7. 각 트레이너가 가진 포켓몬 중에서 공격력(attack)이 100 이상인 포켓몬과 100 미만인 포켓몬의 수를 각각 계산해주세요. 트레이너 이름과 두 조건에 해당하는 포켓몬 수 출력해주세요. 

# 쿼리를 작성하는 목표, 확인할 지표 : 트레이너 이름 | 공격력 100이상인 포켓몬 | 100 미만인 포켓몬 수
# 쿼리 계산 방법 : COUNTIF 트레이너 이름 | 공격력 100이상인 포켓몬 
# 데이터 기간 : X
# 사용할 테이블 : trainer, trainer_pokemon
# Join Key : trainer.id = trainer_pokemon.trainer_id
# 데이터 특징 : 보유한 포켓몬  

WITH active_and_training_pokemon AS (
    SELECT
       	*
    FROM basic.trainer_pokemon
    WHERE 
    	status IN ("Active", "Training") 
),

AS trainer_high_and_low_attack_cnt (
    SELECT
        atp.trainer_id,
        COUNTIF(p.attack >= 100) AS high_attack_cnt,
        COUNTIF(p.attack < 100) AS low_attack_cnt
    FROM active_and_training_pokemon AS atp
    LEFT JOIN basic.pokemon AS p
    ON atp.pokemon_id = p.id
    GROUP BY
        p.attack 
)

SELECT
	t.name,
    thala.*
FROM trainer_high_and_low_attack_cnt AS thala
LEFT JOIN basic.trainer AS t
ON thala.trainer_id = t.id 

-- 맞는 지 데이터 확인
#trainer_id = 5, high_attack_cnt = 0, low_attack_cnt = 7 > 값 확인하기 
-- SELECT
	-- atp.trainer_id,
    -- atp.pokemon_id,
    -- p.attack
-- FROM active_and_training_pokemon AS atp
-- LEFT JOIN basic.pokemon AS p
-- ON atp.pokemon_id = p.id
-- WHERER
	-- trainer_id = 5

 

✅ 쿼리 작성할 때의 핵심 : 테이블 확인해 어떤 데이터 / 컬럼 / 목적을 확인하는 게 가장 중요 

 

총 9시간 짜리 구글 빅쿼리 강의 수강 완료 -

 

현업에서 데이터 추출을 하며 현업에서 하는 것을 늘 연습 문제로 생각하고 진행,

개념들을 다시 복습해가며 강의를 활용했더니 로직 이해하기가 더 수월했다.

 

SQL 을 이전에 배운 적이 있었기에 더 복습하는 식으로 수강했는 데,

한번 쯤 꼬옥 들어보면 좋은 강의라는 생각이 든다. 

 

어쨌든 약 6주 정도 시간을 쪼개서 들은 SQL 강의 완 - 

현업에서 활용하면서 확장하도록 하고 데이터 리터러시 강의도 시간 내서 결제하고 들어야겠다!