Running Node Pairs

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

L4

Asked in

Robinhood

Problem

In infra_nodes, a live node's status is stored as both 'running' and 'Running', and both mean the node is up. For failover coverage we need each pair of hostnames that both have a running node in the same region, listed once with the alphabetically-earlier hostname first. Sort by region, then by that first hostname, and within each first hostname list its partners from last to first.

Two servers, same region, both alive.

Tables

infra_nodes

Columns: node_id INTEGER, hostname TEXT, region TEXT, node_type TEXT, cpu_pct REAL, mem_pct REAL, status TEXT

Expected output

Worked solution and explanation

What this really asks

This is a self-join in disguise: 'which hosts run side by side' means joining infra_nodes to itself on region and reading off the hostname pairs. Three moves separate a clean answer from a mess. Normalize the casing before you filter with LOWER(status); use an INEQUALITY in the join with a.hostname < b.hostname so a host never pairs with itself and no pairing shows up in both directions; and keep DISTINCT, because hostnames recur across nodes in a region, so the join emits the same pairing once per matching pair of running nodes. Drop the LOWER and you silently lose every 'Running' node; drop the inequality and each pairing doubles alongside a spurious same-host row; drop DISTINCT and one pairing repeats several times over.

Building it

Step 1: Normalize the status before you filter

The live state lives in status as mixed casing ('running' and 'Running'), so an exact = 'running' match quietly keeps only the lowercase rows. Wrap both sides in LOWER(...) and compare to 'running' so the filter sees every live node.

Step 2: Self-join on region with an inequality

Join infra_nodes to itself on a.region = b.region so both rows sit in the same region, then add a.hostname < b.hostname to the join. That inequality is doing two jobs: it removes the host-matched-with-itself row and it keeps a single direction of each pairing, so ('cache-01','web-01') appears but ('web-01','cache-01') does not.

Step 3: Collapse the node-level fan-out with DISTINCT

Hostnames are not unique within a region, so the same pairing comes back once for every matching pair of running nodes. SELECT DISTINCT collapses those node-level matches down to one row per hostname pairing. Here it changes the answer, not just its tidiness: without it the row count balloons far past the real pairings.

Step 4: Order region, then first host, then partner descending

Order by region, then host_1 alphabetically, then host_2 DESC so each first host's partners read from last to first. Sort on the projected aliases host_1 and host_2, not on a.hostname / b.hostname: both underlying columns are literally named hostname, so a partner key written as b.hostname resolves back onto the first host and its direction stops mattering. The descending tail is what the ordering commits to beyond the region and first-host grouping, so it has to land on host_2.

The solution

SELECT DISTINCT a.region, a.hostname AS host_1, b.hostname AS host_2
FROM infra_nodes a
INNER JOIN infra_nodes b ON a.region = b.region AND a.hostname < b.hostname
WHERE LOWER(a.status) = 'running' AND LOWER(b.status) = 'running'
ORDER BY a.region, host_1, host_2 DESC

Common follow-up questions

• How would you also return, for each pairing, how many running nodes back each host in that region? (Tests whether the candidate can layer a grouped count over the deduplicated pairing, moving from DISTINCT to aggregation.)
• What changes if `region` could be `NULL` for some nodes? (Tests handling of a nullable join key and whether NULL regions should pair at all.)
• How would you extend this to three hosts all running in the same region? (Tests generalizing the two-way inequality self-join to a three-way chained one.)