| 1 | -- ?2 the weeks as a JSON array of { start, from, to, cut }: the first UTC |
| 2 | -- day, the bounds (from inclusive, to exclusive) and when the week is read |
| 3 | -- as ended for stalled pull requests (to, or now when that is earlier). |
| 4 | -- |
| 5 | -- A person's verdict is an approval or request for changes by someone |
| 6 | -- other than the owner, not g1t's review agent, and not a workspace agent |
| 7 | -- (which keeps its verdict apart): the first one is the first review, as |
| 8 | -- insights.sql counts it. |
| 9 | , |
| 10 | w AS ( |
| 11 | SELECT json_extract(value, '$.start') AS start, |
| 12 | json_extract(value, '$.from') AS lo_at, json_extract(value, '$.to') AS hi_at, |
| 13 | julianday(json_extract(value, '$.from')) AS lo, |
| 14 | julianday(json_extract(value, '$.to')) AS hi, |
| 15 | julianday(json_extract(value, '$.cut')) AS cut |
| 16 | FROM json_each(?2) |
| 17 | ), |
| 18 | -- The earliest and the latest cut. |
| 19 | cuts AS (SELECT MIN(cut) AS lo, MAX(cut) AS hi FROM w), |
| 20 | x AS MATERIALIZED ( |
| 21 | SELECT pr.id, pr.repo_id, pr.number, pr.status, pr.agent, pr.draft_seconds, pr.head_commit, |
| 22 | julianday(pr.created_at) AS c, julianday(pr.merged_at) AS m, julianday(pr.updated_at) AS u, |
| 23 | julianday(pr.head_pushed_at) AS pushed, |
| 24 | julianday((SELECT MIN(cm.created_at) FROM comments cm |
| 25 | WHERE cm.repo_id = pr.repo_id AND cm.number = pr.number |
| 26 | AND cm.verdict IN ('approve', 'request_changes') |
| 27 | AND cm.author_id != pr.owner_id AND cm.author_id != 'usr_g1t_agent' AND cm.agent_id IS NULL)) AS r, |
| 28 | -- An agent's merged pull request a person asked for changes on before it merged. |
| 29 | (pr.agent AND pr.status = 'merged' AND EXISTS (SELECT 1 FROM comments cm |
| 30 | WHERE cm.repo_id = pr.repo_id AND cm.number = pr.number AND cm.verdict = 'request_changes' |
| 31 | AND cm.author_id != pr.owner_id AND cm.author_id != 'usr_g1t_agent' AND cm.agent_id IS NULL |
| 32 | AND julianday(cm.created_at) < julianday(pr.merged_at))) AS reworked |
| 33 | FROM pr |
| 34 | ), |
| 35 | -- The pull requests that may be stalled at some cut: opened three days |
| 36 | -- before the latest, and not finished before the earliest (a closed one's |
| 37 | -- updated_at stands in for when it closed, which pulls do not keep). |
| 38 | -- Only their activity is read, each by its own index. |
| 39 | cand AS MATERIALIZED ( |
| 40 | SELECT x.* FROM x, cuts |
| 41 | WHERE x.c <= cuts.hi - 3 AND NOT COALESCE(x.m < cuts.lo, 0) |
| 42 | AND NOT (x.status = 'closed' AND COALESCE(x.u < cuts.lo, 0)) |
| 43 | ), |
| 44 | -- What each of them saw, as spans [t0, t1] (a moment where t0 = t1). |
| 45 | -- CROSS JOIN keeps each read starting from the few candidates. D1 takes |
| 46 | -- at most five terms in one compound SELECT, so the sources are read in |
| 47 | -- two halves of four and joined. |
| 48 | act_a(pull_id, t0, t1) AS MATERIALIZED ( |
| 49 | -- Comments of every kind: reviews, and the timeline's events (labels, |
| 50 | -- assignees, review requests, milestones, ready, draft, closed, |
| 51 | -- reopened, base changes, the merge queue, auto-merge, branches deleted |
| 52 | -- and restored). Pushes are no timeline event. |
| 53 | SELECT k.id, julianday(cm.created_at), julianday(cm.created_at) |
| 54 | FROM cand k CROSS JOIN comments cm ON cm.repo_id = k.repo_id AND cm.number = k.number |
| 55 | -- Its latest push, the only one pulls keep. |
| 56 | UNION ALL SELECT k.id, k.pushed, k.pushed FROM cand k WHERE k.pushed IS NOT NULL |
| 57 | -- Entries recorded on it. |
| 58 | UNION ALL SELECT k.id, julianday(e.at), julianday(e.at) FROM cand k CROSS JOIN session_entries e ON e.pull_id = k.id |
| 59 | -- Its acceptance checks, from queued to finished. |
| 60 | UNION ALL SELECT k.id, julianday(cr.created_at), julianday(COALESCE(cr.finished_at, cr.created_at)) |
| 61 | FROM cand k CROSS JOIN check_runs cr ON cr.pull_id = k.id |
| 62 | ), |
| 63 | act_b(pull_id, t0, t1) AS MATERIALIZED ( |
| 64 | -- Statuses and check runs on its head commit, the one commit pulls keep |
| 65 | -- (a status keeps only when it last changed). |
| 66 | SELECT k.id, julianday(st.updated_at), julianday(st.updated_at) |
| 67 | FROM cand k CROSS JOIN commit_statuses st ON st.repo_id = k.repo_id AND st.sha = k.head_commit |
| 68 | UNION ALL SELECT k.id, julianday(ck.created_at), julianday(ck.updated_at) |
| 69 | FROM cand k CROSS JOIN commit_check_runs ck ON ck.repo_id = k.repo_id AND ck.head_sha = k.head_commit |
| 70 | -- The sessions tied to it, and their subagents, from start to last |
| 71 | -- heartbeat: a session that ran then counts, busy or not. |
| 72 | UNION ALL SELECT k.id, julianday(s.started_at), julianday(s.last_seen_at) |
| 73 | FROM cand k CROSS JOIN tied t ON t.pull_id = k.id CROSS JOIN sessions s ON s.id = t.session_id |
| 74 | UNION ALL SELECT k.id, julianday(s.started_at), julianday(s.last_seen_at) |
| 75 | FROM cand k CROSS JOIN tied t ON t.pull_id = k.id CROSS JOIN sessions s ON s.parent_id = t.session_id |
| 76 | ), |
| 77 | act(pull_id, t0, t1) AS (SELECT * FROM act_a UNION ALL SELECT * FROM act_b), |
| 78 | -- Each week a pull request saw something in the three days before its cut. |
| 79 | busy AS MATERIALIZED ( |
| 80 | SELECT DISTINCT a.pull_id, w.start FROM act a JOIN w ON a.t0 < w.cut AND a.t1 >= w.cut - 3 |
| 81 | ), |
| 82 | pw AS ( |
| 83 | SELECT x.repo_id, w.start, |
| 84 | COUNT(CASE WHEN x.status = 'merged' AND x.m >= w.lo AND x.m < w.hi THEN 1 END) AS merged, |
| 85 | COUNT(CASE WHEN x.status = 'merged' AND x.m >= w.lo AND x.m < w.hi AND x.agent THEN 1 END) AS agent_merged, |
| 86 | COUNT(CASE WHEN x.status = 'merged' AND x.m >= w.lo AND x.m < w.hi AND x.reworked THEN 1 END) AS agent_reworked, |
| 87 | -- Opened in the week by an agent whose session started first. |
| 88 | COUNT(CASE WHEN x.agent AND x.draft_seconds IS NOT NULL AND x.c >= w.lo AND x.c < w.hi THEN 1 END) AS agent_drafted, |
| 89 | TOTAL(CASE WHEN x.agent AND x.c >= w.lo AND x.c < w.hi THEN x.draft_seconds END) AS agent_draft_seconds, |
| 90 | COUNT(CASE WHEN x.r >= w.lo AND x.r < w.hi THEN 1 END) AS reviewed, |
| 91 | TOTAL(CASE WHEN x.r >= w.lo AND x.r < w.hi THEN MAX(0, (x.r - x.c) * 86400) END) AS review_seconds, |
| 92 | 0 AS stalled, 0.0 AS session_cost_usd, 0 AS agent_commits |
| 93 | FROM x CROSS JOIN w |
| 94 | GROUP BY x.repo_id, w.start |
| 95 | ), |
| 96 | -- Open at the cut (not merged, and not closed, before it), opened at |
| 97 | -- least three days before, and nothing in the three days before it. |
| 98 | sx AS ( |
| 99 | SELECT k.repo_id, w.start, 0 AS merged, 0 AS agent_merged, 0 AS agent_reworked, 0 AS agent_drafted, |
| 100 | 0.0 AS agent_draft_seconds, 0 AS reviewed, 0.0 AS review_seconds, COUNT(*) AS stalled, 0.0 AS session_cost_usd, 0 AS agent_commits |
| 101 | FROM cand k CROSS JOIN w LEFT JOIN busy b ON b.pull_id = k.id AND b.start = w.start |
| 102 | WHERE b.pull_id IS NULL AND k.c <= w.cut - 3 |
| 103 | AND NOT COALESCE(k.m < w.cut, 0) |
| 104 | AND NOT (k.status = 'closed' AND COALESCE(k.u < w.cut, 0)) |
| 105 | GROUP BY k.repo_id, w.start |
| 106 | ), |
| 107 | -- What the sessions started in the week cost, subagents and all: each |
| 108 | -- reports only its own transcript's. |
| 109 | sw AS ( |
| 110 | SELECT s.repo_id, w.start, 0 AS merged, 0 AS agent_merged, 0 AS agent_reworked, 0 AS agent_drafted, |
| 111 | 0.0 AS agent_draft_seconds, 0 AS reviewed, 0.0 AS review_seconds, 0 AS stalled, |
| 112 | TOTAL(s.cost_usd) AS session_cost_usd, 0 AS agent_commits |
| 113 | FROM sessions s JOIN w ON s.started_at >= w.lo_at AND s.started_at < w.hi_at |
| 114 | WHERE s.repo_id IN (SELECT value FROM json_each(?1)) |
| 115 | AND s.started_at >= (SELECT MIN(lo_at) FROM w) AND s.started_at < (SELECT MAX(hi_at) FROM w) |
| 116 | GROUP BY s.repo_id, w.start |
| 117 | ), |
| 118 | -- The commits agent sessions pushed outside any pull request (to a |
| 119 | -- branch no pull request is from, the default branch most of all): a |
| 120 | -- session's own commits, by when it recorded them, each once. |
| 121 | cw AS ( |
| 122 | SELECT t.repo_id, w.start, 0 AS merged, 0 AS agent_merged, 0 AS agent_reworked, 0 AS agent_drafted, |
| 123 | 0.0 AS agent_draft_seconds, 0 AS reviewed, 0.0 AS review_seconds, 0 AS stalled, 0.0 AS session_cost_usd, |
| 124 | COUNT(DISTINCT t.value) AS agent_commits |
| 125 | FROM session_touches t JOIN w ON t.at >= w.lo_at AND t.at < w.hi_at |
| 126 | WHERE t.repo_id IN (SELECT value FROM json_each(?1)) AND t.kind = 'commit' |
| 127 | AND t.session_id NOT IN (SELECT session_id FROM tied) |
| 128 | GROUP BY t.repo_id, w.start |
| 129 | ) |
| 130 | SELECT repo_id, start, SUM(merged) AS merged, SUM(agent_merged) AS agent_merged, |
| 131 | SUM(agent_reworked) AS agent_reworked, SUM(agent_drafted) AS agent_drafted, |
| 132 | TOTAL(agent_draft_seconds) AS agent_draft_seconds, SUM(reviewed) AS reviewed, |
| 133 | TOTAL(review_seconds) AS review_seconds, SUM(stalled) AS stalled, TOTAL(session_cost_usd) AS session_cost_usd, |
| 134 | SUM(agent_commits) AS agent_commits |
| 135 | FROM (SELECT * FROM pw UNION ALL SELECT * FROM sx UNION ALL SELECT * FROM sw UNION ALL SELECT * FROM cw) |
| 136 | GROUP BY repo_id, start |
| 137 | HAVING SUM(merged) + SUM(agent_drafted) + SUM(reviewed) + SUM(stalled) + SUM(agent_commits) > 0 OR TOTAL(session_cost_usd) > 0 |
| 138 | ORDER BY start, repo_id |