Largest CDN Response

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

Domain

SQL

Difficulty

hard

Seniority

L4

Asked in

ESPN

Problem

Every row in cdn_logs is one response an edge location served, sized in bytes. Return the full record of the largest response served in 2026, and if two responses served the same number of bytes, keep the one with the smaller log id.

One edge location served something massive.

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

Beneath the CDN framing this is a top-1 selection dressed with a dirty column. The bytes field looks numeric but is stored as text, and some rows carry a trailing 'KB' suffix. Type ORDER BY bytes DESC LIMIT 1 and you sort lexically, not by magnitude: a junk string like '99KB' outranks the genuine maximum '133800', so the edge_loc and req_path you report belong to the wrong response entirely. Two more traps sit on top: the largest response overall was served in a different year and must be scoped out, and the true in-year maximum is shared by more than one response, so a bare LIMIT returns whichever row the engine happens to surface.

Building the query

Step 1: Read from cdn_logs

Everything needed is already in cdn_logs, and the output is the full log record, so no join or subquery is required. Select the seven columns directly.

Step 2: Scope to the year

Restrict served_at to the requested year with a half-open range on the timestamp text: from January 1 of 2026 up to, but not including, January 1 of 2027. Skip this and a bigger response from another year wins.

Step 3: Normalize, then sort by size

Order by the NORMALIZED byte count descending: REPLACE the 'KB' suffix out and CAST to INTEGER so magnitude, not lexical text, drives the sort. The largest response floats to the top.

Step 4: Break the tie, then take one

The maximum is not unique within the year, so append log_id ascending as a tiebreaker and take LIMIT 1. The winning row is now a function of the data alone, identical on every run.

The solution

SELECT log_id, edge_loc, req_path, status, bytes, cache_hit, served_at
FROM cdn_logs
WHERE served_at >= '2026-01-01' AND served_at < '2027-01-01'
ORDER BY CAST(REPLACE(bytes, 'KB', '') AS INTEGER) DESC, log_id ASC
LIMIT 1

Common follow-up questions

• How would you return the largest response if `cdn_logs` were sharded across many databases? (Tests scatter-gather reasoning: each shard normalizes and returns its local top-1, then you merge and take the global max with the same tiebreak rule.)
• How would you extend this to the largest response per edge location in one query? (Tests window-function fluency versus `LIMIT`, and how ties are handled by `RANK` vs `ROW_NUMBER` once bytes is normalized.)
• How would you stop this KB-suffix problem from recurring on every query against this table? (Tests data-hygiene instinct: normalize on write with a typed or generated column so reads never re-parse text.)