Largest Single 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

Siemens

Problem

Which single line in cloud_costs carries the largest amount? A finance analyst chasing the biggest charge on the cloud bill needs its service name and amount, taking the service whose name comes first alphabetically when two lines tie at the top.

One line item. The biggest bill of all.

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 an argmax dressed up as a billing audit: the question wants the line that holds the biggest amount, not the biggest amount on its own. Everyone reaches for MAX(amount), and that is the trap. MAX hands back a number with no svc_name attached, and the reflex repair, GROUP BY svc_name, quietly answers a different question: one maximum per service instead of one winner overall. Sort the real rows and keep the top one, and the service and the amount come from the same entry by construction.

Walk it to the answer

Step 1: Decide the grain: one existing row

The answer is a line already sitting in cloud_costs, not a sum and not a per-service rollup. That rules out GROUP BY: the moment you group, the result describes services, not entries.

Step 2: Sort entries by amount, biggest first

ORDER BY amount DESC puts the largest line on top. The seed also holds 12 rows with a missing amount and 10 negative credits. Credits sink to the bottom, and this grader places missing amounts after every real one in descending order, so neither can win.

Step 3: Break the tie, then keep one row

The top amount, 1784.5, appears on two lines. Both are 'EC2' on this seed, but nothing in the data promises that, so svc_name ASC decides which line wins and LIMIT 1 keeps it. Without the second key the engine may return either tied row.

SELECT svc_name, amount
FROM cloud_costs
ORDER BY amount DESC, svc_name ASC
LIMIT 1

Computing the largest value

SELECT MAX(amount) FROM cloud_costs returns 1784.5 and nothing else. Putting svc_name beside it errors in Postgres and most warehouses; SQLite happens to fill it from the max row, a quirk no reviewer should have to know. Adding GROUP BY svc_name returns one maximum per service: ten rows here.

Retrieving the largest row

ORDER BY amount DESC, svc_name ASC LIMIT 1 never breaks the row apart, so svc_name and amount arrive together from one entry.

Common follow-up questions

• What if the analyst wants every entry tied at the top, not just one? (Tests swapping the row cap for a filter against the maximum, or a ranking that keeps ties.)
• How would you return the largest entry per `provider`, given that the column mixes 'aws' and 'AWS'? (Tests scoping the argmax per group and normalizing case before grouping.)
• Should a negative `amount` (a credit) ever change this answer, and when would it start to matter? (Tests reasoning about credits netting against charges once the question moves to totals.)