오늘도 데이터분석

Ollivander's Inventory 문제 바로가기 

문제 

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.

1. 론이 관심있을만한 wand 기준 : power, age는 높고, non-evil wand(is_evil = 0)이며 코인은 최소로 드는 wand
2. 론이 관심있을만한 wand의 id, age, coins_needed, power 를 결과값으로 나타내는 쿼리 입력
3. power 내림차순(정렬기준1), power같으면 age 내림차순(정렬기준2)
* wand_property 테이블에서 code와 age 매핑은 1:1이다 => 한 코드마다 나이 하나, 즉 동일 코드 = 동일 나이

문제 이해하는데 완전 오래걸림.....; 더 자세한 정보 및 예시는 위 문제링크에서 확인

풀이 

SELECT w.id, p.age, w.coins_needed, w.power
FROM (SELECT code, power, MIN(coins_needed)coins_needed
      FROM wands
      GROUP BY code, power) sub               -- code(age), power를 기준으로 최소 코인값만 저장
      INNER JOIN wands_property p ON sub.code = p.code -- property 조인 
      INNER JOIN wands w ON sub.code = w.code
                        AND sub.coins_needed = w.coins_needed 
                        AND sub.power = w.power  -- id 속성을 위해 wands 다시 조인 
WHERE p.is_evil = 0
ORDER BY power DESC, age DESC;

*PC화면에서 보기 적합하게 작성되었습니다. 

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.

정리
- Hackers 테이블과 Challenges 테이블을 가지고 해커 id, 이름, 학생이 한 챌린지의 총 수를 결과값으로 나타내는 쿼리 입력 (*한 학생이 여러 챌린지 수행함)
- 결과는 챌린지의 수에 따라 내림차순(정렬기준1), 시행한 챌린지 수가 같으면 해커 id(정렬기준2)로 정렬
- 단, 시행한 챌린지의 수가 같을 때 그 챌린지 수가 MAX값이 아니면 결과에서 제외

Sample Input 0

Hackers Table: 

 Challenges Table: 

Sample Output 0

21283 Angela 6
88255 Patrick 5
96196 Lisa 1

For Sample Case 0, we can get the following details:

Students  5077 and 62743  both created  4 challenges, but the maximum number of challenges created is 6 so these students are excluded from the result.

풀이 

풀이순서 
- 두 테이블 조인
- hacker_id와 name으로 그룹화 
- having으로 challenge_created(각 학생이 시행한 챌린지 수) 조건문
  1) challenge_created가 MAX값일 경우 
  2) challenge_created가 중복값이 없는 경우
- 정렬기준 충족

STEP 1

두 테이블을 조인하고 해커 id, 이름, challenges_created를 결과로 나타냄

각 학생별로 id, name, 시행한 챌린지 수가 결과로 나옴

SELECT h.hacker_id, h.name, COUNT(challenge_id) challenges_created
FROM hackers h JOIN challenges c ON c.hacker_id = h.hacker_id
GROUP BY h.hacker_id, h.name

이 쿼리의 결과는 challenges_created 학생별로 시행한 챌린지 수가 같은 경우(중복)가 나옴

가장 큰 값(max)을 제외하고는 중복되는 값은 제외해줘야 함. 그래서 조건 필요

STEP 2

HAVING으로 challenges_created 조건

2-1 challenge_created가 MAX값일 경우 

SELECT MAX(challenge_created)
FROM (SELECT hacker_id,
                            COUNT(*) challenge_created
            FROM challenges
            GROUP BY hacker_id) sub

2-2 challenge_created가 중복값이 없는 경우

SELECT challenge_created
FROM (SELECT hacker_id,
                            COUNT(*) challenge_created
              FROM challenges
             GROUP BY hacker_id) sub
GROUP BY challenge_created
HAVING COUNT(*) = 1

최종 HAVING 문

각 조건을 서브쿼리로 지정

HAVING challenges_created IN (SELECT challenge_created
                                                      FROM (SELECT hacker_id,
                                                                                  COUNT(*) challenge_created
                                                                    FROM challenges
                                                                    GROUP BY hacker_id) sub
                                                      GROUP BY challenge_created
                                                      HAVING COUNT(*) = 1)
          OR challenges_created = (SELECT MAX(challenge_created)
                                                      FROM (SELECT hacker_id,
                                                                                   COUNT(*) challenge_created
                                                                    FROM challenges
                                                                    GROUP BY hacker_id) sub)

STEP 3 

정렬기준 포함한 최종 쿼리

SELECT h.hacker_id, h.name, COUNT(challenge_id) challenges_created
FROM hackers h 
     JOIN challenges c ON c.hacker_id = h.hacker_id
GROUP BY h.hacker_id, h.name
HAVING  challenges_created IN (SELECT challenge_created
                               FROM (SELECT hacker_id,                                  
                                     COUNT(*) challenge_created
                                  FROM challenges
                                  GROUP BY hacker_id) sub
                            GROUP BY challenge_created
                            HAVING COUNT(*) = 1)
    OR challenges_created = (SELECT MAX(challenge_created)
                             FROM (SELECT hacker_id, 
                                   COUNT(*) challenge_created
                                    FROM challenges
                                    GROUP BY hacker_id) sub)
ORDER BY challenges_created DESC, h.hacker_id

위와 동일한 쿼리를 WITH문 사용하는 경우 

WITH counter AS(
  SELECT h.hacker_id
       , h.name
       , COUNT(*) created_challenges
  FROM hackers h
      INNER JOIN challenges c ON h.hacker_id = c.hacker_id
  GROUP BY h.hacker_id, h.name
) 

SELECT  counter.hacker_id
      , counter.name
      , counter.created_challenges
FROM counter
WHERE created_challenges = (SELECT MAX(created_challenges) 
                            FROM counter)
