# The Footprint > Some regions carry more of the fleet than others. Show how it is spread. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 ## Problem Capacity planning is reviewing how the server fleet spreads across regions. Show the node count for each region, from the most to the fewest. ## Worked solution and explanation ### What this is really testing Underneath the capacity-planning framing this is a plain group-and-tally: one row per region, COUNT(*) as the metric, sorted so the heaviest region floats to the top. Anyone can write the GROUP BY. What separates candidates is making the sort deterministic. When two regions carry the same node count, a bare ORDER BY node_count DESC leaves their relative order to the engine, so the row you call 'first' can shift between runs. Add a tiebreaker and the output is stable. --- ### Building it #### Step 1: Collapse to one row per region GROUP BY region collapses the 10,000 node rows into one row per region. Every non-aggregated column in the SELECT has to be the grouping key, which is why only region and the aggregate appear. #### Step 2: Count every node COUNT(*) tallies every node in each region, including stopped, draining, and offline ones. The prompt asks for the fleet's spread, not just the live nodes, so there is no status filter. If it asked for active nodes only, you would move that condition into a WHERE clause before grouping. #### Step 3: Sort heaviest first, deterministically ORDER BY node_count DESC puts the densest region first. Append region as a secondary sort so regions tied on count come back in a fixed order rather than an arbitrary one. --- ### The solution **Node count per region, densest first** ```sql SELECT region, COUNT(*) AS node_count FROM infra_nodes GROUP BY region ORDER BY node_count DESC, region ``` *One scan, one grouped aggregate, one sort.* > **Ties make the order non-deterministic** > > The classic mistake is trusting ORDER BY node_count DESC alone. On real data several regions tie, and without a secondary key the engine is free to return them in any order, so a reviewer running your query sees a different row sequence than you did. The region tiebreaker costs nothing and removes the ambiguity. > **One scan, and that is enough** > > A single grouped COUNT over 10,000 rows is one sequential scan feeding a hash aggregate, then a sort over at most a dozen groups. There is nothing to optimize here; region has only 12 distinct values, so the aggregate and sort are effectively free. Reaching for an index or a window function on a query this shape is a signal you are overcomplicating it. > **What the interviewer is watching** > > Interviewers watch whether you return exactly the two requested columns, whether you count every node rather than silently filtering by status, and whether your ordering is reproducible. Volunteering the tiebreaker before being asked is the tell of someone who has been burned by flaky ordering in production. ## Common follow-up questions - How would you change the query to count only nodes whose status is running, given the status column has mixed casing like 'Running' and 'STOPPED'? _(Tests whether the candidate can pivot from all-nodes to a filtered metric using WHERE before the aggregate.)_ - If capacity planning wanted the single densest region within each node_type instead, how would your approach change? _(Tests knowledge of moving from ORDER BY plus LIMIT to a windowed or correlated approach when only the leader is wanted per some partition.)_ - If some rows had a NULL region, how would they appear in your result, and would you keep or exclude them? _(Tests handling of NULL grouping keys and whether the candidate accounts for them explicitly.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_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.