SQL Query by Hacker Rank Medium

솔비·2024년 6월 29일

📚 SQL. 관계형 데이터베이스

목록 보기

19/19

SQL Project Planning

테이블에 프로젝트 시작일과 종료일이 기록되어있고, 그 차이는 모두 1일 차.
종료일과 동일한 시작일이 존재할경우 프로젝트는 종료되지 않은것이고
종료일과 동일한 시작일이 존재하지 않는다면 프로젝트는 종료된것.
종료된 프로젝트의 시작일과 종료일을 출력하되 걸린 일수대로 정렬할것

WITH df1 as (
SELECT Start_Date
    , row_number() over (order by Start_Date) as Start_rk
FROM Projects 
WHERE Start_Date not in (SELECT End_Date From Projects)
    )
    
, df2 as (
SELECT END_Date
    , row_number() over (order by Start_Date) as END_rk
FROM Projects 
WHERE END_Date not in (SELECT Start_Date From Projects)
    )
    

SELECT df1.Start_Date
    , df2.End_Date
FROM df1 
    JOIN df2
    ON df1.Start_rk = df2.End_rk
ORDER BY datediff(df2.End_Date, df1.Start_Date)

📌 아래처럼 풀 수도 있으나 위 답이 좀 더 깔끔하다

WITH df1 as (
SELECT p1.Start_Date
    , row_number() over (order by p1.Start_Date) as rk
FROM Projects as p1
    LEFT OUTER JOIN Projects as p2
    ON p1.Start_Date = p2.End_Date
WHERE p2.Start_Date is null
    )
, df2 as (
SELECT p2.End_Date
    , row_number() over (order by p2.End_Date) as rk
FROM Projects as p1
    RIGHT OUTER JOIN Projects as p2
    ON p1.Start_Date = p2.End_Date
WHERE p1.End_Date is null
    )
SELECT Start_Date
    , End_Date
FROM df1 
    JOIN df2
    ON df1.rk = df2.rk
ORDER BY datediff(df2.End_Date, df1.Start_Date)

The Report

id, name, mark가 있는 Students 테이블과
grade, Min_Mark, Max_Mark가 있는 Grades 테이블을 활용해
학생 mark별 어떤 grade인지 출력하는 문제.
단, 8등급 이하의 학생이름은 null로 처리하고 학년별로 내림차순
같은 등급(8-10)을 받은 학생이 두 명 이상인 경우, 해당 학생의 이름을 알파벳 순으로 정렬

SELECT case when Grade >= 8 then Name end as name
    , Grade
    , Marks
FROM Students as a
    LEFT OUTER JOIN Grades as b
    ON a.Marks between b.Min_Mark and b.Max_Mark
ORDER BY Grade desc, name

📌 join이 범위일 경우 between을 사용하면 되는데 이 문제는 모기업 쿼리테스트에도 활용되었던 적이 있다. (그때는 몰라서 틀렸지만..)

Occupations

이름과 직업이 있는 테이블에서
의사, 교수, 가수, 배우의 이름을 각 행에 출력하되
직업당 최대 이름 수 보다 작은열에 대한 빈셀 데이터는 null값으로 채울것

WITH df as (
SELECT name
    , case when Occupation = "Doctor" then name end as Doctor
    , case when Occupation = "Professor" then name end as Professor
    , case when Occupation = "Singer" then name end as Singer
    , case when Occupation = "Actor" then name end as Actor
    , row_number() over (partition by Occupation order by Name ) as rk
FROM OCCUPATIONS
)

SELECT MAX(Doctor)
    , MAX(Professor)
    , MAX(Singer)
    ,MAX( Actor)
FROM df
GROUP BY rk

📌
1. 각 직업별 컬럼을 만들고 해당직업일경우 이름이 오게끔 case when을 작성한다.
2. order by를 위해 rk 컬럼을 생성한다.
3. max()로 각 컬럼을 호출한다 : null값 제거
4. group by로 null과 이름을 정렬한다.
참고 : https://like-or-like.tistory.com/79

