[MySQL] 해커랭크 문제 풀이 (3)

 

SQL 쿼리 연습을 위해 다양한 문제를 풀어보고 있다.

다만, 대부분의 코딩테스트 플랫폼에서는 BigQuery 선택지가 없어서 MySQL 기준으로 연습하는 중이다.

(대부분의 문법이 비슷하지만 간혹 문법이나 정책이 다른 경우가 있는 듯하다)

 

아래와 같은 문제들을 기록해두고 복기하려고 한다.

• 어려워서 풀이에 실패한 문제
• 풀긴 했으나 좀 더 좋은 쿼리가 있었던 문제
• 몰랐던 문법이나 함수를 알게 된 문제
• 그 외 배울 점이 있었던 문제

---

1. Weather Observation Station 11 (해커랭크)

📌문제

• Query the list of CITY names from STATION that either do not start with vowels or do not end with vowels.
• Your result cannot contain duplicates.

 

📌풀이

-- 풀이(1) LEFT, RIGHT 함수 활용
SELECT
    DISTINCT(city) AS city_name
FROM station
WHERE
    LEFT(city,1) NOT IN("a","e","i","o","u") OR
    RIGHT(city,1) NOT IN("a","e","i","o","u");
    
-- 풀이(2) LIKE 전부 수동으로 설정
SELECT
    DISTINCT(city) AS city_name
FROM station
WHERE
    (city NOT LIKE 'a%' AND city NOT LIKE 'e%' AND city NOT LIKE 'i%' AND city NOT LIKE 'o%' AND city NOT LIKE 'u%')
    OR (city NOT LIKE '%a' AND city NOT LIKE '%e' AND city NOT LIKE '%i' AND city NOT LIKE '%o' AND city NOT LIKE '%u');

💡배운 점

• 1) OR 연산자 뒤에 조건식이 아닌 단순 값이 오면 SQL은 이를 조건으로 해석하지 않고 단순히 참(True)으로 간주
  • ex) WHERE a=1 OR a=2 와 WHERE a=1 OR 2 는 다름! 후자는 단순히 2라고 적었기 때문에, where절이 항상 참이 돼서 모든 행을 반환함
  • ⚠️그래서 풀이2에서도 city NOT LIKE 'a%' OR 'e%' OR 'i%' OR 'o%' OR 'u%' 처럼 한 번에 쓸 수 없는 것!!

2.  Weather Observation Station 18 (해커랭크)

📌문제

• Consider P1(a,b) and P2(c,d) to be two points on a 2D plane. Query the Manhattan Distance between points P1 and P2 and round it to a scale of 4 decimal places.
  • a happens to equal the minimum value in Northern Latitude (LAT_N in STATION).
  • b happens to equal the minimum value in Western Longitude (LONG_W in STATION).
  • c happens to equal the maximum value in Northern Latitude (LAT_N in STATION).
  • d happens to equal the maximum value in Western Longitude (LONG_W in STATION).

📌풀이

-- 1) 일단 각 좌표값 출력해서 확인
SELECT
    MIN(LAT_N) AS a,
    MIN(LONG_W) AS b,
    MAX(LAT_N) AS c,
    MAX(LONG_W) AS d
FROM station

-- 2) 맨해튼 거리로 계산해서 출력
SELECT
    ROUND(ABS(MIN(LAT_N) - MAX(LAT_N)), 4)
    + ROUND(ABS(MIN(LONG_W) - MAX(LONG_W)), 4)
FROM station

💡배운 점

• 1) 숫자 타입의 연산 함수 (추가로 알게된 것)
  
  • POWER(컬럼/값, n) : 값을 n제곱해서 반환 = POW로 써도 됨 (ex. POW(2,3) = 8)
  • SQRT(컬럼/값) : 값의 제곱근을 반환 (ex. SQRT(9) = 3)
    • ⚠️N제곱근을 구하고 싶을 땐 POWER를 활용 (ex. POWER(8, 1/3) = 2)
  • MOD(컬럼/값, n) : 값을 n으로 나눈 나머지를 반환 (% 연산과 동일)
  • ABS(컬럼/값) : 값의 절댓값을 반환 (ex. ABS(-4.5) = 4.5)

3.  Weather Observation Station 19 (해커랭크)

📌문제

• 2번 문제와 동일한 조건에서, 유클리드 거리로 계산하기

📌풀이

• 거리 공식으로 계산; U-distancd = sqrt((b-a)^2 + (d-c)^2)

-- 1) 각 좌표 출력해서 확인
SELECT
    MIN(LAT_N) AS a,
    MAX(LAT_N) AS b,
    MIN(LONG_W) AS c,
    MAX(LONG_W) AS d
FROM station

-- 2) 유클리드 거리 계산 = sqrt( (b-a)^2 + (d-c)^2 )
SELECT
    ROUND(SQRT(POWER(MAX(LAT_N) - MIN(LAT_N),2) + POWER(MAX(LONG_W) - MIN(LONG_W),2)),4)    
FROM station

💡배운 점

• 2번 문제와 동일

4. New Companies (해커랭크)

📌문제

• Amber's conglomerate corporation just acquired some new companies. Each of the companies follows this hierarchy: Founder > Lead Manager > Senior Manager > Manager > Employee
• Write a query to print the company_code, founder name, total number of lead managers, total number of senior managers, total number of managers, and total number of employees. Order your output by ascending company_code.

📌풀이

