The Warm Edges

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

Domain

SQL

Difficulty

medium

Seniority

L4

Asked in

Postmates by Uber

Problem

An edge location counts as caching API traffic only once it has served at least one request on an API path from cache, and those are the only locations worth comparing. From cdn_logs, the CDN team wants the total bytes each of those locations served on every request whose path includes 'api', per edge location and path, smallest total first.

Some edges keep the cache warm. Measure what they carry.

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 tests

This is a semi-join dressed up as a CDN report. One condition decides which edges are in, and a different one decides which rows get added up. The qualifier is an edge-level fact: has this location ever served an API request from cache? The total is a row-level sum over every API request there, hits and misses alike. Anyone can write the SUM. What separates candidates is three things: keeping cache_hit = 1 out of the rows being summed, scoping that test to API paths, and collapsing the qualifier to one row per edge before joining. Miss any one and the totals are wrong: hits only, an extra edge, or doubled bytes.

Break down the requirements

Step 1: Build the qualifying edge set

SELECT DISTINCT edge_loc FROM cdn_logs WHERE cache_hit = 1 AND req_path LIKE '%api%'. The path test belongs here too. Every edge in the data has a cache hit on some static file, so a hit on any path qualifies everyone. LHR serves API traffic but its hits are all on assets, and it must drop out. A missing cache_hit never equals 1, so those rows never qualify an edge.

Step 2: Filter the main scan to API paths

Put WHERE cl.req_path LIKE '%api%' on the outer scan, with no cache_hit condition. The misses at a qualifying edge are part of what it served, so they belong in the total.

Step 3: Join on the deduplicated set

Join the derived table on edge_loc. The DISTINCT is load-bearing: each qualifying edge has two API hit rows in the data, so joining the raw hit rows matches every API request twice and doubles every total.

Step 4: Total and sort smallest first

GROUP BY cl.edge_loc, cl.req_path with SUM(cl.bytes), which skips a missing byte count, then ORDER BY total_bytes ascending. IAD and SIN tie on the data, and either may come first.

The solution

SELECT
  cl.edge_loc,
  cl.req_path,
  SUM(cl.bytes) AS total_bytes
FROM cdn_logs cl
JOIN (
  SELECT DISTINCT edge_loc
  FROM cdn_logs
  WHERE cache_hit = 1
    AND req_path LIKE '%api%'
) api_cached
  ON cl.edge_loc = api_cached.edge_loc
WHERE cl.req_path LIKE '%api%'
GROUP BY cl.edge_loc, cl.req_path
ORDER BY total_bytes;

Flat filter (wrong)

One WHERE cache_hit = 1 AND req_path LIKE '%api%'. It is shorter and needs no join, but it sums only the hits: IAD reports 64376 bytes instead of 235712.

Semi-join shape (right)

The cache_hit test only builds api_cached. The sum still sees every API row at those edges, misses included.

SELECT edge_loc, req_path, SUM(bytes) AS total_bytes
FROM cdn_logs
WHERE cache_hit = 1
  AND req_path LIKE '%api%'
GROUP BY edge_loc, req_path
ORDER BY total_bytes;

Common follow-up questions

Common follow-up questions

• Rewrite the qualifier with EXISTS instead of a join. Does the dedup step still matter? (Probes whether you know a semi-join never multiplies the outer rows.)
• Switch the metric to average bytes. Would leaving out the `DISTINCT` still change the answer? (Tests whether you see why a uniform fan-out cancels in an average but not in a sum.)
• Add a 7-day window. Where does the `served_at` predicate go, and does the qualifier get the same window? (Partition pruning, and scoping the qualifier and the metric to the same period.)
• What changes if `cache_hit` is a boolean instead of an integer? (Surfaces missing values and `IS TRUE` versus `= 1`.)