The PADS

occupation테이블의 모든 이름 목록을 알파벳 순서로 나열하고 각 직업의 첫 글자를 괄호 안에 묶어라

occupation테이블에서 직업과 각 직업의 수를 오름차순으로 출력해라 직업은 소문자로 출력

SELECT concat(name, "(",  left(Occupation,1)  ,")")
FROM OCCUPATIONS 
ORDER BY name,  left(Occupation,1)


SELECT CONCAT('There are a total of ', count(Occupation), ' ', LOWER(Occupation), 's.')
FROM OCCUPATIONS
GROUP BY Occupation
ORDER BY count(occupation), occupation

mysql에서는 두개의 쿼리를 동시에 출력할 수 없기 때문에 mssql을 이용할것

Placements

id, name이 적힌 Students 테이블
id, Friend_id가 적힌 Friends 테이블
id, salary가 적힌 Packages 테이블
친구가 더 높은 월급을 받는 학생들의 이름을 출력
친구들의 월급액수에 따라 정렬

SELECT s.NAME
FROM Students as s
    LEFT OUTER JOIN Friends as f
    ON s.id = f.id
    LEFT OUTER JOIN Packages as p1
    ON s.id = p1.id
    LEFT OUTER JOIN  Packages as p2
    ON f.Friend_id = p2.id
WHERE p1.Salary < p2.Salary
ORDER BY p2.Salary

Symmetric Pairs

X1 = Y2 및 X2 = Y1인 경우 두 쌍(X1, Y1)과 (X2, Y2)은 대칭 쌍
모든 대칭 쌍을 X 값으로 오름차순으로 출력하는 쿼리를 작성
X1 ≤ Y1이 되도록 행을 나열

SELECT X, Y
FROM Functions
WHERE X = Y
GROUP BY X, Y
HAVING count(*) = 2

UNION

SELECT f1.X, f1.Y
FROM Functions as f1
   INNER JOIN Functions as f2
   ON f1.X = f2.Y
   AND f1.Y = f2.X
WHERE f1.X < f1.Y

ORDER BY x

📌 사실 아래 쿼리만 where절을 <=로 작성하면 되는거 아닌가 했는데
X와 Y가 같은쌍이 2개여야하기 때문에 UNION으로 풀어야한다.
이 문제에서 배운 포인트는
GROUP BY X,Y로 중복제거가 가능하고
COUNT(*) 로 x,y 쌍의 갯수를 필터할 수 있다는것 😅

Weather Observation Station 18

=> P1(a, b) 와 P2(c, d) 2D 평면의 두 점이다
a : LAT_N 최소값
b : LONG_W 최소값
c : LAT_N 최대값
d : LONG_W 최대값
STATION 테이블에서 P1과 P2 사이의 맨하탄 거리를 소수점 4자리까지 형식으로 조회해라

WITH df as (
SELECT MIN(LAT_N) as a
    , MIN(LONG_W) as b
    , MAX(LAT_N) as c
    , MAX(LONG_W) as d
FROM STATION
)

SELECT  round((c-a)+(d-b) ,4)
FROM df

맨하탄 거리 : 격자를 기반으로 하는 측정 체계, 2개의 점들 간의 거리는 각 방향 직각의 거리나 격자 셀 수로 정의
계산식 : (c-a)+(d-b)

Top Competitors

하나 이상의 도전에서 만점을 획득한 해커들의 각각의 hacker_id와 이름을 인쇄하세요. 해커가 만점을 획득한 도전의 총 수만큼 출력물을 내림차순으로 주문하세요. 같은 수의 도전에서 둘 이상의 해커가 만점을 받은 경우, hacker_id를 오름차순으로 정렬하세요.

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

Contest Leaderboard

