Ollivander's Inventory, Challenges,Contest Leaderboard
Ollivander's Inventory 📝
https://www.hackerrank.com/challenges/harry-potter-and-wands/problem?isFullScreen=true
➡️ wands 테이블, wands_property 테이블
- 출력 : wands의 code, wands_property의 age,wands의 coins_needed,wands의 power
- 조건 1️⃣ : wands, wands_property JOIN하기 -> code 같음
- 조건 2️⃣ : non-evil을 뽑아야하므로 is_evil = 0
- 조건 3️⃣ : age,power가 같은 경우 coins_needed가 작은 값을 찾기 (galleons을 적게 들게 )
- 조건 4️⃣ : ORDER BY power -> ORDER BY age
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-evilwand 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,(code1,age1) and (code2,age2), then code1 ≠ code2 and age1 ≠ age2.
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
The data for wands of age 20 (code 4):
- The minimum number of galleons needed for wand(age = 20, power =5 ) = 504
- The minimum number of galleons needed for wand(age = 20, power =6) = 7651
- The minimum number of galleons needed for wand(age = 20, power = 8 ) = 3688
The data for wands of age 17 (code 5):
- The minimum number of galleons needed for wand(age = 17, power =3 ) = 5689
- The minimum number of galleons needed for wand(age = 17, power =10 ) = 9897
--방법 1
SELECT w.id,p.age,w.coins_needed,w.power
FROM wands_property p JOIN wands w ON p.code=w.code
WHERE p.is_evil=0
AND w.coins_needed=(SELECT MIN(coins_needed)
FROM wands w1 JOIN wands_property p1
ON w1.code = p1.code
WHERE w.power=w1.power AND p.age=p1.age)
ORDER BY w.power DESC,p.age DESC;
--방법 2
SELECT W.id, P.age, W.coins_needed, W.power
FROM (SELECT code, MIN(coins_needed) AS coins_needed, power
FROM Wands
GROUP BY code, power) AS M
INNER JOIN Wands AS W ON M.code=W.code AND M.power=W.power AND M.coins_needed=W.coins_needed
INNER JOIN Wands_Property AS P ON P.code=W.code
WHERE P.is_evil=0
ORDER BY W.power DESC, P.age DESC;
Challenges📝
https://www.hackerrank.com/challenges/challenges/problem?isFullScreen=true
➡️ hackers,challenges 테이블
- 출력 : hacker_id,name,COUNT(hacker_id)-> hacker마다 몇번 시도했는지
- 조건 1️⃣
challenges 테이블에서 challenge_id COUNT 해서 challenge_created 컬럼 만들기
-> challenges_created 의 개수를 세었을 때 1인 challenges 값을 찾는다.
-> 이 때 찾은 challenges 값들이 cnt 값에 있는 경우 찾기
- 조건 2️⃣
challenges 테이블에서 challenge_id COUNT해서 challenge_created 컬럼 만들기
-> challenges_created의 MAX 값과 같은 cnt 값 찾기
Julia asked her students to create some coding challenges. Write a query to print the hacker_id, name, and the total number of challenges created by each student. Sort your results by the total number of challenges in descending order. If more than one student created the same number of challenges, then sort the result by hacker_id. If more than one student created the same number of challenges and the count is less than the maximum number of challenges created, then exclude those students from the result.
Input Format
The following tables contain challenge data:
- Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker.
- Challenges: The challenge_id is the id of the challenge, and hacker_id is the id of the student who created the challenge.
Sample Input 0
Hackers Table:
Challenges Table:
Sample Output 0
21283 Angela 6
88255 Patrick 5
96196 Lisa 1
Sample Input 1
Hackers Table:
Challenges Table:
Sample Output 1
12299 Rose 6
34856 Angela 6
79345 Frank 4
80491 Patrick 3
81041 Lisa 1
Explanation
For Sample Case 0, we can get the following details:
Students and both created challenges, but the maximum number of challenges created is so these students are excluded from the result.
For Sample Case 1, we can get the following details:
Students and both created challenges. Because is the maximum number of challenges created, these students are included in the result.
SELECT h2.hacker_id,h2.name,COUNT(h2.hacker_id) AS cnt
FROM hackers h2 JOIN challenges c2
ON h2.hacker_id = c2.hacker_id
GROUP BY h2.hacker_id,h2.name
HAVING cnt IN (SELECT challenges_created
FROM
(SELECT COUNT(c.challenge_id) challenges_created
FROM challenges c
GROUP by hacker_id) m1
GROUP BY challenges_created
HAVING COUNT(challenges_created)=1)
OR cnt = (SELECT MAX(challenges_created)
FROM
(SELECT COUNT(c.challenge_id) challenges_created
FROM challenges c
GROUP by hacker_id) m1)
ORDER BY cnt DESC,h2.hacker_id;
Contest Leaderboard 📝
https://www.hackerrank.com/challenges/contest-leaderboard/problem?isFullScreen=true
➡️ wands 테이블, wands_property 테이블
- 출력 : H.hacker_id, H.name, sub2.total_score
- 단계 1️⃣ : hacker_id,challenge_id로 그룹 지었을 때 MAX(score)의 값을 구하기 -> 즉 값은 문제를 맞혔을 경우 큰 점수를 맞았을 때의 값을 구하도록
- 단계 2️⃣ : hacker_id로 그룹지었을 때 sum 구하기
- 단계 3️⃣ : 그렇게 만든 hacker_id로 JOIN해서 hacker_id,name,total_score 출력하기
You did such a great job helping Julia with her last coding contest challenge that she wants you to work on this one, too!
The total score of a hacker is the sum of their maximum scores for all of the challenges. Write a query to print the hacker_id, name, and total score of the hackers ordered by the descending score. If more than one hacker achieved the same total score, then sort the result by ascending hacker_id. Exclude all hackers with a total score of from your result.
Input Format
The following tables contain contest data:
- Hackers: The hacker_id is the id of the hacker, and name is the name of the hacker.
-
- Submissions: The submission_id is the id of the submission, hacker_id is the id of the hacker who made the submission, challenge_id is the id of the challenge for which the submission belongs to, and score is the score of the submission.
-
Sample Input
Hackers Table:
Submissions Table:
Sample Output
4071 Rose 191
74842 Lisa 174
84072 Bonnie 100
4806 Angela 89
26071 Frank 85
80305 Kimberly 67
49438 Patrick 43
Explanation
Hacker 4071 submitted solutions for challenges 19797 and 49593, so the total score = 95 + max(43,96) = 191
Hacker 74842 submitted solutions for challenges 19797and 63132, so the total score = max(98,5) +76 = 174
Hacker 84072 submitted solutions for challenges 49593and 63132, so the total score = 100+0 = 100.
The total scores for hackers 4806, 26071, 80305, and 49438 can be similarly calculated.
SELECT H.hacker_id, H.name, sub2.total_score
FROM (SELECT sub.hacker_id, SUM(max_score) AS total_score
FROM (SELECT hacker_id, challenge_id, MAX(score) AS max_score
FROM Submissions
GROUP BY hacker_id, challenge_id) sub
GROUP BY sub.hacker_id
HAVING total_score != 0) sub2
INNER JOIN Hackers H ON sub2.hacker_id = H.hacker_id
ORDER BY sub2.total_score DESC, H.hacker_id