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

The CDN team is comparing API traffic at the edge locations that are actually caching: those that have logged at least one cache hit on any path. For request paths that include 'api', find the average bytes served at each edge location and path combination, leanest 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

Strip the CDN costume and this is a set-membership filter feeding an aggregate: average bytes per (edge, path), but only over edges that belong to a separately-computed set (those that ever cached). The trap is where you put cache_hit = 1. Put it in the outer WHERE and you silently average only cache-hit rows, which is a different number. Keep it inside the qualifying subquery and you average all API traffic at edges that happen to cache. On 2B rows the interviewer also watches which membership shape you reach for: a JOIN (SELECT DISTINCT ...), an IN subquery, or EXISTS. All three are correct; they scan and materialize differently.

Break down the requirements

Step 1: Build the qualifying edge set

SELECT DISTINCT edge_loc FROM cdn_logs WHERE cache_hit = 1. This is a separate logical pass over cdn_logs. Distinct edge count is small (hundreds), so the result is tiny even though the scan is large.

Step 2: Filter main scan to API paths

WHERE cl.req_path LIKE '%api%'. Leading wildcard kills index use; you are scanning anyway because of the aggregation, so this is fine. State that out loud so the interviewer knows you noticed.

Step 3: Inner join, not IN, not EXISTS

JOIN (subquery) ON edge_loc = edge_loc. The qualifying set is small enough to broadcast or hash-build cheaply. IN (SELECT ...) and EXISTS work but force the planner to re-evaluate per row on some engines.

Step 4: Aggregate and sort low to high

GROUP BY cl.edge_loc, cl.req_path, AVG(cl.bytes), then ORDER BY avg_bytes, cl.edge_loc, cl.req_path (ascending, the prompt says leanest first). Two (edge, path) combinations can share an average, so the edge_loc then req_path tiebreakers make the order total: without them the tied rows come back in engine order and a correct answer can grade wrong. Each group has a unique (edge_loc, req_path), so those two keys fully determine the row order. No LIMIT requested.

The solution

SELECT
  cl.edge_loc,
  cl.req_path,
  AVG(cl.bytes) AS avg_bytes
FROM cdn_logs cl
JOIN (
  SELECT DISTINCT edge_loc
  FROM cdn_logs
  WHERE cache_hit = 1
) cached
  ON cl.edge_loc = cached.edge_loc
WHERE cl.req_path LIKE '%api%'
GROUP BY cl.edge_loc, cl.req_path
ORDER BY avg_bytes, cl.edge_loc;

COMMON FOLLOW-UP QUESTIONS

Common follow-up questions

• Rewrite this with EXISTS instead of a join. When does each shape win? (Probes correlated subquery semantics and how the optimizer treats semi-joins on partitioned tables.)
• Add a 7-day window. Where do you push the `served_at` predicate and why? (Tests partition-pruning awareness; the predicate has to land on the base scan, not after the join.)
• What changes if `cache_hit` is BOOLEAN instead of INT? (Surfaces three-valued logic and whether you write `cache_hit IS TRUE` vs `= 1`.)
• How would you detect edges where `avg_bytes` for API paths jumped 50% week-over-week? (Stretches the candidate into windowed comparisons, `LAG` over a weekly aggregate, and ratio thresholds.)