해커의 총 점수는 모든 도전 과제에 대한 최대 점수의 합입니다.
내림차순 점수로 정렬된 해커의 hacker_id, 이름 및 총 점수를 출력하기 위해 쿼리를 작성합니다.
둘 이상의 해커가 동일한 총 점수를 달성한 경우 hacker_id를 오름차순으로 정렬하십시오.
총 점수가 0인 모든 해커를 결과에서 제외하십시오.

SELECT sub.hacker_id
    , name
    , sum(score)
FROM (
            SELECT  s.hacker_id
                , challenge_id
                , max(score) as score
            FROM Submissions as s
            GROUP BY 1,2
                ) as sub
    LEFT OUTER JOIN Hackers as h
    ON sub.hacker_id = h.hacker_id
GROUP BY 1,2
HAVING sum(score) > 0
ORDER BY sum(score) desc,  hacker_id

New Companies

company_code, 설립자명, 총 주간사 수, 총 고위 관리자 수, 총 관리자 수, 총 직원 수를 출력하는 쿼리를 작성하고 출력물을 오름차순으로 주문합니다.

SELECT c.company_code
    , founder
    , count(distinct lm.lead_manager_code) as lead_cnt
    , count(distinct sm.senior_manager_code) as senior_cnt
    , count(distinct m.manager_code) as manager_cnt
    , count(distinct e.employee_code) as employee_cnt
FROM Company as c
    LEFT OUTER JOIN Lead_Manager as lm
        ON c.company_code = lm.company_code
    LEFT OUTER JOIN Senior_Manager as sm
        ON lm.lead_manager_code = sm.lead_manager_code
    LEFT OUTER JOIN Manager as m
        ON sm.senior_manager_code = m.senior_manager_code
    LEFT OUTER JOIN Employee as e
        ON m.manager_code = e.manager_code
GROUP BY 1,2
ORDER BY 1

Challenges

hacker_id, 이름, 그리고 각 학생이 만든 도전과제의 총 수를 출력하기 위한 질문을 작성하세요. 내림차순으로 전체 도전과제 수를 기준으로 결과를 정렬하세요. 만약 두 명 이상의 학생이 같은 수의 도전과제를 생성했다면, 결과를 hacker_id로 정렬하세요. 만약 두 명 이상의 학생이 같은 수의 도전과제를 생성했는데 그 수가 최대 도전과제 수보다 적다면, 그런 학생들을 결과에서 제외하세요.

WITH df as (
        SELECT *
            , rank() over (order by cnt desc ) as rk
        FROM (
            SELECT h.hacker_id
                , name
                , count(distinct challenge_id) as cnt
            FROM Hackers as h
                LEFT OUTER JOIN Challenges as c
                ON h.hacker_id = c.hacker_id
            GROUP BY 1,2
            ) as sub
        WHERE cnt != 0
    )

, ddf as (
SELECT *
    , case when rk_cnt >= 2 and cnt < max_cnt then 1 else 0 end as filter
FROM (
    SELECT *
        , count(rk) over (partition by rk) as rk_cnt
        , (select max(cnt) from df) as max_cnt
    FROM df
    ) as sub
)

SELECT hacker_id
    , name
    , cnt
FROM ddf 
WHERE filter = 0

Ollivander's Inventory

SELECT id, age, coins_needed,power
FROM (
    SELECT W.id
         , WP.age
         , W.power
         , W.coins_needed
         , ROW_NUMBER() OVER (PARTITION BY WP.age, W.power ORDER BY coins_needed) rn
    FROM Wands W
        INNER JOIN Wands_Property WP
            ON W.code = WP.code
    WHERE is_evil = 0
) t
WHERE t.rn = 1
ORDER BY power DESC, age DESC

Study Note

솔비

Study Log

이전 포스트

SQL Query by Hacker Rank Medium

📚 SQL. 관계형 데이터베이스

SQL Project Planning

The Report

Occupations

The PADS

Placements

Symmetric Pairs

Weather Observation Station 18

Top Competitors

Contest Leaderboard

New Companies

Challenges

Ollivander's Inventory

Study Note

SQL 환경별 함수

0개의 댓글