OR created_challenges IN (SELECT created_challenges 
                          FROM counter 
                          GROUP BY created_challenges 
                          HAVING COUNT(*) = 1) 
ORDER BY counter.created_challenges DESC, counter.hacker_id

Binary Tree Nodes 문제 바로 가기

문제

문제 정리

- BST 테이블에 노드 값이 담긴 N과 노드의 부모값이 담긴 P  두 가지 속성이 있음.
- 이진트리의 각 노드의 노드타입을 결과로 나타내는 쿼리입력.
   (1)노드가 루트노드면 Root (최상위 노드, 부모가 없는 노드)
   (2)노드가 리프노트면 Leaf (자식이 없는 노드)
   (3)노드가 이너노드면 Inner(루트도, 잎 노드도 아닌 노드)
- N 내림차순 정렬

You are given a table, BST, containing two columns: N and P, where N represents the value of a node in Binary Tree, and P is the parent of N.

Write a query to find the node type of Binary Tree ordered by the value of the node. Output one of the following for each node:

• Root: If node is root node.
• Leaf: If node is leaf node.
• Inner: If node is neither root nor leaf node.

Sample Input

Sample Output

1 Leaf
2 Inner
3 Leaf
5 Root
6 Leaf
8 Inner
9 Leaf

Explanation

The Binary Tree below illustrates the sample:

풀이

CASE 문 만들기 

* CASE문은 조건문 여러개 가능
EX)  CASE (조건문1)WHEN ~ THEN ~ (조건문2)WHEN ~ THEN ~ (조건문3)ELSE ~  END

1. 노드가 루트노드면 Root (최상위 노드, 부모가 없는 노드)
  
 P가NULL이면 부모가 없는 최상위 노트
-->  CASE WHEN P IS NULL THEN 'Root'

2.노드가 리프노트면 Leaf (자식이 없는 노드)

(1) 자식이 없다 => 부모가 아니다 => N값(부모)이 P에 없다 
N NOT IN P

(2) 속성P의 속성값을 범위로 나타내려면 서브쿼리로 표시
SELECT P FROM BST 

(3) P 속성값 중 NULL이 있는 경우 NOT IN을 하면 아무 row도 선택하지 않음 
ex) N not in (2,, null) => N <> 2 AND N <> NULL 의미,
그러나 NULL은 IS NOT NULL로 찾을 수 있기 때문에 N not in (2,, null) 식은 항상 FALSE를 반환하게 됨 
P의 NULL값을 제거하는 범위 지정

--> WHEN N NOT IN (SELECT P FROM BST WHERE P IS NOT NULL) THEN 'Leaf'

3. 노드가 이너노드면 Inner(루트도, 잎 노드도 아닌 노드)

1, 2 조건문이 아니면 이너노드 
--> ELSE 'Inner'

중요 포인트 

1. CASE 문 안에 서브쿼리

2. NOT IN 의 'NULL' 함정

최종 쿼리 

SELECT N,
              (CASE WHEN P IS NULL THEN 'Root'
                          WHEN N NOT IN (SELECT P FROM BST WHERE P IS NOT NULL) THEN 'Leaf'
                           ELSE 'Inner'
              END) AS type
FROM BST
ORDER BY N

Contest Leaderboard 문제 바로가기

문제

1. 해커 아이디, 이름, 전체점수를 나타내는 쿼리 입력
2. 전체점수(total score) 은 해커가 푼 챌린지 중 가장 높은 점수끼리 더한 값 
    *한 해커가 동일한 챌린지를 여러 번 풀기도 함
3. 전체 점수가 0 인 경우는 제외하기 
4.  결과값은 전체 점수가 높은 순(내림차순) 정렬, 점수같으면 해커아이디 오름차순 정렬

풀이

서브쿼리로 문제풀이 

SELECT h.hacker_id, h.name, sum(s.score)
FROM (SELECT hacker_id , challenge_id , max(score) AS score
             FROM submissions
             GROUP BY hacker_id, challenge_id)  AS s
            JOIN hackers h ON h.hacker_id = s.hacker_id
GROUP BY h.hacker_id, h.name
HAVING sum(s.score) <> 0
ORDER BY sum(s.score) DESC , h.hacker_id

Placements 문제 바로가기 

문제

You are given three tables: Students, Friends and Packages. Students contains two columns: ID and Name. Friends contains two columns: ID and Friend_ID (ID of the ONLY best friend). Packages contains two columns: ID and Salary (offered salary in $ thousands per month).

Write a query to output the names of those students whose best friends got offered a higher salary than them. Names must be ordered by the salary amount offered to the best friends. It is guaranteed that no two students got same salary offer.

친구가 더 높은 salary를 받는 사람의 이름이 나오도록 쿼리 입력
결과값 정렬은 친구의 salary순으로 정렬 
같은 salary를 받는 학생은 없음

풀이

1. 테이블 3개를 조인하는 방법

SELECT s.name
FROM students s
           JOIN friends f ON s.id = f.id
           JOIN packages p ON s.id = p.id
           JOIN packages p2 ON f.friend_id = p2.id
WHERE p.salary < p2.salary
ORDER BY p2.salary;

2. 서브쿼리를 이용하는 방법

SELECT me.Name
FROM (SELECT s.ID, s.Name, f.Friend_ID, p.salary AS me_salary
             FROM students s
            JOIN friends f ON s.ID = f.ID
            JOIN packages p ON s.ID = p.ID) AS me
JOIN packages p2 ON me.Friend_ID = p2.ID
WHERE me.me_salary < p2.salary
ORDER BY p2.salary;

Output

Stuart
Priyanka
Paige
Jane
Julia
Belvet
Amina
Kristeen
Scarlet
Priya
Meera