๋ณธ๋ฌธ ๋ฐ”๋กœ๊ฐ€๊ธฐ

SQL

[SQL] HackerRank - Ollivander's Inventory ๋ฌธ์ œํ’€์ด

๋ฐ˜์‘ํ˜•

๐Ÿ”Š ๋ณธ ํฌ์ŠคํŒ…์—์„œ ์‚ฌ์šฉ๋˜๋Š” ํ…Œ์ด๋ธ”์˜ ์ž๋ฃŒ์™€ ์ถœ์ฒ˜๋Š” HackerRank ์ž„์„ ๋ฐํž™๋‹ˆ๋‹ค. ๋” ๋‹ค์–‘ํ•œ SQL ๋ฌธ์ œ๋ฅผ ํ’€์–ด๋ณด์‹œ๋ ค๋ฉด

HackerRank ์‚ฌ์ดํŠธ๋ฅผ ๋ฐฉ๋ฌธํ•ด ๋ณด์„ธ์š”!

 

HackerRank ์‚ฌ์ดํŠธ

 

์ด๋ฒˆ ํฌ์ŠคํŒ…์—์„œ๋Š” HackerRank์˜ Olivander's Inventory ๋ฌธ์ œ๋ฅผ ํ’€์–ด๋ณด๋ ค๊ณ  ํ•œ๋‹ค. ํ•ด๋‹น ๋ฌธ์ œ๋Š” ํ•„์ž๊ฐ€ ์•ฝ 1์‹œ๊ฐ„ ๋™์•ˆ ์ฉ”์ฉ” ๋งค๋‹ค๊ฐ€ ๋ช‡ ๋ฒˆ์˜ ์‹œ๋„ ๋์—๋„ ํ’€๋ฆฌ์ง€ ์•Š์•„์„œ ๋ฌธ์ œ์˜ Discussion์„ ๋ณด๊ณ  ์ดํ•ดํ•œ ๋ฌธ์ œ๋‹ค. ๊ฐœ์ธ์ ์œผ๋กœ ๋ฌธ์ œ๊ฐ€ ํ•ด๋ฆฌํฌํ„ฐ์™€ ์—ฐ๊ด€๋œ ์ด์•ผ๊ธฐ๋กœ ๋‚˜์˜ค๊ธธ๋ž˜ ์˜คํžˆ๋ ค ๋ฌธ์ œ๊ฐ€ ๋” ์ž˜ ์ฝํžˆ์ง€๋„ ์•Š์•˜๊ณ  ํ—ท๊ฐˆ๋ ธ๋‹ค.(Discussion์„ ๋ณด๋‹ˆ ๋ช‡ ์‚ฌ๋žŒ๋“ค์ด ๋ฌธ์ œ ์„ค๋ช…์ด ๋ช…์‹œ์ ์ด์ง€ ์•Š๋‹ค๊ณ  ๋ถˆ๋งŒ์„ ์ œ๊ธฐํ•œ ์˜๊ฒฌ๋“ค๋„ ๊ฝค ๋˜์—ˆ๋‹ค..)

 

