The Weight of Giants

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

L6

Asked in

Finicity

Problem

The FinOps team is hunting where cloud spend concentrates across the deployment regions. Take each region's two costliest services by total spend and return them as one combined leaderboard, from the biggest spender down.

In every sky, only two hold the throne.

Tables

cloud_costs

Columns: cost_id INTEGER, provider TEXT, svc_name TEXT, region TEXT, amount REAL, acct_id TEXT, bill_date TEXT

Expected output

Worked solution and explanation

What this really is

Strip the FinOps costume and this is a partitioned top-N wearing a leaderboard. The skill being probed: can you rank each region's services inside that region alone, keep the two costliest, and only then flatten everything into one spend-ordered list? Anyone can write the GROUP BY and the outer sort. The trap is selecting with a single global cutoff: order the aggregated services by total_spend DESC and take the top few, and you pull the globally costliest services while every smaller region gets starved. Each region bills several services, so PARTITION BY region plus the rnk <= 2 filter is what actually chooses the rows; get it wrong and small regions vanish and the leaderboard never reconciles with the FinOps totals.

Break down the requirements

Step 1: Aggregate spend per region and service

GROUP BY region, svc_name with SUM(amount) collapses the raw billing rows into one total per service within each region. A service whose rows are all NULL amounts sums to NULL and sorts to the bottom under DESC, so it never reaches the top two.

Step 2: Rank services within each region

DENSE_RANK() OVER (PARTITION BY region ORDER BY SUM(amount) DESC) restarts the ranking for every region, so a large us-central1 service can never crowd another region's own top service out of the selection. DENSE_RANK also keeps a tied pair on the same rank rather than forcing an arbitrary winner, so the selection stays correct if two services ever draw level at the cutoff.

Step 3: Keep the top two, then rank the leaderboard

Wrap the ranked set in a subquery and filter WHERE rnk <= 2, keeping only each region's two costliest services. Then order the whole result by total_spend DESC so the services come out as one leaderboard, biggest spender first. That final sort is load-bearing: without it the rows fall out in the engine's partition order, not the spend order the FinOps team reads.

The solution

SELECT region, svc_name, total_spend
FROM (
    SELECT
        region,
        svc_name,
        SUM(amount) AS total_spend,
        DENSE_RANK() OVER (
            PARTITION BY region
            ORDER BY SUM(amount) DESC
        ) AS rnk
    FROM cloud_costs
    GROUP BY region, svc_name
) ranked
WHERE rnk <= 2
ORDER BY total_spend DESC

Common follow-up questions

• What if you needed the top 2 services within each region-provider pair? (Compound partitioning: `PARTITION BY region, provider`.)
• How would you break ties between services that draw level on total spend? (Add a secondary sort key such as `region ASC` to the final `ORDER BY` to keep a stable, readable layout.)
• What if there were 1,000 regions instead of a handful? (More partitions; per-partition work stays small but total sort volume grows.)
• Could you solve this without window functions? (A correlated subquery counting higher-spend services per region works but is harder to read and optimize.)