Splitting the Load

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

L3

Asked in

Visa

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.

The heaviest bills each team carries.

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

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. It throws away svc_name and region and only surfaces the single top charge, so you can neither reach the second and third tiers nor build the label. The tempting patch is a correlated amount = (SELECT MAX(amount) ... WHERE team_name = outer.team_name), but it recovers only the first tier. Stretching it to three needs a correlated COUNT(DISTINCT amount) predicate that is gnarlier than the window it is dodging. A self-join back on the max amount also fans out the moment two charges tie at it. 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 and keep every row in the top three tiers: 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 gives the same rank to every row tied at a cost tier, so this keeps all three tiers and every tied entry within them. ROW_NUMBER would instead 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, which gives a stable, total ordering even when a team has tied entries.

The solution

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

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