(다시 풀어야 할) HackerRank SQL 문제 풀이

다 너무 어려웠다....

---

HackerRank - Placements: https://www.hackerrank.com/challenges/placements/problem

이 사람의 베스트 프랜드가 이 사람보다 더 높은 연봉을 받고 있는 사람들을 출력하는 문제이다. 정렬은 친구의 월급으로 한다.

비교를 하기 위해서는 이 사람의 샐러리와 친구의 샐러리를 비교해야 하는데 같은 샐러리를 받고 있는 사람은 없다

• 샘플 확인
• 테이블이 3개니까 3개의 테이블을 조인하는데, 3 테이블의 정보가 다 필요하니까 그냥 이너 조인
• 어떤 기준으로 값이 없어도 가져오고 싶을 땐 LEFT JOIN

-- JOIN으로 총 4개의 테이블을 생성
SELECT S.name
FROM friedns F 
	INNER JOIN students S
    ON F.id = S.id
    INNER JOIN pacakge P
    ON F.id = p.id -- 여기까지가 내 월급
    INNER JOIN pacakges P2
    ON F.friend_id = P2.id -- 여기가 베프 월급 
WHERE P.salary < P2.salary -- 모두의 샐러리를 비교해야하니까 INNER JOIN
ORDER BY P2.salary

---

HackerRank - Binary Tree Nodes: https://www.hackerrank.com/challenges/binary-search-tree-1/problem

루트 : P가 NULL이나 없는 것
리프 : N에는 있는데 P에는 없는 것
이너 : 둘다 아닌 경우

• 조건에 따라 나눌때는 CASE문을 사용한다
• 자가복제처럼 본인의 테이블에서 LEFT JOIN을 해주면 해당 조건에 해당하면 JOIN이 되어서 테이블이 생성되고 답을 찾을 수 있다.
• LEFT 조인을 하는 이유는 조건에 해당하지 않는 값에 NULL을 주려고!
• 조인의 조건을 파악하는 게 중요!
• 그림으로 그려보면서 확인할 것

-- 1) JOIN을 사용한 풀이
SELECT DISTINCT b1.n,
	CASE WHEN b1.p IS NULL THEN 'root' -- 루트부터 생각해보면
    WHEN b2.n IS NULL THEN 'leaf' -- 리프 찾기(WHEN을 써주면 if else와 비슷한 것)
    ELSE 'inner' END
FROM bst b1
	LEFT JOIN bst b2 -- JOIN이 되어서 b2의 N이 새 테이블로 옆에 달라부텄을 것
    ON b1.n = b1.p -- n에도 있고 p에도 있는 경우에 이렇게 붙여줄 수 있다
ORDER BY 1

(캡쳐 사진 추가)

-- 서브쿼리를 사용한 풀이
SELECT n,
    CASE WHEN p IS NULL THEN 'Root'
        WHEN n IN (SELECT DISTINCT p FROM bst) THEN 'Inner'
        ELSE 'Leef' END
FROM bst
ORDER BY n;

---

HackerRank - Weather Observation Station 17 : https://www.hackerrank.com/challenges/weather-observation-station-17/problem

• 가장 큰 거, 상위 이런 거 구할 때 ORDER BY와 LIMIT을 활용하면 된다
• 나는 MIN()을 사용했는데 어쩐지 안 되더라......

SELECT ROUND(long_w, 4)
FROM station
WHERE lat_n > 38.7780
ORDER BY lat_n
LIMIT 1

• MIN()을 사용해서 풀고 싶다면?
• FROM station
• WHERE lat_n > 38.7780
• 이 테이블을 따로 만들고 MIN()을 해줘야 한다

-- MIN() 사용 풀이
SELECT ROUND(S1.long_w, 4)
FROM station S1
WHERE S1.lat_n = 
	(SELECT MIN(S2.lat_n) -- 서브쿼리 구조로 MIN() 값과 같은 값을 찾아준다
	FROM station S2
	WHERE S2.lat_n > 38.7780)

-- RANK()로 풀기
SELECT ROUND(long_w, 4)
FROM (SELECT *,
		ROW_NUMBER() OVER(ODER BY lat_n) rnk -- OVER(이 정렬 기준으로) 가져오기 위해 rnk라는 별칭을 만들어준다.
	FROM station
	WHERE lat_n > 38.7780) A -- FROM에 서브쿼리를 줄 때는 이것이 일종의 테이블처럼 들어가기 때문에 꼭 별칭을 써줘야 한다.
WHERE rnk = 1

---

HackerRank - Contest Leaderboard : https://www.hackerrank.com/challenges/contest-leaderboard/problem

• 서브쿼리를 사용하는 문제
• 해커아이디와 챌린저 아이디가 같을 때는 MAX 값만 가져온다
  1) 기준이 되는 거는 어떤 테이블일까? submission

