| 1 | -- ?1 the repositories' ids as a JSON array; ?2 and ?3 the bounds of the 90 |
| 2 | -- days ending with `until`. Each owner's merged pull requests in each |
| 3 | -- repository over its whole history, under the name on their latest. |
| 4 | -- Agents and g1t are left out by the caller (signals.rs `is_person`). |
| 5 | WITH m AS ( |
| 6 | SELECT repo_id, COALESCE(requested_by_id, author_id) AS owner_id, |
| 7 | CASE WHEN requested_by_id IS NULL THEN author_name ELSE requested_by_name END AS owner_name, merged_at |
| 8 | FROM pulls |
| 9 | WHERE repo_id IN (SELECT value FROM json_each(?1)) AND status = 'merged' AND merged_at IS NOT NULL |
| 10 | ) |
| 11 | SELECT repo_id, owner_id, |
| 12 | (SELECT n.owner_name FROM m n WHERE n.repo_id = m.repo_id AND n.owner_id = m.owner_id |
| 13 | ORDER BY n.merged_at DESC LIMIT 1) AS owner_name, |
| 14 | MIN(merged_at) AS first_merged_at, MAX(merged_at) AS last_merged_at, COUNT(*) AS merged, |
| 15 | COUNT(CASE WHEN merged_at >= ?2 AND merged_at < ?3 THEN 1 END) AS recent |
| 16 | FROM m GROUP BY repo_id, owner_id ORDER BY repo_id, first_merged_at |