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
An unexpected error occurred. Please try reloading the page. If problem persists, please contact support@hackerrank.com
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;