Node Utilization

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

L4

Problem

Which teams carry the biggest net cloud spend in each region of cost_allocs, and what is each team's average allocation there in whole dollars with the cents dropped? A team is one team however its name is capitalized, reported in lowercase, and a team whose credits leave its net at zero or below is left out. A team's standing in its region is one more than the number of teams there with a bigger net total.

Overloaded nodes hiding in busy regions. Spot the hot spots.

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

This is a per-region leaderboard dressed up as a chargeback report, with a dirty key hiding under it. Anyone can sum amount by team and region. Two things separate candidates. First, team_name is stored in more than one case, so grouping it raw splits 'data-eng' and 'DATA-ENG' into two half-teams, each with an understated total and a standing that is too low. Second, the standing has to restart in every region and let tied teams share a spot, with the team below a tie skipping past it. That is RANK() and nothing else. The quiet third trap is the average: whole-dollar allocations average out to fractions, and the statement wants the cents dropped, not rounded.

Build it in two passes

Step 1: Fold the team name before you group

Group on LOWER(team_name), not the stored column, and select the same expression as team_name. Fold the key where the grouping happens; cleaning it afterwards is too late, because the totals were already split.

Step 2: Net the spend and drop the wiped-out teams

SUM(amount) per team and region is a net, because credits are negative lines. HAVING SUM(amount) > 0 drops the teams whose credits wiped them out; it sits in HAVING because it reads an aggregate, not a row.

Step 3: Truncate the average allocation

AVG(amount) is fractional even when every allocation is whole dollars. CAST(AVG(amount) AS INTEGER) truncates toward zero, which is exactly cents dropped.

Step 4: Rank inside each region on the net

In the outer query, RANK() OVER (PARTITION BY region ORDER BY total_amount DESC) restarts at 1 in each region, gives equal nets the same standing, and leaves the gap the statement's definition asks for.

WITH team_totals AS (
  SELECT LOWER(team_name) AS team_name,
         region,
         SUM(amount) AS total_amount,
         CAST(AVG(amount) AS INTEGER) AS avg_alloc_dollars
  FROM cost_allocs
  GROUP BY LOWER(team_name), region
  HAVING SUM(amount) > 0
)
SELECT team_name,
       region,
       total_amount,
       avg_alloc_dollars,
       RANK() OVER (PARTITION BY region ORDER BY total_amount DESC) AS rnk
FROM team_totals

The definition in the statement

RANK() on totals 900, 700, 700, 400 gives 1, 2, 2, 4. The 400 team sits fourth because three teams beat it, which matches "one more than the number of teams with a bigger total".

The two near misses

DENSE_RANK() gives 1, 2, 2, 3, which undercounts the teams above the 400 team. ROW_NUMBER() gives 1, 2, 3, 4 and splits the tie arbitrarily, so the result can change between runs.

Common follow-up questions

• How would you return only the top three standings per region, and how many rows can a region contribute? (Tests filtering on a window result: wrap the ranked rows and keep `rnk <= 3`, which can return more than three teams when a tie straddles the cut.)
• If new spellings keep showing up in `team_name`, where would you fix it for good? (Tests where cleaning belongs in production: a canonical team dimension or a cleaned column at ingestion, instead of `LOWER` in every query.)
• If finance only wanted one billing `period`, where does that filter go and what does it save? (Tests partition pruning: a filter on `period` inside the CTE scans one of 36 partitions.)