| 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. Agent commits |
| 6 | -- are those agent sessions pushed outside any pull request (weeks.sql). |
| 7 | , |
| 8 | ev(repo_id, day, opened, merged, agent_merged, reworked, reviews, sessions, agent_commits) AS ( |
| 9 | SELECT repo_id, substr(created_at, 1, 10), 1, 0, 0, 0, 0, 0, 0 FROM pr |
| 10 | WHERE created_at >= ?2 AND created_at < ?3 |
| 11 | UNION ALL SELECT repo_id, substr(merged_at, 1, 10), 0, 1, agent, 0, 0, 0, 0 FROM pr |
| 12 | WHERE status = 'merged' AND merged_at >= ?2 AND merged_at < ?3 |
| 13 | UNION ALL SELECT c.repo_id, substr(c.created_at, 1, 10), 0, 0, 0, c.verdict IS 'request_changes', 1, 0, 0 |
| 14 | FROM comments c JOIN pr ON pr.repo_id = c.repo_id AND pr.number = c.number |
| 15 | WHERE c.repo_id IN (SELECT value FROM json_each(?1)) AND c.created_at >= ?2 AND c.created_at < ?3 |
| 16 | AND c.author_id != pr.owner_id AND c.agent_id IS NULL |
| 17 | AND c.author_id NOT IN ('usr_g1t_agent', 'g1t', 'g1t_policy', 'svc_runner', 'g1t_runner') |
| 18 | UNION ALL SELECT repo_id, substr(started_at, 1, 10), 0, 0, 0, 0, 0, 1, 0 FROM sessions |
| 19 | WHERE repo_id IN (SELECT value FROM json_each(?1)) AND started_at >= ?2 AND started_at < ?3 |
| 20 | UNION ALL SELECT t.repo_id, substr(t.at, 1, 10), 0, 0, 0, 0, 0, 0, COUNT(DISTINCT t.value) FROM session_touches t |
| 21 | WHERE t.repo_id IN (SELECT value FROM json_each(?1)) AND t.kind = 'commit' AND t.at >= ?2 AND t.at < ?3 |
| 22 | AND t.session_id NOT IN (SELECT session_id FROM tied) |
| 23 | GROUP BY t.repo_id, substr(t.at, 1, 10) |
| 24 | ) |
| 25 | SELECT repo_id, day, SUM(opened) AS opened, SUM(merged) AS merged, SUM(agent_merged) AS agent_merged, |
| 26 | SUM(reworked) AS reworked, SUM(reviews) AS reviews, SUM(sessions) AS sessions, |
| 27 | SUM(agent_commits) AS agent_commits |
| 28 | FROM ev GROUP BY repo_id, day ORDER BY day, repo_id |