Regional Footprint
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
- L3
- Asked in
- Forbes
Problem
Our capacity plan for 'us-east-1' doesn't match what we believe is deployed there. How many infrastructure nodes sit in that region, whatever state they're in?
Every node costs money. Know what you own.
Tables
infra_nodes
Columns: node_id INTEGER, hostname TEXT, region TEXT, node_type TEXT, cpu_pct REAL, mem_pct REAL, status TEXT
| node_id | hostname | region | node_type | cpu_pct | mem_pct | status |
|---|---|---|---|---|---|---|
| 1037 | web-02 | us-west-2 | memory | 13 | 19 | stopped |
| 1074 | api-01 | eu-west-1 | storage | 26 | 38 | draining |
| 1111 | api-02 | sa-east-1 | gpu | 39 | 57 | offline |
| 1148 | db-primary | eu-central-1 | general | 52 | 76 | Running |
| 1185 | db-replica | us-east-1 | compute | 65 | 95 | STOPPED |
| 1222 | cache-01 | us-west-2 | memory | 78 | 14 | running |
| 1259 | worker-01 | eu-west-1 | Storage | NULL | NULL | stopped |
| 1296 | worker-02 | ap-south-1 | gpu | 4 | 52 | draining |
| 1333 | gpu-node-01 | eu-central-1 | general | 17 | 71 | offline |
| 1370 | WEB-03 | us-east-1 | compute | 30 | 90 | Running |
Expected output
| node_count |
|---|
| 40 |
Worked solution and explanation
What this is really testing
Do not let the one-liner fool you: this is a scoping test wearing a counting problem's clothes. Anyone can type SELECT COUNT(*). What the interviewer watches is whether your count covers exactly the population asked for: every node in 'us-east-1', no more and no fewer. Drop the region filter and you report 200, the whole fleet across six regions, five times the real 40. The subtler miss is the flinch. You notice the messy status column ('Running', 'running', 'STOPPED') and bolt on a running-only clause nobody asked for. That shrinks a footprint headcount to 14, or to 6 if you match lowercase only.
Build it
Step 1: Filter to the region
WHERE region = 'us-east-1' scopes the scan to the primary US region. Because region has no nulls and exact values, an equality predicate is complete. There is no case-folding or trimming to reconcile, the way the status column would demand.
Step 2: Count the survivors
COUNT(*) returns the headcount of surviving rows. Since node_id is the unique, non-null key, one row is one physical node, so counting rows is counting nodes. Alias it to node_count so the result column names itself.
The solution
SELECT COUNT(*) AS node_count
FROM infra_nodes
WHERE region = 'us-east-1'Common follow-up questions
- Now give me the node count for every region, busiest first. (Drop the `WHERE`, add `GROUP BY region`, and order by the count. Tests whether they can pivot from one region to a per-region breakdown.)
- Restrict that to only nodes that are actually running, given how the status values are recorded. (Now the dirty `status` column becomes load-bearing: they must normalize case (`LOWER(status) = 'running'`) before filtering, the exact restraint that was correct to avoid on `region`.)
- In us-east-1, break the count down by `node_type` in a single query. (A grouped or conditional count per `node_type` in one pass, noticing that 'Storage' and 'storage' are one type. Tests whether they reach for a second scan or a single grouped query.)