Repository navigation
Expand file tree
/
Copy path03_weekly_backlog.sql
More file actions
41 lines (41 loc) · 1.82 KB
/
Copy path03_weekly_backlog.sql
File metadata and controls
41 lines (41 loc) · 1.82 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
-- R3: detail and clearly labeled total rows. Overall cutoff is a guard.
WITH weeks AS (
SELECT generate_series(
%(weekly_start)s::timestamptz,
LEAST(%(weekly_end)s::timestamptz, %(cutoff)s::timestamptz),
INTERVAL '7 days'
) AS week_cutoff_utc
), team_dim AS (
SELECT team_id, team_name, created_at, 'team'::text AS bucket_kind FROM teams
UNION ALL SELECT NULL::bigint, 'Unowned'::text, NULL::timestamptz, 'unowned'::text
), grid AS (
SELECT w.week_cutoff_utc, t.team_id, t.team_name, t.bucket_kind
FROM weeks AS w CROSS JOIN team_dim AS t
WHERE t.bucket_kind = 'unowned' OR t.created_at < w.week_cutoff_utc
), counts AS (
SELECT w.week_cutoff_utc, s.owner_team_id, COUNT(*) AS actionable_count
FROM weeks AS w
CROSS JOIN LATERAL finding_snapshot(w.week_cutoff_utc) AS s
WHERE s.status IN ('open', 'in_progress')
GROUP BY w.week_cutoff_utc, s.owner_team_id
), detail AS (
SELECT g.week_cutoff_utc, g.team_id, g.team_name, g.bucket_kind,
COALESCE(c.actionable_count, 0) AS actionable_count
FROM grid AS g
LEFT JOIN counts AS c ON c.week_cutoff_utc = g.week_cutoff_utc
AND c.owner_team_id IS NOT DISTINCT FROM g.team_id
), labeled AS (
SELECT week_cutoff_utc, team_id, team_name, bucket_kind, actionable_count FROM detail
UNION ALL
SELECT week_cutoff_utc, NULL::bigint, 'TOTAL'::text, 'total'::text,
SUM(actionable_count)
FROM detail GROUP BY week_cutoff_utc
)
SELECT week_cutoff_utc, team_id, team_name, bucket_kind, actionable_count,
actionable_count - LAG(actionable_count) OVER (
PARTITION BY bucket_kind, team_id ORDER BY week_cutoff_utc
) AS change_from_previous
FROM labeled
ORDER BY week_cutoff_utc,
CASE bucket_kind WHEN 'team' THEN 1 WHEN 'unowned' THEN 2 ELSE 3 END,
team_id;