The Roads In

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

Airbnb

Problem

The content marketing team is auditing where blog traffic originates. From the page views whose URL contains '/blog', pull the unique referrer values.

Every reader arrives from somewhere. Trace where the blog's traffic begins.

Tables

page_views

Columns: view_id INTEGER, page_url TEXT, user_id INTEGER, referrer TEXT, dur_ms INTEGER, device TEXT, viewed_at TEXT

Expected output

Worked solution and explanation

What this problem really is

Strip the traffic-analysis costume and this is a filter-then-dedup problem: match the blog URLs, then collapse the referrer column to its unique values. Everyone writes the pattern match. The tell is the leading wildcard: anchor the pattern to the start of the string and you silently drop every real-world URL like /content/blog/article, so the referrer list comes back short with no error to warn you. The second trap is the NULL referrer. It is the largest slice of blog traffic, SELECT DISTINCT keeps it as one row, and a habitual WHERE referrer IS NOT NULL erases direct traffic from the audit.

Break down the requirements

Step 1: Filter to blog pages

WHERE page_url LIKE '%/blog%' keeps any URL where '/blog' appears anywhere in the path. The leading % is what catches nested paths, not just those that start with /blog.

Step 2: Deduplicate referrer

SELECT DISTINCT referrer collapses the surviving rows to one row per referrer value. NULL survives as a single distinct row, standing in for direct traffic.

The solution

SELECT DISTINCT referrer
FROM page_views
WHERE page_url LIKE '%/blog%'

Common follow-up questions

• How would you also count views per referrer? (Replace `DISTINCT` with `GROUP BY referrer` and add `COUNT(*)`. The `NULL` group still appears.)
• What if you only want external referrers and need to drop the direct-traffic nulls? (`NULL` appears as a distinct value here; tests whether the candidate knows to exclude it deliberately for external-only sources.)
• How would you make this fast on a 700M-row table? (Tests partial indexes, a normalized `referrer_domain` column, materialized views, or pre-filtering strategies.)