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.
oracle nested query :
select start_date, end_date from
(select project_id,min(start_date) as start_date, max(end_date) as end_date, max(end_date) - min(start_date) completion_date from (
select task_id, start_date,end_date , sum(group_flag) over (order by start_date) as project_id from
(select task_id, start_date,end_date,datediff, case when datediff <= 1 then 0 else 1 end as group_flag from
(select task_id, start_date, end_date, lag(end_date,1) over (order by end_date) as lag_date,
end_date - lag(end_date,1, end_date) over (order by end_date) as datediff
from projects)
)
)
group by project_id
)order by completion_date,start_date;
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 →
oracle nested query : select start_date, end_date from (select project_id,min(start_date) as start_date, max(end_date) as end_date, max(end_date) - min(start_date) completion_date from ( select task_id, start_date,end_date , sum(group_flag) over (order by start_date) as project_id from (select task_id, start_date,end_date,datediff, case when datediff <= 1 then 0 else 1 end as group_flag from (select task_id, start_date, end_date, lag(end_date,1) over (order by end_date) as lag_date, end_date - lag(end_date,1, end_date) over (order by end_date) as datediff from projects) ) ) group by project_id )order by completion_date,start_date;