# Under the Line

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

Canonical URL: <https://datadriven.io/problems/under_the_line>

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, smallest total first.

## Tables

### cost_allocs

- alloc_id INTEGER
- team_name TEXT
- svc_name TEXT
- region TEXT
- amount REAL
- period TEXT
- acct_id TEXT
- category TEXT

Sample rows (10 of 200):

| alloc_id | team_name | svc_name | region | amount | period | acct_id | category |
| --- | --- | --- | --- | --- | --- | --- | --- |
| 129 | data-eng | S3 | us-west-2 | 83.21 | 2026-02 | acct-101 | storage |
| 158 | ml-team | RDS | eu-west-1 | 156.42 | 2026-03 | acct-102 | network |
| 187 | frontend | Lambda | ap-south-1 | 229.63 | 2026-04 | acct-103 | database |
| 216 | backend | BigQuery | us-central1 | -120.5 | 2026-05 | acct-104 | ml |
| 245 | devops | CloudFront | europe-west1 | 376.05 | 2026-06 | acct-105 | other |
| 274 | security | EKS | ap-southeast-1 | 449.26 | 2026-07 | acct-106 | compute |
| 303 | mobile | SQS | eu-central-1 | 522.47 | 2026-08 | acct-107 | storage |
| 332 | Platform | EC2 | us-east-1 | 595.68 | 2026-09 | NULL | network |
| 361 | DATA-ENG | S3 | us-west-2 | 668.89 | 2026-01 | acct-109 | database |
| 390 | platform | RDS | eu-west-1 | 742.1 | 2026-02 | acct-110 | ml |

## Expected output

First 5 of 12 rows:

| team_name | total_spend | avg_svc_spend |
| --- | --- | --- |
| legal | 100 | 100 |
| finance | 150 | 150 |
| people | 200 | 200 |
| design | 250 | 250 |
| marketing | 300 | 300 |

## 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.

> **Trick to solving**
>
> Two grains, then one scalar. First fold to per-service spend with a sum over (team, service). Then fold again to per-team, where the total is the sum of those service spends and the service average is their mean. Only after that do you compare each team total to twice the average of all team totals.

> **The casing landmine**
>
> Allocations arrive as 'data-eng', 'DATA-ENG', and 'Data-Eng'. Group on the raw string and one team fractures into three, each holding a slice of the real spend. Worse, the global average is then taken over these phantom teams, so the twice-average threshold shifts and admits or drops the wrong teams. Normalize the name (lowercase it) before either rollup.

---

### 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

**Normalize, roll up twice, then compare against the global average**

```sql
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)
ORDER BY total_spend, team_name
```

> **Interviewers watch for**
>
> The tell is whether you notice the two grains and the casing. Candidates who write AVG(amount) in the same pass as SUM(amount) have quietly answered a different question and rarely catch it. The senior move is to name the service grain out loud and to ask whether mixed casing should be folded before writing a single line.

> **Why it stays cheap**
>
> One pass over the 15M-row table builds the per-service CTE; everything after works on a few hundred rows. The scalar subquery re-scans the team CTE, but that is roughly 50 rows, so the plan is dominated by the single aggregating scan. A covering index on (`team_name`, `svc_name`, amount) lets the first rollup skip the heap entirely.

**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.)_

## Related

- [All practice problems](https://datadriven.io/problems)
- [Mock interview mode](https://datadriven.io/interview/under_the_line)
- [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.