Total Compute Cloud Cost

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

Forbes

Problem

Reconciling the compute budget line comes down to one figure for the FinOps team: the total spend in cloud_costs for the EC2 service.

Total compute spend. The number.

Tables

cloud_costs

Columns: cost_id INTEGER, provider TEXT, svc_name TEXT, region TEXT, amount REAL, acct_id TEXT, bill_date TEXT

Expected output

Worked solution and explanation

This is a one-line-item total in a FinOps costume, and the only real decision is which column names the line item. Anyone can write a SUM. The trap is provider: EC2 is an AWS product, so filtering on 'AWS' feels equivalent. It is not. In this table every EC2 row is billed under 'AWS', but so is every Cloud Run row, and the lowercase 'aws' rows carry Lambda and CloudFront. Filter on the vendor and the compute line quietly absorbs other services, and the reconciliation never ties out to the invoice.

Walking to the answer

Step 1: Filter on the service, not the vendor

svc_name holds ten clean service names with 'EC2' spelled one way everywhere, so svc_name = 'EC2' keeps every EC2 charge and nothing else. provider is a vendor, it comes in two cases, and on this data it maps to more than one service, so it cannot stand in for the line item.

Step 2: Total amount over what survives

SUM(amount) adds the surviving charges. A few EC2 rows carry no amount at all; SUM skips them, so they add nothing to the figure instead of breaking it.

Step 3: Return one figure

The question asks for one number, so nothing is broken out: the aggregate over the filtered rows collapses to a single total_spend row.

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

Filtering on the vendor

WHERE provider = 'AWS' returns EC2 plus every Cloud Run row billed under 'AWS'. Normalizing with LOWER(provider) = 'aws' makes it worse by adding Lambda and CloudFront. Either way the figure is materially larger than the EC2 line.

Filtering on the service

WHERE svc_name = 'EC2' matches the one spelling the service has, so the total is exactly the EC2 line item: 13215.2.

Common follow-up questions

• What does `SUM` return when no row is 'EC2'? (A sum over zero rows is NULL, not 0; `COALESCE` turns it into a reportable zero.)
• How would you break the EC2 total down by `region`? (Adds a grain to the result without touching the filter.)
• FinOps wants the EC2 total for each month of `bill_date`. What changes? (Truncating the bill date to its month and totalling per month.)