CDN-Related DNS Lookups

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
Oracle

Problem

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

DNS lookups tied to the CDN.

Tables

dns_lookups

Columns: lookup_id INTEGER, domain TEXT, rec_type TEXT, ttl_secs INTEGER, status TEXT, latency REAL, looked_at TEXT

dns_lookups: 10 of 200 rows
lookup_iddomainrec_typettl_secsstatuslatencylooked_at
129cdn.example.comAAAA47NXDOMAIN0.472026-02-02 01:03:00
158auth.example.comCNAME64SERVFAIL0.842026-03-03 02:06:00
187search.example.comMX81TIMEOUT1.212026-04-04 03:09:00
216db.internal.netTXT98REFUSED1.582026-05-05 04:12:00
245cache.internal.netNS115NOERROR1.952026-06-06 05:15:00
274example.comA132NXDOMAIN2.322026-07-07 06:18:00
303www.example.comAAAA149SERVFAIL2.692026-08-08 07:21:00
332API.example.comCNAMENULLTIMEOUT3.062026-09-09 08:24:00
361Cdn.Example.ComMX183REFUSED3.432026-01-10 09:27:00
390api.example.comTXT200NOERRORNULL2026-02-11 10:30:00

Expected output

First 5 of 40 rows
lookup_iddomainrec_typettl_secsstatuslatencylooked_at
129cdn.example.comAAAA47NXDOMAIN0.472026-02-02 01:03:00
361Cdn.Example.ComMX183REFUSED3.432026-01-10 09:27:00
419cdn.example.comNS217NXDOMAIN4.172026-03-12 11:33:00
651Cdn.Example.ComAAAA353REFUSED7.132026-02-20 19:57:00
709cdn.example.comMX387NXDOMAIN7.872026-04-22 21:03:00

Worked solution and explanation

This is a data-cleaning problem dressed up as a one-line filter. The domain column stores the same CDN host under two spellings, 'cdn.example.com' and 'Cdn.Example.Com', and the whole question is whether your match survives that. Anyone can write LIKE '%cdn%'; what separates candidates is normalizing the column before matching. Skip it and 20 of the 40 lookups vanish, and on this data every one of them is a REFUSED lookup (361, 651, 941 and on), exactly the failures the team is hunting.

Walking to the answer

Step 1: Look at the spellings before the filter

Scan the values in domain first. 'cdn.example.com' sits next to 'Cdn.Example.Com', and 'api.example.com' next to 'API.example.com'. Case is not a property you can trust on this column, so any match that depends on it is already wrong.

Step 2: Normalize the column, then match

Wrap the stored value: LOWER(domain) LIKE '%cdn%'. Lowercasing domain and comparing it against a lowercase pattern makes the test blind to however the host was typed in, so both spellings land in the result.

Step 3: Return the whole record

The team wants the lookups themselves, not a summary, so the projection is every column of dns_lookups. No order is asked for, so none is imposed.

Case-insensitive substring match
SELECT *
FROM dns_lookups
WHERE LOWER(domain) LIKE '%cdn%'
Raw match
domain LIKE '%cdn%' keeps only the 20 'cdn.example.com' rows, every one NXDOMAIN. The list looks complete and contains no refusals at all.
Normalized match
LOWER(domain) LIKE '%cdn%' keeps all 40, including the 20 'Cdn.Example.Com' rows whose status is 'REFUSED'.

Common follow-up questions