Auth Endpoints

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

City and County of San Francisco

Problem

Our credential submissions are the POST requests to the auth routes in api_calls, and clients log the HTTP method in whatever case they send it, so 'post' and 'POST' are the same request. Pull those calls with their latency in the order they were logged, earliest first.

Not all endpoints are visible to everyone.

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 really is

This is a dirty-categorical filter dressed up as a security review. The route side is the easy half: endpoint LIKE '%auth%' catches both /api/v1/auth/login and /api/v1/auth/logout. The real test is method, which this log stores as 'POST' for most clients and 'post' for some. Anyone who writes method = 'POST' gets 11 rows, sees uppercase in the result and ships it. Six of the 17 credential submissions are gone, and nothing in the output tells you.

Getting there

Step 1: Profile method before filtering on it

Before trusting a literal, look at what the column holds. method has seven spellings for five verbs: 'get' and 'post' sit beside 'GET' and 'POST'. That one look decides the whole query.

Step 2: Fold the case, then compare

LOWER(method) = 'post' collapses both spellings into one value. You fold the column, not the literal: the literal is already in the case you chose.

Step 3: Match the route by substring

Auth traffic is split across login and logout, so equality against either path loses the other. endpoint LIKE '%auth%' takes both. On this table the paths are already lowercase, so the route side needs no folding.

Step 4: Sort by call_time

'Earliest first' means call_time. call_id is not assigned in time order here, so sorting by it scrambles the timeline the reviewer reads.

SELECT call_time, endpoint, method, latency
FROM api_calls
WHERE endpoint LIKE '%auth%'
  AND LOWER(method) = 'post'
ORDER BY call_time

SELECT call_time, endpoint, method, latency
FROM api_calls
WHERE endpoint LIKE '%auth%'
  AND method = 'POST'
ORDER BY call_time

Literal as written

method = 'POST' returns 11 rows and drops the 6 logged as 'post'. The output is still tidy, which is exactly why nobody catches it.

Case folded

LOWER(method) = 'post' returns all 17. The method column in the result still shows both spellings, because the folding happens only in the filter.

Common follow-up questions

• How would you confirm no other spelling of POST exists, say with stray whitespace? (Tests whether the candidate profiles a categorical rather than guessing its values.)
• The security team now wants only the failed submissions. Which column tells you that, and what is your cutoff? (Tests reading `status` as a range of codes rather than a single value.)
• If this ran daily over 50M rows partitioned on `call_time`, how would you keep it cheap? (Tests partition pruning and the cost of wrapping a column in a function.)