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

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

TIL 📝

목록 보기
86/96
post-thumbnail

2021. 03. 09 화요일

HackerRank SQL 코딩테스트

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

Weather Observation Station 19

  • a는 북위 최소값 / b는 북위 최대값 / c는 경도 최소값 / d는 경도 최대값이다.

  • 이 때, P1(a,c) 와 P2(b,d) 사이의 유클리드 거리를 소수점 4자리에서 반올림하여 구하기

  • 유클리드 거리 구하는 공식 : (ba)2+(dc)2\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
;
profile
https://www.rarebeef.co.kr/

0개의 댓글