[1日3 SQL]HackerRank-Mediums,Hards

Advanced JoinSQL > Project Planning
私の答え
最初の中間プロセス

select  *
from    (
select  B.task_id, B.start_date, B.end_date, B.flag
        ,case 
            when @prev_value = B.flag then @row_num := @row_num + 1
            else @row_num := 0 -- first row : '' != flag(1)  -> 0
        end as rn
        , @prev_value := B.flag
from    (select A.*
         ,(SELECT @row_num := 0) x
         ,(SELECT @prev_value := '') y

        from    (
                select  A.*
                , case when B.start_date is not null then 1 else 0 end as flag

                from    projects A
                left join   Projects B
                        on  B.start_date = A.end_date
                order by    2,3
            ) A
         
    ) B
)C

where   C.rn = 0

mysqlのrow number実装により,以下の手順を完了した.

2行を切断すると、プロジェクト期間が1つになります.
他人の答え

/* Choose start dates that are not end dates of other projects 
(if a start date is an end date, it is part of the same project) */

SELECT Start_Date, MIN(End_Date)
FROM 
    (SELECT Start_Date 
     FROM Projects 
     WHERE Start_Date NOT IN (
     	SELECT End_Date FROM Projects
        )
    ) a,
    
/* Choose end dates that are not end dates of other projects */

    (SELECT end_date 
     FROM PROJECTS 
     WHERE end_date NOT IN (
     	SELECT start_date FROM PROJECTS
        )
    ) b
/* At this point, we should have a list of start dates and end dates that don't necessarily correspond with each other */

/* This makes sure we only choose end dates that fall after the start date, 
and choosing the MIN means for the particular start_date, 
we get the closest end date that does not coincide with the start of another task */

    where start_date < end_date
    GROUP BY start_date
    ORDER BY datediff(start_date, MIN(end_date)) DESC, start_date