Top 5 Slowest DNS Lookups

A medium SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

medium

Seniority

L3

Problem

List the domain and record type pairs with the five longest worst-case lookups in dns_lookups, slowest first, for the network team's latency review. If pairs tie for fifth, keep every one of them.

Five DNS lookups that took too long.

Tables

dns_lookups

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

Expected output

Worked solution and explanation

This is a top five of groups dressed up as a top five of lookups. Anyone can sort by latency. The skill being probed is whether you collapse each domain and rec_type pair to its single worst lookup before you cut the list. Skip that and dns_lookups hands you the same pair twice. Every lookup in this log is recorded twice at the same latency, so the raw top five covers three pairs, and two of the real offenders never make the report.

What the raw top five returns

SELECT domain, rec_type, latency
FROM dns_lookups
ORDER BY latency DESC
LIMIT 5;

Building the answer

Step 1: Collapse each pair to its worst lookup

Group dns_lookups by domain and rec_type and take MAX(latency) as max_latency. The question is about a pair's worst case, so one row per pair is the grain everything after this works on. MAX skips NULLs, so a pair keeps its slowest timed lookup even when some of its lookups never recorded one.

Step 2: Number the pairs by worst case, ties sharing a place

DENSE_RANK() OVER (ORDER BY MAX(latency) DESC) runs after the grouping, so it sees thirty pairs, not two hundred lookups. Pairs with the same maximum get the same number, and the next value takes the next number. That is exactly what keeping every pair tied for fifth asks for.

Step 3: Cut at five, then sort slowest first

A window result cannot be filtered in the WHERE of the query that computes it, so the pair_worst CTE holds the numbered pairs and the outer query keeps latency_rank <= 5. Then ORDER BY max_latency DESC delivers slowest first. That is the only order promised, so no tiebreaker is owed.

WITH pair_worst AS (
  SELECT domain,
         rec_type,
         MAX(latency) AS max_latency,
         DENSE_RANK() OVER (ORDER BY MAX(latency) DESC) AS latency_rank
  FROM dns_lookups
  GROUP BY domain, rec_type
)
SELECT domain, rec_type, max_latency
FROM pair_worst
WHERE latency_rank <= 5
ORDER BY max_latency DESC;

A fixed five rows

ORDER BY max_latency DESC LIMIT 5 after the grouping returns exactly five rows. It matches today only because no two pairs share a maximum. The day two pairs tie for fifth, one is dropped, and the engine decides which.

Five worst-case values

DENSE_RANK() with latency_rank <= 5 keeps every pair holding one of the five longest worst cases. A tie for fifth lengthens the list instead of silently truncating it.

Common follow-up questions

• How would you return the five slowest record types for each domain instead? (Moves the cut inside each `domain` with `PARTITION BY domain`.)
• What changes if the team wants the 95th percentile latency per pair instead of the worst lookup? (Swaps `MAX` for a percentile aggregate and asks how ties behave on a smoother metric.)
• How would you show whether a pair's worst case is getting worse month over month? (Adds a month bucket from `looked_at` and compares each month with the one before.)