주간 SQL Study 8차시 (윈도우 함수, 서브쿼리)

1️⃣ Contest Leaderboard

Q1. https://www.hackerrank.com/challenges/contest-leaderboard/problem

Contest Leaderboard | HackerRank

 

다시한번 느끼지만 문제를 이해하고 정의하는 능력이 중요함!!

 

콘테스트 리더보드 관련 문제다.

해커id, 이름이 저장된 Hackers 테이블이 있고

Submission_id, hacker_id, challenge_id, score가 저장된 submissions 테이블이 있다.

 

두개 테이블 조인해서 무언가 해야하는 문제라는걸 알 수 있다.

제시된 조건은 해커, 챌린지별 Max Score 값을 구하고 그 값들을 더한 값을 Total Score로 정의해서 출력해줘야 한다.

 

1차적으로 해커, 챌린지별 집계를 해준다.

SELECT hacker_id
     , challenge_id
     , max(score) score_max
FROM Submissions
GROUP BY hacker_id, challenge_id

 

다음 해당 쿼리를 서브쿼리로 감싸주고 hackers와 submissions 테이블을 join해준다.

group by 써서 해커아이디, 이름별로 출력해주고 having 조건절 써서 total score가 0이 아닌 값만 가져온다.

다음 order by 로 total_score 값으로 내림차순 하고 동일할경우 hacker id로 정렬해주는 쿼리를 짠다.

 

SELECT h.hacker_id
     , h.name
     , SUM(score_max) total_score
FROM(
    SELECT hacker_id
         , challenge_id
         , max(score) score_max
    FROM Submissions
    GROUP BY hacker_id, challenge_id
) t INNER JOIN Hackers h ON h.hacker_id = t.hacker_id
GROUP BY h.hacker_id, h.name
HAVING total_score !=0
ORDER BY total_score desc, h.hacker_id

 

 

 

2️⃣ Ollivander's Inventory

Q2. https://www.hackerrank.com/challenges/harry-potter-and-wands/problem

Ollivander's Inventory | HackerRank

해리포터 지팡이 사는 문제

id, code, coins_needed, power 컬럼이 들어있는 Wands 테이블

code, age, is_evil 등 완드의 스펙이 들어있는 Wands_Property 테이블

Power가 높은 순, 같은 파워면 나이가 높은 순인 지팡이를 찾아야 한다.

order by는 coins_needed로

단, is_evil은 0으로

 

1차적으로 쿼리를 만들면 아래와 같이 만들 수 있음.

SELECT w.id
     , wp.age
     , w.coins_needed
     , w.power
     , ROW_NUMBER() OVER (PARTITION BY age, power ORDER BY coins_needed) rn
FROM wands
WHERE is_evil = 0

 

ROW_NUMBER() OVER (PARTITION BY age, power ORDER BY coins_needed) rn

만들어 준이유가 age랑 power별 가격 순위를 알고 싶었기 때문임.

 

age, power별로 가격이 낮은 하나만 출력을 해주어야 하니 서브쿼리를 활용해 나머지 쿼리를 만들어준다.

 

SELECT id, age, coins_needed, power
FROM (
SELECT w.id
     , wp.age
     , w.coins_needed
     , w.power
     , ROW_NUMBER() OVER (PARTITION BY age, power ORDER BY coins_needed DESC) rn
FROM wands w left join wands_property wp ON w.code = wp.code
WHERE is_evil = 0
) t
WHERE rn = 1
ORDER BY power DESC, age DESC