Servers Returning to Origin

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

L4

Asked in

Amadeus

Problem

In our infra_nodes table a node that has not sent a CPU reading yet is treated as fully loaded, heavier than every node that has reported, and among nodes at the same load the lower node_id is the lighter one. Count the regions where the node under the lightest CPU load and the node under the heaviest have the same hostname.

Servers that migrated back home.

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

Under the infrastructure story, this is a 'what sits at each end' read, done one region at a time. A GROUP BY gives you each region's lowest and highest cpu_pct, but not the hostname sitting there, and MAX quietly skips the nodes that have not reported. The statement puts those nodes at the heavy end, and that detail is the whole answer. In 'us-west-2' the lightest node is a 'worker-01' at 3% and the heaviest is the unreported 'worker-01' with the highest node_id, so the region matches. Read the heaviest off MAX(cpu_pct) and you land on the 98% 'WEB-03' instead. Order each region with unreported loads last, read both ends with FIRST_VALUE and LAST_VALUE over the whole partition, and count the regions where they agree: 2. Get the null placement or the tie direction wrong and you get 1. Leave the frame at its default and every region matches: 6.

Building it up

Step 1: Order each region from lightest to heaviest

Partition by region and order by cpu_pct NULLS LAST. The lightest reporting node then opens the partition and the unreported nodes close it. This grader sorts NULLs first on an ascending order, which is the opposite of what the statement says, so the NULLS LAST is not optional. Add node_id as the second key: each region has several unreported nodes tied at the top with different hostnames, and because the lower id is the lighter one, the highest id is the heaviest.

Step 2: Pin the frame to the whole partition

Both windows need ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. With it, every node in a region carries that region's lightest_host and heaviest_host as constants, ready to compare. Without it, LAST_VALUE reads the running frame's last row, which is the current node.

Step 3: Collapse nodes back to regions

The subquery emits one row per node, so the filter on lightest_host = heaviest_host keeps every node of the two matching regions. COUNT(DISTINCT region) folds them back to 2. COUNT(*) would answer with a count of machines.

The solution

SELECT COUNT(DISTINCT region) AS same_host_regions
FROM (SELECT region,
             FIRST_VALUE(hostname) OVER (PARTITION BY region ORDER BY cpu_pct NULLS LAST, node_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS lightest_host,
             LAST_VALUE(hostname)  OVER (PARTITION BY region ORDER BY cpu_pct NULLS LAST, node_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS heaviest_host
      FROM infra_nodes) node_ends
WHERE lightest_host = heaviest_host

Common follow-up questions

• If unreported nodes should count as the lightest instead of the heaviest, what changes in the query, and how many regions still match? (Tests treating NULL placement as a deliberate business rule rather than an engine default.)
• How would the query change if 'load' meant the greater of `cpu_pct` and `mem_pct` rather than CPU alone? (Tests adapting the ordering key when the definition of load changes.)
• How would you report, per region, which host sits at each end instead of just the matching count? (Tests extending a scalar count into a per-region breakdown.)