Repository navigation
Expand file tree
/
Copy path05_backlog_flows.sql
More file actions
64 lines (64 loc) · 3.63 KB
/
Copy path05_backlog_flows.sql
File metadata and controls
64 lines (64 loc) · 3.63 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
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
-- R5: team movement rows and one global reconciliation row.
WITH ordered AS (
SELECT e.event_id, e.finding_id, e.occurred_at, e.owner_team_id, e.status,
LAG(e.status) OVER (
PARTITION BY e.finding_id ORDER BY e.occurred_at, e.event_id
) AS previous_status
FROM finding_events AS e
JOIN findings AS f ON f.finding_id = e.finding_id
WHERE f.detected_at < %(cutoff)s::timestamptz
AND e.occurred_at < %(cutoff)s::timestamptz
), movements AS (
SELECT owner_team_id, occurred_at,
CASE
WHEN previous_status IS NULL AND status IN ('open', 'in_progress') THEN 'new'
WHEN previous_status IN ('resolved','false_positive','not_applicable','accepted_risk')
AND status IN ('open','in_progress') THEN 'reopened'
WHEN previous_status IN ('open','in_progress')
AND status IN ('resolved','false_positive','not_applicable','accepted_risk') THEN
CASE status WHEN 'resolved' THEN 'remediated' ELSE status END
ELSE NULL
END AS movement
FROM ordered
), window_flows AS (
SELECT owner_team_id, movement, COUNT(*) AS movement_count
FROM movements
WHERE occurred_at >= %(window_start)s::timestamptz AND movement IS NOT NULL
GROUP BY owner_team_id, movement
), totals AS (
SELECT COALESCE(SUM(movement_count) FILTER (WHERE movement='new'), 0) AS new_count,
COALESCE(SUM(movement_count) FILTER (WHERE movement='reopened'), 0) AS reopened_count,
COALESCE(SUM(movement_count) FILTER (WHERE movement='remediated'), 0) AS remediated_count,
COALESCE(SUM(movement_count) FILTER (WHERE movement='false_positive'), 0) AS false_positive_count,
COALESCE(SUM(movement_count) FILTER (WHERE movement='not_applicable'), 0) AS not_applicable_count,
COALESCE(SUM(movement_count) FILTER (WHERE movement='accepted_risk'), 0) AS accepted_risk_count
FROM window_flows
), boundaries AS (
SELECT (SELECT COUNT(*) FROM finding_snapshot(%(window_start)s::timestamptz)
WHERE status IN ('open','in_progress')) AS beginning_backlog,
(SELECT COUNT(*) FROM finding_snapshot(%(cutoff)s::timestamptz)
WHERE status IN ('open','in_progress')) AS observed_ending_backlog
), reconciliation AS (
SELECT b.beginning_backlog, t.*,
b.beginning_backlog + t.new_count + t.reopened_count
- t.remediated_count - t.false_positive_count
- t.not_applicable_count - t.accepted_risk_count AS expected_ending_backlog,
b.observed_ending_backlog
FROM boundaries AS b CROSS JOIN totals AS t
)
SELECT 'movement'::text AS row_kind, f.owner_team_id AS team_id,
COALESCE(t.team_name, 'Unowned') AS team_name, f.movement, f.movement_count,
NULL::bigint AS beginning_backlog, NULL::numeric AS new_count,
NULL::numeric AS reopened_count, NULL::numeric AS remediated_count,
NULL::numeric AS false_positive_count, NULL::numeric AS not_applicable_count,
NULL::numeric AS accepted_risk_count, NULL::numeric AS expected_ending_backlog,
NULL::bigint AS observed_ending_backlog, NULL::numeric AS reconciliation_residual
FROM window_flows AS f LEFT JOIN teams AS t ON t.team_id = f.owner_team_id
UNION ALL
SELECT 'reconciliation', NULL::bigint, 'TOTAL', NULL::text, NULL::bigint,
beginning_backlog, new_count, reopened_count, remediated_count,
false_positive_count, not_applicable_count, accepted_risk_count,
expected_ending_backlog, observed_ending_backlog,
observed_ending_backlog - expected_ending_backlog
FROM reconciliation
ORDER BY row_kind, team_id NULLS LAST, movement;