11.20(목)(sql 2일차)

오늘 배운 것 : 시간데이터, 조건문, JOIN

문제 1

트레이너가 포켓몬을 포획한 날짜(catch_date)를 기준으로, 2023년 1월에 포획한 포켓몬의 수를 계산해주세요.

# 쿼리를 작성하는 목표, 확인할 지표 : Y=2023 / M = 1 / 포켓몬 수
# 쿼리 계산 방법 : 
# 데이터의 기간 : 
# 사용할 테이블 : trainer_pokemon
# Join KEY : 
# 데이터 특징 : 

SELECT
  count(pokemon_id)
FROM `basic.trainer_pokemon`
WHERE
  EXTRACT(YEAR FROM catch_date) = 2023 and
  EXTRACT(MONTH FROM catch_date) = 1

문제 2

배틀이 일어난 시간(battle_datetime)을 기준으로, 오전 6시에서 오후 6시 전 사이에 일어난 배틀의 수를 계산해주세요.

# 쿼리를 작성하는 목표, 확인할 지표 : 오전 6시 ~ 오후 6시 / 배틀의 수
# 쿼리 계산 방법 : 
# 데이터의 기간 : 
# 사용할 테이블 : battle
# Join KEY : 
# 데이터 특징 : 

SELECT
  count(id)
FROM `basic.battle`
WHERE
  EXTRACT(HOUR FROM battle_datetime)>=6 and
  EXTRACT(HOUR FROM battle_datetime)<18
  
#between 함수를 써도 된다! 참고~!
#EXTRACT(HOUR FROM battle_datetime) BETWEEN 6 AND 18

문제 3

각 트레이너별로 그들이 포켓몬을 포획한 첫 날(catch_date)을 찾고, 그 날짜를 'DD/MM/YYYY' 형식으로 출력해주세요. (2024-01-01 => 01/01/2024)

# 쿼리를 작성하는 목표, 확인할 지표 : 트레이너 별 / 포켓몬 포획한 첫날 / dd mm yyyy
# 쿼리 계산 방법 : 
# 데이터의 기간 : 
# 사용할 테이블 : 
# Join KEY : 
# 데이터 특징 : 

    SELECT
      trainer_id,
      FORMAT_DATETIME("%d/%m/%Y", MIN(catch_date)) as date
    FROM basic.trainer_pokemon
    GROUP BY trainer_id
    ORDER BY trainer_id asc

문제 4

배틀이 일어난 날짜(battle_date)를 기준으로, 요일별로 배틀이 얼마나 자주 일어났는지 계산해주세요.

# 쿼리를 작성하는 목표, 확인할 지표 : 날짜 기준 / 요일 별 / 배틀 횟수
# 쿼리 계산 방법 : 
# 데이터의 기간 : 
# 사용할 테이블 : 
# Join KEY : 
# 데이터 특징 : 

SELECT
    FORMAT_DATE("%A", battle_date) as day,
    count(id) as cnt
FROM basic.battle
GROUP BY day

문제 5

트레이너가 포켓몬을 처음으로 포획한 날짜와 마지막으로 포획한 날짜의 간격이 큰 순으로 정렬하는 쿼리를 작성해주세요.

# 문제 : 트레이너가 포켓몬을 처음으로 포획한 날짜와 마지막으로 포획한 날짜의 간격이 큰 순으로 정렬하는 쿼리를 작성해주세요.
# 쿼리를 작성하는 목표, 확인할 지표 : 날짜의 간격 = DATE_DIFF / 큰 순 정렬 ORDER BY DESC
# 쿼리 계산 방법 : 
# 데이터의 기간 : 
# 사용할 테이블 : 
# Join KEY : 
# 데이터 특징 : 

SELECT
  trainer_id,
  DATETIME_DIFF(first_datetime, second_datetime, DAY) AS day_diff,
  
FROM (
  SELECT
    MIN(catch_datetime) AS second_datetime,
    MAX(catch_datetime) AS first_datetime,
    trainer_id
  FROM basic.trainer_pokemon
  GROUP BY trainer_id
)
ORDER BY day_diff DESC

# 배운 점
# 1. MIN, MAX는 SELECT 안에서 쓸 수 있다.
# 2. FROM 서브쿼리에서 이미 묶인 걸 밖에서 다시 묶을 수 없다!

문제 6

포켓몬의 'speed'가 70 이상이면 '빠름', 그렇지 않으면 '느림'으로 표시하는 새로운 컬럼 'Speed_Category'를 만들어 주세요