The Ones Nobody Calls

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

L3

Asked in

Microsoft

Problem

Our POST counts per endpoint in api_calls keep coming out low, because the method field is sometimes logged in lowercase and a 'post' is still a POST. To shortlist endpoints for sunsetting, list the ones in the three lowest positions by POST call volume, with each endpoint's call count and its position.

The POST paths the traffic forgot.

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 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 under both 'POST' and 'post'. Filter on method = 'POST' and you quietly drop every lowercase row, undercounting endpoints and reshuffling the whole bottom of the standings. 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.

Building it step by step

Step 1: Filter to POST, case-insensitively

Filter to POST traffic, but fold the case first: WHERE UPPER(method) = 'POST'. The seed logs the same verb 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 shortlist is built 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 equal counts share a position with no skipped number after them.

Step 4: Keep the three lowest positions

Keep rnk <= 3 in a wrapping query. The filter has to live outside the SELECT that computes the window, because a window alias cannot be referenced in that same WHERE.

The solution

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 <= 3

Case-sensitive filter (wrong)

WHERE method = 'POST' keeps only rows logged in exact uppercase. Every endpoint 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 every casing into one bucket. Counts reflect all POST traffic, and the standings hold regardless of how the logger cased the verb.

Common follow-up questions

• You normalized `method`. What else would you sanity-check before trusting the counts, and how would you spot leading spaces or trailing slashes in `endpoint`? (Reasoning about dirty categorical data beyond a single column.)
• What changes in the output if `DENSE_RANK` becomes `RANK`, and when does that difference matter for a bottom-N query? (Tie-handling semantics of 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? (Adapting the query to a moving window instead of all-time volume.)
• Could you write this without the wrapping subquery? What forces the `rnk <= 3` filter to sit outside the `SELECT` that computes it? (Readability trade-offs around window functions and rank filters.)