[SQL] HackerRank 문제 풀기 (SQL Project Planning)

해당 문제는 HackerRank에서 사용한 문제이며 모든 테이블의 자료와 출처는 Hackerank임을 밝힙니다.

해커 랭크

해당 글에서는 윈도우 함를 사용하여 푸는 문제로 난이도가 어느정도 있는 문제입니다.

해당 문제 원본을 확인 하시려면 해당 사이트를 들어가시면 될 것 같습니다.

 

시작하겠습니다

 

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.


작업의 종료일이 연속적이라면 동일한 프로젝트의 일부가 됩니다. 사만다는 완료된 다양한 프로젝트의 총 수를 찾는 데 관심이 있습니다.

쿼리를 작성하여 프로젝트를 완료하는 데 걸린 일수를 오름차순으로 나열한 프로젝트의 시작 및 종료 날짜를 출력합니다. 완료 일수가 같은 프로젝트가 두 개 이상인 경우 프로젝트 시작 날짜까지 순서를 정합니다.

해당 문제는 Window 함수(LAG, LEAD)를 이용하여 풀어보겠습니다.

하지만 생각해야 할 것은 LAG를 하며 내려갈 마지막 행은 사라진다는 것입니다. 그것을 고려하여 해당 테이블의 마지막 행을 UNION을 통해 합쳐줄 것을 꼭 기억해주십시오.

그리고 마지막에 LEAD를 이용하여 내렸던 날짜들을 다시 올려 줄 것입니다.

 

1. LAG함수와 UNION 함수 적용하기

select 
    Start_Date,
    LAG(End_DATE) OVER (ORDER BY Start_Date, End_Date) lag
from Projects
UNION
select Top 1
    Start_Date,
    End_Date
from Projects
order by Start_Date DESC, End_Date DESC

코드 결과

우선, LAG를 이용하여 End_Date를 한칸씩 내려 주었습니다.(모두 1일 간격이기 때문에 가능)

하지만 LAG를 이용하면 마지막 행까지 내려가긴 하지만 사라지는 것을 확인 할 수 있습니다. 그래서 UNION을 통해 마지막 행에 있을 값 하나를 UNION을 통해 묶어주었습니다.(MySQL이라면 LIMIT를 사용하면 되지만, MSSQL이라 TOP 1 사용.)

---

2. Start와 End_Date같은 것을 제외 후 LEAD 함수를 사용하여 다시 End_Date 올려주기

WITH CTE AS (
    select 
        Start_Date,
        LAG(End_DATE) OVER (ORDER BY Start_Date, End_Date) lag
    from Projects
    UNION
    select Top 1
        Start_Date,
        End_Date
    from Projects
    order by Start_Date DESC, End_Date DESC
)
SELECT Start_Date
        , LEAD(lag) OVER (ORDER BY Start_Date, lag) lead
FROM CTE
WHERE lag IS NULL OR Start_Date IS NULL OR Start_Date != lag

코드 결과

우선 End_Date가 Null인 값을 가져오는 이유는 맨 처음 날짜는 LAG 함수로 인해 END_Date가 없는 상태이기 때문입니다.

그리고 Start_Date와 End_Date의 같은 날짜를 제외하는 이유는 해당 프로젝트에서 연결되는 날짜이기 때문이며 저희는 첫 날과 마지막 날만 알면 되기 때문입니다.

이제 LEAD를 통해 End_Date를 올려주면 프로젝트의 첫 날과 마지막 날짜가 연결된 것을 확인 할 수 있습니다.

---

3. Null값 제외와 정렬해주기

WITH CTE AS (
    select 
        Start_Date,
        LAG(End_DATE) OVER (ORDER BY Start_Date, End_Date) lag
    from Projects
    UNION
    select Top 1
        Start_Date,
        End_Date
    from Projects
    order by Start_Date DESC, End_Date DESC
)
SELECT t.Start_Date, t.lead
FROM (
    SELECT Start_Date
        , LEAD(lag) OVER (ORDER BY Start_Date, lag) lead
    FROM CTE
    WHERE lag IS NULL OR Start_Date IS NULL OR Start_Date != lag
) t
WHERE t.lead IS NOT NULL
ORDER BY DATEDIFF(DAY, t.Start_Date, t.lead) ASC, t.Start_Date ASC

우선 왜 NULL을 제외시키는 것인지 설명하도록 하겠습니다.

LEAD 함수 사용으로 인해 End_Date를 위로 한 칸씩 올리는 바람에 마지막 행에서의 End_Date는 Null이 남게 됩니다. 그리고 저희는 처음에 UNION을 해서 마지막 행을 추가해주었는데 해당 값은 LAG로 인해 사라진 마지막 행의 End_Date를 대체 하기 위해 End_Date를 추가 한 것이며 해당 End_Date는 위로 한 칸 올라갔기 때문에 제거를 해도 무방합니다.

 

마지막으로 정렬을 할 때 날짜를 빼주어야 하기 때문에 DATEDIFF를 사용해주었습니다.

---

이상으로 Hacker Rank 문제 풀이를 마치도록 하겠습니다.