# The Ones Nobody Calls > The POST paths the traffic forgot. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 ## Problem The API deprecation team is hunting for endpoints that barely see any POST traffic, the natural candidates for sunsetting. The method field is logged inconsistently, sometimes lowercase, so treat every casing of POST as the same request. Show the two lowest positions by POST call volume, quietest first, with each endpoint, its call count, and its position. ## Worked solution and explanation ### What this problem is really testing This is a bottom-N-with-ties problem wearing a deprecation costume, and the data hides a second trap: the `method` column is logged in mixed case, so the same endpoint shows up as both `POST` and `post`. Filter on `method = 'POST'` and you quietly drop every lowercase row, undercounting endpoints and reshuffling the entire bottom of the leaderboard. The dense-rank piece is the puzzle everyone sees. The casing is what separates people who inspect their data from people who assume it is clean. > **Trick to solving** > > Two independent decisions crack this: normalize casing with `UPPER(method) = 'POST'` so no POST traffic escapes the filter, and use `DENSE_RANK()` instead of `ROW_NUMBER()` or `LIMIT` so tied endpoints share a position without gaps. Miss either and the output is wrong in a way that still looks plausible. --- ### Building it step by step #### Step 1: Filter to POST, case-insensitively Filter to POST traffic, but normalize the case first: WHERE UPPER(method) = 'POST'. The seed logs the same method as POST and post, and a case-sensitive equality keeps only one of them. #### Step 2: Count calls per endpoint Group by endpoint and take COUNT(*) as the call volume. This is the number the deprecation team ranks on. #### Step 3: Position by ascending volume Apply DENSE_RANK() OVER (ORDER BY COUNT(*) ASC). Ascending order puts the quietest endpoints at position 1, and DENSE_RANK lets equal counts share the same position with no skipped numbers afterward. #### Step 4: Keep the two lowest positions Keep rnk <= 2 in a wrapping query, then order by call_count ascending with endpoint as a stable tiebreaker. The rank filter has to live outside the window computation because you cannot reference a window alias in the same WHERE. --- ### The solution **Bottom endpoints by POST volume** ```sql SELECT endpoint, call_count, rnk FROM ( SELECT endpoint, COUNT(*) AS call_count, DENSE_RANK() OVER (ORDER BY COUNT(*) ASC) AS rnk FROM api_calls WHERE UPPER(method) = 'POST' GROUP BY endpoint ) ranked WHERE rnk <= 2 ORDER BY call_count ASC, endpoint ASC ``` **Case-sensitive filter (wrong)** WHERE method = 'POST' keeps only the rows logged in exact uppercase. Every endpoint that was ever logged as post loses those calls, so counts come out low and the bottom positions shift to the wrong endpoints. **Case-normalized filter (correct)** WHERE UPPER(method) = 'POST' folds POST, post, and any other casing into one bucket. Counts reflect all POST traffic, and the leaderboard is stable regardless of how the logger cased the verb. > **Cost analysis** > > The base table is 80M rows (20 GB), partitioned on call_time. The window function runs after the GROUP BY, so it sorts roughly 70 endpoint groups, not 80M rows. Wrapping UPPER() around method blocks any plain index on that column, but with only five distinct methods a scan-and-aggregate is the expected plan anyway. > **Interviewers watch for** > > Strong candidates ask whether method is consistently cased before writing the filter, and they justify DENSE_RANK over ROW_NUMBER out loud. Both moves signal someone who has been burned by dirty logs before. > **Common pitfall** > > Two silent failures live here. A case-sensitive method filter drops lowercase rows and undercounts. ROW_NUMBER or LIMIT breaks ties arbitrarily and returns two rows even when three endpoints share the second-lowest volume. Both produce clean-looking output that is simply wrong. --- ## Common follow-up questions - You normalized method casing. What else in this table would you sanity-check before trusting the counts, and how would you spot leading or trailing whitespace on endpoint values? _(Tests whether the candidate can reason about dirty categorical data beyond a single column.)_ - You used DENSE_RANK. What changes in the output if you switch to RANK, and when would that difference actually matter for a bottom-N query? _(Tests understanding of tie-handling semantics between the ranking functions.)_ - The team only cares about the last 30 days of traffic. How do you fold call_time into this query, and how does the partition key help? _(Tests whether the candidate can adapt the query to a moving window instead of all-time volume.)_ - Could you write this without the wrapping subquery? What forces the rnk <= 2 filter to sit outside the SELECT that computes it? _(Tests readability trade-offs around window functions and rank filters.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_ones_nobody_calls) - [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.