• 단순히 company 테이블과 employee 테이블만 조인하는 실수를 범하기 쉬움 (이 문제에서는 통과되지만, 엄밀히 틀린 것!)
  
  • ex) SM4라는 매니저에게 소속된 lead_manager가 없는 경우, SM4는 employee 테이블에 들어있지 않음!
  • 이런 식으로, employee 테이블에는 위계가 쭉 연결된(하위 매니저가 있는) 경우의 행만 담고 있음. 따라서, LM, SM, M 중에서 누락되는 경우가 생길 수 있음
• 누락이 없으려면, 각 원본 테이블의 개수를 세어줘야 함! (ex. SM은 sm.senior_manager_code의 개수로 세어줘야 정확함!)

SELECT
    c.company_code,
    c.founder,
    COUNT(DISTINCT(lm.lead_manager_code)), # 테이블 명시해줘야 함 + Distinct 필수!!
    COUNT(DISTINCT(sm.senior_manager_code)),
    COUNT(DISTINCT(m.manager_code)),
    COUNT(DISTINCT(employee_code))
FROM Company AS c
LEFT JOIN Lead_Manager AS lm ON c.company_code=lm.company_code # 각 원본 테이블의 code를 셀 수 있도록 조인
LEFT JOIN Senior_Manager AS sm ON lm.lead_manager_code=sm.lead_manager_code # 마찬가지
LEFT JOIN Manager AS m ON sm.senior_manager_code=m.senior_manager_code # 마찬가지
LEFT JOIN Employee AS e ON m.manager_code=e.manager_code # 마찬가지
GROUP BY 1,2
ORDER BY company_code ASC

💡배운 점

• 1) 여러 테이블이 연쇄적으로 구성된 경우, 누락 위험이 없는지 살피자!
• 2) COUNT 할 때는 습관적으로 DISTINCT를 함께 떠올리자!!

5. Top Competitors (해커랭크)

📌문제

• Julia just finished conducting a coding contest, and she needs your help assembling the leaderboard! Write a query to print the respective hacker_id and name of hackers who achieved full scores for more than one challenge.
• Order your output in descending order by the total number of challenges in which the hacker earned a full score. If more than one hacker received full scores in same number of challenges, then sort them by ascending hacker_id.

📌풀이

• 단계별 풀이 과정

/*
출력 컬럼: hacker_id, name 
테이블:
	- Hackers, Difficulty, Challenges(출제된 문제), Submissions(문제 풀이 시도)
	- Difficulty.score는 만점 기준, Submissions.score는 획득 점수
조건:
	- 2개 이상의 문제에서 만점을 획득한 hacker_id를 출력
	- 만점 개수 기준으로 내림차순 정렬, 개수 동일하면 hacker_id 기준 오름차순 정렬
*/

-- 1) 일단 필요한 테이블 조인 & 출력해서 확인
SELECT
    sub.*,
    ch.difficulty_level,
    d.score AS full_score -- 이게 만점 기준점
FROM submissions as sub
LEFT JOIN challenges as ch ON sub.challenge_id=ch.challenge_id
LEFT JOIN difficulty as d ON ch.difficulty_level=d.difficulty_level

-- 2) 만점 조건 추가해서 만점 개수 출력
SELECT
    sub.hacker_id,
    COUNT(submission_id) as full_score_cnt
FROM submissions as sub
LEFT JOIN challenges as ch ON sub.challenge_id=ch.challenge_id
LEFT JOIN difficulty as d ON ch.difficulty_level=d.difficulty_level
WHERE sub.score = d.score -- 만점 조건
GROUP BY hacker_id
HAVING full_score_cnt >= 2 -- 만점 개수 2개 이상인 경우

-- 3) 이름 출력을 위해 hackers 테이블 조인 & 정렬 조건 추가
SELECT
    sub.hacker_id,
    h.name as hacker_name
    COUNT(submission_id) as full_score_cnt
FROM submissions as sub
LEFT JOIN challenges as ch ON sub.challenge_id=ch.challenge_id
LEFT JOIN difficulty as d ON ch.difficulty_level=d.difficulty_level
LEFT JOIN hackers as h ON sub.hacker_id=h.hacker_id
WHERE sub.score = d.score
GROUP BY hacker_id
HAVING full_score_cnt >= 2
ORDER BY full_score_cnt DESC, hacker_id

-- 4) 최종 답안 출력! (실제로 개수는 출력하지 않으니까, COUNT를 having절로 이동)
SELECT
    sub.hacker_id,
    h.name as hacker_name
FROM submissions as sub
LEFT JOIN challenges as ch ON sub.challenge_id=ch.challenge_id
LEFT JOIN difficulty as d ON ch.difficulty_level=d.difficulty_level
LEFT JOIN hackers as h ON sub.hacker_id=h.hacker_id
WHERE sub.score = d.score
GROUP BY hacker_id, hacker_name
HAVING COUNT(submission_id) >= 2 -- SELECT에서 full_score_cnt 정의하는 대신 직접 COUNT를 여기다 씀
ORDER BY COUNT(submission_id) DESC, hacker_id -- SELECT에서 full_score_cnt 정의하는 대신 직접 COUNT를 여기다 씀

💡배운 점

• 1) GROUP BY 쓸 때 (SELECT에 안 쓰고) HAVING에만 집계함수 넣어도 작동함!! (지난 번에 이미 배운 내용)

---

💡배운 점 모아두기

1. OR 연산자 주의사항: 반드시 조건식으로 쓰기 (단순 값 X)
2. 숫자 타입 연산 함수: POWER, SQRT, MOD, ABS
3. 여러 테이블이 연쇄적으로 구성된 경우, 누락 위험이 없는지 살피자!
4. COUNT 할 때는 습관적으로 DISTINCT 생각하기!!