SQL 코드카타 176 - SQL Project Planning

https://www.hackerrank.com/challenges/sql-projects/problem?isFullScreen=true

SQL Project Planning | HackerRank

 

Column	Type
Task_Id	Integer
Start_Date	Date
End_Date	Date

 

Projects 테이블:

• Task_Id : 작업의 id
• Start_Date : 작업 시작 날짜
• End_Date :  작업 종료 날짜

만약 작업들의 End_Date가 연속적이라면, 그것들은 동일한 프로젝트의 일부입니다. Samantha는 완료된 서로 다른 프로젝트의 총 수를 찾는 것에 관심이 있습니다.

프로젝트 완료에 소요된 일수를 기준으로 오름차순으로 나열된 프로젝트의 시작일과 종료일을 출력하는 쿼리를 작성하세요. 만약 완료 일수가 동일한 프로젝트가 여러 개 있다면, 프로젝트의 시작일을 기준으로 정렬합니다.

 

    select
        Task_Id,
        Start_Date,
        End_Date,
        date_add(Start_Date, interval -row_number() over (order by Start_Date) day) grp
    from Projects

 

동일한 프로젝트들을 찾기 위해서 date_add를 통해서 프로젝트의 시작 날짜가 같은 프로젝트들을 계산한다.

각 열은 1일씩의 데이터를 나타내고 있기 때문에 시작날짜에서 떨어진 일자만큼 값을 빼준다면 동일한프로젝트들은 모두 grp라는 열에서 동일한 값을 가지게 된다.

 

with GroupedProjects  as (
    select
        Task_Id,
        Start_Date,
        End_Date,
        date_add(Start_Date, interval -row_number() over (order by Start_Date) day) grp
    from Projects
)

select
    min(Start_Date) Start_Date,
    max(End_Date) End_Date,
    count(*) Duration
from GroupedProjects
group by grp

 

위의 쿼리를 with절을 사용해서 새로운 테이블로 만들어준다(서브쿼리를 사용해도 상관 없다.) 그리고 grp로 그룹화한 데이터들을(같은 프로젝트) 기준으로 시작날짜에서 가장 작은 값(같은 프로젝트에서 가장 작은 시작날짜를 가지고 있으므로 프로젝트가 가장 처음 시작한 날짜이다.)을 표시하고 종료날짜에서 가장 큰값(같은 프로젝트에서 가장 큰 종료날짜를 가지고 있으므로 프로젝트 전체가 종료된 날짜이다.을 표시하고 그 사이의 기간들을 센다.

 

select
    Start_Date,
    End_Date
from
(select
    min(Start_Date) Start_Date,
    max(End_Date) End_Date,
    count(*) Duration
from GroupedProjects
group by grp) a
order by a.Duration, Start_Date

 

위의 쿼리를 서브쿼리로 해서(with절에 쿼리를 추가해도 상관 없다.) 프로젝트 시작일과 종료일을 입력한다. 그리고 기간과 시작날짜를 기준으로 정렬하면 완료!

 

동일한 프로젝트들을 묶어내는 아이디어가 필요했던 문제였다.