# Largest CDN Response > One edge location served something massive. Canonical URL: 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. ## Tables ### cdn_logs - log_id INTEGER - edge_loc TEXT - req_path TEXT - status INTEGER - bytes INTEGER - cache_hit INTEGER - served_at TEXT Sample rows (10 of 200): | log_id | edge_loc | req_path | status | bytes | cache_hit | served_at | | --- | --- | --- | --- | --- | --- | --- | | 537 | SFO | /js/app.min.js | 200 | 1437 | 0 | 2026-02-02 01:07:13 | | 574 | LHR | /css/main.css | 200 | 2774 | 0 | 2026-03-03 02:14:26 | | 611 | NRT | /images/hero.jpg | 206 | 4111 | 1 | 2026-04-04 03:21:39 | | 648 | SIN | /api/v1/data | 301 | 5448 | 0 | 2026-05-05 04:28:52 | | 685 | FRA | /fonts/roboto.woff2 | 304 | NULL | 0 | 2026-06-06 05:35:05 | | 722 | SYD | /video/intro.mp4 | 403 | 8122 | NULL | 2026-07-07 06:42:18 | | 759 | GRU | /favicon.ico | 404 | 9459 | NULL | 2026-08-08 07:49:31 | | 796 | IAD | /assets/logo.png?v=2 | 500 | 10796 | NULL | 2026-09-09 08:56:44 | | 833 | SFO | /js/app.min.js | 503 | 12133 | NULL | 2026-01-10 09:03:57 | | 870 | LHR | /assets/logo.png | 200 | 13470 | 0 | 2026-02-11 10:10:10 | ## Expected output First 1 of 1 rows: | log_id | edge_loc | req_path | status | bytes | cache_hit | served_at | | --- | --- | --- | --- | --- | --- | --- | | 4200 | SIN | /assets/logo.png | 200 | 133800 | 0 | 2026-02-17 04:40:40 | ## 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. > **Size is a number only after you make it one** > > Normalize before you order. Strip the 'KB' suffix with `REPLACE` and `CAST` to a number inside the `ORDER BY`, so the comparison is a real magnitude comparison. Then scope `served_at` to the year, and make the sort total by appending `log_id` so exactly one row can ever win. --- ### 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 **Deterministic top-1 by normalized byte count** ```sql 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 ``` > **Three ways to return the wrong row** > > `ORDER BY bytes DESC LIMIT 1` on the raw text column is the classic near-miss. It compares strings, so '99KB' sorts above '133800' and you return the wrong response; drop the year scope and you return a response from the wrong year; drop the `log_id` tiebreaker and you return a nondeterministic row when the maximum ties. Grading compares against one specific row, so each of these is a real failure, not a cosmetic one. > **Reading the data before the column name** > > Strong candidates read the data before they trust the column name. Saying out loud 'bytes is text with a KB suffix, so I have to strip and cast before I sort' and 'the max might not be unique, so I need a tiebreaker' unprompted is the tell that someone has been burned by dirty CDN logs in production. > **An expression sort cannot ride the index** > > The functional expression in the `ORDER BY` (`REPLACE` + `CAST`) means a plain B-tree index on `bytes` can't be walked directly; the engine must normalize every in-year row before sorting. In production you would store bytes as a real integer column (or a computed/generated column) and index that, turning the query into a single top-of-index read instead of a normalize-then-sort over the whole year's partition. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/largest_cdn_response) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.