相関のあるワークフローのタイムラインを再構築する

WITH target_workflow AS (
  SELECT workflow_id
  FROM workflow_timeline_events
  WHERE timestamp_utc >= now() - INTERVAL '24 hours'
    AND environment = 'production'
    AND status = 'failed'
  ORDER BY timestamp_utc DESC
  LIMIT 1
),
workflow_start AS (
  SELECT
    events.workflow_id,
    MIN(events.timestamp_utc) AS started_at
  FROM workflow_timeline_events AS events
  JOIN target_workflow AS target
    ON target.workflow_id = events.workflow_id
  GROUP BY events.workflow_id
)
SELECT
  events.workflow_id,
  ROW_NUMBER() OVER (
    PARTITION BY events.workflow_id
    ORDER BY events.timestamp_utc, events.event_name
  ) AS step_number,
  events.event_name,
  events.service,
  events.status,
  date_part('epoch', events.timestamp_utc - starts.started_at)
    AS elapsed_seconds
FROM workflow_timeline_events AS events
JOIN workflow_start AS starts
  ON starts.workflow_id = events.workflow_id
ORDER BY events.workflow_id, step_number;