Self-Approved Pull Requests

An engineering team requires every pull request to be approved by someone other than its author. The review log shows that some developers have approved their own pull requests anyway.

Return one column, developer_id, listing each developer who approved at least one of their own pull requests. Each developer must appear only once, no matter how many times they did it. Sort the rows by developer_id in ascending order. If nobody approved their own work, return no rows.

Tables

code_reviews — One row per approval given on a pull request. A pull request can collect several approvals.

Example

code_reviews

Expected output

Return the rows in exactly the order described. Your query also runs against hidden datasets with tricky edge cases.

SQL Path · Select & Filter · problem 4 of 70 · SQLite

The idea

Every row is one approval, so a self-approval is simply a row where author_id = reviewer_id. WHERE can compare two columns of the same row just as easily as a column and a constant.

A developer can self-approve many times, and even approve the same pull request twice, so the filtered rows contain repeats. SELECT DISTINCT keeps one row per developer id. Aliasing the column as developer_id gives the requested name, and ORDER BY developer_id makes the order explicit. Without ORDER BY, SQL makes no promise about row order: the result often follows insertion order, which is why a missing sort can look right on small data and fail later.

The alternative uses GROUP BY author_id. With no aggregate functions it produces one row per distinct value, exactly like DISTINCT, and it is the natural starting point if you later want to count offences per developer.

Solution

SELECT DISTINCT author_id AS developer_id
FROM code_reviews
WHERE author_id = reviewer_id
ORDER BY developer_id;

Another way

SELECT author_id AS developer_id
FROM code_reviews
WHERE author_id = reviewer_id
GROUP BY author_id
ORDER BY author_id;

Why common answers fail

Forgets DISTINCT, so a developer who self-approved several times is listed several times.

SELECT author_id AS developer_id FROM code_reviews WHERE author_id = reviewer_id ORDER BY developer_id;

Leaves out ORDER BY; rows then come back in whatever order the table is scanned, which is not guaranteed to be ascending.

SELECT DISTINCT author_id AS developer_id FROM code_reviews WHERE author_id = reviewer_id;

Sorts in descending order, but the task asks for ascending.

SELECT DISTINCT author_id AS developer_id FROM code_reviews WHERE author_id = reviewer_id ORDER BY developer_id DESC;

What the interviewer is checking

Tests comparing two columns in one row, removing duplicates and remembering that row order is only guaranteed with ORDER BY.

Mistakes to avoid

• Forgetting DISTINCT and returning duplicates.
• Relying on the natural table order instead of ORDER BY.
• Returning reviewer_id and author_id as two columns instead of one.

Follow-ups to expect

• Show how many times each developer self-approved. (GROUP BY author_id with COUNT(*))
• Find pull requests whose only approvals came from their author. (GROUP BY pr_id HAVING SUM(author_id <> reviewer_id) = 0)
• List developers who never self-approved. (anti join or NOT IN against this result)

In real work

Compliance and audit checks look for segregation-of-duties violations, such as people approving their own expenses, deploys or code.

Practice next