# Cheapest Line for Network-Heavy Teams > Among the network spenders, the smallest single line. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L4 ## Problem Among teams that spend more on networking than on ML in total, find the single lowest network allocation amount. Team names are compared case-insensitively. Show the team name and that amount. ## Worked solution and explanation ### Why this problem exists in real interviews FinOps reviews routinely surface 'teams whose network spend exceeds their ML spend, then drill into the smallest line item to find quick wins.' Interviewers use this two-stage problem to test whether you can compute conditional sums in one pass to qualify teams, then re-query the base table filtered to those teams to pick the single cheapest row with a deterministic tiebreak. --- ### Break down the requirements #### Step 1: Sum network and ML spend per team in one pass Per team (case-insensitive), total the network spend and the ML spend with conditional SUM. #### Step 2: Keep teams where network strictly exceeds ML Keep teams whose total network spend exceeds their total ML spend. #### Step 3: Pick the smallest network row, tiebreak by alloc_id Among those teams' network allocations, return the single lowest amount. --- ### The solution **Conditional aggregate, qualifier filter, smallest network row** ```sql WITH team_cat AS (SELECT LOWER(team_name) AS team, SUM(CASE WHEN category='network' THEN amount ELSE 0 END) AS net, SUM(CASE WHEN category='ml' THEN amount ELSE 0 END) AS ml FROM cost_allocs GROUP BY LOWER(team_name)), heavy AS (SELECT team FROM team_cat WHERE net > ml) SELECT LOWER(ca.team_name) AS team_name, MIN(ca.amount) AS amount FROM cost_allocs ca JOIN heavy h ON LOWER(ca.team_name)=h.team WHERE ca.category='network' GROUP BY LOWER(ca.team_name) ORDER BY amount ASC LIMIT 1 ``` > **Cost Analysis** > > `cost_allocs` is 15M rows partitioned by `period` across 36 monthly partitions. The `totals` CTE does one full scan and hash-aggregates into 50 team rows. The second scan is also full but emits only the network slice (10 distinct categories, so roughly 1.5M rows survive the filter), then a top-1 sort over the IN-list members. Two scans of the same 15M table is the cost; a covering index on `(category, team_name, amount, alloc_id)` would let the engine satisfy the second stage as an index-only seek. > **Interviewers Watch For** > > Interviewers watch whether you use conditional aggregation (one pass) versus two separate aggregates and a join (two passes plus a join), whether you remember strict `>` rather than `>=` for the qualification, and whether you include the `alloc_id ASC` tiebreak so two network rows tied at the same amount resolve deterministically. The literal `'network'` (not `'networking'`) is also worth a sanity check against the schema. > **Common Pitfall** > > Hard-coding the wrong category literal is the classic miss here: the prompt says `'network'` and `'ml'`, not `'networking'` or `'machine_learning'`. Either of those typos returns zero rows from the conditional sums, every team's `net_total` is 0, and the qualifier CTE is empty. The other trap is forgetting `alloc_id ASC` in the final `ORDER BY`, which makes the tied-amount case non-deterministic. --- ## Common follow-up questions - How would you return the top three smallest network rows for qualified teams, including ties on amount? _(Tests whether the candidate switches `LIMIT 1` to a windowed `RANK() OVER (ORDER BY amount ASC)` and filters `rank <= 3` so ties at the boundary stay in the result.)_ - How would you scope the qualification to a single `period` (one month) rather than across all history? _(Tests partition-key awareness. Add `WHERE period = '2026-01'` to both the totals CTE and the final SELECT so the qualifier and the picked row share the same time window. Also enables partition pruning on the partition key.)_ - If the categories list grew to include `'storage'` and the prompt asked 'network exceeds the sum of ml plus storage,' how would your CTE change? _(Tests whether the candidate adds a third conditional sum and updates the `WHERE net_total > ml_total + storage_total` predicate without rewriting the join structure.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/cheapest_line_for_network_heavy_teams) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.