Repository navigation
Expand file tree
/
Copy path06_rollout_progress.sql
More file actions
40 lines (40 loc) · 2.17 KB
/
Copy path06_rollout_progress.sql
File metadata and controls
40 lines (40 loc) · 2.17 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
-- R6: rollout rows plus one weighted portfolio total.
WITH per_rollout AS (
SELECT r.rollout_id, r.rollout_name,
COUNT(s.task_id) AS visible_task_count,
COUNT(s.task_id) FILTER (WHERE s.status <> 'canceled') AS eligible_task_count,
COUNT(s.task_id) FILTER (WHERE s.status = 'done') AS done_count,
COUNT(s.task_id) FILTER (WHERE s.status = 'blocked') AS blocked_count,
COUNT(s.task_id) FILTER (
WHERE s.status IN ('pending','in_progress','blocked')
AND s.due_at IS NOT NULL AND s.due_at < %(cutoff)s::timestamptz
) AS overdue_remaining_count,
COUNT(s.task_id) FILTER (WHERE s.status = 'canceled') AS canceled_count,
COUNT(s.task_id) FILTER (
WHERE s.status IN ('pending','in_progress','blocked') AND s.owner_team_id IS NULL
) AS missing_owner_remaining_count,
COUNT(s.task_id) FILTER (
WHERE s.status IN ('pending','in_progress','blocked') AND s.due_at IS NULL
) AS missing_due_remaining_count
FROM rollouts AS r
LEFT JOIN rollout_task_snapshot(%(cutoff)s::timestamptz) AS s
ON s.rollout_id = r.rollout_id
WHERE r.created_at < %(cutoff)s::timestamptz
GROUP BY r.rollout_id, r.rollout_name
), labeled AS (
SELECT 'rollout'::text AS row_kind, * FROM per_rollout
UNION ALL
SELECT 'portfolio'::text, NULL::bigint, 'Portfolio'::text,
COALESCE(SUM(visible_task_count),0), COALESCE(SUM(eligible_task_count),0),
COALESCE(SUM(done_count),0), COALESCE(SUM(blocked_count),0),
COALESCE(SUM(overdue_remaining_count),0), COALESCE(SUM(canceled_count),0),
COALESCE(SUM(missing_owner_remaining_count),0),
COALESCE(SUM(missing_due_remaining_count),0)
FROM per_rollout
)
SELECT row_kind, rollout_id, rollout_name, visible_task_count,
eligible_task_count, done_count, blocked_count, overdue_remaining_count,
canceled_count, missing_owner_remaining_count, missing_due_remaining_count,
100.0 * done_count / NULLIF(eligible_task_count, 0) AS completion_percent
FROM labeled
ORDER BY CASE row_kind WHEN 'rollout' THEN 1 ELSE 2 END, rollout_id;