# Top Services by Regional Cost

> Top spenders in one region.

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

Domain: SQL · Difficulty: medium · Seniority: L3

## Problem

For each service in the 'us-west-2' region, show the total cloud cost, sorted from highest spend to lowest.

## Worked solution and explanation

### Why this problem exists in real interviews

This tests your ability to combine filtering and aggregation in a single query. Interviewers want to see that you apply the `WHERE` clause before grouping, not after, and that you understand how `GROUP BY` collapses rows to one per group.

---

### Break down the requirements

#### Step 1: Filter to the target region

Apply `WHERE region = 'us-west-2'` to restrict the 12M row `cloud_costs` table to only the region of interest before any aggregation happens.

#### Step 2: Aggregate cost per service

`GROUP BY svc_name` with `SUM(amount)` produces one row per service with its total spend.

#### Step 3: Sort descending by total

`ORDER BY total_cost DESC` surfaces the highest spenders first, which is what the prompt requests.

---

### The solution

**Filter, aggregate, and sort by spend**

```sql
SELECT svc_name, SUM(amount) AS total_cost
FROM cloud_costs
WHERE region = 'us-west-2'
GROUP BY svc_name
ORDER BY total_cost DESC
```

> **Cost Analysis**
>
> The `WHERE` clause narrows 12M rows to roughly 1M (1 of 12 regions) before aggregation. An index on `(region, svc_name, amount)` would make this a covering index scan, avoiding a full table scan entirely.

> **Interviewers Watch For**
>
> Candidates who filter after grouping (using `HAVING region = ...`) instead of `WHERE` reveal a misunderstanding of SQL evaluation order. The `WHERE` clause is also more efficient because it reduces the input to the aggregation step.

> **Common Pitfall**
>
> Forgetting to alias `SUM(amount)` makes the output column name engine-dependent. Always alias aggregate expressions for deterministic column names in downstream consumption.

---

## Common follow-up questions

- How would you modify this to show the top 5 services only? _(Tests whether you use `LIMIT` vs. a window function depending on tie requirements.)_
- What if `amount` could be negative (representing credits)? _(Checks awareness that SUM handles negatives, and whether a net-cost filter is needed.)_
- How would you add a percentage-of-total column? _(Probes knowledge of window functions: `SUM(amount) / SUM(SUM(amount)) OVER ()`.)_

## Related

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