# The Warm Edges

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

Canonical URL: <https://datadriven.io/problems/the_warm_edges>

Domain: SQL · Difficulty: medium · Seniority: L4

## 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 of those edge locations and path combination, from the lowest average to the highest.

## 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)`, `ORDER BY avg_bytes` (ascending, the prompt says lowest first). No `LIMIT` requested.

---

### The solution

**THE WARM EDGES**

```sql
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;
```

> **Cost Analysis**
>
> Two passes over 2B rows. Pass one filters on `cache_hit = 1`, projects only `edge_loc`, distincts to a few hundred rows. Pass two scans the same data filtered to API paths and hash-joins against the tiny set. If `served_at` is the only partition key, no pruning helps here; ask whether a date window is implied.

> **Interviewers Watch For**
>
> Ask aloud: 'Is the qualifying-edge check across all time, or scoped to the same window as the metric?' The prompt is silent. Picking 'all time' is fine if you name the choice. Also ask whether `req_path LIKE '%api%'` should be anchored to '/api/' to avoid matching something like '/rapidapi-test/'.

> **Common Pitfall**
>
> Putting `cache_hit = 1` in the outer `WHERE` instead of the subquery. That filters the averaging set to cache hits only, so `avg_bytes` becomes 'avg bytes on cache hits', not 'avg bytes on all traffic at edges that ever cached'. In the sample, SIN caches but has no API path (so it drops out), while NRT and FRA have API paths but never cached (so they are excluded): the two filters do different jobs, and swapping them changes the answer.

---

### 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.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/the_warm_edges)
- [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.