WITH successful_deletes AS (
    SELECT wb.workspace_id, wb.created_at as delete_time, wb.id as delete_build_id
    FROM workspace_builds wb
    JOIN provisioner_jobs pj ON wb.job_id = pj.id
    WHERE wb.transition = 'delete'
      AND pj.completed_at IS NOT NULL
      AND pj.error IS NULL
  )
  SELECT
    w.id as workspace_id,
    u.username || '/' || w.name as workspace,
    wb.id as violating_build_id,
    wb.transition as violating_transition,
    wb.created_at as violating_build_time,
    sd.delete_time,
    sd.delete_build_id,
    wb.created_at - sd.delete_time as time_after_delete,
    COALESCE(
      string_agg(
        DISTINCT wr.name || ' (' || wr.type || ')',
        ', ' ORDER BY wr.name || ' (' || wr.type || ')'
      ),
      'No resources'
    ) as workspace_resources
  FROM workspace_builds wb
  JOIN successful_deletes sd ON wb.workspace_id = sd.workspace_id
  JOIN workspaces w ON wb.workspace_id = w.id
  JOIN users u ON w.owner_id = u.id
  LEFT JOIN workspace_resources wr ON wb.job_id = wr.job_id
    AND wr.type NOT LIKE 'coder_%'
  WHERE wb.transition IN ('start', 'stop')
    AND wb.created_at > sd.delete_time
  GROUP BY
    w.id, w.name, u.username, wb.id, wb.transition,
    wb.created_at, sd.delete_time, sd.delete_build_id
  ORDER BY wb.created_at DESC;