Average Node CPU by Region

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

phData

Problem

Capacity planning is sizing up where to add hardware, and only cares about the nodes that are actually running our critical services. Report the average CPU utilization per region across the nodes that have hosted a payment-api, gateway, or auth-svc pod, counting each node once however many such pods it ran.

Average infrastructure node CPU usage broken down by region

Tables

pod_assignments

Columns: assignment_id INTEGER, node_id INTEGER, pod_id INTEGER, svc_name TEXT, assigned_at TEXT, status TEXT

infra_nodes

Columns: node_id INTEGER, hostname TEXT, region TEXT, node_type TEXT, cpu_pct REAL, mem_pct REAL, status TEXT

Expected output

Worked solution and explanation

The real shape under the capacity question

Under the capacity-planning story this is a membership question: which nodes belong to the average is decided by a filtered lookup into pod_assignments, not by joining to it. Anyone can average cpu_pct by region; the tell is how you gate the node set. Inner-join infra_nodes to pod_assignments and each node fans out once per matching pod, so a node hosting dozens of critical pods is counted dozens of times and the regional average skews toward your busiest boxes. Drop the service filter and you average every node, including ones that never touched a critical workload. The clean move is a semi-join: WHERE n.node_id IN (SELECT node_id FROM pod_assignments WHERE svc_name IN ('payment-api', 'gateway', 'auth-svc')), which keeps each qualifying node exactly once.

Break down the requirements

Step 1: Pin the qualifying node set

A node qualifies if it has ever hosted a critical-service pod, so build that set with a subquery over pod_assignments filtered by svc_name IN ('payment-api', 'gateway', 'auth-svc'). Selecting node_id from that subquery yields the membership list, duplicates and all, which is fine because IN only tests presence.

Step 2: Test membership, do not join

Use WHERE n.node_id IN (...) against infra_nodes rather than an inner join. The semi-join keeps one row per node no matter how many matching pods it ran, so a busy node cannot fan out and drag its region's average upward.

Step 3: Average per region

GROUP BY n.region and take AVG(n.cpu_pct). AVG skips NULL cpu_pct automatically, and there is no filter on either table's status column: hosting a critical pod is the only condition that matters.

The solution

SELECT n.region, AVG(n.cpu_pct) AS avg_cpu
FROM infra_nodes n
WHERE n.node_id IN (
    SELECT node_id
    FROM pod_assignments
    WHERE svc_name IN ('payment-api', 'gateway', 'auth-svc')
)
GROUP BY n.region;

Common follow-up questions

• How would the query change if you needed only nodes currently running an active critical pod, given that `status` in `pod_assignments` is mixed-case ('Active', 'ACTIVE', 'active')? (Tests whether the candidate can move from 'ever hosted' to a current-state predicate and handle the mixed-case status values.)
• If you had written this with an inner join instead of `IN`, what would happen to a region's `AVG(cpu_pct)` and why? (Tests understanding of fan-out: joining before grouping inflates the aggregate.)
• How would you also return the count of qualifying nodes per region alongside the average? (Tests extending the aggregate while preserving distinct-node semantics.)
• Could you express the same filter with `EXISTS` instead of `IN`, and when would the choice matter? (Tests fluency with equivalent existence constructs and their trade-offs.)