Cheapest CDN Route

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

L5

Asked in

Meta

Problem

Our SFO edge cache keeps evicting objects, and we suspect it was sized below the heaviest payload it actually delivers. From cdn_logs, find the largest payload in bytes among the status-200 responses that edge served.

The cheapest path across regions.

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 is really asking

This is a scoped scalar extreme wearing a capacity-planning costume. The skill: narrow cdn_logs to one cohort (the SFO edge, successful responses only) and take MAX(bytes) over the survivors. The trap is that a bare MAX(bytes) returns the global heaviest object, a SIN response at 133800, and dropping the status filter lets a SFO 404 at 129789 win. Get either filter wrong and you report the wrong object's size instead of SFO's real 108397.

Break down the requirements

Step 1: Filter the cohort

Two predicates on cdn_logs: LOWER(edge_loc) = 'sfo' matches the SFO edge and defends against casing variants in free-text geo codes, and status = 200 keeps only clean deliveries so an error response's byte count cannot leak in.

Step 2: Aggregate to one number

MAX(bytes) over the filtered set. No GROUP BY, no ORDER BY, no LIMIT. MAX ignores the null bytes rows (the 304 and 206 responses) for free, and the prompt asks for a single value.

The solution

SELECT MAX(bytes) AS largest_payload
FROM cdn_logs
WHERE LOWER(edge_loc) = 'sfo'
  AND status = 200

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• Return the heaviest successful payload for every edge location. (Adds `GROUP BY LOWER(edge_loc)` and lifts the SFO filter; the scalar becomes a per-edge leaderboard.)
• How would partition pruning help, and what predicate would unlock it? (Tests whether you reach for a `served_at >= '2026-08-13'` filter and explain that without it every partition reads.)
• What happens to the null `bytes` rows logged with no body? (`MAX` skips NULLs, so the answer is still defined, but a senior candidate names the assumption and may add `WHERE bytes IS NOT NULL` for clarity.)