SQL Project Planning / HackerRank, SQL, MySQL

문제

source: HackerRank

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.

 

 

 

 

제출답안(MySQL)

SET @prj_num := 0;

WITH prj AS (
    SELECT
        curr.start_date, curr.end_date,
        CASE WHEN prev.end_date IS NULL
            THEN @prj_num := @prj_num + 1 
            ELSE @prj_num END AS project_number
    FROM 
      projects AS curr
      LEFT JOIN projects AS prev
        ON curr.start_date = prev.end_date
    ), 
    ls_prj AS (
        SELECT MIN(start_date) AS sp, MAX(end_date) AS ep
        FROM prj
        GROUP BY project_number
    )


SELECT * FROM ls_prj
ORDER BY DATEDIFF(ep, sp), sp

 

 

 

풀이(MySQL)

Project 테이블 두 개를 조인

>> 현재 행의 시작일과 이전 행의 종료일을 기준으로 조인

SELECT curr.start_date, curr.end_date, prev.end_date
    FROM 
      projects AS curr
      LEFT JOIN projects AS prev
        ON curr.start_date = prev.end_date

 

>> 프로젝트 첫날은 NULL로 표시됨

>> NULL을 기준으로 프로젝트 번호 매기기

 

지역변수 prj_num 에 0 값을 주고 Null일 경우 prj_num 1을 올려주는 서브쿼리 생성

SET @prj_num := 0;

WITH prj AS (
    SELECT
        curr.start_date, curr.end_date,
        CASE WHEN prev.end_date IS NULL
            THEN @prj_num := @prj_num + 1 
            ELSE @prj_num END AS project_number
    FROM 
      projects AS curr
      LEFT JOIN projects AS prev
        ON curr.start_date = prev.end_date
    )

 

프로젝트 번호를 기준으로 그룹화하고 시작일의 최소값, 종료일의 최대값을 불러오는 서브쿼리 작성

    ls_prj AS (
        SELECT MIN(start_date) AS sp, MAX(end_date) AS ep
        FROM prj
        GROUP BY project_number
    )

 

ls_prj를 기간과 시작일 기준으로 정렬하는 쿼리 작성

SELECT * FROM ls_prj
ORDER BY DATEDIFF(ep, sp), sp

 

 

 

 

문제 바로가기(MySQL)