Echo Chamber

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

Amazon

Problem

List the top 20 pairs of different calls in api_calls that returned the same HTTP method and the same status code, each pair identified by its two call IDs. A reliability reviewer hunting duplicated responses reads them highest status first, and within a status by the lower call ID and then the higher one.

Same status, same pattern. Coincidence?

Tables

api_calls

Columns: call_id INTEGER, endpoint TEXT, method TEXT, status INTEGER, latency REAL, user_id INTEGER, call_time TEXT, err_msg TEXT

Expected output

Worked solution and explanation

What this is really testing

This is a self-join that forms unique unordered pairs, wearing a duplicate-detection costume. The skill being probed: can you take one table and match each row against other rows that share a key (method and status here) without pairing a row with itself and without listing the same pair twice? Anyone can write FROM api_calls a JOIN api_calls b ON a.method = b.method AND a.status = b.status. The trick is the a.call_id < b.call_id predicate. Drop it and every call pairs with itself and every real pair shows up in both orderings, so your top 20 becomes duplicated noise.

The one predicate that matters

Equality on method and status gives you the candidate pairs, but it also gives you (x, x) and both (x, y) and (y, x). The single character that fixes both problems is the strict inequality on call_id. Because < is strict, a call can never match itself; because it is directional, exactly one of the two mirror orderings survives. That is what makes each real pair land on the list once.

Equality only

ON a.method = b.method AND a.status = b.status. Ten matching calls in a group produce 100 rows: 10 self-pairs plus every real pair counted twice. LIMIT 20 fills up with garbage before you reach a distinct pair.

Add `a.call_id < b.call_id`

Same join plus a.call_id < b.call_id. The same group of ten produces 45 rows, one per genuine unordered pair. Self-pairs are gone and no mirror duplicates remain, so the top 20 is 20 real pairs.

Building it

Step 1: Join the table to itself on the shared key

Alias api_calls twice, as a and b, and match on a.method = b.method AND a.status = b.status. Every combination of two calls that agree on both columns becomes a candidate row.

Step 2: Kill self-pairs and mirror duplicates

Add a.call_id < b.call_id. This does two jobs at once: it excludes a.call_id = b.call_id so no call pairs with itself, and it keeps only one of (x, y) / (y, x) so each unordered pair appears exactly once, smaller ID first.

Step 3: Order totally, then cap

Sort by status descending, then by call_id_1 and call_id_2, and LIMIT 20. The call ID keys are not cosmetic: status 500 alone holds 17 pairs on this data and only six slots remain after the 503s and 502s, so without them any six of those pairs would satisfy the order and the cut would be arbitrary.

The solution

SELECT a.call_id AS call_id_1, b.call_id AS call_id_2, a.method, a.status
FROM api_calls a
JOIN api_calls b
  ON a.method = b.method
 AND a.status = b.status
 AND a.call_id < b.call_id
ORDER BY a.status DESC, call_id_1, call_id_2
LIMIT 20

Common follow-up questions

• How would you extend this to only pair calls that happened within five minutes of each other? (Tests whether the candidate can add a range predicate to the join without breaking the pair-uniqueness logic.)
• The `<` gives you one row per unordered pair. What changes if the interviewer wants both directions, an ordered pair? (Tests understanding that the strict inequality is what collapses ordered pairs into unordered ones.)
• At 100M rows a naive self-join is quadratic. What index or pre-filter keeps it from doing a full cross scan? (Tests whether the candidate can keep a self-join tractable at scale with indexing or pre-filtering.)
• Drop `call_id_1` and `call_id_2` from the sort. Which 20 rows come back now, and would two runs agree? (Tests awareness that a LIMIT cutting through tied rows returns an arbitrary subset unless the order is total.)