Microsoft: Top Human Contributor per GitHub Repository in 2025

Medium
787.

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.

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

ColumnTypeValues
pr_idint
repo_namevarchar
author_loginvarchar
stateenumopen, closed, merged
merged_atdatetime

Primary key: pr_id.

One row per pull request. merged_at is set only when state is merged.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

PullRequest

pr_idrepo_nameauthor_loginstatemerged_at
1vscodepriya-devmerged2025-02-10 12:00:00
2vscodepriya-devmerged2025-05-03 09:30:00
3vscoderahul-codesmerged2025-03-14 16:00:00
4vscoderahul-codesmerged2025-11-20 11:45:00
5vscodedependabot[bot]merged2025-01-05 02:00:00
6vscodedependabot[bot]merged2025-02-05 02:00:00
7vscodedependabot[bot]merged2025-03-05 02:00:00
8vscodezara-mlmerged2024-12-31 23:59:59
9vscodezara-mlopenNULL
10terminalkabir-opsmerged2025-06-01 10:00:00
2 more rows

Output

repo_nameauthor_loginmerged_prs
terminalkabir-ops1
vscodepriya-dev2
vscoderahul-codes2
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
pr_idint
repo_namevarchar
author_loginvarchar
stateenum(open, closed, merged)
merged_atdatetime

Primary key: pr_id.

One row per pull request. merged_at is set only when state is merged.

Examples

Example 1

PullRequest

pr_idrepo_nameauthor_loginstatemerged_at
1vscodepriya-devmerged2025-02-10 12:00:00
2vscodepriya-devmerged2025-05-03 09:30:00
3vscoderahul-codesmerged2025-03-14 16:00:00
4vscoderahul-codesmerged2025-11-20 11:45:00
5vscodedependabot[bot]merged2025-01-05 02:00:00
6vscodedependabot[bot]merged2025-02-05 02:00:00
7vscodedependabot[bot]merged2025-03-05 02:00:00
8vscodezara-mlmerged2024-12-31 23:59:59
9vscodezara-mlopenNULL
10terminalkabir-opsmerged2025-06-01 10:00:00
11terminalarjun-kclosedNULL
12PowerToysmeera-tsopenNULL

Output

repo_nameauthor_loginmerged_prs
terminalkabir-ops1
vscodepriya-dev2
vscoderahul-codes2

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 = 1

Another 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 →