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.
with ordered_dates as (
select task_id
, start_date
, end_date
, lag(end_date) over (order by end_date) as last_end_date
, lead(start_date) over (order by start_date) as next_start_date
from projects
), start_dates_tables as (
select case when datediff(day, last_end_date, end_date) > 1 or datediff(day, last_end_date, end_date) is null then start_date end as project_start_date
from ordered_dates
), ranked_start_dates as (
select project_start_date
, rank() over (order by project_start_date) as rank
from start_dates_tables
where project_start_date is not null
), end_dates_table as (
select case when datediff(day, start_date, next_start_date) > 1 or datediff(day, start_date, next_start_date) is null then end_date end as project_end_date
from ordered_dates
), ranked_end_dates as (
select project_end_date
, rank() over (order by project_end_date) as rank
from end_dates_table
where project_end_date is not null
)
select project_start_date
, project_end_date
from ranked_start_dates s
inner join ranked_end_dates e
on e.rank = s.rank
order by datediff(day, project_start_date, project_end_date), project_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 →
I used window functions in my solution:
with ordered_dates as ( select task_id , start_date , end_date , lag(end_date) over (order by end_date) as last_end_date , lead(start_date) over (order by start_date) as next_start_date from projects ), start_dates_tables as ( select case when datediff(day, last_end_date, end_date) > 1 or datediff(day, last_end_date, end_date) is null then start_date end as project_start_datefrom ordered_dates ), ranked_start_dates as ( select project_start_date , rank() over (order by project_start_date) as rank from start_dates_tables
where project_start_date is not null ), end_dates_table as ( select case when datediff(day, start_date, next_start_date) > 1 or datediff(day, start_date, next_start_date) is null then end_date end as project_end_date from ordered_dates ), ranked_end_dates as ( select project_end_date , rank() over (order by project_end_date) as rank from end_dates_table
where project_end_date is not null ) select project_start_date , project_end_date from ranked_start_dates s inner join ranked_end_dates e on e.rank = s.rank order by datediff(day, project_start_date, project_end_date), project_start_date