# CDN Image Request Paths

> CDN image traffic. Every path.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The CDN team is investigating image serving patterns. Pull the edge location and request path for every log entry where the path includes 'image'.

## Worked solution and explanation

### Why this problem exists in real interviews

This is a basic string-matching filter question. It tests that you can use LIKE or a similar operator to filter text columns and select specific fields from a single table.

---

### Break down the requirements

#### Step 1: Filter by path containing 'image'

`WHERE req_path LIKE '%image%'` matches any path containing the substring 'image'.

#### Step 2: Select the required columns

Return `edge_loc` and `req_path` only.

---

### The solution

**Substring filter with LIKE**

```sql
SELECT edge_loc, req_path
FROM cdn_logs
WHERE req_path LIKE '%image%'
```

> **Cost Analysis**
>
> A full scan of 500M rows with a LIKE filter. The leading wildcard prevents index usage on `req_path`. A trigram index (pg_trgm) or a full-text index would accelerate this at scale.

> **Common Pitfall**
>
> Using `LIKE 'image%'` (no leading wildcard) would only match paths starting with 'image', missing paths like '/static/image/logo.png'.

---

## Common follow-up questions

- How would you make the filter case-insensitive? _(Tests ILIKE (PostgreSQL) or LOWER() wrapping for cross-database compatibility.)_
- What index type would help with leading-wildcard LIKE queries? _(Tests knowledge of trigram (GIN) indexes for substring searches.)_
- What if you needed to extract just the file extension from req_path? _(Tests string functions like SUBSTRING, SPLIT_PART, or REGEXP_SUBSTR.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/cdn_image_request_paths)
- [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.