서브쿼리 응용(4)

https://www.hackerrank.com/challenges/harry-potter-and-wands/problem?isFullScreen=true 

Ollivander's Inventory | HackerRank

 

Harry Potter and his friends are at Ollivander's with Ron, finally replacing Charlie's old broken wand.

Hermione decides the best way to choose is by determining the minimum number of gold galleons needed to buy each non-evil wand of high power and age. Write a query to print the id, age, coins_needed, and power of the wands that Ron's interested in, sorted in order of descending power. If more than one wand has same power, sort the result in order of descending age.

Input Format

The following tables contain data on the wands in Ollivander's inventory:

• Wands: The id is the id of the wand, code is the code of the wand, coins_needed is the total number of gold galleons needed to buy the wand, and power denotes the quality of the wand (the higher the power, the better the wand is).

Wands_Property: The code is the code of the wand, age is the age of the wand, and is_evil denotes whether the wand is good for the dark arts. If the value of is_evil is 0, it means that the wand is not evil. The mapping between code and age is one-one, meaning that if there are two pairs 

 

Sample Input

Wands Table:

Wands_Property Table:

Sample Output

9 45 1647 10
12 17 9897 10
1 20 3688 8
15 40 6018 7
19 20 7651 6
11 40 7587 5
10 20 504 5
18 40 3312 3
20 17 5689 3
5 45 6020 2
14 40 5408 1

Explanation

The data for wands of age 45 (code 1):

• The minimum number of galleons needed for wand(age=45,power=2)=6020
• The minimum number of galleons needed for wand(age=45,power=10)=1647

The data for wands of age 40 (code 2):

• The minimum number of galleons needed for wand(age=40,power=1)=5408
• The minimum number of galleons needed for wand(age=40,power=3)=3312
• The minimum number of galleons needed for wand(age=40,power=5)=7587
• The minimum number of galleons needed for wand(age=40,power=7)=6018

 

위 문제는 주어진 두 테이블 wands, wands_property에서 is_evil=0이면서 code,age,power별 coin_needed 값이 최소값이 위와 같이 주어졌을 때, 그에 해당하는 경우의 id,age,coin_needed,power를 구하는 문제이다.

 

 

정답

SELECT B.ID,A.AGE,A.MINCOIN,A.POWER FROM (
SELECT W.CODE,P.AGE,W.POWER,MIN(COINS_NEEDED) AS MINCOIN
FROM WANDS AS W
JOIN WANDS_PROPERTY AS P
ON W.CODE=P.CODE
WHERE P.IS_EVIL=0
GROUP BY W.CODE,P.AGE,W.POWER) AS A
JOIN WANDS B
ON B.CODE=A.CODE
WHERE A.MINCOIN=B.COINS_NEEDED
ORDER BY A.POWER DESC, A.AGE DESC

문제에서 요구하는 열은 id,age,coin_needed,power이지만 coin_needed는 code,age,power별 최소값인 경우이다. 따라서 

code,age,power와 min(coins_needed)를 구하는 서브쿼리를 먼저 만든다.  서브쿼리 테이블은 다음과 같다.

 

SELECT W.CODE,P.AGE,W.POWER,MIN(COINS_NEEDED) AS MINCOIN
FROM WANDS AS W
JOIN WANDS_PROPERTY AS P
ON W.CODE=P.CODE
WHERE P.IS_EVIL=0
GROUP BY W.CODE,P.AGE,W.POWER

code	age	power	min(coins_needed)
1	301	1	2701
1	301	2	4361

·

·

·

오른쪽 min(coin_needed)값을 가지는 id를 찾으면 된다. 이 문제는 wands 테이블에서의 id를 찾는 문제이므로 필자는 위 서브쿼리에 code열을 기준으로 wands테이블을 조인했다. 이때 where절로 기존 wands테이블의 coin_needed와 서브쿼리에서 구한 min(coin_needed)가 일치하는 행을 찾으면 정답이 나온다.

 

• 1038 496 4789 10 
  1130 494 9439 10
  1315 492 4126 10
  9 491 7345 10