# The Loudest Threads > The code reviews that started a debate. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## Problem A platform team is reviewing which code reviews sparked the most back-and-forth this cycle. Return the repo name and author for the three reviews that drew the most comments, most discussed first. ## 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. > **Trick to solving** > > Do not reach for MAX(), a correlated subquery, or a window function. When you want the few biggest rows and nothing else, ORDER BY the metric and LIMIT N is both the fastest plan and the one a reviewer reads in a second. Save the window function for when you need a per-group top-N. --- ### 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 **Top three via ORDER BY plus LIMIT** ```sql SELECT repo_name, author FROM code_reviews ORDER BY comments DESC, review_id LIMIT 3 ``` *comments DESC ranks the busiest reviews; review_id makes the third slot reproducible.* > **Why the plan stays cheap** > > On the 400K-row table this is a single sort feeding a LIMIT, so the planner can stop after emitting three rows. With an index on comments it degrades to a short index scan and never materializes the full ordering. The naive alternatives (a self-join against MAX, or ranking every row with a window function) both force work across the whole table before discarding it. **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 pitfall** > > A bare ORDER BY comments DESC LIMIT 3 is non-deterministic at the boundary: if the third and fourth reviews tie on comments, which one you keep is left to the engine. The tiebreaker column is what turns a flaky answer into a stable one. > **Interviewers watch for** > > The tell is the tiebreaker. Plenty of candidates write ORDER BY comments DESC LIMIT 3 and stop. Adding a second sort key, unprompted, signals someone who has been burned by non-reproducible top-N results in production. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_loudest_threads) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.