Highest Cost Per Team

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

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Forbes

Problem

Our peak-allocation report lists 'platform' and 'Platform' as two different teams, so one team's biggest charge ends up split across two rows. From cost_allocs, give each team's largest single allocation amount, one row per team, biggest peak first.

Peak cost, team by team.

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

This is a per-entity maximum with a dirty key. Anyone can write MAX(amount). What the problem actually probes is whether you notice that team_name stores one team under several capitalizations ('platform' and 'Platform', 'data-eng' and 'DATA-ENG') and fold them before you aggregate. Group on the raw column and you get 16 rows for 14 teams: two teams appear twice, and the duplicate with the smaller peak reads like a separate team's number.

Walking to the answer

Step 1: Look at the keys before the metric

Scan the values of team_name first. The sample rows already show 'Platform' and 'platform' side by side, and the full table adds 'DATA-ENG' next to 'data-eng'. Every other team has a single spelling, which is exactly why the bug hides: 12 of the 14 teams look fine.

Step 2: Group on the folded name

Put LOWER(team_name) in the GROUP BY, not just in the SELECT. The grouping key decides which rows compete for one maximum. The selected label only decides what gets printed.

Step 3: Take the peak, then sort by it

MAX(amount) per folded team is that team's single largest allocation. Sorting on max_amount descending puts the biggest peaks on top, which is the order the preview shows.

The solution

SELECT LOWER(team_name) AS team_name, MAX(amount) AS max_amount FROM cost_allocs GROUP BY LOWER(team_name) ORDER BY max_amount DESC

Raw label

GROUP BY team_name produces 16 groups. 'platform' and 'Platform' each get their own MAX(amount), so the team shows up twice with two different peaks.

Folded label

GROUP BY LOWER(team_name) produces 14 groups. Every row of a team competes for one maximum, and the team appears once with its true peak.

Common follow-up questions

• What changes if some `team_name` values also carry trailing spaces? (Tests whether the normalization stacks: trimming and case folding together.)
• Cost owners now want the service behind each peak. How do you return the `svc_name` of the allocation that set the maximum? (Moves from an aggregate to picking a whole row per group, including what happens on a tied peak.)
• `cost_allocs` contains a credit of -120.5. If you needed the largest movement in either direction, what would you change? (Tests reading the sign of the metric: a maximum of the absolute value, not of the raw amount.)
• How would you display each team under the capitalization most of its rows use, instead of all lowercase? (Tests separating the grouping key from the display label.)