cinimex_projects_periods_query.sql

WITH deltas AS (
	SELECT 
		sub.task_id,
		sub.start_date,
		sub.end_date,
		sub.end_date - sub.end_date_prev tasks_delta
	FROM (
		SELECT p.*,
			lag(p.end_date, 1) OVER () end_date_prev
		FROM projects p
	) sub
), boundary_tasks AS (
	SELECT 
		sub.task_id start_task_id,
		COALESCE(sub.task_lag - 1, (SELECT MAX(task_id) FROM projects)) end_task_id
	FROM (                          
		SELECT *,
			lag(d.task_id, -1) OVER () task_lag
		FROM deltas d 
		WHERE d.tasks_delta <> 1 OR d.tasks_delta is NULL
	) sub
), start_tasks AS (
	SELECT 
		task_id, 
		start_date 
	FROM projects
	WHERE task_id in (SELECT bt.start_task_id FROM boundary_tasks bt)
), end_tasks AS (
	SELECT 
		task_id, 
		end_date 
	FROM projects
	WHERE task_id IN (SELECT bt.end_task_id FROM boundary_tasks bt)
)
SELECT 
	st.task_id first_task_id, 
	et.task_id last_task_id,
	st.start_date,
	et.end_date,
	et.end_date - st.start_date project_period
FROM boundary_tasks bt
JOIN start_tasks st 
	ON st.task_id = bt.start_task_id
JOIN end_tasks et 
	ON et.task_id = bt.end_task_id 
ORDER BY project_period DESC;