# Splitting the Load > The heaviest bills each team carries. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 ## Problem We keep a cost allocation table where each row is one service charge booked to a team. For each team, surface its three highest-cost charges as the team name, a label combining the service and region, and the amount, sorted by team name and then from the largest charge down. ## Worked solution and explanation ### What this really is This is a per-team top-N argmax hiding behind cost reporting: for each team you have to return the three highest-cost rows whole, with their own service and region, not just three numbers. Reach for GROUP BY team_name with MAX(amount) and the aggregation collapses the group, throwing away svc_name and region and only ever surfacing the single top charge, so you can neither reach the second and third tiers nor build the label. The tempting patch, a correlated WHERE amount = (SELECT MAX(amount) ... WHERE team_name = outer.team_name), recovers only the first tier; stretching it to three forces a correlated COUNT(DISTINCT amount) predicate that is gnarlier than the window it is dodging, and a self-join on amount fans out on this seed where round values like 250.00 repeat. And ROW_NUMBER caps each team at exactly three rows, so a team whose third-place cost is shared by two services silently loses one of them. Rank the rows within each team, keep every row in the top three tiers, and each winner stays intact with its own columns and its ties. --- ### Break down the requirements #### Step 1: Rank rows inside each team Rank every row against the others in its own team with DENSE_RANK() OVER (PARTITION BY team_name ORDER BY amount DESC). Partition on the raw team_name so 'data-eng' and 'DATA-ENG' stay separate teams rather than folding together. #### Step 2: Keep the top three cost tiers Filter the ranked rows to rnk <= 3. DENSE_RANK assigns the same rank to every row tied at a cost tier, so this keeps all three tiers AND every tied entry within them, unlike ROW_NUMBER, which would cut the group down to exactly three rows. #### Step 3: Build the output Project team_name, the concatenated label svc_name || ' - ' || region AS entry_label, and amount, then order by team_name, amount descending, and entry_label for a stable, total ordering even when a team has tied entries. --- ### The solution **Rank each team's rows by amount, keep the top three tiers, then build the label** ```sql SELECT team_name, svc_name || ' - ' || region AS entry_label, amount FROM ( SELECT team_name, svc_name, region, amount, DENSE_RANK() OVER (PARTITION BY team_name ORDER BY amount DESC) AS rnk FROM cost_allocs ) ranked WHERE rnk <= 3 ORDER BY team_name, amount DESC, entry_label ``` > **Cost Analysis** > > On 12M rows this is a single pass: the window ranks within each of the ~40 team partitions, then the outer filter keeps the top three tiers. A covering index on (team_name, amount) lets the ranking read in partition order with no separate sort. Crucially there is no self-join, so nothing fans out on the 2M-cardinality amount column, and no correlated re-scan per team. > **Interviewers Watch For** > > Interviewers specifically test whether you use PARTITION BY in the window. Omit it and you get a single global ranking, so LIMIT-style thinking returns the three biggest charges in the whole company instead of three per team, which is a fundamentally different (and wrong) answer. > **Common Pitfall** > > Reaching for ROW_NUMBER() instead of DENSE_RANK(). ROW_NUMBER assigns 1, 2, 3 to exactly three rows per team, so when a team's third-highest cost is shared by two services, one of them silently vanishes. DENSE_RANK gives every row tied at a tier the same rank, so rnk <= 3 keeps all of them. --- ## Common follow-up questions - If two entries share a team's third-highest amount, does ROW_NUMBER quietly drop one of them? _(Tests that ROW_NUMBER caps the group at three rows and breaks ties non-deterministically, while DENSE_RANK with rnk <= 3 keeps every tied entry at each tier.)_ - How do you construct the label combining svc_name and region, and what happens if one of them is NULL? _(Tests NULL propagation in concatenation; CONCAT and || behave differently on NULL arguments across engines.)_ - Could you get the top three tiers with a correlated subquery instead, and would it be more or less efficient here? _(Tests query plan reasoning; a correlated distinct-count subquery re-scans per team and is far heavier than a single window pass.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/splitting_the_load) - [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.