Character Position in Endpoint

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Square

Problem

The HTTP method in api_calls was logged inconsistently, sometimes uppercase and sometimes lowercase, and the API team's audit of POST traffic needs every one of those requests whichever way it was spelled. For each, return its call ID and endpoint alongside the position where the letter 's' first appears in the endpoint, in call ID order, lowest first.

URL patterns, character by character.

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

Beneath the audit story, this is a case-normalized filter feeding a 1-based string search. Anyone can call a position function. What separates people is the method column: it was logged inconsistently, so 'POST' and 'post' both live in the data. Match the value as stored and you silently keep only one spelling, reporting on barely half the POST requests, with every position computed for the wrong slice.

Working through it

Step 1: Fold the method's case

Nothing normalized the method on write, so the same verb shows up as 'POST' and 'post'. LOWER(method) = 'post' collapses both onto one value, so every POST request qualifies instead of just one casing.

Step 2: Locate the first 's'

INSTR(endpoint, 's') returns the 1-based position of the first 's'. The first character is position 1, not 0, and an endpoint with no 's' at all comes back as 0 rather than disappearing from the result.

Step 3: Report one row per request

Keep call_id in the SELECT so every request is its own row. Many POST calls hit the same endpoint, so the output is per request, not one row per path.

Step 4: Sort by call_id

call_id is unique, so ORDER BY call_id gives a total order: no two rows can trade places, and the grader has exactly one correct sequence to check.

The solution

SELECT call_id, endpoint, INSTR(endpoint, 's') AS s_position
FROM api_calls
WHERE LOWER(method) = 'post'
ORDER BY call_id

Matches the raw value

WHERE method = 'post' matches only the lowercase rows and misses every 'POST', so the audit silently runs on a fraction of the traffic.

Folds the case first

WHERE LOWER(method) = 'post' collapses both spellings onto one value, so every POST request appears exactly once.

Common follow-up questions

• How would you find the position of the second 's', not just the first? (Tests `SUBSTR` and `INSTR` composition for repeated matches.)
• How would you collapse this to one row per `endpoint` with a count of POST calls? (Tests grouping and aggregation over the row-level result.)
• What changes if some endpoints were also logged with uppercase letters? (Tests whether the candidate also normalizes the searched column, not just the filtered one.)