# CDN-Related DNS Lookups

> DNS lookups tied to the CDN.

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

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The network team is tracking down CDN-related DNS resolution issues. Pull all lookup records where the domain contains 'cdn' regardless of casing, ordered by domain.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests case-insensitive string matching and basic ordering. Despite its simplicity, it screens for whether you know ILIKE vs LIKE, or how to normalize case with LOWER().

---

### Break down the requirements

#### Step 1: Case-insensitive filter

Use `LOWER(domain) LIKE '%cdn%'` or `domain ILIKE '%cdn%'` to match 'cdn' regardless of casing.

#### Step 2: Order by domain

`ORDER BY domain` sorts results alphabetically.

---

### The solution

**Case-insensitive substring match**

```sql
SELECT *
FROM dns_lookups
WHERE LOWER(domain) LIKE '%cdn%'
ORDER BY domain
```

> **Cost Analysis**
>
> Full scan of 800M rows applying LOWER() to each domain value. A functional index on `LOWER(domain)` combined with a trigram index would dramatically improve performance. Without indexes, this is pure I/O-bound.

> **Common Pitfall**
>
> Using `LIKE '%cdn%'` without LOWER() or ILIKE would miss domains like 'CDN.example.com' or 'cdn-PROD.net'. Always handle case explicitly when the prompt says "regardless of casing."

---

## Common follow-up questions

- What is the difference between ILIKE and LOWER() + LIKE? _(ILIKE is PostgreSQL-specific; LOWER() + LIKE is portable. Tests cross-database awareness.)_
- How would you optimize this query for repeated execution? _(Tests knowledge of expression indexes: CREATE INDEX ON dns_lookups (LOWER(domain)).)_
- What if you only needed the first match per domain? _(Tests DISTINCT ON or GROUP BY with MIN/MAX for deduplication.)_

## Related

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