Cheapest Line for Network-Heavy Teams

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

L4

Asked in

City and County of San Francisco

Problem

List every team in cost_allocs whose total networking spend outweighs its total ML spend, alongside the smallest single allocation that team carries in any category, leanest first. The FinOps analyst chasing these quick wins knows the same team is logged under more than one capitalization, and those spellings are one team.

Among the network spenders, the smallest single line.

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 one grouping that has to read the same rows two ways, dressed up as a FinOps review. Each team qualifies on two slices (network against ML) and then reports a figure over all of its rows (its smallest line of any kind). Anyone can total a category. What separates candidates is slicing inside the aggregates instead of the table: filter the rows down to 'network' and 'ml' and the comparison still works, but the minimum silently loses every storage, compute and database line. The second trap is identity: 'Platform' and 'platform' sit side by side in team_name, so total on the raw name and one team becomes two, each judged on half its spend.

Slice the table

WHERE category IN ('network', 'ml') before the GROUP BY. The totals are right, but MIN(amount) can only see those two categories, so it reports a line that is not the team's smallest.

Slice inside the aggregates

Keep every row. A CASE inside each SUM picks its own category, and MIN(amount) sees the whole team in the same grouping.

SELECT team_name, MIN(amount) AS amount
FROM cost_allocs
WHERE category IN ('network', 'ml')
GROUP BY team_name
HAVING SUM(CASE WHEN category = 'network' THEN amount ELSE 0 END)
     > SUM(CASE WHEN category = 'ml' THEN amount ELSE 0 END)
ORDER BY amount ASC

Build it in one pass

Step 1: Settle the team identity first

Lowercase team_name in an allocs CTE so nothing downstream ever sees the raw spelling. Doing it once means the totals, the minimum and the output can never disagree about who a team is.

Step 2: Total network and ML side by side

SUM(CASE WHEN category = 'network' THEN amount ELSE 0 END) and the same for 'ml'. The ELSE 0 matters: a team with no ML rows needs an ML total of zero, because network_total > NULL is never true and the team would vanish.

Step 3: Take the smallest line across everything

A bare MIN(amount) in the same GROUP BY. Because the table was never filtered, it ranges over every category the team spends on.

Step 4: Keep the network-heavy teams, leanest first

WHERE network_total > ml_total on the aggregated CTE, strict because outweighs means more, then ORDER BY amount ASC so the leanest line leads.

The solution

WITH allocs AS (
    SELECT LOWER(team_name) AS team_name, category, amount
    FROM cost_allocs
),
team_spend AS (
    SELECT
        team_name,
        SUM(CASE WHEN category = 'network' THEN amount ELSE 0 END) AS network_total,
        SUM(CASE WHEN category = 'ml' THEN amount ELSE 0 END) AS ml_total,
        MIN(amount) AS smallest_line
    FROM allocs
    GROUP BY team_name
)
SELECT team_name, smallest_line AS amount
FROM team_spend
WHERE network_total > ml_total
ORDER BY amount ASC

Common follow-up questions

• How would you scope the whole review to a single `period`? (Filter `period` in the `allocs` CTE so the totals and the minimum share one window, which also prunes partitions on the partition key.)
• Finance now wants each team's smallest networking line instead. What changes? (Replace `MIN(amount)` with `MIN(CASE WHEN category = 'network' THEN amount END)`: a third slice, still in the same grouping.)
• Finance also wants the allocation id of each smallest line. How does the query change? (Tests whether you return the line's `alloc_id` by ranking rows per team, since `MIN` alone cannot say which row it came from.)