The Floor Price

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

Google

Problem

Ahead of vendor negotiations, the FinOps team wants each cloud provider's single largest charge. Some line items in cloud_costs post with no billing account attached and can't be tied to a contract, so leave those out and return each provider with that peak charge.

Before the negotiation, find what each provider really charges at its cheapest.

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 is really testing

This looks like a one-line MAX-per-group warmup, and that is exactly the trap. Two things in the data quietly decide the answer: the provider label is recorded in mixed case (AWS and aws are the same vendor), and some large charges post with no billing account attached. A candidate who writes the obvious MAX(amount) grouped by the raw provider splits each vendor in two and lets an unattributable cost win GCP's peak, a wrong answer that still looks plausible, which is the worst kind in an interview.

The two decisions that separate answers

Step 1: Normalize the grain

provider arrives as AWS, aws, GCP, gcp. Group on the raw string and each vendor splits into two rows, fragmenting the peak. Fold it with UPPER(provider) in both the SELECT and the GROUP BY so each vendor is exactly one row.

Step 2: Drop what you can't attribute

A charge with no acct_id is unallocated spend: an org-level discount or an untagged fee, not a line item any account owner will negotiate. Restrict to acct_id IS NOT NULL so one of those cannot become the reported ceiling. On this data it is exactly the largest GCP row, 1766.67, that has to go.

Step 3: Take the peak

With the noise filtered and the key folded, MAX(amount) per group is the single largest attributable charge each provider ever billed.

The solution

SELECT UPPER(provider) AS provider, MAX(amount) AS max_amount
FROM cloud_costs
WHERE acct_id IS NOT NULL
GROUP BY UPPER(provider)

Naive (wrong)

SELECT provider, MAX(amount) FROM cloud_costs GROUP BY provider. Splits AWS into AWS and aws, and hands GCP a peak of 1766.67 that came from a row with no acct_id, a cost no contract can touch. Looks clean, fails grading.

Correct

Fold the key with UPPER(provider) and filter acct_id IS NOT NULL, so GCP's peak is the real attributable 1713.18 and each vendor is one row. Same shape, two predicates that change the meaning.

Common follow-up questions

• Now return the service (`svc_name`) that incurred each provider's peak attributable charge, not just the amount. (Tests whether they reach for a window function or a correlated subquery instead of a second pass.)
• Finance wants the peak per provider per month. How would you group by provider and the month of `bill_date`? (Tests aggregation over a derived time grain and reuse of the same filtering discipline.)
• If unattributed line items should instead be bucketed under a single 'unallocated' provider rather than dropped, how does your query change? (Tests handling nulls as a category with COALESCE rather than excluding them.)