์ด ๋ฌธ์ œ๋ฅผ ํ†ตํ•ด์„œ ๋ฐฐ์šฐ๊ฒŒ ๋œ ์ ์€ ์„œ๋ธŒ ์ฟผ๋ฆฌ๋ฅผ ์ž‘์„ฑํ•  ๋•Œ ๋ฉ”์ธ ์ฟผ๋ฆฌ์—์„œ ํ™œ์šฉํ•œ ํ…Œ์ด๋ธ”์˜ ์นผ๋Ÿผ์„ ์ฐธ๊ณ ํ•  ์ˆ˜ ์žˆ๋‹ค๋Š” ์ ์ด์—ˆ๋‹ค. ์ด์— ๋Œ€ํ•œ ์ž์„ธํ•œ ์‚ฌํ•ญ์€ ๋ฌธ์ œ๋ฅผ ํ’€์–ด๋ณด๋ฉด์„œ ์ดํ•ดํ•ด๋ณด์ž. ๋ฌธ์ œ ์›๋ณธ์€ ์—ฌ๊ธฐ๋ฅผ ํด๋ฆญํ•ด์„œ ํ™•์ธํ•˜์ž. ๋ฌธ์ œ์˜ ์š”๊ตฌ์‚ฌํ•ญ์€ ๋‹ค์Œ๊ณผ ๊ฐ™๋‹ค.

 

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 eachnon-evilwand of high power and age. Write a query to print theid,age,coins_needed, andpowerof the wands that Ron's interested in, sorted in order of descendingpower. If more than one wand has same power, sort the result in order of descendingage.
(ํ•ด๋ฆฌํฌํ„ฐ์™€ ์นœ๊ตฌ๋“ค์€ ์ฐฐ๋ฆฌ์˜ ์˜ค๋ž˜๋˜๊ณ  ๋ถ€์…”์ง„ ์ง€ํŒก์ด๋ฅผ ๊ต์ฒดํ•˜๊ธฐ ์œ„ํ•ด ๋ก ๊ณผ ์˜ฌ๋ฆฌ๋ฒค๋” ์ง€ํŒก์ด ๊ฐ€๊ฒŒ๋ฅผ ๋ฐฉ๋ฌธํ–ˆ๋‹ค.
ํ—ค๋ฅด๋ฏธ์˜จ๋Š๋Š” ์ง€ํŒก์ด๋ฅผ ๊ณ ๋ฅด๋Š” ์ตœ์„ ์˜ ๋ฐฉ๋ฒ•์œผ๋กœ์„œ non-evil ์ง€ํŒก์ด์ด๋ฉด์„œ ๋†’์€ Power์™€ Age ํŠน์„ฑ์„ ๊ฐ–๋Š” ์ง€ํŒก์ด๋ฅผ ์‚ฌ๊ธฐ ์œ„ํ•ด ํ•„์š”ํ•œ ์ตœ์†Œํ•œ์˜ ๋ˆ(gold galleons)์„ ๊ณ ๋ฅด๋Š” ๋ฐฉ๋ฒ•์„ ์ œ์•ˆํ–ˆ๋‹ค. ์ด ๋ฐฉ๋ฒ•์„ ์ ์šฉํ–ˆ์„ ๋•Œ, ๋ก ์ด ๊ด€์‹ฌ์žˆ์–ด ํ•  ๋งŒํ•œ ์ง€ํŒก์ด์˜ id, age, coins_needed, power ๊ฐ’๋“ค์„ ์ถœ๋ ฅํ•ด๋ผ. ์ด ๋•Œ 1์ฐจ์ ์œผ๋กœ ์ •๋ ฌ ๊ธฐ์ค€์€ power ๊ธฐ์ค€์œผ๋กœ ๋‚ด๋ฆผ์ฐจ์ˆœ ์ •๋ ฌํ•˜๋ฉฐ, ๋™์ผํ•œ power ๊ฐ’์ด ์žˆ์„ ๋•Œ๋Š” age ๊ฐ’์„ ๊ธฐ์ค€์œผ๋กœ ๋‚ด๋ฆผ์ฐจ์ˆœ ์ •๋ ฌํ•ด๋ผ.)

๋ฌธ์ œ๊ฐ€ ๊ฝค ๊ธธ๊ณ  ๋ณต์žกํ•˜๋‹ค. ์šฐ๋ฆฌ๋Š” ๋ฌธ์ œ๋ฅผ ํ•ด๊ฒฐํ•˜๊ธฐ ์œ„ํ•ด ๋‹ค์Œ๊ณผ ๊ฐ™์€ ์‚ฌํ•ญ์„ ๋งŒ์กฑ์‹œ์ผœ์•ผ ํ•œ๋‹ค.

 

  1. is_evil=0 ๊ฐ’์ธ non-evil ์ง€ํŒก์ด๋“ค๋งŒ ๊ณจ๋ผ์•ผ ํ•œ๋‹ค.
  2. Age, Power ๊ฐ’์ด ๊ฐ๊ฐ ๊ฐ™์€ ์ง€ํŒก์ด๋“ค ์ค‘์—์„œ coins_needed ๊ฐ’์„ ์ตœ์†Œ๋กœ ํ•˜๋Š” ์ง€ํŒก์ด๋ฅผ ๊ณจ๋ผ์•ผ ํ•œ๋‹ค.
  3. 2๊ฐœ์˜ ์ •๋ ฌ ๊ธฐ์ค€์„ ๋งŒ์กฑ์‹œ์ผœ์•ผ ํ•œ๋‹ค.

