HackerRank SQL 4문제 풀이 (2)

1. Population Density Difference

https://www.hackerrank.com/challenges/population-density-difference/problem?isFullScreen=true

Population Density Difference | HackerRank

 

-- 최대 인구와 최소 인구의 차를 구해라
SELECT MAX(population) - MIN(population)
FROM city

 

 

2. Weather Observation Station 11

https://www.hackerrank.com/challenges/weather-observation-station-11/problem?isFullScreen=true

Weather Observation Station 11 | HackerRank

 

-- 모음으로 시작하지않거나 끝나지 않는 city 이름 조회
-- 결과는 중복되지 않게
SELECT DISTINCT(city)
FROM station
WHERE city NOT REGEXP '^[aeiou]'
OR city NOT REGEXP '[aeiou]$'

 

• LEFT, RIGHT 함수를 사용한 또다른 풀이 가능 (해설 참고함)

SELECT DISTINCT city
FROM station
WHERE LEFT(city, 1) NOT IN ('a', 'e', 'i', 'o', 'u')
OR RIGHT(city, 1) NOT IN ('a', 'e', 'i', 'o', 'u')

 

 

 

3. Weather Observation Station 13

https://www.hackerrank.com/challenges/weather-observation-station-13/problem?isFullScreen=true

Weather Observation Station 13 | HackerRank

 

-- 38.7880보다 크고 137.2345보다 작은 값을 가진 LAt_n의 합계
-- 소수점 4자리까지 반올림
SELECT ROUND(SUM(lat_n), 4)
FROM station
WHERE lat_n BETWEEN 38.7880 AND 137.2345

 

 

↓ 4번은 난이도 상

 

4. Top Competitors

https://www.hackerrank.com/challenges/full-score/problem?isFullScreen=true

Top Competitors | HackerRank

 

문제 접근... 이라고 쓰고 고민이라고 읽는다

 

1. 1번 보다 많은 챌린지에서 만점을 달성한 각 hacker_id, name 추출

2. 각 챌린지 단계마다 만점이 정해져있음, 이 중에서 만점 달성한 hacker만 추출하는 것

    >> difficulty_level 별로 group by해놓고 그 중에 만점인 점수를 필터링해서 해야하나

    >> 만점인 사람 걸러낼 때는 having..?
3. 총 도전 횟수를 기준으로 내림차순

    >> 근데 도전 횟수를 뭘로 판단하지, id가 중복된게 여러개 있으면 같은 사람이 여러 번 도전한거니까 그걸로 판단?

    >> 재도전할 때마다, 재제출할 때마다 id는 새로 부여되는건가 >> 그래서 동일한 name이어도
4. 만약 한명 이상이 같은 수의 도전에서 full score를 달성했으면 id로 오름차순
5. 테이블이 여러개라 조인해야됨, 같은 키 칼럼이 있는 테이블끼리 순차적으로(hacekr, challenges, difficulty, submission 순으로)

 

 

문제점/오류

 

1. 조인을 저 순서대로 하니까 difficulty level이나 id 쪽에서 칼럼 중복이 생김

2. group by를 difficulty level로 하는게 아니었음

3. 조건이 여러 개라 where절과 having절이 둘 다 쓰여야했음

4. 어떤 challenge_id가 한번 이상 나타나면 distinct

 

 

최종 코드

SELECT h.hacker_id, h.name
FROM Hackers h
     LEFT JOIN Submissions s ON h.hacker_id = s.hacker_id
     LEFT JOIN Challenges c ON s.challenge_id = c.challenge_id
     LEFT JOIN Difficulty d ON c.difficulty_level = d.difficulty_level
WHERE s.score = d.score
GROUP BY h.hacker_id, h.name
HAVING COUNT(DISTINCT s.challenge_id) > 1
ORDER BY COUNT(DISTINCT s.challenge_id) DESC, h.hacker_id

 

 

Line별 코드 해설

 

1: 어떤 테이블의 hacker_id인지 명시

2: alias 사용

3~5: left join 사용, 조인 순서는 hacker, submissions, challenges, difficulty 순

6: submissions에서 hacker들이 받은 score랑 원래 정해져있는 difficulty level에서의 만점 점수가 동일해야함

7: hacker_id와 name으로 group by해서 그 기준별 도전을 몇 번 했는지 체크

8: 도전 횟수가 한 번 이상인 사람 조건 걸기

9: 특정 challenge_id를 가진 사람이 한번 이상 나타나는걸로 정렬하려면, 각 id의 고유값의 개수를 알아야하므로 count(distinct( ))를 써줌

 

 

• INNER JOIN을 사용한 또다른 풀이 가능 (해설 참고함)

SELECT h.hacker_id, h.name
FROM Submissions s
     INNER JOIN Hackers h ON s.hacker_id = h.hacker_id
     INNER JOIN Challenges c ON s.challenge_id = c.challenge_id
     INNER JOIN Difficulty d ON c.difficulty_level = d.difficulty_level
WHERE s.score = d.score
GROUP BY h.hacker_id, h.name
HAVING COUNT(DISTINCT s.challenge_id) > 1
ORDER BY COUNT(DISTINCT s.challenge_id) DESC, h.hacker_id

 

 

더 해결해야 할 궁금증

1. 테이블이 여러 개 있을 때 조인 순서를 어떻게 해야하는지 파악하는 방법이 있을까?

   >> 그냥 쿼리를 많이 작성해보고, run code를 해보는게 답일지..

2. 이 문제는 테이블 조인의 순서만 괜찮으면 outer join이나 inner join이나 상관없는걸까

 

 

복습 필수..