HackerRank: SQL 풀이 56

56. Advanced Join: SQL Project Planning

• LEAD함수: 다음 행의 날짜를 가져오는 함수

LEAD(DateColumn) OVER (ORDER BY DateColumn)

• DATEDIFF함수: 현재 행과 다음 행의 날짜 차이를 계산한다.

DATEDIFF(DAY, DateColumn, LEAD(DateColumn) OVER (ORDER BY DateColumn))

틀린풀이 1

WITH GroupTable AS (SELECT Task_ID,
                           Start_Date,
                           End_Date,
                           SUM(CASE WHEN
                                    DATEDIFF(DAY, End_Date, LAG(End_Date) OVER (ORDER BY Start_Date)) = 1 THEN 0
                                ELSE 1
                                END) OVER(ORDER BY Start_Date) AS Group_ID
                    FROM Projects)
SELECT MIN(Start_Date), MAX(End_Date), DATEDIFF(DAY, End_Date, Start_Date) AS Duration
FROM GroupTable
GROUP BY Group_ID
ORDER BY Duration, MIN(Start_Date);

window 함수는 또다른 window 함수 안에서 사용 불가-!
그냥 CTE 하나 더 만들자!

틀린풀이 2: 오타 실화..? (WHERE말고 FROM;;; Gorup말고 Group ;;

WITH Day_Diff AS (
SELECT 
    Task_ID,
    Start_Date,
    End_Date,
    CASE
        WHEN DATEDIFF(DAY, End_Date, LAG(End_Date) OVER (ORDER BY Start_Date)) = 1  THEN 0 
        ELSE 1
        END AS Date_Diff
),
Group_Task AS (
SELECT
    Task_ID,
    Start_Date,
    End_Date,
    SUM(Date_Diff) OVER (ORDER BY Start_Date) AS Gorup_ID
FROM Day_Diff
)
SELECT MIN(Start_Date), MAX(End_Date), DATEDIFF(DAY, MAX(End_Date), MIN(Start_Date)) + 1 AS Duration
WHERE Group_Task
GROUP BY Group_ID
ORDER BY Duration, MIN(Start_Date);

틀린풀이 3: 아니 ;; DATEDIFF는 요소 자리가 중요했네요... 절댓값계산이 아니라 첫번째 인자에서 두번쨰 인자 까지 걸리는 시간입니다!

WITH CTE_1 AS (
SELECT 
    Task_ID, 
    Start_Date, 
    End_Date,
    CASE
        WHEN Start_Date = LAG(End_Date) OVER (ORDER BY Start_Date) THEN 0
        ELSE 1
        END AS Consecutive 
FROM Projects
),
CTE_2 AS (
SELECT
    Task_ID,
    Start_Date,
    End_Date,
    SUM(Consecutive) OVER (ORDER BY Start_Date) AS Group_ID
FROM CTE_1
),
CTE_3 AS (
SELECT
    MIN(Start_Date) AS SD,
    MAX(End_Date) AS ED,
    DATEDIFF(DAY, MAX(End_Date), MIN(Start_Date)) + 1 AS Date_Diff    
FROM CTE_2
GROUP BY Group_ID
)
SELECT SD, ED
FROM CTE_3
ORDER BY Date_Diff, SD;

Min, Max 자리 바꿔줄것!

정답..... 2시간걸림;;; 실화인가요..

WITH CTE_1 AS (
SELECT 
    Task_ID, 
    Start_Date, 
    End_Date,
    CASE
        WHEN Start_Date = LAG(End_Date) OVER (ORDER BY Start_Date) THEN 0
        ELSE 1
        END AS Consecutive 
FROM Projects
),
CTE_2 AS (
SELECT
    Task_ID,
    Start_Date,
    End_Date,
    SUM(Consecutive) OVER (ORDER BY Start_Date) AS Group_ID
FROM CTE_1
),
CTE_3 AS (
SELECT
    MIN(Start_Date) AS SD,
    MAX(End_Date) AS ED,
    DATEDIFF(DAY, MIN(Start_Date), MAX(End_Date)) + 1 AS Date_Diff    
FROM CTE_2
GROUP BY Group_ID
)
SELECT SD, ED
FROM CTE_3
ORDER BY Date_Diff, SD;