# Echo Chamber > Same status, same pattern. Coincidence? Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 · Asked in: Amazon ## Problem A reliability review is hunting for duplicated API responses: cases where two different calls returned the same HTTP method and the same status code. List those matching pairs by their two call IDs, highest status codes first, and keep only the top 20. ## Tables ### api_calls - call_id INTEGER - endpoint TEXT - method TEXT - status INTEGER - latency REAL - user_id INTEGER - call_time TEXT - err_msg TEXT Sample rows (10 of 670): | call_id | endpoint | method | status | latency | user_id | call_time | err_msg | | --- | --- | --- | --- | --- | --- | --- | --- | | 8079 | /api/v1/orders | POST | 200 | 4.2 | 100 | 2026-02-02 01:07:00 | NULL | | 8158 | /api/v2/products | PUT | 200 | 7.9 | 197 | 2026-03-03 02:14:00 | NULL | | 8237 | /api/v1/search | DELETE | 201 | 11.6 | 294 | 2026-04-04 03:21:00 | NULL | | 8316 | /api/v1/auth/login | PATCH | 204 | 15.3 | 391 | 2026-05-05 04:28:00 | NULL | | 8395 | /api/v1/auth/logout | get | 301 | 19 | NULL | 2026-06-06 05:35:00 | NULL | | 8474 | /api/v2/analytics | post | 400 | 22.7 | 585 | 2026-07-07 06:42:00 | Not found | | 8553 | /api/v1/payments | GET | 401 | 26.4 | 682 | 2026-08-08 07:49:00 | Unauthorized | | 8632 | /api/v1/users/ | POST | 403 | 30.1 | 779 | 2026-09-09 08:56:00 | Rate limit exceeded | | 8711 | /api/v2/products/ | PUT | 404 | 33.8 | 876 | 2026-01-10 09:03:00 | Internal server error | | 8790 | /api/v1/users | DELETE | 500 | 37.5 | NULL | 2026-02-11 10:10:00 | Bad gateway | ## Expected output First 5 of 20 rows: | call_id_1 | call_id_2 | method | status | | --- | --- | --- | --- | | 8948 | 16492 | get | 503 | | 9975 | 17025 | PATCH | 503 | | 8869 | 16451 | PATCH | 502 | | 9896 | 16984 | DELETE | 502 | | 8790 | 16410 | DELETE | 500 | ## 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 leaderboard 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. #### Step 3: Order and cap Sort by `status` descending so the highest codes surface first, then by `a.call_id` and `b.call_id` for a deterministic tie-break, and `LIMIT 20`. --- ### The solution **Self-join with an inequality to form unique pairs** ```sql 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, a.call_id, b.call_id LIMIT 20 ``` > **Interviewers watch for** > > The tell is the inequality operator. A candidate who writes `a.call_id <> b.call_id` still double-counts every pair; one who writes `a.call_id < b.call_id` has thought about unordered-pair uniqueness. Interviewers read that single character as a proxy for rigor. > **Common pitfall** > > Using `!=` or `<>` instead of `<`. It removes self-pairs but keeps both (x, y) and (y, x), so every pair is counted twice and your `LIMIT 20` returns only ten real pairs' worth of rows, each duplicated. > **In production** > > The table is 100M rows (26 GB) partitioned on `call_time`. An unconstrained self-join is quadratic, so in production you would back the match with a composite index on `(method, status, call_id)` and add a time filter to prune partitions before the join. The `LIMIT 20` lets the planner stop once the top of the ordering is filled. --- ## 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.)_ - Two pairs share the same status. Without the `call_id` tie-breakers, is the output order deterministic? _(Tests awareness that ORDER BY on a non-unique column is non-deterministic without a tiebreaker.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/echo_chamber) - [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.