Low-Byte CDN Responses

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

Salesforce

Problem

Each row of cdn_logs is one response served from an edge location, and the ones under 5000 bytes are the likely truncated or error payloads. List those responses, smallest first.

Tiny responses from the edge.

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 is

Strip off the CDN story and this is a filter, a projection and a sort. The interviewer is grading whether your output matches the request row for row. Anyone writes WHERE bytes < 5000. What separates candidates is reading 'smallest first' as ORDER BY bytes ASC instead of the DESC that every top-N question has trained into their fingers, and keeping the 5000 boundary exclusive. Flip the sort and the 1437-byte responses the investigation is hunting end up at the bottom of the list.

Step 1: Keep only responses under 5000 bytes

WHERE bytes < 5000 is strict on purpose: a 5000-byte response is not small enough to flag. The same predicate also handles the rows where bytes is NULL. A comparison against NULL is never true, so the 304s and other unsized responses drop out without a separate IS NOT NULL guard. That is the right outcome, because a size nobody recorded is not a small payload.

Step 2: Name the columns instead of SELECT *

Every field is wanted, so list all seven. The result is the same today, but an explicit list keeps the output stable when someone adds a column to cdn_logs, and it shows the reviewer you know exactly what you are returning.

Step 3: Sort smallest first, and stop there

ORDER BY bytes ASC puts the 1437-byte responses on top. On this data each size appears twice, once for the original and once for a later copy. The request says nothing about which of a tied pair leads, so either order is correct. Do not invent a tiebreaker the question never asked for.

SELECT log_id, edge_loc, req_path, status, bytes, cache_hit, served_at
FROM cdn_logs
WHERE bytes < 5000
ORDER BY bytes ASC

Largest first

ORDER BY bytes DESC returns the same six rows with the 4111-byte responses on top, so whoever scans the list reads the least suspicious payloads first.

Smallest first

ORDER BY bytes ASC leads with the 1437-byte responses, the ones most likely to be truncated, which is the reason anyone ran the query.

Common follow-up questions

• How would you limit this to the last 24 hours without scanning every partition? (Tests partition pruning on `served_at`.)
• The team now wants a count of small responses per `edge_loc` and `status`. What changes? (Moves from a row listing to a grouped summary.)
• Should the responses with no `bytes` value be reported separately, and how would you surface them? (Tests treating a missing size as its own signal instead of silently dropping it.)