Where the Money Pools

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

Each row of cloud_costs is one billing line for a service in a region, and credits appear as negative amounts. Show the total cost of each service billed in 'us-west-2', from highest spend to lowest.

Top spenders in one region.

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

Totals, not lines

This is a one-region rollup dressed up as a billing report: many billing lines per service have to collapse into one number per service before anything is ordered. Everyone writes the region filter. What separates candidates is whether they order totals or order lines. Sort the 34 'us-west-2' lines by amount and you return 34 rows with every service repeated. Compare services by their biggest single charge and 'Pub/Sub' (1,410.07) lands above 'Lambda' (1,303.09). Summed, 'Lambda' is at 4,609.14 and 'Pub/Sub' at 4,383.08, because 'Pub/Sub' carries two -93.70 credits that only a full sum sees.

SELECT svc_name, amount
FROM cloud_costs
WHERE region = 'us-west-2'
ORDER BY amount DESC

Building it

Step 1: Keep only the 'us-west-2' lines

WHERE region = 'us-west-2' runs before any grouping, so the sum only ever sees that region's 34 lines. Drop it and all ten services from six regions come back, with totals that mean nothing for this region.

Step 2: Collapse each service to one total

GROUP BY svc_name with SUM(amount) folds every line into one row per service. The key is the service, not the vendor, so lines billed under 'GCP', 'gcp', 'Azure', 'aws' and 'AWS' all land in the same total, and the credits net out as they would on the invoice.

Step 3: Order the totals, not the lines

ORDER BY total_cost DESC sorts the five computed totals. The alias works in ORDER BY because that clause is evaluated after the SELECT list; it would not work in WHERE.

SELECT svc_name, SUM(amount) AS total_cost
FROM cloud_costs
WHERE region = 'us-west-2'
GROUP BY svc_name
ORDER BY total_cost DESC

Common follow-up questions

• How would you return only the top three services? (Whether you check for ties at the cut before reaching for `LIMIT 3`.)
• How would you add each service's share of the region's total? (Probes a window over an aggregate: `SUM(amount) / SUM(SUM(amount)) OVER ()`.)
• Finance wants the same report per provider. What changes? (Tests whether you notice 'aws' and 'AWS' are one vendor and normalise `provider` with `LOWER` before grouping.)