Clean Cache CDN Edges

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

Airbnb

Problem

The reliability team is auditing which assets keep failing to serve from the CDN. List the unique request paths whose responses came back with an error status of 400 or above.

Cached, clean, error-free edges.

Tables

cdn_logs

Columns: log_id INTEGER, edge_loc TEXT, req_path TEXT, status INTEGER, bytes INTEGER, cache_hit INTEGER, served_at TEXT

Expected output

Worked solution and explanation

What this really asks

Strip the CDN framing and this is a set question asked over a log: which paths appear at least once among the failing responses? The log does not hand you paths, it hands you requests, and each path here sits on 20 of them, so the 80 rows with a status of 400 or above name just four assets. Anyone writes the filter. What separates candidates is remembering that the answer is the set, not the rows, and leaving the paths exactly as logged: trim them and the padded '/js/app.min.js ' that actually failed turns into '/js/app.min.js', which never failed once.

Walk it through

Step 1: Keep only the failing rows

WHERE status >= 400 keeps every 4xx and 5xx response and drops the 2xx and 3xx traffic. The cutoff is inclusive because 400 Bad Request is already a failure. On this log the lowest failing code happens to be 403, but a production log is full of 400s, and status > 400 would quietly lose them.

Step 2: Collapse requests into paths

Each surviving path still appears 20 times, once per failed request. SELECT DISTINCT req_path turns those 80 rows into the four paths the audit is about. Skip it and the reliability team scrolls through the same asset over and over.

Step 3: Leave the paths as logged

req_path holds '/js/app.min.js' and '/js/app.min.js ' (trailing space) as separate values. Only the padded one ever failed. The question is which logged paths failed, so no cleanup belongs in this query.

The solution

SELECT DISTINCT req_path
FROM cdn_logs
WHERE status >= 400

Filter only

SELECT req_path FROM cdn_logs WHERE status >= 400 returns 80 rows: each of the four failing paths 20 times.

Filter, then collapse

Adding DISTINCT returns four rows, one per failing path, and that is the audit list.

Common follow-up questions

• How would you list the failing paths by how many times they failed? (Swap `DISTINCT` for `GROUP BY req_path` with `COUNT(*)`, then sort that count descending.)
• Which failures are the CDN's fault versus the client's? (Split 4xx from 5xx with two ranges on `status`; server errors are usually the on-call priority.)
• How would you scope this to just the last hour of traffic? (Add a window predicate on `served_at`, the partition key, so the engine prunes every other day.)