CDN Image Request Paths

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

L3

Asked in

City and County of San Francisco

Problem

We're auditing image delivery across our CDN, and any request whose path includes 'image' counts as image traffic. Pull the edge location and request path of every such entry in cdn_logs, repeat requests included.

CDN image traffic. Every path.

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

This is an unanchored substring filter in a CDN costume. Every value in req_path starts with '/', so 'image' is never the whole path and never its first characters: it sits inside '/images/hero.jpg'. Anyone can type LIKE. What separates candidates is where the wildcards go. Write = 'image' or LIKE 'image%' and the query runs cleanly, returns zero rows, and the team concludes there is no image traffic at all.

Getting there

Step 1: Read the paths before writing the pattern

Look at req_path in the sample rows: '/images/hero.jpg' opens with a slash and continues past the word. The token is embedded, so the pattern needs a % on both sides: req_path LIKE '%image%'.

Step 2: Keep every log line

The ask is every entry, and one edge serves the same path many times. Leave out DISTINCT: the 20 matching rows include 'NRT' with '/images/hero.jpg' more than once, and each of those is a separate request.

Step 3: Project only edge_loc and req_path

Those are the two columns asked for. Nothing in the question asks for an order, so none is imposed.

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

Anchored pattern

LIKE 'image%' asks whether the path begins with 'image'. Every req_path begins with '/', so it returns 0 rows.

Unanchored pattern

LIKE '%image%' asks whether 'image' appears anywhere, and it matches all 20 '/images/hero.jpg' requests.

Common follow-up questions

• How would you make the filter case-insensitive? (Tests `ILIKE` or wrapping the column in `LOWER()`.)
• What index type would help with leading-wildcard `LIKE` queries? (Tests knowledge of trigram (GIN) indexes for substring search.)
• What if you needed just the file extension from `req_path`? (Tests string functions like `SPLIT_PART` or `REGEXP_SUBSTR`, and handling of suffixes like '?v=2'.)