CodeKata

SQL

172. Top Competitors

• 작성한 쿼리

SELECT
  h.hacker_id
  , name
FROM
  submissions s
  JOIN hackers h
  USING(hacker_id)
  JOIN challenges c
  USING(challenge_id)
  JOIN difficulty d
  USING(difficulty_level)
WHERE
  s.score = d.score
GROUP BY
  h.hacker_id
  , name
HAVING
  COUNT(h.hacker_id) > 1
ORDER BY
  COUNT(h.hacker_id) DESC
  , h.hacker_id
;

173. Ollivander's Inventory

• 작성한 쿼리

WITH rnk_coin AS (
  SELECT
    w.id
    , p.age
    , w.power
    , w.coins_needed
    , RANK() OVER (PARTITION BY p.age, w.power ORDER BY w.coins_needed) rnk
  FROM
    wands w
    JOIN wands_property p
    ON w.code = p.code
  WHERE
    p.is_evil = 0
  )
SELECT
  id
  , age
  , power
  , coins_needed
FROM
  rnk_coin
WHERE
  rnk = 1
ORDER BY
  power DESC
  , age DESC
;

→ 제출하면 계속 오류나서 다른 방법으로 다시 풀었음

SELECT
  w.id
  , p.age
  , w.coins_needed
  , w.power
FROM
  (
  SELECT
    *
  FROM
    Wands
  WHERE 
    (code,power,coins_needed) IN 
      (
      SELECT
        code
        , power
        , MIN(coins_needed)
      FROM
        Wands
      GROUP BY
        code
        , power
      )
  ) AS w
  JOIN 
  (
  SELECT
    code
    , age
  FROM
    Wands_Property
  WHERE
    is_evil = 0
  ) AS P
ON w.code = p.code
ORDER BY
  w.power DESC
  , p.age DESC
;

QCC 4회차

시험&해설

태블로 특강

메트릭 태블로 시각화

회고

• 주말에 태블로 공부하고 QCC 복습해야지…