TIL_84. Hacker rank SQL 코딩 테스트 (3)

소고기는레어·2021년 3월 9일

TIL sql

TIL 📝

목록 보기

86/96

2021. 03. 09 화요일

HackerRank SQL 코딩테스트

제목이 파란색으로 강조된 문제는 풀면서 개인적으로 흥미롭거나 재밌었다고 느낀 문제이다.

Weather Observation Station 19

a는 북위 최소값 / b는 북위 최대값 / c는 경도 최소값 / d는 경도 최대값이다.
이 때, P1(a,c) 와 P2(b,d) 사이의 유클리드 거리를 소수점 4자리에서 반올림하여 구하기
유클리드 거리 구하는 공식 : $\sqrt{(b-a)^2 + (d-c)^2}$

SELECT
    ROUND(SQRT(POW((MAX(LAT_N) - MIN(LAT_N)), 2) + POW((MAX(LONG_W) - MIN(LONG_W)), 2)), 4) AS Eucl_d
FROM
    STATION
;

Weather Observation Station 20

LAT_N의 중앙값을 소수점 4자리에서 반올림하여 구하기

SET @num = -1;
SELECT
    ROUND(AVG(LAT_N), 4) AS median
FROM
    (
    SELECT
        @num := @num + 1 AS row_num,
        LAT_N
    FROM
        STATION
    ORDER BY
        LAT_N
    )  AS sub
WHERE
    row_num IN (FLOOR(@num/2), CEIL(@num/2))
;

Average Population

모든 도시 인구의 평균값을 정수로 반올림하여 구하기

SELECT
    ROUND(AVG(population)) AS avg_pop
FROM
    CITY
;

Japan Population

일본 모든 도시의 인구 합계 구하기

SELECT
    SUM(population) AS sum_pop
FROM
    CITY
WHERE
    countrycode = 'JPN'
;

Population Density Difference

인구 최대값과 최소값의 차이 구하기

SELECT
    MAX(population) - MIN(population) AS diff
FROM
    CITY
;

The Blunder

직원 명단과 각 직원의 월급이 포함된 테이블이 있다.
모든 직원의 평균 월급을 계산할 때 키보드의 0 버튼이 고장나서 모든 값에서 0이 빠진 채로 계산이 되었다.(테이블은 정상이나 계산만 잘못됨.)
이 때, 정상적인 평균 월급과 잘못 계산된 평균 월급의 차이를 정수로 올림하여 구하기

SELECT
    CEIL(AVG(salary) - avg(replace(salary, '0', '')))
FROM
    employees
;

Type of Triangle

Column A, B, C 는 삼각형의 각 변 길이를 나타낸다.
이 때, 각 삼각형을 아래의 유형으로 구분하여 불러오기
- Equilateral : 모든 변 길이가 동일
- Isosceles : 두개의 변 길이가 동일
- Scalene : 모든 변 길이가 다름
- Not A Triangle : 가장 긴 변의 길이 > 나머지 두 변의 합

SELECT
    CASE WHEN A + B > C 
        THEN
            CASE WHEN A = B AND B = C THEN 'Equilateral'
            WHEN A != B AND B != C AND A != C THEN 'Scalene'
            ELSE 'Isosceles' END
    ELSE 'Not A Triangle' END
FROM
    TRIANGLES
;

The PADS

이름과 직업(Doctor, Professor, Singer, Actor)이 포함된 테이블이 있다.
1번 쿼리 : 이름을 알파벳순으로 정렬하고 이름 뒤 괄호 안에 직업 첫글자를 대문자로 나타내기.
ex : Maria(A)
2번 쿼리 : 직업별로 아래 문장 나타내기
There are a total of [occupation_count] [occupation]s.
ex : There are a total of 2 doctors.

SELECT
    CONCAT(name, '(', LEFT(occupation, 1), ')')
FROM
    occupations
ORDER BY
    name
;

SELECT
    CONCAT('There are a total of ', COUNT(*), ' ', LOWER(occupation), 's.')
FROM
    occupations
GROUP BY
    occupation
ORDER BY
    COUNT(*)
;

Asian Population

아시아에 속한 국가의 도시 인구수 합계 구하기

SELECT
    SUM(CITY.population)
FROM
    CITY
INNER JOIN
    COUNTRY
ON CITY.COUNTRYCODE = COUNTRY.CODE
WHERE
    CONTINENT = 'Asia'
;

African Cities

아프리카에 속한 모든 도시 불러오기

SELECT
    NAME
FROM
    CITY
INNER JOIN
    COUNTRY
ON CITY.COUNTRYCODE = COUNTRY.CODE
WHERE
    CONTINENT = 'Africa'
;

Average Population of Each Continent

대륙별 도시 평균 인구를 정수로 내림하여 불러오기

SELECT
    CONTINENT,
    FLOOR(AVG(CITY.POPULATION))
FROM
    COUNTRY
INNER JOIN
    CITY
ON COUNTRY.CODE = CITY.COUNTRYCODE
GROUP BY
    CONTINENT
;

The Report

id, 이름, 점수가 포함된 students 테이블과
등급과 등급에 해당하는 점수 범위가 포함된 grades 테이블이 있다.
이름, 등급, 점수를 등급 내림차순으로 불러오기
등급이 같을 경우 이름순으로 정렬
8등급 미만의 이름은 NULL로 불러오고 점수 내림차순으로 정렬

SELECT
    CASE WHEN GRADE > 7 THEN NAME
        ELSE NULL END,
    GRADE,
    MARKS
FROM
    STUDENTS
INNER JOIN
    GRADES
ON MARKS >= MIN_MARK AND MARKS <= MAX_MARK
ORDER BY
    GRADE DESC,
    NAME,
    MARKS DESC
;

Top Competitors

hackers 테이블 : hacker_id, name
difficulty 테이블 : difficulty_level, score
challenges 테이블 : challenge_id, hacker)id, difficulty_level
submissions 테이블 : submission_id, hacker_id, challenge_id, score
하나 이상의 챌린지에서 만점을 획득한 해커의 이름과 id 불러오기
만점을 획득한 챌린지 수의 내림차순 정렬
챌린지 수 동일할 경우 hacker_id 오름차순 정렬

SELECT
    h.hacker_id, 
    h.name
FROM 
    submissions AS s
INNER JOIN 
    challenges AS c
ON s.challenge_id = c.challenge_id
INNER JOIN 
    difficulty AS d
ON c.difficulty_level = d.difficulty_level 
INNER JOIN
    hackers AS h
ON s.hacker_id = h.hacker_id
WHERE 
    s.score = d.score
    AND c.difficulty_level = d.difficulty_level
GROUP BY
    h.hacker_id, 
    h.name
HAVING COUNT(s.hacker_id) > 1
ORDER BY 
    COUNT(s.hacker_id) DESC, 
    s.hacker_id
;