Written by xi-jjun
on 

BigQuery - 연습문제 풀이 2

BigQuery

개요

인프런 강의 추천을 받게 됐고, 회사에서 현재 사용하고 있는 툴이어서 알고쓰자는 마음에 공부하게 됐습니다.


연습문제 풀이

문제1) 포캣몬 중에서 type2 값이 없는 포캣몬의 수를 구하는 쿼리를 작성해라

-- 총 135건
SELECT
  COUNT(*) AS cnt
FROM `basic.pokemon`
WHERE 
  type2 IS NULL;


문제2) type2가 없는 포캣몬의 type1 과 type1 의 포캣몬의 수를 구하는 쿼리를 작성해라 (type1의 포캣몬의 수가 큰 순서로 정렬)

SELECT
  type1,
  COUNT(*) AS type1_cnt
FROM `basic.pokemon`
WHERE 
  type2 IS NULL
GROUP BY
  type1
ORDER BY
  type1_cnt DESC;


문제3) type2에 상관없이 type1 의 포캣몬의 수를 구하는 쿼리를 작성해라

SELECT
  type1,
  COUNT(*) AS cnt
FROM `basic.pokemon`
GROUP BY
  type1;


문제4) 전설 여부에 따른 포켓몬 수를 구하는 쿼리를 작성해라

SELECT
  is_legendary,
  COUNT(*) AS cnt
FROM `basic.pokemon`
GROUP BY
  is_legendary; -- GROUP BY 1 로 써도 된다. select 대상 1번째 컬럼으로 GROUP BY하겠다는 의미. 빠른 쿼리 작성이 가능해진다.


문제5) 동명이인이 존재하는 이름을 구하는 쿼리를 작성해라

SELECT
  name
FROM `basic.trainer`
GROUP BY
  name
HAVING
  COUNT(*) > 1;


문제6) trainer 테이블에서 Iris트레이너 정보를 알 수 있는 쿼리를 작성해라

SELECT
  *
FROM `basic.trainer`
WHERE 
  name = 'Iris';


문제7) trainer 테이블에서 Iris, Whitney, Cynthia 트레이너 정보를 구하는 쿼리를 작성해라

SELECT
  *
FROM `basic.trainer`
WHERE 
  name IN ('Iris', 'Whitney', 'Cynthia');


문제 8~10은 스킵 (이전에 풀었던 문제와 비슷)


문제11) type2값이 존재하는 포켓몬 중 제일 많은 type1을 구하는 쿼리를 작성해라

SELECT
  type1,
  COUNT(*) AS cnt
FROM `basic.pokemon`
WHERE
  type2 IS NOT NULL
GROUP BY
  type1
ORDER BY
  cnt DESC
LIMIT 1;


문제12) 하나의 타입만 존재하는 포켓몬 중 가장 많은 type1을 구하는 쿼리를 작성해라

SELECT
  type1,
  COUNT(*) AS cnt
FROM `basic.pokemon`
WHERE
  (type1 IS NULL AND type2 IS NOT NULL)
  OR (type1 IS NOT NULL AND type2 IS NULL)
GROUP BY
  type1
ORDER BY
  COUNT(*) DESC
LIMIT 1;


문제15) 트레이너가 보유한 포켓몬이 가장 많은 트레이너를 구하는 쿼리를 작성해라

SELECT
  name
FROM `basic.trainer`
WHERE id = (
  SELECT 
    trainer_id
  FROM (
    SELECT
      DISTINCT
      trainer_id,
      pokemon_id
    FROM `basic.trainer_pokemon`
    GROUP BY
      trainer_id, pokemon_id
  ) AS tmp
  GROUP BY
    trainer_id
  ORDER BY
    COUNT(*) DESC
  LIMIT 1
);

-- 정답
-- 간과했던 사실 : 트레이너는 같은 포켓몬을 여러마리 가질 수 있다는 사실
--   - 해당 사실을 알기 위해서는 trainer_pokemon 테이블을 잘 봤어야 했음
--   - 해당 테이블을 보면 pokemon_id가 같은 경우가 있지만, 그 이외의 데이터가 다름. 따라서 같은 종류의 포켓몬을 여러마리 가질 수 있었음을 알 수 있음.
SELECT
  trainer_id,
  COUNT(pokemon_id) AS pokemon_count
FROM `basic.trainer_pokemon`
GROUP BY
  pokemon_id

-- 수정.
-- 개인적으로 실제로 업무를 진행할 때에는 단순 trainer_id 가 아니라, 실제로 '이름'이 누군지 궁금할 것임.
-- 따라서 서브쿼리로 가장 많은 포켓몬을 보유한 트레이너(id=17)의 이름을 구하는 쿼리를 작성
SELECT
  name
FROM `basic.trainer`
WHERE id = (
  SELECT
    trainer_id
  FROM `basic.trainer_pokemon`
  GROUP BY
    trainer_id
  ORDER BY
    COUNT(*) DESC
  LIMIT 1
)


문제16) 포켓몬을 가장 많이 풀어준 트레이너를 구하는 쿼리를 작성해라

SELECT
  name
FROM `basic.trainer`
WHERE id = (
  SELECT
    trainer_id
  FROM `basic.trainer_pokemon`
  WHERE status = 'Released'
  GROUP BY
    trainer_id
  ORDER BY
    COUNT(*) DESC
  LIMIT 1
)


문제17) 포켓몬을 풀어준 비율이 20%가 넘는 트레이너를 구하는 쿼리를 작성해라

SELECT
  trainer.id AS trainer_id,
  trainer.name AS trainer_name
FROM (
  SELECT
    trainer_id,
    COUNT(pokemon_id) AS total_pokemon_cnt
  FROM `basic.trainer_pokemon`
  GROUP BY
    trainer_id
) AS total_cnt
JOIN
  (
    SELECT
      trainer_id,
      COUNT(pokemon_id) AS total_released_pokemon_cnt
    FROM `basic.trainer_pokemon`
    WHERE status = 'Released'
    GROUP BY
      trainer_id
  ) AS released_cnt ON released_cnt.trainer_id = total_cnt.trainer_id
JOIN
  `basic.trainer` AS trainer ON trainer.id = total_cnt.trainer_id
WHERE
  (total_released_pokemon_cnt / total_pokemon_cnt) > 0.2

-- COUNTIF 함수에 대해서 알았으면 더 간단하게 작성이 가능했을 듯 하다
SELECT
  trainer_id,
  COUNTIF(status = 'Released') AS released_cnt, -- 풀어준 포켓몬 수
  COUNT(pokemon_id) AS total_cnt -- 전체 잡은 포켓몬 수
FROM `basic.trainer_pokemon`
GROUP BY
  trainer_id
HAVING
  (released_cnt / total_cnt) > 0.2


References