Where the Money Burns

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

Domain

SQL

Difficulty

medium

Seniority

L3

Asked in

Zillow

Problem

Every row of cloud_costs is one billing record, and the FinOps team's spend review benchmarks each cloud service against the overall average cost across all of those records. Return the services whose own average cost per record sits above that benchmark, in alphabetical order.

Some services quietly burn more than the rest.

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

What this problem really is

This is a two-scope comparison dressed up as a FinOps review. Each service's own average per billing record is measured against one benchmark taken over every record in cloud_costs. Anyone can write the per-service rollup. What separates candidates is keeping the benchmark at its own scope. Apply it to individual charges before rolling up and you get every service that ever billed one expensive record, which on this data is all ten, instead of the six whose own average clears 835.86.

Building it step by step

Step 1: Take the fleet-wide benchmark over every record

(SELECT AVG(amount) FROM cloud_costs) averages every billing record that carries an amount: 835.86 on this data. AVG skips the twelve records whose amount is blank, and it does the same inside each service, so both sides of the comparison rest on the same records. The ten negative amounts are credits and stay in.

Step 2: Roll the table up to one row per service

GROUP BY svc_name with AVG(amount) gives each service its average cost per record. Group on svc_name alone: provider is spelled in mixed case and plays no part in the question.

Step 3: Keep the services above the benchmark, alphabetically

HAVING AVG(amount) > (SELECT AVG(amount) FROM cloud_costs) filters whole services after their averages exist, which WHERE cannot do. The comparison is strict, so a service exactly on the benchmark stays out, and ORDER BY svc_name returns the names alphabetically.

The solution

SELECT svc_name
FROM cloud_costs
GROUP BY svc_name
HAVING AVG(amount) > (SELECT AVG(amount) FROM cloud_costs)
ORDER BY svc_name

Benchmark applied per record

WHERE amount > (SELECT AVG(amount) FROM cloud_costs) runs before grouping, so it keeps any service with one charge above 835.86. Every service has one, so all ten come back, S3 (821.68) and Cloud Run (650.66) included.

Benchmark applied per service

HAVING AVG(amount) > (...) runs after grouping and judges each service by its own average. Six clear the bar: BigQuery, CloudFront, EC2, Lambda, Pub/Sub and RDS.

Common follow-up questions

• What happens to your results if `svc_name` in `cloud_costs` contains trailing whitespace or mixed casing for the same service? (Tests awareness of text normalization that silently fragments GROUP BY buckets.)
• On a table with millions of rows and no index on `amount`, how do you make sure the overall-average subquery is computed once rather than re-run for every service? (Tests understanding of when a scalar subquery is evaluated once versus per group, and how to keep it that way at scale.)
• How would the result change if a service's average exactly equals the overall average, and how would you adjust the query to include it? (Tests whether the candidate reasons about strict-versus-inclusive comparison and ties.)
• Could you express this with a CTE that computes the benchmark instead of an inline subquery? What does that buy you in readability, and does it change the plan? (Tests whether the candidate can restructure nested logic and weigh readability against the single-pass form.)