# Below the Peaks > The biggest bills catch every eye. The overspend hiding just beneath them does not. Canonical URL: 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. ## 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 > **Do not average the survivors** > > If you compute the team average AFTER removing the top five, you have poisoned the number you are comparing against. The five biggest allocations pull the mean up; drop them and every remaining row looks larger relative to a smaller average, so rows that should fail the above-average test slip through. Compute AVG over the full team in its own CTE, untouched by the rank filter, and only then compare. > **The off-by-one on the rank** > > Outside the top five means rn > 5, which keeps rows 6, 7, 8 and beyond. The classic slip is rn >= 5, which readmits the fifth-biggest allocation. ROW_NUMBER starts at 1, so the fifth row carries rn = 5 and must be excluded, not kept. #### 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. **Above the mean, below the top five** ```sql 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 ``` *Two CTEs, one join: the mean is computed over the full team, the rank threshold excludes the top five.* > **The window-only shortcut** > > The senior move is realizing both conditions are per-team window computations, so you can skip the GROUP BY and the join entirely. AVG(amount) OVER (PARTITION BY team_name) hands you the team mean on every row alongside the ROW_NUMBER, and you filter both in one outer query over a single scan. Reaching for the join is fine and fully correct; naming the window-only alternative unprompted is the tell that you actually understand what PARTITION BY buys you. **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. > **Cost at scale** > > On millions of rows the window-only form does a single partition sort per team and one scan; the GROUP BY plus join does two scans and a hash join. Both are close to linear once the data is sorted. An index or pre-sort on (team_name, amount) makes the ROW_NUMBER partition sort nearly free and lets the planner stream both predicates. ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/below_the_peaks) - [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.