# The Floor Price > Before the negotiation, find what each provider really charges at its cheapest. Canonical URL: 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. ## Tables ### cloud_costs - cost_id INTEGER - provider TEXT - svc_name TEXT - region TEXT - amount REAL - acct_id TEXT - bill_date TEXT Sample rows (10 of 200): | cost_id | provider | svc_name | region | amount | acct_id | bill_date | | --- | --- | --- | --- | --- | --- | --- | | 543 | GCP | S3 | us-west-2 | 19.33 | acct-1001 | 2026-02-01 | | 586 | Azure | RDS | eu-west-1 | 37.16 | acct-1002 | 2026-03-01 | | 629 | aws | Lambda | ap-southeast-1 | 54.99 | acct-1003 | 2026-04-01 | | 672 | gcp | BigQuery | us-central1 | 72.82 | acct-1004 | 2026-05-01 | | 715 | AWS | Cloud Run | europe-west1 | 90.65 | acct-1005 | 2026-06-01 | | 758 | GCP | Blob Storage | us-east-1 | 108.48 | acct-1006 | 2026-07-01 | | 801 | Azure | Cosmos DB | us-west-2 | 126.31 | acct-1007 | 2026-08-01 | | 844 | aws | CloudFront | eu-west-1 | 144.14 | acct-1008 | 2026-09-01 | | 887 | gcp | Pub/Sub | ap-southeast-1 | 161.97 | NULL | 2026-01-01 | | 930 | AWS | EC2 | us-central1 | 179.8 | acct-1010 | 2026-02-01 | ## Expected output First 3 of 3 rows: | provider | max_amount | | --- | --- | | AWS | 1784.5 | | AZURE | 1731.01 | | GCP | 1713.18 | ## 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 peak hides where you can't see it** > > The preview shows folded, uppercased vendors (`AWS`, `AZURE`, `GCP`), which tells you to normalize the label. The subtler catch is not in the preview at all: the biggest GCP charge posts with no `acct_id`, so a plain `MAX` anchors on a cost nobody can attribute. Filter to rows that carry an account before you aggregate. ### 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 **Max attributable charge per provider** ```sql SELECT UPPER(provider) AS provider, MAX(amount) AS max_amount FROM cloud_costs WHERE acct_id IS NOT NULL GROUP BY UPPER(provider) ``` *Filter out the unattributed rows, fold the group key, then aggregate.* **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. > **One scan, one aggregate** > > The whole plan is a single scan of the 12M rows: filter out the null-account rows, then hash-aggregate on a 3-value folded key. No join, no subquery, no window. `MAX` needs one pass and the group key is tiny, so this stays cheap no matter how the table grows. > **Catch the dirty data before they mention it** > > What separates a senior answer is spotting both problems unprompted: the case-variant vendor labels and the unattributed line items. Say out loud that you are folding case and dropping rows with no `acct_id`, and you have shown the judgment the question is really probing. > **Unallocated spend is real** > > Cloud bills routinely carry line items with no account: org-level committed-use discounts, shared support fees, untagged spend. Let one of those win a `MAX` and your negotiation ceiling is a number no account owner can be held to. ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_floor_price) - [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.