Where the Money Goes

A hard SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

hard

Seniority

mid

Asked in

PwC

Problem

Finance is auditing cloud spend, and the provider names arrive inconsistently cased across the billing exports, so the same vendor shows up under several spellings. For each provider, find the single service with the highest total cost, and show those winners with the biggest spend first.

Every provider has one service quietly eating the budget. Find it.

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

This is a top-one-per-group query wearing a finance costume, and the whole thing hinges on a detail that never gets said out loud: 'aws' and 'AWS' are the same vendor. Anyone can sum amount by service. What separates candidates is whether they unify the provider casing BEFORE they group, and whether they reach for a partitioned row number instead of trying to bolt the per-provider maximum onto a plain GROUP BY. Skip the normalization and you split one provider into two ledgers, each with its own fake 'top service'. Skip the partitioning and you cannot get the winning service name back out of an aggregate.

Break down the requirements

Step 1: Unify the vendor before anything else

provider holds 'aws', 'AWS', 'gcp', 'GCP', 'Azure'. Group on LOWER(provider) so every spelling of a vendor collapses into one bucket. Do this in the first pass, not after, or the sums are already wrong by the time you rank.

Step 2: Total spend at provider-plus-service grain

SUM(amount) grouped by LOWER(provider) and svc_name gives you one row per vendor-service pair. This is the grain the question actually cares about; the raw billing lines are noise until they are rolled up here.

Step 3: Keep only each provider's leader

ROW_NUMBER() OVER (PARTITION BY provider ORDER BY total_spend DESC, svc_name) numbers services within each provider by spend. Filter to rn = 1. The svc_name tiebreaker makes the winner deterministic when two services spend the same, so the graded result is stable.

Step 4: Order the winners

ORDER BY total_spend DESC puts the heaviest line item at the top, so the biggest single cost driver across all providers reads first.

The solution

WITH provider_service AS (
  SELECT LOWER(provider) AS provider,
         svc_name,
         SUM(amount) AS total_spend
  FROM cloud_costs
  GROUP BY LOWER(provider), svc_name
),
ranked AS (
  SELECT provider,
         svc_name,
         total_spend,
         ROW_NUMBER() OVER (PARTITION BY provider ORDER BY total_spend DESC, svc_name) AS rn
  FROM provider_service
)
SELECT provider,
       svc_name AS top_service,
       total_spend
FROM ranked
WHERE rn = 1
ORDER BY total_spend DESC

Common follow-up questions

• Return the top TWO services per provider instead of just one. (Tests whether they understand `ROW_NUMBER` generalizes: change the filter to rn <= 2. Also opens the RANK vs `ROW_NUMBER` tie-handling discussion.)
• Scope this to a single billing year and explain how partition pruning helps. (Pushes a `bill_date` predicate onto the base scan of a date-partitioned fact table, and checks they know the filter must land before the aggregation.)
• What if region should also be normalized and the answer is top service per provider per region? (Extends the partition key and re-raises the casing-hygiene question for a second dimension.)