Microsoft: Top Human Contributor per GitHub Repository in 2025
Microsoft's open-source programme office wants to thank the leading human contributor of each of its GitHub repositories for 2025.
- Difficulty: Medium
- Topics: Window Functions, Aggregation, Strings
- Dialect: MySQL
- Problem: #787
Problem statement
Microsoft's open-source programme office wants to thank the leading human contributor of each of its GitHub repositories for 2025.
Count each author's pull requests merged in 2025 per repository, ignoring bot accounts (any login ending in [bot], such as dependabot[bot]). For every repository, return the author or authors with the most merged PRs — all of them when tied — as repo_name, author_login and merged_prs. Repositories with no qualifying merged PR are not listed. Return the rows in any order.
A practice scenario set at Microsoft: the tables and data are invented, and this is not a question from, or endorsed by, Microsoft.
Tables
Table: PullRequest
| Column | Type |
|---|---|
| pr_id | int |
| repo_name | varchar |
| author_login | varchar |
| state | enum(open, closed, merged) |
| merged_at | datetime |
Primary key: pr_id.
One row per pull request. merged_at is set only when state is merged.
Examples
Example 1
PullRequest
| pr_id | repo_name | author_login | state | merged_at |
|---|---|---|---|---|
| 1 | vscode | priya-dev | merged | 2025-02-10 12:00:00 |
| 2 | vscode | priya-dev | merged | 2025-05-03 09:30:00 |
| 3 | vscode | rahul-codes | merged | 2025-03-14 16:00:00 |
| 4 | vscode | rahul-codes | merged | 2025-11-20 11:45:00 |
| 5 | vscode | dependabot[bot] | merged | 2025-01-05 02:00:00 |
| 6 | vscode | dependabot[bot] | merged | 2025-02-05 02:00:00 |
| 7 | vscode | dependabot[bot] | merged | 2025-03-05 02:00:00 |
| 8 | vscode | zara-ml | merged | 2024-12-31 23:59:59 |
| 9 | vscode | zara-ml | open | NULL |
| 10 | terminal | kabir-ops | merged | 2025-06-01 10:00:00 |
| 11 | terminal | arjun-k | closed | NULL |
| 12 | PowerToys | meera-ts | open | NULL |
Output
| repo_name | author_login | merged_prs |
|---|---|---|
| terminal | kabir-ops | 1 |
| vscode | priya-dev | 2 |
| vscode | rahul-codes | 2 |
How to solve Microsoft: Top Human Contributor per GitHub Repository in 2025
Two steps. First, count: filter to PRs that were merged inside 2025 (a half-open range, so 23:59:59 on New Year's Eve 2024 and midnight on 1 January 2026 are both out), drop logins ending in [bot], and group by repository and author. Bots must be removed before ranking — otherwise dependabot[bot] would top a busy repository and hide the humans entirely.
Second, pick the leader per repository. RANK() OVER (PARTITION BY repo_name ORDER BY merged_prs DESC) numbers authors within each repository with ties sharing a rank, so WHERE pos = 1 returns every tied leader. ROW_NUMBER() would pick one of them arbitrarily, and the answer would depend on storage order.
The alternative avoids window functions: compute the per-repository maximum in a derived table and join back on equality, which also keeps ties. It uses RIGHT(login, 5) <> '[bot]' for the bot test. Both aggregate once.
Reference solution (MySQL)
WITH counts AS (
SELECT repo_name, author_login, COUNT(*) AS merged_prs
FROM PullRequest
WHERE state = 'merged'
AND merged_at >= '2025-01-01' AND merged_at < '2026-01-01'
AND author_login NOT LIKE '%[bot]'
GROUP BY repo_name, author_login
), ranked AS (
SELECT repo_name, author_login, merged_prs,
RANK() OVER (PARTITION BY repo_name ORDER BY merged_prs DESC) AS pos
FROM counts
)
SELECT repo_name, author_login, merged_prs FROM ranked WHERE pos = 1Another way
WITH counts AS (
SELECT repo_name, author_login, COUNT(*) AS merged_prs
FROM PullRequest
WHERE state = 'merged' AND YEAR(merged_at) = 2025 AND RIGHT(author_login, 5) <> '[bot]'
GROUP BY repo_name, author_login
)
SELECT c.repo_name, c.author_login, c.merged_prs
FROM counts c
JOIN (SELECT repo_name, MAX(merged_prs) AS best FROM counts GROUP BY repo_name) m
ON m.repo_name = c.repo_name AND m.best = c.merged_prs← Microsoft: Bing Query Abandonment Rate by Market · Microsoft: Customers Billed for Both Azure and Microsoft 365 →