Top of the Bill

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

Slickdeals

Problem

Finance is reviewing the cloud bills and wants to see which services dominate the spend. Among the services that also appear in the cost allocations, list the two with the highest total cloud cost, the biggest spender first.

Every cloud bill has a headliner or two. Find them.

Tables

cloud_costs

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

cost_allocs

Columns: alloc_id INTEGER, team_name TEXT, svc_name TEXT, region TEXT, amount REAL, period TEXT, acct_id TEXT, category TEXT

Expected output

Worked solution and explanation

What this really is

Underneath the FinOps framing this is a semi-join feeding a top-N. The real question the interviewer is probing: can you express 'services that also have allocations on file' as a membership test, not a row-multiplying join? Reach for a plain INNER JOIN on svc_name and every service with many allocation rows gets its cloud cost summed once per allocation, so the totals balloon and the top two you report is not the top two finance sees. cost_allocs is a filter here, never a source of dollars: the moment you SUM across the join, or add its amounts in, the numbers stop tying out.

Reading the requirements

Step 1: Restrict to allocated services without fanning out

The requirement is membership, not a join: cc.svc_name IN (SELECT svc_name FROM cost_allocs). This keeps each cloud_costs row exactly once. An INNER JOIN would duplicate a cost row once per matching allocation and inflate SUM(cc.amount).

Step 2: Aggregate and take the two heaviest

SUM(cc.amount) GROUP BY cc.svc_name gives per-service spend, then ORDER BY total_amount DESC LIMIT 2 takes the two heaviest. Sum cc.amount only; cost_allocs is a filter, never a metric source.

The solution

SELECT cc.svc_name,
       SUM(cc.amount) AS total_amount
FROM cloud_costs cc
WHERE cc.svc_name IN (SELECT svc_name FROM cost_allocs)
GROUP BY cc.svc_name
ORDER BY total_amount DESC
LIMIT 2;

Wrong (join fans out)

INNER JOIN cost_allocs ca ON cc.svc_name = ca.svc_name duplicates each cost row once per matching allocation, so SUM(cc.amount) is multiplied by the allocation count per service.

Right (semi-join)

cc.svc_name IN (SELECT svc_name FROM cost_allocs) is a membership test: each cost row survives exactly once and the sum stays honest.

Common follow-up questions

Common follow-up questions

• Rewrite it so that a tie for second place surfaces every tied service. (Forces the `DENSE_RANK` refactor and tests whether you understand LIMIT's tie behavior.)
• How would you confirm `cost_allocs` does not inflate the per-service totals? (Probes join-cardinality discipline: a COUNT(*) versus COUNT(DISTINCT `svc_name`) sanity check on `cost_allocs`.)
• What if finance only wants a single billing year's spend? How would you add that window? (Adds a date predicate on `bill_date` and tests keeping it sargable so partition pruning fires on the 15M-row table.)
• How would you show each service's allocated budget alongside its actual cloud cost? (Tests joining back to `cost_allocs` after aggregation without reintroducing fan-out.)