# The Well-Defended Borders > Some frontiers already stand ready. Find the ones that do. Canonical URL: Domain: SQL · Difficulty: easy · Seniority: L3 ## Problem The infrastructure team is enforcing a minimum cluster size policy: any region with fewer than 14 running nodes needs additional provisioning. Show the regions that already clear the bar, along with their running-node count. ## Worked solution and explanation ### What this really is Strip off the 'minimum cluster size policy' costume and this is a case-insensitive count with a threshold. The whole problem lives in two words of the data: the status column is written 'Running', 'running', and 'STOPPED' in the same table. Everyone writes GROUP BY region and HAVING COUNT(*) >= 14 in their sleep. The thing that actually separates people is noticing that a plain status = 'running' quietly drops every row spelled 'Running'. Miss it and you undercount running nodes, flag regions that already meet the policy as under-provisioned, and hand the infra team a bill to spin up capacity they already have. > **Trick to solving** > > Normalize before you compare. LOWER(status) = 'running' collapses 'Running', 'running', and 'RUNNING' into one bucket. The instant you see mixed casing in a sample row, every equality filter on that column becomes suspect. That reflex is the whole problem. ### The two-filter structure There are two different filters here and they run at two different times. One picks which rows count (running nodes only). The other picks which groups survive (regions with 14+). Conflating them is where the query goes wrong. #### Step 1: Filter rows with a case-folded WHERE WHERE runs before grouping, so this is where you decide what a 'running node' is. LOWER(status) = 'running' is the load-bearing line. Skip the LOWER() and 'Running' / 'STOPPED' rows silently change your counts, but the query still runs and still returns plausible-looking numbers. That is the dangerous kind of bug: no error, just a wrong answer. #### Step 2: Collapse to one row per region GROUP BY region turns the surviving rows into one row per region, and COUNT(*) tallies each bucket. Because the WHERE already threw away non-running nodes, COUNT(*) here means 'running nodes in this region', which is exactly the metric the policy cares about. #### Step 3: Keep only groups over the line with HAVING The 14+ test is about the count, which does not exist until after grouping. HAVING is the only place you can reference COUNT(*). Regions below 14 fall away here, which is correct: they are precisely the ones needing provisioning, so they should NOT appear in a 'already meets threshold' report. On this data the split is real: the regions that come back sit at exactly 14 running nodes, right on the line, while the ones dropped land at 12, two short. **Regions with 14+ running nodes** ```sql SELECT region, COUNT(*) AS node_count FROM infra_nodes WHERE LOWER(status) = 'running' GROUP BY region HAVING COUNT(*) >= 14 ``` *One scan: case-fold in WHERE, tally with GROUP BY, threshold with HAVING.* **Undercounts silently** WHERE status = 'running' only matches the exactly-lowercase rows. 'Running' and 'RUNNING' nodes vanish from the tally, so a region whose 14 running nodes are spelled three different ways is counted as far fewer, drops below 14, and disappears from the report even though it already meets the policy. **Correct** WHERE LOWER(status) = 'running' puts every casing variant in the same bucket, so the count reflects reality and the HAVING threshold is applied to a true total. > **Interviewers watch for** > > The tell of a senior candidate is that they inspect the distinct values of a low-cardinality text column before filtering on it ('let me check how status is actually written') instead of trusting the column name. Reaching for LOWER() (or TRIM()) unprompted signals someone who has been burned by dirty enum-like columns in production and no longer assumes clean data. > **Common pitfall** > > Trying to push the count threshold into WHERE, as in WHERE COUNT(*) >= 14, errors out because the aggregate does not exist yet at WHERE time. The subtler, non-erroring version of this mistake is filtering status without case-folding: that one runs clean and just returns wrong numbers, which is far worse in a review. > **Performance insight** > > At scale this is a single sequential scan with a hash aggregate over a handful of region groups, effectively free. Watch the LOWER(status): wrapping the column in a function means any plain index on status can't be used for a seek. At this size that's irrelevant, but at hundreds of millions of rows you'd want a functional index on LOWER(status) or a normalized generated column so the case-fold doesn't force a full scan. > **In production...** > > Status columns get dirty because they're written by many producers: one service posts 'Running', another 'running', a migration backfills 'RUNNING'. Nobody adds a CHECK constraint until after the first bad report. The durable fix is upstream (constrain or normalize on write), but the analyst's defensive move is to case-fold on read every single time. ## Common follow-up questions - Along with each qualifying region's node_count, also return the count of non-running nodes in that same region. How do you get both without scanning the table twice? _(Tests conditional aggregation, COUNT(*) FILTER (WHERE LOWER(status)='running') alongside a total count, versus the instinct to run two queries or self-join.)_ - The policy changes so a region only qualifies if it has 14+ running nodes AND running nodes are a majority of its total nodes. How does the query change? _(Tests combining two group-level predicates in HAVING using conditional aggregates over the full region population, not just the filtered rows.)_ - Instead of a fixed 14, the threshold becomes 'top 20% of regions by running-node count'. How would you restructure this? _(Tests moving from a static HAVING constant to a window function (PERCENT_RANK / NTILE) or a subquery that computes the cutoff dynamically.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_well_defended_borders) - [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.