์ด ๋•Œ, ๋ฌธ์ œ ์›๋ณธ์—์„œ ํ•œ ๊ฐ€์ง€ ์ถ”๊ฐ€์ ์ธ ์‚ฌํ•ญ์„ ๋ถ€์—ฌํ–ˆ๋Š”๋ฐ, code์™€ age ๊ฐ’์˜ ๊ด€๊ณ„์˜€๋‹ค. 1:1 ๋งคํ•‘์ด ๋˜์–ด ์žˆ๋‹ค๊ณ  ํ•˜๋Š”๋ฐ ์ฒ˜์Œ์—” ์ด๊ฒŒ ๋ฌด์Šจ ๋ง์ธ์ง€ ์ดํ•ด๊ฐ€ ์ž˜ ์•ˆ๊ฐ”๋‹ค. ์‹œ๊ฐ„์ด ์ข€ ์ง€๋‚˜๊ณ  ์ดํ•ด๊ฐ€ ๋˜์—ˆ๋Š”๋ฐ 1:1 ๋งคํ•‘๋˜์—ˆ๋‹ค๋ผ๋Š” ์˜๋ฏธ๋Š” code-age ๋งคํ•‘๋˜์–ด ์žˆ๋Š” ๊ฐ’๋“ค์˜ ์ข…๋ฅ˜๊ฐ€ unique ํ•˜๋‹ค๋Š” ๊ฒƒ์ด๋‹ค. ์ฆ‰ ๋‹ค์Œ๊ณผ ๊ฐ™์ด ์ค‘๋ณต๋œ ์ข…๋ฅ˜์˜ code-age ๊ฐ’๋“ค์€ ์กด์žฌํ•˜์ง€ ์•Š๋Š”๋‹ค๋Š” ๊ฒƒ์ด๋‹ค.

 

code age power
3 50 7
3 50 9

 

์œ„ ํ…Œ์ด๋ธ” ๊ฐ™์€ ํ˜•ํƒœ์˜ ๋ฐ์ดํ„ฐ๋Š” ์กด์žฌํ•˜์ง€ ์•Š๋Š”๋‹ค๋Š” ๊ฒƒ์ด๋‹ค. ๋‹ค์‹œ ๋งํ•ด, code๊ฐ’์ด 3์ด๋ฉด์„œ age๊ฐ’์ด 50์ธ ๋ฐ์ดํ„ฐ ํ–‰์€ 2๊ฐœ๊ฐ€ ์กด์žฌํ•˜๋Š” ๊ฒƒ์ด ์•„๋‹Œ 1๊ฐœ๋งŒ ์กด์žฌํ•˜๋‹ค๋Š” ๊ฒƒ์ด๋‹ค.

 

๊ทธ๋Ÿผ ์ด์ œ ์ •๋‹ต SQL ๊ตฌ๋ฌธ์„ ์‚ดํŽด๋ณด์ž.

 

SELECT W.id, P.age, W.coins_needed, W.power
FROM Wands W
 INNER JOIN Wands_Property P ON W.code = P.code
WHERE P.is_evil = 0
AND W.coins_needed = (SELECT MIN(W1.coins_needed)
                      FROM Wands W1
                       INNER JOIN Wands_Property P1 ON W1.code = P1.code
                      WHERE P1.is_evil = 0 
                      AND W1.power = W.power
                      AND P1.age = P.age)
ORDER BY W.power DESC, P.age DESC

 

๋น„๊ต์  ์ •๋‹ต SQL ๊ตฌ๋ฌธ์€ ๋ฌธ์ œ ์„ค๋ช…์— ๋น„ํ•ด ๋‹จ์ˆœํ•˜๊ธฐ๋„ ํ•œ ๊ฒƒ ๊ฐ™๋‹ค. ์„œ๋ธŒ์ฟผ๋ฆฌ๋ฅผ 1๊ฐœ ๋ฐ–์— ์‚ฌ์šฉํ•˜์ง€ ์•Š์•˜๋‹ค. ํ•˜์ง€๋งŒ ๋ˆˆ์—ฌ๊ฒจ๋ณด์•ผ ํ•  ์  ํ•œ ๊ฐ€์ง€๋Š” ์„œ๋ธŒ์ฟผ๋ฆฌ์˜ WHERE ๊ตฌ๋ฌธ์—์„œ ๋ฉ”์ธ์ฟผ๋ฆฌ์—์„œ ์กฐ์ธํ•œ ํ…Œ์ด๋ธ”๋“ค์˜ ์นผ๋Ÿผ์„ ํ™œ์šฉํ–ˆ๋‹ค๋Š” ์ ์ด๋‹ค. ์ด๋ฅผ ์ด์šฉํ•ด ๊ฐ™์€ Age, Power ๊ฐ’๋“ค์ธ ์ง€ํŒก์ด ๋“ค ์ค‘์—์„œ coins_needed ๊ฐ’์„ ์ตœ์†Œ๋กœํ•˜๋Š” ๋ฐ์ดํ„ฐ๋“ค์„ ์ฐพ์„ ์ˆ˜ ์žˆ์—ˆ๋‹ค๋Š” ์ ์ด๋‹ค.

๋ฐ˜์‘ํ˜•