Green Lights on the Order Line

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

Yelp

Problem

How many of our API calls to the '/api/v1/orders' endpoint came back with a successful 200 status?

How often did the orders API just... work?

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

This is a two-condition count dressed up as a reliability audit, and the whole question lives in the word 'successful'. Anyone can count rows in api_calls. What separates candidates is pinning both conditions to the same row: the exact endpoint string and the exact status 200. Drop the status test and you report all 132 order calls, 5xx failures included, as wins. Drop the endpoint test and you report 518 successes from every route in the log. Read 'successful' as any 2xx and you quietly land on 116.

Two conditions, one row

Step 1: Pin the status to exactly 200

status is an integer holding 200, 201, 204, 301 and a spread of 4xx and 5xx codes. The question names 200, so the test is status = 200. A range such as status BETWEEN 200 AND 299 adds the two 201s and one 204 that order calls returned.

Step 2: Match the endpoint string exactly

The target is the literal '/api/v1/orders'. The log has routes that differ by one character, such as '/api/v1/users' and '/api/v1/users/'. One order row is also stored as '/api/v1/orders ' with a trailing space, but it is a 502, so it can never be a success. Equality keeps the comparison on the one route asked about.

Step 3: Join the two with AND, then count rows

Both conditions must hold on the same row, so they sit in one WHERE joined by AND. COUNT(*) counts the rows that survive. Neither filter column is ever NULL, so a column count would give the same number, but COUNT(*) says what you mean: rows.

SELECT COUNT(*) AS success_count
FROM api_calls
WHERE status = 200 AND endpoint = '/api/v1/orders'

One condition

WHERE endpoint = '/api/v1/orders' returns 132: every order call, including the 503 from a retry storm and the 4xx failures. WHERE status = 200 returns 518: every success on every route.

Both conditions

WHERE status = 200 AND endpoint = '/api/v1/orders' returns 113, the only number that answers the question asked.

Common follow-up questions

• If `status` were stored as TEXT ('200') rather than INTEGER, what could go wrong with `status = 200`? (Tests type awareness: depending on the engine, the comparison may cast silently, match nothing, or fail.)
• Would `COUNT(*)` and `COUNT(call_id)` differ here, and when would they diverge? (Tests whether you know `COUNT(column)` skips NULLs while `COUNT(*)` counts rows.)
• How would you also report the average `latency` of those successful calls in the same query? (Tests extending the aggregate; `latency` has NULLs, and `AVG` skips them.)
• How would you break the count out by `method`, given the log stores both 'get' and 'GET'? (Tests normalizing a dirty grouping key before counting.)