# Where the Fleet Lives > Six regions on the map. Where does the fleet cluster? Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 ## Problem The capacity planning team is auditing how nodes are distributed across six key regions: us-east-1, us-west-2, eu-west-1, eu-central-1, ap-southeast-1, and ap-northeast-1. Break the node count down by region, busiest first. ## Worked solution and explanation ### What this really is This is a filtered per-group count wearing a capacity-audit costume. The skill being probed: can you count nodes within a fixed whitelist of regions without dragging in the regions nobody asked about, and without expecting a row for a region that has no nodes? Anyone can write a count. The move that separates candidates is putting the region filter and the region grouping in the same query and knowing exactly which rows survive each. Forget the filter and you report all twelve regions; assume the grouping hands back all six and you will chase a phantom row for a region the fleet never deployed to. --- ### The two moves #### Step 1: Fence off the six regions `WHERE region IN ('us-east-1', 'us-west-2', 'eu-west-1', 'eu-central-1', 'ap-southeast-1', 'ap-northeast-1')` is evaluated first, before any grouping. Everything downstream only ever sees rows from those six regions, so the audit stays scoped no matter how many other regions live in the table. #### Step 2: Count per surviving region `GROUP BY region` collapses the filtered rows into one bucket per region, and `COUNT(*)` tallies every node in each bucket. `COUNT(*)` counts rows, so a node with a null cpu or mem still counts, which is exactly what you want for a headcount. --- ### The solution **Filter, group, count, order** ```sql SELECT region, COUNT(*) AS node_count FROM infra_nodes WHERE region IN ('us-east-1', 'us-west-2', 'eu-west-1', 'eu-central-1', 'ap-southeast-1', 'ap-northeast-1') GROUP BY region ORDER BY node_count DESC, region ``` *The IN-list scopes the scan; GROUP BY buckets by region; the tie-break keeps the ordering deterministic.* > **WHERE happens before GROUP BY** > > The filter runs row by row before any bucketing, so the count you get is already scoped to the whitelist. There is no need for a HAVING clause here: HAVING filters groups after aggregation, but the region restriction is a per-row condition, so it belongs in WHERE where it can also skip work earlier. > **Six regions in, maybe fewer rows out** > > GROUP BY only emits a row for a region that has at least one node. If the fleet never deployed to ap-northeast-1, that region simply will not appear, and no amount of staring at the IN-list will conjure it. If the audit needs a zero next to empty regions, that is a different query: a region reference list left-joined to these counts. > **Interviewers watch for** > > The tell is whether you filter the same column you group by, and whether you can say out loud why the filter goes in WHERE and not HAVING. Candidates who reach for HAVING on a per-row condition, or who count all regions and mentally discard the extras, signal they are pattern-matching rather than reasoning about the order of operations. > **Cost at scale** > > At 10,000 rows this is a trivial scan. At fleet scale (tens of millions of node records) an index on region turns the WHERE into a set of index range reads and feeds the grouping cheaply, since region has low cardinality. The count itself stays linear in the surviving rows. **Filter then count** WHERE region IN (...) scopes the scan up front, so grouping and counting only ever touch the six regions. One pass, nothing aggregated that you throw away. **Count then discard** Grouping all twelve regions and dropping the unwanted ones in application code aggregates rows you will discard and moves the filter out of the engine. More work, and easier to get wrong. ## Common follow-up questions - How would you return a row for every one of the six regions, showing zero for any region with no nodes? _(Tests whether they reach for a region reference list and an outer join instead of a plain grouped count.)_ - Two regions tie on node count. How do you make the ordering stable and repeatable? _(Tests tie-breaking with a deterministic secondary sort key.)_ - The status column mixes 'running', 'Running', and 'STOPPED'. If the audit wanted only running nodes, how would you filter reliably? _(Tests case-insensitive filtering on messy categorical data.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/where_the_fleet_lives) - [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.