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 all_date as
(
select Start_Date as date_ from Projects
union all
select End_Date as date_ from Projects
)
, table_data_point as
(
select
date_,
count(date_) as number_dup
from all_date
group by 1
having count(date_) = 1
)
, table_add_key_point as
(
select
date_,
case when date_ in (select Start_Date as date_ from Projects) then 'Start' else 'End' end as Segment
from table_data_point
)
, start_table as
(
select
date_,
row_number() over(order by date_) as rk
from table_add_key_point
where 1=1
and Segment = 'Start'
)
, end_table as
(
select
date_,
row_number() over(order by date_) as rk
from table_add_key_point
where 1=1
and Segment = 'End'
)
select
start_table.date_ as start_date,
end_table.date_ as end_date
from start_table
join end_table using(rk)
order by datediff(end_table.date_, start_table.date_) asc, 1 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 →
`with all_date as ( select Start_Date as date_ from Projects union all select End_Date as date_ from Projects ) , table_data_point as ( select date_, count(date_) as number_dup from all_date group by 1 having count(date_) = 1 ) , table_add_key_point as ( select date_, case when date_ in (select Start_Date as date_ from Projects) then 'Start' else 'End' end as Segment from table_data_point ) , start_table as ( select date_, row_number() over(order by date_) as rk from table_add_key_point where 1=1 and Segment = 'Start' ) , end_table as ( select date_, row_number() over(order by date_) as rk from table_add_key_point where 1=1 and Segment = 'End' ) select start_table.date_ as start_date, end_table.date_ as end_date from start_table join end_table using(rk) order by datediff(end_table.date_, start_table.date_) asc, 1 asc