# Distinct Blog Referrers

> Where did the traffic really come from? No repeats.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The content marketing team wants to know where blog traffic originates. Pull the unique referrer values from page views whose URL contains '/blog'.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests string filtering with LIKE combined with DISTINCT. It verifies that you can filter by URL pattern and deduplicate results.

---

### Break down the requirements

#### Step 1: Filter to blog pages

`WHERE page_url LIKE '%/blog%'` matches URLs containing /blog.

#### Step 2: Deduplicate referrers

`SELECT DISTINCT referrer` returns unique referrer values.

---

### 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 index usage. The DISTINCT reduces output to a small set of unique referrers.

> **Interviewers Watch For**
>
> Whether the candidate uses LIKE '%/blog%' (substring) vs LIKE '/blog%' (prefix). The prompt says "contains," so the leading wildcard is necessary.

> **Common Pitfall**
>
> Forgetting the leading % in the LIKE pattern would only match URLs starting with /blog, missing paths like /content/blog/article.

---

## Common follow-up questions

- How would you also count views per referrer? _(Replace DISTINCT with GROUP BY referrer and add COUNT(*).)_
- What if referrer is NULL for direct traffic? _(NULL would appear as a distinct value. Tests whether to exclude it.)_
- How would you optimize this for a 700M-row table? _(Tests partial indexes, materialized views, or pre-filtering strategies.)_

## Related

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