# Where the Money Burns > Some services quietly burn more than the rest. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 ## Problem Find the cloud services whose average cost per billing record sits above the overall average cost across all records. Return those service names in alphabetical order. ## Worked solution and explanation ### What this problem really is This is an above-the-mean filter dressed up as a FinOps review. The skill being probed: can you compute one fleet-wide benchmark a single time and measure every service's average against it? Anyone can write `GROUP BY svc_name`. The trick is putting an uncorrelated scalar subquery in the HAVING clause so the overall average is evaluated exactly once and every group is judged against that same number. Correlate that subquery to the outer row by accident and you either compare each service to itself or pay to recompute the benchmark for every group. > **Trick to solving** > > The phrase 'above the overall average' is the tell for a scalar subquery: `(SELECT AVG(amount) FROM cloud_costs)`. It returns one value, runs once, and slots straight into HAVING. Keep it free of any reference to the outer query and the planner treats it as a constant. --- ### Building it step by step #### Step 1: Compute the fleet-wide benchmark as a scalar subquery Write `(SELECT AVG(amount) FROM cloud_costs)` to get the overall average across every billing record. This is the benchmark each service is measured against. AVG ignores NULL amounts, so the one record with a missing value drops out here just as it does in the per-service averages, keeping both sides comparable. #### Step 2: Group by service and average each one Collapse the table to one row per service with `GROUP BY svc_name` and take `AVG(amount)` inside each group. That gives the average cost per billing record for every service, which is the value the prompt asks you to compare. #### Step 3: Filter the above-average groups and sort Keep only the groups whose average beats the benchmark with `HAVING AVG(amount) > (SELECT AVG(amount) FROM cloud_costs)`, then `ORDER BY svc_name` for the alphabetical output. HAVING (not WHERE) because the comparison is against an aggregate computed per group. --- ### The solution **Above-average services in one aggregate pass** ```sql SELECT svc_name FROM cloud_costs GROUP BY svc_name HAVING AVG(amount) > (SELECT AVG(amount) FROM cloud_costs) ORDER BY svc_name ``` > **Cost analysis** > > On the full 15M-row table this is two aggregate scans, not a join: the scalar subquery materializes the benchmark once, and the GROUP BY collapses 15M rows to roughly 400 service buckets before the HAVING filter runs. No self-join, no per-row recomputation of the average. If the planner caches the constant subquery result, the dominant cost is a single pass to aggregate by svc_name. > **Interviewers watch for** > > Strong candidates name the grain (one row per svc_name) before writing any SQL and explicitly call the benchmark subquery uncorrelated. Saying out loud why it runs once rather than per group is the signal of someone who has been burned by an accidental correlated subquery in production. > **Common pitfall** > > Putting the average comparison in WHERE instead of HAVING. WHERE filters raw rows before aggregation, so `WHERE amount > (SELECT AVG(amount) ...)` answers a different question (individual expensive records) and never produces a per-service average at all. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/where_the_money_burns) - [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.