Cloud Cost Breakdown by Provider

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

L4

Asked in

ESPN

Problem

The FinOps team wants a spend breakdown covering the two most recently completed years, so leave out anything from the current partial year. For each cost category and year in the allocation records, show the total spend, how much of it landed in the us-east-1 region, and the number of line items, from the highest total spend down.

Cloud costs, provider by provider.

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 pivot hiding inside a cost report. Anyone can group by category and year and sum the spend; the skill being probed is producing the us-east-1 column in the SAME pass, as SUM(CASE WHEN region = 'us-east-1' THEN amount ELSE 0 END) beside the plain SUM(amount). Reach instead for a second query filtered to 'us-east-1' and you silently drop every category-year that spent nothing there, so your regional numbers no longer line up with your totals and the counts stop matching. The other quiet trap: period is a YYYY-MM string, so the year is its first four characters, not the output of any date function.

Working through it

Step 1: Pull the year off the period string

period is text like '2025-03', not a date. Take substr(period, 1, 4) to get the year. This is robust and dialect-free; a YEAR(period) or a cast to date is the thing that bites you here.

Step 2: Aggregate per category and year

Group by category and the extracted year. Total spend is SUM(amount); line items is COUNT(*). Keep them separate: spend and count answer different questions, and COUNT(*) still counts rows whose acct_id is null.

Step 3: Fold in the us-east-1 column

Add the regional figure as SUM(CASE WHEN region = 'us-east-1' THEN amount ELSE 0 END) in the same SELECT. Because it rides the same GROUP BY, a group with no us-east-1 rows reads 0 instead of disappearing. Match 'us-east-1' exactly: 'us-west-2' and the GCP regions are not it.

Step 4: Window to two years and order

Bound the window on both ends: substr(period, 1, 4) >= '2024' drops the older years and <= '2025' drops the current partial year, leaving exactly the two most recently completed years. Then order by total_spend descending, with category and yr as tie-breaks held in reserve.

The solution

SELECT
    category,
    substr(period, 1, 4) AS yr,
    SUM(amount) AS total_spend,
    SUM(CASE WHEN region = 'us-east-1' THEN amount ELSE 0 END) AS us_east_spend,
    COUNT(*) AS entry_count
FROM cost_allocs
WHERE substr(period, 1, 4) >= '2024'
  AND substr(period, 1, 4) <= '2025'
GROUP BY category, yr
ORDER BY total_spend DESC, category, yr

Common follow-up questions

• How would you add year-over-year growth as a column? (Tests a `LAG` window function partitioned by `category` ordered by `yr`.)
• What if the FinOps team wanted every region as its own column? (Tests dynamic pivoting via extensive conditional aggregation, one `CASE`-sum per region.)
• How would your query change if `period` stored a full month-end date instead of a YYYY-MM string? (Tests whether the year extraction still holds when the grain changes to a real date.)