HackerRank SQL Project Planning.sql

set search_path to data_sci

create table projects(
id integer generated by default as identity,
start_date date,
finish_date date

)

truncate table projects

-- Task_ID, Start_Date and End_Date. It is guaranteed that the difference between the 
-- End_Date and the Start_Date is equal to 1 day for each row in the table.
insert into projects
(start_date, finish_date)
(select ('2022-02-11'::date + concat(num, ' day')::interval),
	('2022-02-11'::date + concat(num+1, ' day')::interval)
	from generate_series(0,3,1) as num)
	
	
	
-- If the End_Date of the tasks are consecutive, then they are part of the same project. 
-- Samantha is interested in finding the total number of different projects completed.

-- Write a query to output the start and end dates of projects listed by the number of 
-- days it took to complete the project in ascending order. If there is more than one 
-- project that have the same number of completion days, then order by the start date of 
-- the project.


-- This problem is a very special case where start date and finish date are only one day diff.
-- You can use this to find start dates that are not in finish dates and vice versa.
-- Perform a cross join to get all combinations of start and end dates. The filter to get
-- the correct one.
-- Then start dates have to be less than finish dates. That will get you a set of dates
-- so you have to use the min with a group by to fine the final pair
with start_cte as(
	select start_date 
	from projects
	where start_date not in
		(select p2.finish_date from projects p2) ),
finish_cte as (
	select finish_date
	from projects	
	where finish_date not in
		(select p3.start_date from projects p3))
select start_cte.start_date, min(finish_cte.finish_date	) as finish_date
from start_cte
cross join finish_cte
where start_cte.start_date <  finish_cte.finish_date
group by start_cte.start_date
order by (min(finish_cte.finish_date) - start_cte.start_date) asc, start_cte.start_date ASC