what is wrong with the following code? SET @counter = 1;

WITH CTE AS ( SELECT TASK_ID, START_DATE, END_DATE FROM PROJECTS ORDER BY START_DATE ), CTE2 AS ( SELECT CTE., LEAD(START_DATE) OVER (ORDER BY START_DATE) AS NEXT_START_DATE FROM CTE ), CTE3 AS ( SELECT CTE2., CASE WHEN CTE2.NEXT_START_DATE = CTE2.END_DATE THEN @counter ELSE @counter := @counter + 1 END AS FLAG FROM CTE2 ) , CTE4 AS (SELECT DISTINCT FLAG, MIN(START_DATE) OVER (PARTITION BY FLAG) AS NEW_START_DATE, MAX(END_DATE) OVER (PARTITION BY FLAG) AS NEW_END_DATE FROM CTE3)

SELECT CTE4.NEW_START_DATE, CTE4.NEW_END_DATE, DATEDIFF(CTE4.NEW_END_DATE, CTE4.NEW_START_DATE) FROM CTE4 ORDER BY DATEDIFF(CTE4.NEW_END_DATE, CTE4.NEW_START_DATE), CTE4.NEW_START_DATE

I dont know why Hacker rank is blocking me ("Your message has been blocked by our spam checker") when i post all the -text , images and code in one go(post) ..so i have segregated them all in consecutive comments ...just like the question dates(consecutive) ..ironic ..

hope it helps somebody ( instead of irritating ppl because of the unstructured nature of the post )

Summary of What We Are Doing:

1. Identify New Projects: If Start_Date = LAG(End_Date): Assign a flag of 0 to indicate continuity (same project).

If Start_Date ≠ LAG(End_Date): Assign a flag of 1 to indicate the start of a new project.

• Summary :Flag new projects when Start_Date = previous task's End_Date.

• Flag = 1 if consecutive, 0 if not.

  2. Group Tasks into Projects:

• Use a cumulative sum of flags to assign a unique GroupID for each project.

  3. Summarize Each Project:

• Calculate:

  • Start Date: The earliest Start_Date in the group.
  • End Date: The latest End_Date in the group.

  • Duration: Total days spanned by the group.

    4. Sort and Output Results:
• Sort projects by duration, and then by start date if durations are equal.

-- Step 1: Identify consecutive tasks and flag new projects
WITH ConsecutiveGroups AS (
    SELECT 
        Task_ID,
        Start_Date,
        End_Date,
        -- Flag new projects when Start_Date = previous task's End_Date
        CASE 
            WHEN LAG(End_Date) OVER (ORDER BY Start_Date) = Start_Date THEN 0
            ELSE 1
        END AS NewGroupFlag
    FROM Projects
),

Step 1 :

Since we have been given "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."

We can use the logic : Start_Date = LAG(End_Date) to identify same project or not Otherwise we would have used End_Date - LAG(End_Date) = 1 to identify same project or not as already pointed out by debjyoti_ghosh

SQL Query to Identify Consecutive Tasks and Group Projects:

-- Step 1: Identify consecutive tasks and flag new projects
WITH ConsecutiveGroups AS (
    SELECT 
        Task_ID,
        Start_Date,
        End_Date,
        -- Flag new projects when Start_Date = previous task's End_Date
        CASE 
            WHEN LAG(End_Date) OVER (ORDER BY Start_Date) = Start_Date THEN 0
            ELSE 1
        END AS NewGroupFlag
    FROM Projects
),

-- Step 2: Assign a unique GroupID to each project
GroupAssignments AS (
    SELECT 
        Task_ID,
        Start_Date,
        End_Date,
        -- Cumulative sum of flags to generate unique GroupIDs
        SUM(NewGroupFlag) OVER (ORDER BY Start_Date) AS GroupID
    FROM ConsecutiveGroups
),

-- Step 3: Summarize each project by its GroupID
ProjectSummary AS (
    SELECT 
        MIN(Start_Date) AS ProjectStart,
        MAX(End_Date) AS ProjectEnd,
        -- Calculate total project duration in days
        DATEDIFF(DAY, MIN(Start_Date), MAX(End_Date)) + 1 AS ProjectDays
    FROM GroupAssignments
    GROUP BY GroupID
)

-- Step 4: Output start and end dates of each project, sorted as required
SELECT 
    ProjectStart, 
    ProjectEnd
FROM ProjectSummary
ORDER BY ProjectDays ASC, ProjectStart ASC;

Summary of What We Are Doing:

1. Identify New Projects:

   • Flag new projects when Start_Date = previous task's End_Date.
   • Flag = 1 if consecutive, 0 if not.

2. Group Tasks into Projects:

   • Use a cumulative sum of flags to assign a unique GroupID for each project.

3. Summarize Each Project:

   • Calculate:
     • Start Date: The earliest Start_Date in the group.
     • End Date: The latest End_Date in the group.
     • Duration: Total days spanned by the group.

4. Sort and Output Results:

   • Sort projects by duration, and then by start date if durations are equal.

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