반응형
- Population Density Difference: https://www.hackerrank.com/challenges/population-density-difference/problem
- Weather Observation Station 11: https://www.hackerrank.com/challenges/weather-observation-station-11/problem
- Weather Observation Station 13: https://www.hackerrank.com/challenges/weather-observation-station-13/problem
- Top Competitors: https://www.hackerrank.com/challenges/full-score/problem
답안
1. Population Density Difference
SELECT MAX(population)- MIN(population) FROM CITY
2. 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
*/
SELECT distinct(CITY) FROM STATION
WHERE SUBSTR(CITY,1,1) NOT IN ('a','e','i','o','u')
OR SUBSTR(CITY,-1) NOT IN ('a','e','i','o','u')
--LEFT(), RIGHT()함수 사용해도 됨
--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
/*
Query the sum of Northern Latitudes (LAT_N) from STATION having values greater than 38.7880 and less than 137.2345 . Truncate your answer to 4 decimal places.
*/
SELECT truncate(sum(LAT_N),4) FROM STATION
WHERE LAT_N > 38.7880 AND LAT_N < 137.2345
-- 해당값보다 크다했는데 아래와 같은경우 해당값도 포함되므로 정답이라 할 수 없음
-- BETWEEN은 LAT_N >= 38.7880 AND LAT_N <= 137.2345와 같은의미
--WHERE LAT_N BETWEEN 38.7880 AND 137.2345
4. Top Competitors --> 어렵다....................... group by/ having
Difficulty table에 diufficulty_level에 해당하는 점수가 full score
1. Submissions 테이블의 challenge_id에 해당하는 값을
Challenges 테이블 challenge_id 과 매핑 후 difficulty_level 가져오기
2. Difficulty 테이블의 difficulty_level에 해당하는 score가 full score에며
Submissions테이블의 score와 비교
3. Submissions 테이블의 hacker_id가 86870, 904111인 경우 full score와 동일
4. 하나보다는 더 많은 challenge에서 full score를 받은 경우는 hacker_id가 90411 인 경우임
코드 작성
/*
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.
*/
SELECT h.hacker_id, h.name
FROM submissions s
INNER JOIN Challenges c on s.challenge_id = c.challenge_id
INNER JOIN Difficulty d on c.difficulty_level = d.difficulty_level
INNER JOIN Hackers h on s.hacker_id = h.hacker_id
WHERE s.score = d.score
group by h.hacker_id, h.name
having count(distinct(s.submission_id)) > 1
order by count(distinct(s.submission_id)) desc, h.hacker_id asc
'SQL' 카테고리의 다른 글
[고급 SQL 문제풀이] HackerRank 문제 및 풀이(1) (0) | 2022.02.04 |
---|---|
[중급 SQL 문제풀이] HackerRank 문제 및 풀이(3) (0) | 2022.01.19 |
[중급 SQL 문제풀이] HackerRank 문제 및 풀이(1) (0) | 2022.01.13 |
[중급 SQL 문제풀이] leetcode 문제 및 풀이(1) (0) | 2022.01.10 |
[중급 SQL문제풀이]MySQL 숫자를 다루는 함수, 문자열을 다루는 함수 (0) | 2022.01.10 |