Team Cost Allocation Comparison

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

L5

Asked in

Walmart

Problem

How does each tracked allocation in cost_allocs, meaning one that carries a billing account, stack up against the largest untracked (no-account) allocation on its team and against that team's average tracked allocation? A FinOps analyst wants the untracked peak beside every tracked amount as a leakage benchmark, teams in alphabetical order with each team's biggest tracked spenders first. Team names count exactly as entered, so names that differ only in capitalization are separate teams.

Individual spend versus team average.

Tables

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

This is a two-slice enrichment dressed up as a FinOps audit. Every tracked allocation keeps its own row and picks up two team figures read from opposite halves of the same table: the biggest bill with no acct_id, and the average of the bills that have one. Anyone can write a MAX and an AVG. What separates candidates is splitting on the null account the right way round for each figure, and noticing that six one-allocation teams such as 'legal' and 'finance' never left a bill unclaimed. Attach the benchmark with an inner match and those teams fall out of the audit: 173 rows instead of 179, with no error to warn you.

Building it in stages

Step 1: Isolate the untracked benchmark

Compute the per-team peak first, in its own CTE: MAX(amount) over the no-account rows, by team_name. On this seed that is 21 rows spread over the ten original teams. The six small teams never appear in untracked_peak, and that absence is the whole reason step 2 exists.

Step 2: Attach it without losing a team

Bring untracked_peak back with a LEFT JOIN on team_name. A team missing from the CTE keeps its tracked rows with an empty peak_untracked. That empty value is the honest answer: there is no leakage to benchmark against.

Step 3: Put the average on every row

AVG(ca.amount) OVER (PARTITION BY ca.team_name) repeats the team mean on each allocation instead of folding the rows away. Because WHERE ca.acct_id IS NOT NULL runs before the window, untracked rows never enter the mean. The -120.5 credit on 'backend' does, since it carries an account.

Step 4: Order teams, then spenders

Sort by team_name ascending, then amount DESC. Names compare exactly as stored, so 'DATA-ENG' sorts ahead of 'data-eng' and the two stay separate blocks, as the statement asks.

The solution

WITH untracked_peak AS (
    SELECT team_name, MAX(amount) AS peak_untracked
    FROM cost_allocs
    WHERE acct_id IS NULL
    GROUP BY team_name
)
SELECT ca.team_name,
       ca.svc_name,
       ca.amount,
       up.peak_untracked,
       AVG(ca.amount) OVER (PARTITION BY ca.team_name) AS team_avg
FROM cost_allocs ca
LEFT JOIN untracked_peak up ON ca.team_name = up.team_name
WHERE ca.acct_id IS NOT NULL
ORDER BY ca.team_name, ca.amount DESC

Inner match on the benchmark

JOIN untracked_peak keeps only teams that leaked something. The six small teams lose their tracked rows and the result quietly drops to 173 rows.

Left match on the benchmark

LEFT JOIN untracked_peak keeps all 179 tracked allocations; teams with no untracked spend show an empty peak_untracked.

Common follow-up questions

• A team has tracked spend but no untracked allocations at all. What should its benchmark column show, and how does your query guarantee the row survives? (Tests whether the candidate keeps the row and represents the empty benchmark rather than dropping the team.)
• Why is the benchmark built from the null-account rows while the report keeps only the account-bearing rows, and what breaks if you build both from the same slice? (Tests understanding that the two halves read opposite null conditions on the same column.)
• How would you extend this to flag whether each tracked allocation sits above or below the team median instead of the mean? (Tests knowledge that median is not a built-in aggregate in most engines and needs `PERCENTILE_CONT` or a manual approach.)