We use cookies to ensure you have the best browsing experience on our website. Please read our cookie policy for more information about how we use cookies.
%sql
with start_date_cte as (
select *,dense_rank() over ( order by task_id asc) as rownum from (
select *,datediff(day,l,start_date) as dd from (
select start_date,end_date,task_id
,lag(start_date,1) over(order by end_date) as l
from projects ) )
where dd!=-1 or dd is null)
,
end_date_cte as (
select *, dense_rank() over ( order by task_id asc) as rownum from
(
select *,datediff(day,l,start_date) as dd from(
select start_date,end_date,task_id
,lead(start_date,1) over(order by end_date) as l
from projects
)
)
where dd!=1 or dd is null
)
select s.start_date,e.end_date from start_date_cte s join end_date_cte e
on s.rownum=e.rownum;
Cookie support is required to access HackerRank
Seems like cookies are disabled on this browser, please enable them to open this website
SQL Project Planning
You are viewing a single comment's thread. Return to all comments →
%sql with start_date_cte as ( select *,dense_rank() over ( order by task_id asc) as rownum from ( select *,datediff(day,l,start_date) as dd from ( select start_date,end_date,task_id ,lag(start_date,1) over(order by end_date) as l from projects ) ) where dd!=-1 or dd is null) , end_date_cte as ( select *, dense_rank() over ( order by task_id asc) as rownum from ( select *,datediff(day,l,start_date) as dd from( select start_date,end_date,task_id ,lead(start_date,1) over(order by end_date) as l from projects ) ) where dd!=1 or dd is null ) select s.start_date,e.end_date from start_date_cte s join end_date_cte e on s.rownum=e.rownum;