# Node Utilization > Overloaded nodes hiding in busy regions. Spot the hot spots. Canonical URL: Domain: SQL · Difficulty: hard · Seniority: L4 ## Problem The capacity planning team is hunting for overloaded nodes against their regional baseline. In infra_nodes, compute the average cpu_pct per region (skipping NULL cpu_pct), then find nodes whose cpu_pct exceeds their own region's average. Within each region rank those nodes sorted from highest cpu_pct to lowest with ties sharing a position. Return the hostname, region, cpu_pct, regional average, and position. ## Worked solution and explanation ### Why this problem exists in real interviews FinOps chargeback questions land in senior SQL screens because they bundle four traps: every column is TEXT (so you must CAST before doing math), the grouping grain is a composite of two dimensions, you have to filter out non-positive totals, and the ranking is partitioned with the standard `RANK()` (gaps allowed on ties). Getting all four right on the first pass is the signal interviewers care about. --- ### Break down the requirements #### Step 1: Cast TEXT amounts to DOUBLE before summing `amount` is TEXT in `cost_allocs`. `SUM(amount)` would either error or silently coerce. Wrap it as `SUM(CAST(amount AS DOUBLE))` so the totals are numeric. Group by the composite `(team_name, region)` to get one row per team-region pair. #### Step 2: Filter to positive totals after aggregation `HAVING SUM(CAST(amount AS DOUBLE)) > 0` drops team-region pairs where the total is zero or negative (refunds, credits). Putting this in HAVING is required because the predicate references an aggregate. #### Step 3: Apply standard RANK partitioned by region Materialize the per-pair totals in a `team_totals` CTE. Then in the outer SELECT apply `RANK() OVER (PARTITION BY region ORDER BY total_amount DESC)`. Standard `RANK()` (not DENSE_RANK) is what 'standard RANK' means in the prompt: ties share a rank, and the next position skips by the count of ties. #### Step 4: Sort with three explicit keys Final ORDER BY is `region ASC, rnk ASC, team_name ASC` so output is deterministic across regions, ranks, and tied team names within a rank. --- ### The solution **CAST, aggregate, filter, then partitioned RANK** ```sql WITH team_totals AS ( SELECT team_name, region, SUM(CAST(amount AS DOUBLE)) AS total_amount FROM cost_allocs GROUP BY team_name, region HAVING SUM(CAST(amount AS DOUBLE)) > 0 ) SELECT team_name, region, total_amount, RANK() OVER (PARTITION BY region ORDER BY total_amount DESC) AS rnk FROM team_totals ORDER BY region, rnk, team_name ``` > **Cost Analysis** > > `cost_allocs` is 30,000,000 rows partitioned by `period` over 36 months. The hash aggregate keys on `(team_name, region)` (70 teams x 15 regions = up to 1,050 groups), which fits comfortably in memory. The HAVING filter shrinks the input to the window function, and the RANK partition by region (15 regions) sorts each partition independently, so the final sort is over at most 1,050 rows. > **Interviewers Watch For** > > Interviewers want to see the CAST. Forgetting it is a giveaway that you skimmed the schema. They also watch for `RANK()` vs `DENSE_RANK()` vs `ROW_NUMBER()` (the prompt says 'standard RANK', so any other choice is wrong) and for filtering with `WHERE total_amount > 0` (impossible since the alias does not exist before HAVING runs). > **Common Pitfall** > > Writing `SUM(amount) > 0` without the CAST. SQLite will sum TEXT by coercing string-prefix digits, which silently produces wrong totals when amounts contain currency symbols or negative signs in unexpected positions. Always cast TEXT-typed numerics before any arithmetic or comparison. --- ## Common follow-up questions - What would change in the output if you swapped `RANK()` for `DENSE_RANK()` or `ROW_NUMBER()`, and when would each be appropriate? _(Tests RANK family tradeoffs. `DENSE_RANK` keeps consecutive ranks (1,1,2 instead of 1,1,3), `ROW_NUMBER` breaks ties arbitrarily. The prompt asks for standard RANK specifically.)_ - If FinOps only cared about chargeback for one specific `period`, where in the query would you push that filter and what does it save? _(Tests partition pruning awareness. `cost_allocs` is partitioned by `period`. Adding `WHERE period = '2026-01'` (or similar) inside the CTE limits the scan to one partition out of 36.)_ - How would you change the query to return only the top 3 teams per region instead of every team? _(Tests outer filter on window output. The candidate should wrap the CTE result in another CTE or subquery and filter `WHERE rnk <= 3` since you cannot reference window function aliases in the same level's WHERE clause.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/node_utilization) - [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). 100% free data engineering interview prep. Live code execution against Postgres 16, Python 3.11, and Spark sandboxes. No paywall, no premium tier, no signup gate.