The Loudest Threads

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

Yelp

Problem

Return the repo name and author of the three reviews in code_reviews that drew the most comments, most discussed first. Comment counts repeat often across the table, so when reviews tie on comments the one with the lower review_id wins the spot.

The code reviews that started a debate.

Tables

code_reviews

Columns: review_id INTEGER, repo_name TEXT, author TEXT, reviewer TEXT, status TEXT, comments INTEGER, opened_at TEXT, merged TEXT

Expected output

Worked solution and explanation

What this really is

Strip off the code-review costume and this is a top-N-by-one-metric query: order every row by a single column and keep the first three. The comments total is already sitting on each row, so there is nothing to group or count. The whole problem lives in two places most candidates rush past: keeping the SELECT list to exactly the two asked-for columns, and making the third slot deterministic. Sort on comments alone and the boundary between third and fourth place is decided by whatever order the engine happened to scan, so a rerun can silently swap the last row.

Build it

Step 1: Read from code_reviews

You only need repo_name and author from code_reviews. The comments column is already a per-review total, so no aggregation is involved: this is a straight row selection.

Step 2: Order with a tiebreaker

Sort by comments descending to float the busiest reviews to the top, then add review_id as a tiebreaker so the ordering is fully determined even when two reviews share a comment count at the cutoff.

Step 3: Limit and project

Keep the first three rows with LIMIT 3 and project only the two requested columns. Returning reviewer or comments as an extra column is the easiest way to fail strict schema grading.

The solution

SELECT repo_name, author
FROM code_reviews
ORDER BY comments DESC, review_id
LIMIT 3

Window function (overkill here)

RANK() OVER (ORDER BY comments DESC) computes a rank for all 400K rows, then you filter to rank <= 3. Correct, but it ranks everything before throwing almost all of it away, and it hands you ties to reason about explicitly.

ORDER BY plus LIMIT

ORDER BY comments DESC, review_id LIMIT 3 lets the engine short-circuit after three rows and reads cleanly. The right tool when you want a flat top-N, not a per-group one.

Common follow-up questions

• Now return the single most-commented review for each repo instead of the top three overall. How does your query change? (Pushes from a flat top-N to a per-group top-N, which is where the window function finally earns its place.)
• If several reviews are tied at the third-place comment count, which one does your query keep, and how do you make that choice explicit? (Tests whether the candidate can articulate the non-determinism the tiebreaker fixes.)
• What index would let this run without sorting the full table, and does the tiebreaker column need to be part of it? (Tests indexing intuition for order-then-limit queries.)