Under the Line

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

Walmart

Problem

A cloud FinOps platform records cost allocations against a team and a service, with many allocations per team. Find each team's total spend and its average spend across its services, keeping only teams whose total spend is under twice the average team total. The same team sometimes appears under different letter casing, so treat those as one team and list each once.

Twice the average is the ceiling. Find the teams comfortably beneath it.

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

Beneath the FinOps costume this is a two-grain rollup with a global yardstick, and it hides a landmine. You are asked for two numbers per team: a total, and an average across the team's services. The total is a plain sum, but the average is NOT the mean of the raw allocation rows: it is the mean of each service's summed spend, so you have to collapse to one figure per service first. Then every team's total is measured against a single scalar, twice the average team total. The landmine: the same team shows up under different casing, and if you group on the raw name you split one team into several, which poisons both its totals and the global average that gates everyone else.

Walk it

Step 1: Collapse to per-service spend

Sum amount over the normalized team and the service. This yields one row per (team, service) and is the grain the service average is defined on. Skip it and average amount directly and you have answered a different question: the mean allocation size, not the mean service spend.

Step 2: Roll up to per-team

Over the per-service rows, total_spend is the sum of the service spends and avg_svc_spend is their average. Because casing was normalized in step 1, each real team appears exactly once here, so both figures are honest.

Step 3: Compare to the global yardstick

Take AVG(total_spend) across the team-level rows, each team weighted once, and keep teams strictly under twice that value. The scalar subquery re-reads the team CTE, but that is a few dozen rows, so its cost is negligible.

The solution

WITH svc AS (
    SELECT
        LOWER(team_name) AS team_name,
        svc_name,
        SUM(amount) AS svc_spend
    FROM cost_allocs
    GROUP BY LOWER(team_name), svc_name
),
team AS (
    SELECT
        team_name,
        SUM(svc_spend) AS total_spend,
        AVG(svc_spend) AS avg_svc_spend
    FROM svc
    GROUP BY team_name
)
SELECT
    team_name,
    total_spend,
    avg_svc_spend
FROM team
WHERE total_spend < 2 * (SELECT AVG(total_spend) FROM team)

Naive: one pass

SELECT team_name, SUM(amount), AVG(amount) GROUP BY team_name. The average becomes the mean allocation amount, and mixed casing splits teams. Both the average column and the global threshold come out wrong.

Correct: two grains

Sum to per-service first, then average those sums per team, on a normalized team name. The average now means average spend per service, and each team is counted once in the global comparison.

Common follow-up questions

• How would you report the original casing (say, the most common spelling) instead of a lowercased name? (Tests MIN/MAX or mode selection alongside a normalized grouping key.)
• Could you replace the scalar subquery with a window function? (Probes AVG() OVER () to inline the global average.)
• The threshold is twice the average. How would you make that multiplier a parameter without editing the query text? (Tests parameterization and bind variables.)
• `cost_allocs` reaches 1B rows. What physical design keeps the first rollup fast? (Probes partitioning on period and covering indexes for the aggregation.)