Skip to content

Instantly share code, notes, and snippets.

@maratuska
Last active February 21, 2023 09:44
Show Gist options
  • Save maratuska/508e60a440d0aefbc74c4c571d10e0ab to your computer and use it in GitHub Desktop.
Save maratuska/508e60a440d0aefbc74c4c571d10e0ab to your computer and use it in GitHub Desktop.
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;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment