# Where the Heat Pools > Across the fleet, some regions run hotter than others. Canonical URL: 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. ## Tables ### infra_nodes - node_id INTEGER - hostname TEXT - region TEXT - node_type TEXT - cpu_pct REAL - mem_pct REAL - status TEXT Sample rows (10 of 200): | node_id | hostname | region | node_type | cpu_pct | mem_pct | status | | --- | --- | --- | --- | --- | --- | --- | | 1037 | web-02 | us-west-2 | memory | 13 | 19 | stopped | | 1074 | api-01 | eu-west-1 | storage | 26 | 38 | draining | | 1111 | api-02 | ap-south-1 | gpu | 39 | 57 | offline | | 1148 | db-primary | eu-central-1 | general | 52 | 76 | Running | | 1185 | db-replica | us-east-1 | compute | 65 | 95 | STOPPED | | 1222 | cache-01 | us-west-2 | memory | 78 | 14 | running | | 1259 | worker-01 | eu-west-1 | storage | NULL | NULL | stopped | | 1296 | worker-02 | ap-south-1 | gpu | 4 | 52 | draining | | 1333 | gpu-node-01 | eu-central-1 | general | 17 | 71 | offline | | 1370 | WEB-03 | us-east-1 | compute | 30 | 90 | Running | ### k8s_pods - pod_id INTEGER - pod_name TEXT - nspace TEXT - status TEXT - restarts INTEGER - cpu_used REAL - mem_used REAL Sample rows (10 of 200): | pod_id | pod_name | nspace | status | restarts | cpu_used | mem_used | | --- | --- | --- | --- | --- | --- | --- | | 2043 | payment-003ef | production | Pending | 0 | 0.011 | 37 | | 2086 | search-003f6 | staging | CrashLoopBackOff | 0 | 0.022 | 74 | | 2129 | gateway-003fd | monitoring | Completed | 0 | 0.033 | 111 | | 2172 | worker-00404 | kube-system | Error | 0 | 0.044 | 148 | | 2215 | cron-0040b | default | Terminating | 0 | 0.055 | 185 | | 2258 | ml-serve-00412 | production | Running | 0 | NULL | NULL | | 2301 | cache-00419 | staging | Pending | 0 | 0.077 | 259 | | 2344 | auth-00420 | monitoring | CrashLoopBackOff | 0 | 0.088 | 296 | | 2387 | payment-00427 | kube-system | Completed | 0 | 0.099 | 333 | | 2430 | search-0042e | default | Error | 0 | 0.11 | 370 | ## Expected output First 5 of 5 rows: | region | avg_cpu | | --- | --- | | ap-south-1 | 51.35294117647059 | | eu-central-1 | 45.8235294117647 | | eu-west-1 | 48.94117647058823 | | us-east-1 | 49.166666666666664 | | us-west-2 | 48.588235294117645 | ## 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/satisfaction_score_by_region) - [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.