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.
/*
Enter your query here.
*/
with cte as (select start_date, end_date,
- row_number() over (order by end_date) as date_lag,
end_date - row_number() over (order by end_date) as date_lag_2
from projects
order by start_date asc)
, cte2 as (
select min(start_date) min_date, max(end_date) max_date, count(*) total_days
from cte
group by date_lag_2
)
select min_date, max_date from cte2
order by total_days asc, min_date asc
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 →
/* Enter your query here. */ with cte as (select start_date, end_date, - row_number() over (order by end_date) as date_lag, end_date - row_number() over (order by end_date) as date_lag_2 from projects order by start_date asc)
, cte2 as ( select min(start_date) min_date, max(end_date) max_date, count(*) total_days from cte group by date_lag_2 )
select min_date, max_date from cte2 order by total_days asc, min_date asc