DNS Backend Flow for a URL | Dataford Interview Questions

What happens in the backend DNS when you hit www.google.com in a browser?

Using the supplied DNS request and event tables, write a SQL query that summarizes each completed request for www.google.com. Include only requests with recorded resolution events.

Output

One row per qualifying request, with request_id, resolver_ip, lookup_started_at, resolution_path, final_answer, and total_latency_ms.
Order by lookup_started_at ascending, then request_id ascending.

Schema

dns_requests

Column	Type	Description
request_idPK	INT	Unique browser DNS request identifier
hostname	VARCHAR(255)	Hostname requested by the browser
client_ip	INET	Client address that initiated the request
resolver_ip	INET	Recursive resolver address
lookup_started_at	TIMESTAMPTZ	Time at which resolution started
status	VARCHAR(20)	Overall request status

dns_events

Column	Type	Description
event_idPK	INT	Unique DNS event identifier
request_id	INT	Related browser DNS request
stage_order	INT	Position of the event in the resolution path
server_role	VARCHAR(40)	DNS component involved in the event
answer	VARCHAR(255)	DNS answer returned at this stage
response_code	INT	DNS response code
latency_ms	INT	Latency contributed by the event

Your question is DNS Backend Flow for a URL. Start with the requirements and the two tables in the Question tab.

Run and submit as often as you like. When you're ready, talk me through your approach or go straight to the code.

Problem