| 1 | -- ?2 the workspace, ?3 the start of `since` as `YYYY-MM-DDT00:00:00`. |
| 2 | -- Pull requests opened or merged since then, newest first, one more than |
| 3 | -- are returned so the caller knows when there were more. A pull |
| 4 | -- request's milestone is its work item's, else its own when that is one |
| 5 | -- of the workspace's (until the move to work items finishes, a pull |
| 6 | -- request may still hold its repository's old number). |
| 7 | -- |
| 8 | -- A pull request's session cost is its share of what its sessions cost: |
| 9 | -- the sessions tied to it as `tied` ties them, and the subagents of those |
| 10 | -- that are not tied to anything themselves (a subagent counts with its |
| 11 | -- root session's ties). Each session's own cost (NULL as 0) is split |
| 12 | -- evenly among the pull requests it counts with. |
| 13 | , |
| 14 | -- Every session of the repositories, with its root session. |
| 15 | anc(id, root) AS ( |
| 16 | SELECT id, id FROM sessions WHERE repo_id IN (SELECT value FROM json_each(?1)) AND parent_id IS NULL |
| 17 | UNION SELECT s.id, anc.root FROM anc JOIN sessions s ON s.parent_id = anc.id |
| 18 | WHERE s.repo_id IN (SELECT value FROM json_each(?1)) |
| 19 | ), |
| 20 | -- The pull requests each session counts with. |
| 21 | counts_with(session_id, pull_id) AS MATERIALIZED ( |
| 22 | SELECT session_id, pull_id FROM tied |
| 23 | UNION SELECT anc.id, t.pull_id FROM anc JOIN tied t ON t.session_id = anc.root |
| 24 | WHERE anc.id != anc.root AND NOT EXISTS (SELECT 1 FROM tied o WHERE o.session_id = anc.id) |
| 25 | ), |
| 26 | share(pull_id, usd) AS ( |
| 27 | SELECT c.pull_id, TOTAL(COALESCE(s.cost_usd, 0) / n.pulls) |
| 28 | FROM counts_with c JOIN sessions s ON s.id = c.session_id |
| 29 | JOIN (SELECT session_id, COUNT(*) AS pulls FROM counts_with GROUP BY session_id) n ON n.session_id = c.session_id |
| 30 | GROUP BY c.pull_id |
| 31 | ) |
| 32 | SELECT pr.repo_id, pr.number, pr.title, pr.status, pr.agent, pr.owner_id, pr.owner_name, |
| 33 | pr.created_at, pr.merged_at, i.number AS item, |
| 34 | COALESCE(i.milestone, |
| 35 | (SELECT m.number FROM milestones m WHERE m.workspace = ?2 AND m.number = pr.milestone)) AS milestone, |
| 36 | COALESCE(share.usd, 0.0) AS session_cost_usd |
| 37 | FROM pr LEFT JOIN work_items i ON i.id = pr.item_id AND i.workspace = ?2 |
| 38 | LEFT JOIN share ON share.pull_id = pr.id |
| 39 | WHERE pr.created_at >= ?3 OR (pr.status = 'merged' AND pr.merged_at >= ?3) |
| 40 | ORDER BY pr.created_at DESC, pr.repo_id, pr.number DESC |
| 41 | LIMIT 3001 |