# The Roads In > Every reader arrives from somewhere. Trace where the blog's traffic begins. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## Problem The content marketing team is auditing where blog traffic originates. From the page views whose URL contains '/blog', pull the unique referrer values. ## 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. --- ### 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 lets it catch nested paths, not just those that start with /blog. #### Step 2: Deduplicate referrers `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 **URL-filtered distinct referrers** ```sql SELECT DISTINCT referrer FROM page_views WHERE page_url LIKE '%/blog%' ``` > **Cost Analysis** > > Scan of 700M rows with a LIKE filter. The leading wildcard prevents any index on page_url from being used, so this is a full scan. The DISTINCT then reduces output to the small set of unique referrers, which is cheap relative to the scan. > **Interviewers Watch For** > > Whether the candidate uses LIKE '%/blog%' (substring) or LIKE '/blog%' (prefix). The prompt says the URL contains '/blog', so the leading wildcard is required; a prefix pattern is the fast way to look right and be wrong. > **Common Pitfall** > > Dropping the leading % matches only URLs that begin with /blog, missing paths like /content/blog/article. The query still runs and still returns rows, so the mistake ships as an undercounted referrer list rather than an error. --- ## Common follow-up questions - How would you also count views per referrer? _(Replace DISTINCT with GROUP BY referrer and add COUNT(*).)_ - 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 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_roads_in) - [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.