[SQL Runday] HackerRank - Ollivander's Inventory

해리포터와 그 친구들은 올리밴더의 지팡이 상점에 마침내 론이 찰리의 낡고 오래된 지팡이를 바꾸기 위해 왔다. 헤르미온느는 지팡이를 선택하는 가장 최선의 방법은 power과 age에 따른 지팡이를 사기 위해 필요한 최소한의 골드 갈레온을 알아보는 것이라 판단했다. (그리고 non-evil 이어야 함.)

• 론이 관심있는 지팡이의 id, age, coins_needed, power를 출력하는 쿼리를 작성해라.
• power에 따라 내림차순으로 정렬해라.
• 같은 power라면 age에 따라 내림차순으로 정렬해라.

Table

• Wands
  
• Wands_Property
  

Key points

• 서브 쿼리를 통해 최소 coins_needed 값을 구하는 서브 테이블을 만들고, 메인 쿼리에서 그를 참조하는 where 조건을 넣어야함.
• Age, Power 값이 각각 같은 지팡이들 중에서 coins_needed 값을 최소로 하는 지팡이를 골라야 한다. >> 이 구문이 문제에 명확하게 표현되어 있지 않아서, 좀 헤멨다;;
• Age, Power 값이 같을때 최소 coins_needed 값을 조회하는 서브 쿼리

  select min(w.coins_needed)
  from wands w
  join wands_property wp
  on w.code = wp.code
  group by w.power, w.age

  이럴 경우 최소 coins_needed 값을 갖는 지팡이들이 쭉 조회되기 때문에, 우리는 메인 쿼리에서 조회하는 지팡이와 같은 지팡이를 조회해야 한다. 이 때 where절에 조건을 넣어준다.

첫 시도

select w.id, wp.age, w.coins_needed, w.power from wands w
join wands_property wp
on w.code = wp.code
where wp.is_evil = 0
AND w.coins_needed = (select min(coins_needed)
                       from wands w1
                       join wands_property wp1
                       on w1.code = wp1.code
                       where w.power = w1.power AND wp.age = wp1.age
                       group by w1.power, wp1.age
                      )
order by w.power DESC, wp.age DESC

이렇게 했을 경우 PASS 하지만, 똑같은 wands 테이블과 wands_property 테이블을 JOIN하는 쿼리를 또 써줘야 하나? 싶어서 메인 테이블에 썼던 테이블을 그대로 가져오고 싶었음.

• 여기서 참고할만한 부분은 문제에서 code : age 가 1:1 대응한다는 조건을 줬기 때문이다. code와 age 값은 같으므로, group by 할 때 age 대신 code로 해도 최소 coins 칼럼 값을 구할 수 있다.
• min(coins_needed) 값을 그냥 coins_needed 값으로 하는 테이블을 메인 테이블로 만들고, 이에 맞는 code, power를 갖는 지팡이만 조회하여 다른 세부 정보를 붙여주는 것이다.

최종 코드

select w.id, wp.age, m.coins_needed, w.power
from (select min(coins_needed) as coins_needed, code, power
	  from wands
      group by code, power)as m
inner join wands w -- m 테이블에 있는 지팡이만 조회하기 위해 inner join
		on w.code = m.code 
		and w.power = m.power
		and w.coins_needed = m.coins_needed 
        -- 얘까지 조건에 걸어주지 않으면, 그냥 code, power 같은 지팡이들의 
        -- coins_needed 값이 바뀐다. 최소값인 지팡이만 가져와야 한다.
inner join wands_property wp 
on wp.code = m.code
where wp.is_evil = 0
order by w.power DESC, wp.age DESC

두 방법 중 뭐가 더 깔끔한가?는 잘 모르겠다. 근데 join on 조건을 저렇게 다는 것보다 그냥 깔끔하게 서브 테이블을 밑에서 불러오는게 더 실행에 좋을지도?

Errors

• w.coins_needed를 서브쿼리에서 가져온 값으로 어떻게 지정하나 처음엔 고민했는데,
• where 절로 쉽게 w.coins_neede = 로 지정해주면 되는걸 알았다.
• 또 서브 쿼리 안의 where절로 메인 쿼리에 있는 테이블 칼럼을 활용하여 조건을 지정해 줄 수 있는 점도 새롭게 알았다. 이를 통해 w.power = w1.power, wp.age = wp1.age 조건을 걸어서 해당 조건에 맞는 값을 wp.coins_needed로 대신 조회할 수 있었다.