Top Cost Categories

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

Uber

Problem

The cloud spend per budget category in finance's monthly report comes out far above what cloud_costs actually billed, because categories live only on the allocation rows in cost_allocs, where several teams can book the same service, region and month. Each charge counts once, under the category its service and region were allocated to for its billing month (the year-month of bill_date, which is what period holds). List the 3 categories with the most attributed spend, largest first.

Three categories eating the budget.

Tables

cloud_costs

Columns: cost_id INTEGER, provider TEXT, svc_name TEXT, region TEXT, amount REAL, acct_id TEXT, bill_date TEXT

cost_allocs

Columns: alloc_id INTEGER, team_name TEXT, svc_name TEXT, region TEXT, amount REAL, period TEXT, acct_id TEXT, category TEXT

Expected output

Worked solution and explanation

What this really is

This is an attribution join whose lookup side is not one row per key, dressed up as a finance report. Anyone can match cloud_costs to cost_allocs on service, region and month. What separates candidates is noticing that cost_allocs books the same (svc_name, region, period) under several teams, so the raw join hands a charge two or three partner rows and SUM(cc.amount) counts it once per partner. On this seed that is not a rounding error: 'storage' more than doubles and jumps to first place, ahead of 'ml'.

Walking to the answer

Step 1: Pin down the attribution key

Category lives only on cost_allocs, and (svc_name, region) alone is not enough: S3 in 'us-west-2' is 'storage' in one month and 'database' or 'other' in others. Add the billing month and every (svc_name, region, period) points at exactly one category.

Step 2: Align the time grain

cloud_costs.bill_date is a full date and cost_allocs.period is a year-month string. Match them with strftime('%Y-%m', cc.bill_date) = m.period, so each charge is attributed by the allocation for its own month.

Step 3: Collapse the map before joining

Rows sharing a (svc_name, region, period) differ only in team_name and amount, never in category. SELECT DISTINCT svc_name, region, period, category turns them into one map row per key, so each charge finds exactly one partner and its cc.amount lands once.

Step 4: Sum billed spend, largest first

Take SUM(cc.amount) per category, then ORDER BY total_amount DESC LIMIT 3. The money is cloud_costs.amount, what was billed; cost_allocs.amount is the budget.

WITH cat_map AS (
  SELECT DISTINCT svc_name, region, period, category
  FROM cost_allocs
)
SELECT m.category,
       SUM(cc.amount) AS total_amount
FROM cloud_costs cc
JOIN cat_map m
  ON cc.svc_name = m.svc_name
 AND cc.region   = m.region
 AND strftime('%Y-%m', cc.bill_date) = m.period
GROUP BY m.category
ORDER BY total_amount DESC
LIMIT 3;

Raw allocation rows

Each charge matches every team's row for its service, region and month. Totals come out 'storage' 7662.57, 'ml' 7108.34, 'network' 6502.12: 'storage' leads only because its largest charge matched three allocation rows.

Collapsed category map

One map row per (svc_name, region, period). Totals come out 'ml' 4215.38, 'network' 3716.14, 'storage' 3286.72, with every billed amount counted once.

Common follow-up questions

Common follow-up questions

• What if two teams book the same service, region and month under different categories? (The `DISTINCT` map would hand a charge two categories, so you need a rule (largest `amount`, or a split by allocation share) and a way to keep one row per key.)
• Scope this to Q1 only. Where do the time predicates go? (Filter `bill_date` and `period` directly so both sides prune, rather than filtering the derived month.)
• What if a `cloud_costs` row has no allocation for its month? (A `LEFT JOIN` plus `COALESCE(m.category, 'unallocated')` keeps that spend visible instead of dropping it.)
• Return the top 3 categories per region instead of overall. (Aggregate per region and category, then keep `ROW_NUMBER() OVER (PARTITION BY region ORDER BY total_amount DESC) <= 3`.)