Three 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

L4

Asked in

Maanams International

Problem

cost_allocs records cloud spend as individual allocations, each tagged to a team and a billing period, so a team's monthly cost is everything allocated to it in that period. Team names were entered with inconsistent capitalization, and every capitalization of a name is the same team. Surface the three highest unique monthly cost amounts for each team.

Every team's steepest months, and nothing below them.

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

Under the finance wording, this is a per-team top-N on a figure you have to build first. A team's monthly cost is everything allocated to it in that period, across every service, region, account and category. That is SUM(amount) grouped by team and period, never one row's amount. Anyone can write the sum. The trap is the scope of the ranking. Write ORDER BY monthly_cost DESC LIMIT 3 and you get the three biggest figures in the whole company. That is three rows in total, so most teams vanish from the report. Two smaller catches follow. The answer is amounts, not months, so two months with the same total share one slot. And the same team shows up under more than one capitalization, so its spend has to be pooled under one name before anything is summed.

Building it

Step 1: Pool each team's month under one name

cost_allocs holds many rows per team and month, one per service, region, account and category. All of them count toward that month. Group on LOWER(team_name), not the raw value: 'data-eng' and 'DATA-ENG' are one team, and so are 'Platform' and 'platform'. Use the same expression in the SELECT so the output carries one spelling.

Step 2: Drop period, then deduplicate

SELECT DISTINCT team_name, monthly_cost makes three slots mean three different amounts. It only works once period is gone. Leave period in and every row stays distinct, so nothing collapses.

Step 3: Rank inside each team, filter outside

DENSE_RANK() restarts at 1 for every team_name. Filter rnk <= 3 in the outer query, because WHERE cannot see a window alias defined in its own SELECT. The ORDER BY inside OVER (...) only assigns ranks; the report itself is a set of rows, so no outer sort is needed.

The solution

WITH monthly AS (
    SELECT LOWER(team_name) AS team_name, period, SUM(amount) AS monthly_cost
    FROM cost_allocs
    GROUP BY LOWER(team_name), period
),
distinct_costs AS (
    SELECT DISTINCT team_name, monthly_cost
    FROM monthly
),
ranked AS (
    SELECT
        team_name,
        monthly_cost,
        DENSE_RANK() OVER (
            PARTITION BY team_name
            ORDER BY monthly_cost DESC
        ) AS rnk
    FROM distinct_costs
)
SELECT team_name, monthly_cost
FROM ranked
WHERE rnk <= 3
;

Global limit

ORDER BY monthly_cost DESC LIMIT 3 over monthly returns three rows for the whole company, all from whichever teams spend most.

Partitioned window

PARTITION BY team_name restarts the rank per team, so rnk <= 3 keeps up to three distinct amounts for every team.

WITH monthly AS (
    SELECT LOWER(team_name) AS team_name, period, SUM(amount) AS monthly_cost
    FROM cost_allocs
    GROUP BY LOWER(team_name), period
)
SELECT team_name, monthly_cost
FROM monthly
ORDER BY monthly_cost DESC
LIMIT 3;

Common follow-up questions

• How would you return the month or months each top amount came from? (Tests joining `ranked` back to `monthly` on `team_name` and `monthly_cost`, which can yield several rows per amount.)
• What if a team has fewer than three months of data? (Tests seeing that `rnk <= 3` simply returns the amounts that team has.)
• Could you drop the `distinct_costs` CTE and still get unique amounts? (Tests `DENSE_RANK()` over `monthly` followed by `SELECT DISTINCT` on the survivors.)
• What if some team names also arrived with stray spaces? (Tests adding `TRIM` alongside `LOWER` and fixing the data at write time instead.)