# Where the Heat Pools

> Across the fleet, some regions run hotter than others.

Canonical URL: <https://datadriven.io/problems/where_the_heat_pools>

Domain: SQL · Difficulty: easy · Seniority: L3

## Problem

The infra team is comparing CPU load across the node fleet by region. Show each region with its average CPU utilization, listed alphabetically.

## Worked solution and explanation

### What this really is

This is a one-table group average wearing a two-table schema as a costume. The schema drops k8s_pods in front of you, but that table carries no region column and no key that points back at a node, so there is nothing to join it on. The whole answer lives inside infra_nodes. The skill being probed is whether you notice that instead of reflexively pattern-matching two tables into a JOIN, and whether you trust AVG to behave when a node is missing its CPU reading. Reach for a join and you either match nothing or fan the rows out; COALESCE a missing reading to 0 and you quietly understate whichever region that node sits in.

> **Let AVG skip the nulls for you**
>
> A node that never reported cpu_pct shows up as null. SQL AVG ignores nulls in both the running sum and the count, so that region averages only over its real readings. You do not need a WHERE cpu_pct IS NOT NULL, and you must not fill the null with 0.

### Building it

#### Step 1: Pick the one table that has the answer

Both region and cpu_pct live on infra_nodes. k8s_pods has no region and no shared key, so it contributes nothing to this answer. Start from infra_nodes alone and ignore the decoy.

#### Step 2: Collapse to one row per region

Group by region and take AVG(cpu_pct). Every node row folds into the average for its region, leaving one row per region.

#### Step 3: Order the output

The ask is alphabetical, so order by region. It is cosmetic on a small result, but stating the order makes the output deterministic instead of depending on scan order.

**Average CPU per region**

```sql
SELECT region, AVG(cpu_pct) AS avg_cpu
FROM infra_nodes
GROUP BY region
ORDER BY region;
```

*One scan of infra_nodes; k8s_pods never enters the plan.*

> **Do not zero-fill the missing reading**
>
> Writing AVG(COALESCE(cpu_pct, 0)) treats a silent node as a fully idle one and drags its region's average down. A missing measurement is not the same as a measured zero. Leave the null alone and let AVG drop it.

> **The join is a trap, and they are watching for it**
>
> A strong candidate checks whether the two tables even share a key, sees that k8s_pods has no region and nothing linking it to a node, and never writes the join. A weaker one joins the two tables, gets a broken result, and starts debugging phantom data. Saying out loud 'these tables do not share a key' is the tell that you read the schema instead of pattern-matching two tables into a JOIN.

**Joined the decoy table**

Joining k8s_pods produces a broken result: there is no region on that table and no key to match on, so you either get nothing back or a meaningless fan-out, and you lose time hunting a data bug that is not there.

**Single table**

Reading from infra_nodes alone returns every region with its correct average. Fewer rows scanned, no wasted join, and the query says exactly what it means.

## Common follow-up questions

- How would the result change if a region had every node's cpu_pct null? _(Tests whether they know AVG returns null for an all-null group, so the region still appears but with a null average rather than being dropped.)_
- Now average only across nodes whose status is a running variant, treating 'Running' and 'running' as the same and excluding stopped nodes. _(Adds a case-insensitive filter and forces a decision about how to normalize the messy status casing before aggregating.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/where_the_heat_pools)
- [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.