SELECT H.hacker_id,
	H.name,
    SUM(max_score) total_score -- 각 사람당 토탈 스코어를 SUM()으로 구함
FROM (SELECT hacker_id, 
      challenge_id
      MAX(score) max_score -- 이 부분으로 인해 같은 게 있으면 MAX만 남게됨
  FROM submission
  GROUP BY hacker_id, challenge_id) A
  INNER JOIN hackers H -- name을 가져오기 위해 JOIN
  ON A.hacker_id = H.hacker_id
GROUP BY H.hacker_id, H.name -- 사람당! 이니까 GROUP BY
HAVING total_score > 0 -- GROUP BY의 조건이니까 HAVING
ORDER BY total_scroe DESC, H.hacker_id

---

HackerRank - New Companies : https://www.hackerrank.com/challenges/the-company/problem

-- 서브쿼리를 사용한 풀이
-- 각각 카운팅해야 하는 것을 서브쿼리로 만들었음 - 기준이 되는 컴퍼니 코드만 맞춰주면 된다
-- WHERE company_code = C.company_code) => 이너조인의 매칭 조건과 같지!
-- A inner join B on A.id = b.id 
-- from A, B where A.id = b.id 이 같은 거로 푼 거라고 볼 수 있다
-- 조인을 여러 번 하는 게 빠를 땐 이 방법
-- 메모리 문제가 없을 땐 전에 LEFT 조인으로 테이블 만들었던 방법
SELECT C.company_code, 
	C.founder,
    (SELECT COUNT(DISTINCT lead_manager_code) -- 2) 리드 매니저의 숫자 출력 부분을 서브쿼리로! - 리드 매니저 테이블에서 리드 매니저 수를 카운팅
	FROM lead_manager
	WHERE company_code = C.company_code)
    (SELECT COUNT(DISTINCT senior_manager_code) -- 3) 시니어 매니저의 숫자 출력 부분을 서브쿼리로! - 시니어 매니저 테이블에서 리드 매니저 수를 카운팅
	FROM senior_manager
	WHERE company_code = C.company_code)
    (SELECT COUNT(DISTINCT manager_code) -- 3) 매니저의 숫자 출력 부분을 서브쿼리로! - 매니저 테이블에서 리드 매니저 수를 카운팅
	FROM manager
	WHERE company_code = C.company_code)
FROM company C
ORDER BY company_code

• 조각 내서 코드를 돌려보자

---

HackerRank - Occupations : https://www.hackerrank.com/challenges/occupations/problem

• Pivot table이 나왔다!!
• 오큐페이션 별로 정렬해서 오큐페이션 별로 정렬하기
• 윈도우 함수 사용
• 헤더만 보고 싶은 것은 rnk가 1인 것만 보고 싶은 것
• 그룹바이는 집계함수와 같이 사용한다.
• 그룹바이만 하면 그냥 모아둔 것!!! 아무 것도 벌어지지 않은 상태!!!
• 문제에서 피벗을 해달라고 하면 - 윈도우 함수를 활용해 피벗을 할 기준으로 파티션을 시켜주고 케이스를 기준으로 칼럼을 쪼개주고
  로우 넘버로 그룹핑을 시켜주고, 집계함수까지 넣어줘야지 한줄로 만들 수 있따!!!!

SELECT rnk,
	ROW_NUMBER() OVER(PARTITION BY occupation ORDER BY name) rnk
FROM occupation -- 여기까지 해서 돌려보기 - 직업군으로 넘버링된다 - 파티션바이는 이처럼 일종의 구분을 지어주는 것 - 직업을 구준으로, 이거는 알파벳
-- 이 테이블을 서브쿼리로 사용할 것임
-- 첫 번째 칼럼에는 닥터만 나와야 함 : CASE문으로 가능!
SELECT MIN(CASE WHEN occupation = 'Doctor' THEN name END) doctor -- 이거 하나가 칼럼이 됨
	MIN(CASE WHEN occupation = 'Professor' THEN name END) professor,
    MIN(CASE WHEN occupation = 'Singer' THEN name END) singer,
    MIN(CASE WHEN occupation = 'Actor' THEN name END) actor,
FROM (SELECT *,
	ROW_NUMBER() OVER(PARTITION BY occupation ORDER BY name) rnk
	FROM occupation) A
GRUOP BY rnk -- 위에서 만든 row_number() 활용
ORDER BY rnk

---

HackerRank - Weather Observation Station 5 : https://www.hackerrank.com/challenges/weather-observation-station-5/problem

-- 길이를 세는 함수 length()
-- 가장 크고 작은 거 찾는 쉬운 방법 : ORDER BY + LIMIT
SELECT city, LENGTH(city)
FROM station
ORDER BY LENGTH(city), city
LIMIT 1;

SELECT city, LENGTH(city)
FROM station
ORDER BY LENGTH(city) DESC, city
LIMIT 1;