# Nodes in Target Regions

> The target regions need attention.

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

Domain: SQL · Difficulty: easy · Seniority: L3 · Asked in: Vesta Innovations

## Problem

A cross-region latency audit needs the full node record for every infrastructure node in 'us-east-1', 'us-west-2', or 'eu-west-1'.

## 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 | sa-east-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 |

## Expected output

First 5 of 120 rows:

| 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 |
| 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 |

## Worked solution and explanation

### Why this problem exists in real interviews

This focuses on query construction within `infra_nodes`, specifically around the hostname column. Interviewers present it as a fundamentals check because the edge cases around NULL values and boundary conditions reveal depth of understanding.

---

### Break down the requirements

#### Step 1: Filter to the three target regions

`WHERE region IN ('us-east-1', 'us-west-2', 'eu-west-1')` restricts to the audit scope.

#### Step 2: Return all fields

`SELECT *` returns the full node record for every matching row.

---

### The solution

**IN filter returning all columns**

```sql
SELECT *
FROM infra_nodes
WHERE region IN ('us-east-1', 'us-west-2', 'eu-west-1')
```

> **Cost Analysis**
>
> With `infra_nodes` (6,000 rows), this query scans a small dataset. No indexing is needed for this volume. At production scale, an index on the primary filter column would improve performance.

> **Interviewers Watch For**
>
> Interviewers evaluate whether you translate the English requirements into the correct SQL clauses on the first attempt. They watch for clean syntax, correct column references, and whether you verify edge cases before declaring the query complete.

> **Common Pitfall**
>
> The most common mistake is misreading the prompt's filtering or grouping requirements. Double-check which columns to group by, which to aggregate, and whether the output should be filtered with `WHERE` (before grouping) or `HAVING` (after grouping).

---

## Common follow-up questions

- If `infra_nodes`.node_id could contain unexpected NULL values, how would your query behave? _(Tests NULL awareness even when the schema does not currently allow NULLs in `node_id`.)_
- How would you verify that your aggregation on `infra_nodes`.node_id is not double-counting due to duplicate rows? _(Tests data quality awareness and deduplication strategies.)_
- What index would you add to `infra_nodes` to avoid a full table scan when filtering or sorting by `node_id`? _(Tests practical indexing decisions for numeric filter columns.)_

## Related

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