# Regional Footprint > Every node costs money. Know what you own. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## Problem The capacity team needs a headcount of nodes in the primary US region. How many infrastructure nodes are deployed in 'us-east-1'? ## Worked solution and explanation ### What this is really testing Do not let the one-liner fool you: this is a confidence test wearing a counting problem's clothes. Anyone can type SELECT COUNT(*). What the interviewer is actually watching is whether you read the schema, saw the messy status column with its 'Running' / 'running' / 'STOPPED' casing and the null cpu values, and then correctly decided that NONE of that noise touches the region filter. The trap is not a hard predicate. The trap is flinching: overthinking a clean exact-match column because the columns around it are dirty. > **The tell is restraint** > > region is a clean, low-cardinality column with no nulls. The dirty data lives on status and the metric columns, and the question never asks about them. Match 'us-east-1' as a plain literal and move on. Wrapping it in LOWER() or a LIKE just to feel safe is exactly the flinch a senior candidate does not make. --- ### 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 **Filtered count** ```sql SELECT COUNT(*) AS node_count FROM infra_nodes WHERE region = 'us-east-1' ``` > **Cost at rest and at scale** > > This is a single sequential scan of 4K rows with a cheap equality filter. At this size the plan is irrelevant, but the instinct that scales is worth naming: region has only 8 distinct values, so on a large table a btree index on region turns this into an index-only count instead of a full scan. That is the difference between a trivial query today and a hot dashboard query at millions of rows. > **Common pitfall** > > The mistake is not a wrong query, it is an anxious one. Candidates who saw the mixed-case status values sometimes 'defensively' normalize the region filter too, or add a status = 'running' clause nobody asked for, quietly changing a footprint headcount into an availability count. Answer the question that was asked: every node in the region, running or not. > **COUNT(*) vs COUNT(column)** > > COUNT(*) and COUNT(node_id) return the same number here because node_id is never null, but COUNT(*) is the honest way to say 'count rows' and does not invite a reader to wonder which column you were guarding against nulls on. Reach for COUNT(column) only when you specifically mean 'rows where this column is present'. --- ## 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. _(Conditional aggregation: COUNT/SUM with a CASE or FILTER per node_type, in one pass. Tests whether they reach for a second scan or a single grouped query.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/regional_footprint) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.