SQL 공부합니다(3)

꾸준히 코테 풀려고 합니다..
HackerRank Medium으로 풀겠습니다.

코드는 제 개인 깃허브에도 기록해두었습니다.

---

1.Symmetric Pairs

문제
You are given a table, Functions, containing two columns: X and Y.

Two pairs (X1, Y1) and (X2, Y2) are said to be symmetric pairs if X1 = Y2 and X2 = Y1.

Write a query to output all such symmetric pairs in ascending order by the value of X. List the rows such that X1 ≤ Y1.

Sample Input

Sample Output

20 20
20 21
22 23

핵심

두 가지 케이스 구분이 문제의 데이터는 크게 두 종류.
Case A: $X \neq Y$ 인 경우(20, 21)과 (21, 20)처럼 서로 다른 숫자가 쌍을 이루는 경우. 이때는 X1 = Y2 AND X2 = Y1이면서 X1 < Y1이라는 조건을 추가해 중복(21, 20)을 제거하는 것이 핵심.

Case B: $X = Y$ 인 경우(10, 10)처럼 숫자가 같은 경우.
이게 대칭 쌍이 되려면 테이블에 (10, 10)이 최소 2개 이상 존재해야 합니다. GROUP BY와 COUNT를 써야함.

코드

SELECT f1.x, f1.y
FROM Functions f1
JOIN Functions f2 ON f1.x = f2.y AND f1.y = f2.x
GROUP BY f1.x, f1.y
HAVING 
    f1.x < f1.y 
    OR 
    (f1.x = f1.y AND COUNT(*) > 1)
ORDER BY f1.x;

---

2.Placements

문제
You are given three tables: Students, Friends and Packages. Students contains two columns: ID and Name. Friends contains two columns: ID and Friend_ID (ID of the ONLY best friend). Packages contains two columns: ID and Salary (offered salary in $ thousands per month).

Write a query to output the names of those students whose best friends got offered a higher salary than them. Names must be ordered by the salary amount offered to the best friends. It is guaranteed that no two students got same salary offer.

Sample Input

Sample Output

Samantha
Julia
Scarlet

Explanation

See the following table:

Now,

Samantha's best friend got offered a higher salary than her at 11.55
Julia's best friend got offered a higher salary than her at 12.12
Scarlet's best friend got offered a higher salary than her at 15.2
Ashley's best friend did NOT get offered a higher salary than her
The name output, when ordered by the salary offered to their friends, will be:

Samantha
Julia
Scarlet

핵심

JOIN은 두 번 필요. (Packages 테이블)
Packages 테이블을 두 번 조인.
한 번은 나의 급여를 알기 위해 내 ID(S.ID)와 연결하고,
또 한 번은 친구의 급여를 알기 위해 친구 ID(F.Friend_ID)와 연결.

코드

SELECT S.Name
FROM Students S
JOIN Friends F ON S.ID = F.ID
JOIN Packages P1 ON S.ID = P1.ID
JOIN Packages P2 ON F.Friend_ID = P2.ID
WHERE (
    P2.Salary > P1.Salary
)
ORDER BY P2.Salary;

---

3.SQL Project Planning

문제
You are given a table, Projects, containing three columns: Task_ID, Start_Date and End_Date. It is guaranteed that the difference between the End_Date and the Start_Date is equal to 1 day for each row in the table.

If the End_Date of the tasks are consecutive, then they are part of the same project. Samantha is interested in finding the total number of different projects completed.

Write a query to output the start and end dates of projects listed by the number of days it took to complete the project in ascending order. If there is more than one project that have the same number of completion days, then order by the start date of the project.

Sample Input

Sample Output

2015-10-28 2015-10-29
2015-10-30 2015-10-31
2015-10-13 2015-10-15
2015-10-01 2015-10-04

Explanation

The example describes following four projects:

Project 1: Tasks 1, 2 and 3 are completed on consecutive days, so these are part of the project. Thus start date of project is 2015-10-01 and end date is 2015-10-04, so it took 3 days to complete the project.
Project 2: Tasks 4 and 5 are completed on consecutive days, so these are part of the project. Thus, the start date of project is 2015-10-13 and end date is 2015-10-15, so it took 2 days to complete the project.
Project 3: Only task 6 is part of the project. Thus, the start date of project is 2015-10-28 and end date is 2015-10-29, so it took 1 day to complete the project.
Project 4: Only task 7 is part of the project. Thus, the start date of project is 2015-10-30 and end date is 2015-10-31, so it took 1 day to complete the project.

핵심

프로젝트의 시작점(Start Date)과 끝점(End Date) 분리

시작점
Start_Date가 다른 프로젝트의 End_Date에 존재하지 않는다면, 그것이 전체 프로젝트의 진짜 시작일
끝점
End_Date가 다른 프로젝트의 Start_Date에 존재하지 않는다면, 그것이 전체 프로젝트의 진짜 종료일
연결
각 시작점에 대해 그보다 크면서 가장 가까운 종료일을 짝지어주면 하나의 프로젝트 묶음으로 완성

코드

SELECT Start_Date, MIN(End_Date)
FROM
    (SELECT Start_Date FROM Projects WHERE Start_Date NOT IN 
    (SELECT End_Date FROM Projects)) AS S,
    (SELECT END_Date FROM Projects WHERE End_Date NOT IN
    (SELECT Start_Date FROM Projects)) AS E
WHERE Start_Date < End_Date
GROUP BY Start_Date
ORDER BY (MIN(End_Date) - Start_Date), Start_Date;

윈도우 함수로 푸는 법

SELECT MIN(Start_Date), MAX(End_Date)
FROM (
    -- 1. 각 날짜에 순번을 매기고, 날짜에서 순번을 빼서 '그룹 ID'만들기.
    SELECT Start_Date, End_Date,
           Start_Date - ROW_NUMBER() OVER (ORDER BY Start_Date) AS Group_ID
    FROM Projects
) Sub
-- 2. 같은 그룹 ID를 가진 것들끼리 묶어 시작과 끝 찾ㄱ기
GROUP BY Group_ID
-- 3. 문제 조건에 맞춰 정렬
ORDER BY DATEDIFF(MAX(End_Date), MIN(Start_Date)), MIN(Start_Date);

---

내일도 이어서