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.
The easiest way to think about this is to identify a pattern in the original output table (projects). If you look at the table, you can identify that the start_date of a particular project doesn't appear in the end_date column, and the end_date of a particular project doesn't appear in the start_date column. Once you figure this out, you can rank them, join and you are done!
`
with in_start_not_in_end as (
select distinct
start_date,
row_number() over(order by start_date) as rnk
from projects
where start_date not in (select distinct end_date from projects)
),
in_end_not_in_start as (
select distinct
end_date,
row_number() over(order by end_date) as rnk
from projects
where end_date not in (select distinct start_date from projects)
),
w_date_delta as (
select
start_date,
end_date,
datediff(day, start_date, end_date) as delta
from in_start_not_in_end s
left join in_end_not_in_start e
on s.rnk = e.rnk
)
select
start_date,
end_date
from w_date_delta
order by delta, 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 →
The easiest way to think about this is to identify a pattern in the original output table (projects). If you look at the table, you can identify that the start_date of a particular project doesn't appear in the end_date column, and the end_date of a particular project doesn't appear in the start_date column. Once you figure this out, you can rank them, join and you are done!
` with in_start_not_in_end as ( select distinct start_date, row_number() over(order by start_date) as rnk from projects where start_date not in (select distinct end_date from projects)
),
in_end_not_in_start as ( select distinct end_date, row_number() over(order by end_date) as rnk from projects where end_date not in (select distinct start_date from projects) ),
w_date_delta as ( select start_date, end_date, datediff(day, start_date, end_date) as delta from in_start_not_in_end s left join in_end_not_in_start e on s.rnk = e.rnk )
select start_date, end_date
from w_date_delta order by delta, start_date