| 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 | julianday(json_extract(value, '$.from')) AS lo, |
| 13 | julianday(json_extract(value, '$.to')) AS hi, |
| 14 | julianday(json_extract(value, '$.cut')) AS cut |
| 15 | FROM json_each(?2) |
| 16 | ), |
| 17 | x AS MATERIALIZED ( |
| 18 | SELECT pr.repo_id, pr.status, pr.agent, pr.draft_seconds, |
| 19 | julianday(pr.created_at) AS c, julianday(pr.merged_at) AS m, julianday(pr.updated_at) AS u, |
| 20 | julianday((SELECT MIN(cm.created_at) FROM comments cm |
| 21 | WHERE cm.repo_id = pr.repo_id AND cm.number = pr.number |
| 22 | AND cm.verdict IN ('approve', 'request_changes') |
| 23 | AND cm.author_id != pr.owner_id AND cm.author_id != 'usr_g1t_agent' AND cm.agent_id IS NULL)) AS r, |
| 24 | -- An agent's merged pull request a person asked for changes on before it merged. |
| 25 | (pr.agent AND pr.status = 'merged' AND EXISTS (SELECT 1 FROM comments cm |
| 26 | WHERE cm.repo_id = pr.repo_id AND cm.number = pr.number AND cm.verdict = 'request_changes' |
| 27 | AND cm.author_id != pr.owner_id AND cm.author_id != 'usr_g1t_agent' AND cm.agent_id IS NULL |
| 28 | AND julianday(cm.created_at) < julianday(pr.merged_at))) AS reworked |
| 29 | FROM pr |
| 30 | ) |
| 31 | SELECT x.repo_id, w.start, |
| 32 | COUNT(CASE WHEN x.status = 'merged' AND x.m >= w.lo AND x.m < w.hi THEN 1 END) AS merged, |
| 33 | 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, |
| 34 | 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, |
| 35 | -- Opened in the week by an agent whose session started first. |
| 36 | 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, |
| 37 | TOTAL(CASE WHEN x.agent AND x.c >= w.lo AND x.c < w.hi THEN x.draft_seconds END) AS agent_draft_seconds, |
| 38 | COUNT(CASE WHEN x.r >= w.lo AND x.r < w.hi THEN 1 END) AS reviewed, |
| 39 | TOTAL(CASE WHEN x.r >= w.lo AND x.r < w.hi THEN MAX(0, (x.r - x.c) * 86400) END) AS review_seconds, |
| 40 | -- Open over seven days when the week ended, and not finished by then: |
| 41 | -- not merged, and not closed (a closed one's updated_at stands in for |
| 42 | -- when it closed, which pulls do not keep). |
| 43 | COUNT(CASE WHEN x.c < w.cut - 7 |
| 44 | AND NOT COALESCE(x.m < w.cut, 0) |
| 45 | AND NOT (x.status = 'closed' AND COALESCE(x.u < w.cut, 0)) THEN 1 END) AS stalled |
| 46 | FROM x CROSS JOIN w |
| 47 | GROUP BY x.repo_id, w.start |
| 48 | HAVING merged + agent_drafted + reviewed + stalled > 0 |
| 49 | ORDER BY w.start, x.repo_id |