Below the Peaks

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

L5

Problem

Finance is auditing outlier allocations within each team. Surface the allocations that land above their team's own average yet outside that team's five biggest by amount, and return the team name and the amount.

The biggest bills catch every eye. The overspend hiding just beneath them does not.

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

Strip the finance costume off and this is a per-team double filter: keep the rows a team spent above its own average, then discard that team's five biggest. The two conditions look like siblings, but they are computed over different populations, and that is exactly where candidates fall in. Anyone can write an AVG and a ROW_NUMBER. The tell is whether your average is taken over the WHOLE team before any rows are dropped, and whether your rank threshold lands on rn > 5 rather than rn >= 5.

The trap: what the average is averaged over

Step 1: Average the whole team, exactly once

Build a team_avg CTE that groups by team_name and takes AVG(amount) across every allocation the team has. Keeping this in its own scope guarantees the mean is computed over the full population, so no later filter can distort it. This is the number every candidate row will be measured against.

Step 2: Number each team's rows from the top

In a second CTE, assign ROW_NUMBER() OVER (PARTITION BY team_name ORDER BY amount DESC). Partitioning restarts the count for each team, and ordering by amount descending puts the biggest allocation at rn = 1. Now 'outside the top five' is just a predicate on rn.

Step 3: Join, then apply both filters

Join the numbered rows back to team_avg on team_name so every row carries its own team's mean, then keep rows where amount is above that mean AND rn is greater than 5. Both conditions are independent predicates on the same row, ANDed together: pass both to survive.

WITH team_avg AS (
  SELECT team_name, AVG(amount) AS avg_amount
  FROM cost_allocs
  GROUP BY team_name
),
ranked AS (
  SELECT ca.team_name, ca.amount,
         ROW_NUMBER() OVER (PARTITION BY ca.team_name ORDER BY ca.amount DESC) AS rn
  FROM cost_allocs ca
)
SELECT r.team_name, r.amount
FROM ranked r
INNER JOIN team_avg ta ON r.team_name = ta.team_name
WHERE r.amount > ta.avg_amount AND r.rn > 5

GROUP BY plus join (canonical)

Aggregate team averages in one CTE, number rows in another, then join on team_name and filter. Clear, explicit, and easy to reason about; costs two scans and a hash join.

Two window functions, no join

SELECT team_name, amount FROM (SELECT team_name, amount, AVG(amount) OVER (PARTITION BY team_name) AS team_avg, ROW_NUMBER() OVER (PARTITION BY team_name ORDER BY amount DESC) AS rn FROM cost_allocs) t WHERE amount > team_avg AND rn > 5. One scan, both per-team values computed inline, no join to maintain.

Common follow-up questions

• If two allocations tie on amount right at the fifth position, which one is 'outside the top five'? (Probes `ROW_NUMBER` versus RANK/`DENSE_RANK` and whether ties should share a position or be broken arbitrarily.)
• Team names arrive with inconsistent casing, like data-eng and DATA-ENG. Should those collapse into one team? (Tests whether the candidate spots that grouping on the raw column keeps them separate and when to normalize with LOWER(`team_name`).)
• How would you tighten the rule to only flag allocations at least 20 percent above the team average? (Parameterizing the threshold: amount > `avg_amount` * 1.2 rather than a bare comparison.)