| 1 | -- ?2 and ?3 the first day's start and the day after the last's, as |
| 2 | -- `YYYY-MM-DDT00:00:00`. One row per repository and UTC day that had |
| 3 | -- anything in it. Reviews are comments of any kind on a pull request by a |
| 4 | -- person other than its owner (not g1t's agent, a workspace agent or g1t |
| 5 | -- itself); reworked, the ones of them that ask for changes. |
| 6 | , |
| 7 | ev(repo_id, day, opened, merged, agent_merged, reworked, reviews, sessions) AS ( |
| 8 | SELECT repo_id, substr(created_at, 1, 10), 1, 0, 0, 0, 0, 0 FROM pr |
| 9 | WHERE created_at >= ?2 AND created_at < ?3 |
| 10 | UNION ALL SELECT repo_id, substr(merged_at, 1, 10), 0, 1, agent, 0, 0, 0 FROM pr |
| 11 | WHERE status = 'merged' AND merged_at >= ?2 AND merged_at < ?3 |
| 12 | UNION ALL SELECT c.repo_id, substr(c.created_at, 1, 10), 0, 0, 0, c.verdict IS 'request_changes', 1, 0 |
| 13 | FROM comments c JOIN pr ON pr.repo_id = c.repo_id AND pr.number = c.number |
| 14 | WHERE c.repo_id IN (SELECT value FROM json_each(?1)) AND c.created_at >= ?2 AND c.created_at < ?3 |
| 15 | AND c.author_id != pr.owner_id AND c.agent_id IS NULL |
| 16 | AND c.author_id NOT IN ('usr_g1t_agent', 'g1t', 'g1t_policy', 'svc_runner', 'g1t_runner') |
| 17 | UNION ALL SELECT repo_id, substr(started_at, 1, 10), 0, 0, 0, 0, 0, 1 FROM sessions |
| 18 | WHERE repo_id IN (SELECT value FROM json_each(?1)) AND started_at >= ?2 AND started_at < ?3 |
| 19 | ) |
| 20 | SELECT repo_id, day, SUM(opened) AS opened, SUM(merged) AS merged, SUM(agent_merged) AS agent_merged, |
| 21 | SUM(reworked) AS reworked, SUM(reviews) AS reviews, SUM(sessions) AS sessions |
| 22 | FROM ev GROUP BY repo_id, day ORDER BY day, repo_id |