Unique Hostnames per Region

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

L5

Asked in

City and County of San Francisco

Problem

Our capacity report counts 'WEB-03' and 'web-01' as two different kinds of machine, so we need to see where infra_nodes records a role under more than one spelling. Take a host's role as the part of its hostname before the first hyphen with capitalization ignored, and for each region and role report the hostname_count of unique hostnames plus the spellings: the prefix as written when every node there agrees, or both spellings uppercase-first as 'WEB / web' when they don't.

How many distinct machines live in each region?

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 is

This is a key-normalization problem dressed up as an inventory audit. Everyone can cut a hostname down to its role. What separates candidates is that the clean key and the dirty spelling both have to survive: you group on the folded role, but you compare the raw spellings behind it, node against node, inside the same region and role. Fold too early and 'WEB' and 'web' look identical, so there is no clash to report. Fold too late and they land in different groups, each one looking perfectly consistent.

Building it

Step 1: Derive both keys once in node_roles

INSTR(hostname, '-') finds the first hyphen and SUBSTR keeps what precedes it, so 'gpu-node-01' becomes 'gpu' and 'db-primary' becomes 'db'. Wrapping that cut in LOWER gives the role; leaving it bare gives the spelling. Computing them once means the rest of the query names them instead of repeating the expression.

Step 2: Pair each node with its neighbours in spelling_clashes

Join node_roles to itself on the same region and the same role, and keep only pairs where a.spelled_role <> b.spelled_role. Any surviving row proves that one region holds the role under two spellings. Scoping the join by role is what stops 'db' from being flagged just because 'WEB' sits in the same region.

Step 3: Label each region and role

Group node_roles by region and role. COUNT(DISTINCT hostname) counts the named hosts, not the rows. A correlated EXISTS against spelling_clashes decides the label: a clashing role shows MIN(spelled_role) || ' / ' || MAX(spelled_role), which puts the uppercase spelling first because capitals sort before lowercase. A role every node agrees on shows its one spelling as written, MIN(spelled_role), so a region that only ever writes 'WEB' reports 'WEB', not a tidied 'web' nobody typed.

WITH node_roles AS (
  SELECT
    region,
    hostname,
    SUBSTR(hostname, 1, INSTR(hostname, '-') - 1) AS spelled_role,
    LOWER(SUBSTR(hostname, 1, INSTR(hostname, '-') - 1)) AS role
  FROM infra_nodes
),
spelling_clashes AS (
  SELECT a.region, a.role
  FROM node_roles a
  JOIN node_roles b
    ON b.region = a.region
   AND b.role = a.role
  WHERE a.spelled_role <> b.spelled_role
)
SELECT
  n.region,
  n.role,
  COUNT(DISTINCT n.hostname) AS hostname_count,
  CASE
    WHEN EXISTS (
      SELECT 1
      FROM spelling_clashes c
      WHERE c.region = n.region
        AND c.role = n.role
    )
    THEN MIN(n.spelled_role) || ' / ' || MAX(n.spelled_role)
    ELSE MIN(n.spelled_role)
  END AS spellings
FROM node_roles n
GROUP BY n.region, n.role

Grouped on the raw prefix

Grouping on spelled_role puts 'WEB' and 'web' in separate rows. Each row holds one spelling, the self-join finds nothing, and the report says the inventory is clean.

Grouped on the folded role

Grouping on role while comparing spelled_role keeps 'WEB' and 'web' in one row and lets the pair surface as 'WEB / web'.

Common follow-up questions

• What happens to a `hostname` with no hyphen at all? (`INSTR` returns 0, so the cut yields an empty string. A fallback to the whole name keeps those hosts countable.)
• A role shows up under three spellings. Does the label still tell the whole story? (`MIN` and `MAX` only show the two ends; listing every spelling needs a per-group string aggregation.)
• Stray whitespace starts appearing in hostnames. Where does the fix go? (Apply `TRIM` before the cut, so ' web-01' and 'web-01' produce the same role and spelling.)