# Total Compute Cloud Cost

> Total compute spend. The number.

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

Domain: SQL · Difficulty: easy · Seniority: L3 · Asked in: Forbes

## Problem

The FinOps team is reconciling the compute budget line item. What is the total cloud spend for the EC2 service?

## Tables

### cloud_costs

- cost_id INTEGER
- provider TEXT
- svc_name TEXT
- region TEXT
- amount REAL
- acct_id TEXT
- bill_date TEXT

Sample rows (10 of 200):

| cost_id | provider | svc_name | region | amount | acct_id | bill_date |
| --- | --- | --- | --- | --- | --- | --- |
| 543 | GCP | S3 | us-west-2 | 19.33 | acct-1001 | 2026-02-01 |
| 586 | Azure | RDS | eu-west-1 | 37.16 | acct-1002 | 2026-03-01 |
| 629 | aws | Lambda | ap-southeast-1 | 54.99 | acct-1003 | 2026-04-01 |
| 672 | gcp | BigQuery | us-central1 | 72.82 | acct-1004 | 2026-05-01 |
| 715 | AWS | Cloud Run | europe-west1 | 90.65 | acct-1005 | 2026-06-01 |
| 758 | GCP | Blob Storage | us-east-1 | 108.48 | acct-1006 | 2026-07-01 |
| 801 | Azure | Cosmos DB | us-west-2 | 126.31 | acct-1007 | 2026-08-01 |
| 844 | aws | CloudFront | eu-west-1 | 144.14 | acct-1008 | 2026-09-01 |
| 887 | gcp | Pub/Sub | ap-southeast-1 | 161.97 | NULL | 2026-01-01 |
| 930 | AWS | EC2 | us-central1 | 179.8 | acct-1010 | 2026-02-01 |

## Expected output

First 1 of 1 rows:

| total_spend |
| --- |
| 13215.2 |

## Worked solution and explanation

### Why this problem exists in real interviews

This is a fundamental filter-and-aggregate pattern. Interviewers use it to verify that candidates can write a clean `WHERE` + `SUM` query without overcomplicating it.

---

### Break down the requirements

#### Step 1: Filter to the compute service

The prose asks for the total cloud spend for the EC2 service, so filter `cloud_costs` to rows where `svc_name` = 'EC2' (the actual service identifier in the table).

#### Step 2: Sum the amount

Aggregate the spend column with SUM(amount); SUM ignores the NULL amounts present on several EC2 rows, giving the true billed total.

---

### The solution

**Filter and aggregate a single service**

```sql
SELECT SUM(amount) AS total_spend
FROM cloud_costs
WHERE svc_name = 'EC2';
```

> **Cost Analysis**
>
> With 200 distinct services, the filter reduces 8M rows to roughly 40K. An index on `svc_name` turns this into an index seek plus a sum scan over the matching rows.

> **Interviewers Watch For**
>
> Overcomplicating with GROUP BY when only a single scalar is needed. No GROUP BY is required because the entire result is one row.

> **Common Pitfall**
>
> Case sensitivity in the filter. If `svc_name` stores mixed-case values, `WHERE LOWER(svc_name) = 'compute'` would be safer, though it prevents index usage.

---

## Common follow-up questions

- What does SUM return if no rows match the filter? _(Tests knowledge that SUM returns NULL (not 0) when no rows match.)_
- How would you compute cost per region for the compute service? _(Tests adding a GROUP BY region to the existing filter.)_
- What if the table had a date column and you needed monthly totals? _(Tests date truncation combined with GROUP BY for time-series aggregation.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/total_compute_cloud_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). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.