Annual Cloud Spend Summary

An easy SQL interview practice problem on DataDriven. Write the query and run it against a real database, with instant feedback.

Domain

SQL

Difficulty

easy

Seniority

L3

Asked in

Uber

Problem

The annual infrastructure review needs a slide showing how cloud spend and service sprawl have grown, walking the timeline from the earliest fiscal year up to the most recent. For each fiscal year, report the total amount spent and how many unique services appeared on the bill.

A year of cloud bills. Add it all up.

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

Why this problem exists in real interviews

Working with cloud_costs, this problem isolates date extraction and truncation. The interviewer expects candidates to articulate why provider, svc_name, region matter for correctness before touching the keyboard.

Break down the requirements

Step 1: Group by STRFTIME('%Y'

Group cloud_costs rows by fiscal year, extracted from bill_date with strftime('%Y', ...).

Step 2: Compute SUM(amount)

Sum amount per year to get total_spend, and use COUNT(DISTINCT svc_name) to count the unique services on the bill.

Step 3: Order by the metric

Order by fiscal_year so the per-year rows come out in a stable, deterministic sequence for the slide.

The solution

SELECT strftime('%Y', bill_date) AS fiscal_year,
       SUM(amount) AS total_spend,
       COUNT(DISTINCT svc_name) AS service_count
FROM cloud_costs
GROUP BY strftime('%Y', bill_date)
ORDER BY fiscal_year;

Common follow-up questions

• What happens to your results if `provider` in `cloud_costs` contains trailing whitespace or mixed casing? (Tests awareness of text normalization issues that silently fragment GROUP BY results.)
• Your GROUP BY aggregates `cost_id` from `cloud_costs`. If two groups have the same aggregate value, how is the output ordered, and is that deterministic? (Tests awareness that ORDER BY on a non-unique value produces non-deterministic row order without a tiebreaker.)
• `cost_id` in `cloud_costs` has ~8M distinct values. What index strategy keeps your query from doing a full table scan? (Tests whether the candidate can design indexes for high-cardinality columns and understands selectivity.)
• Could you express this same logic as a single query without CTEs or subqueries? What readability trade-off does that introduce? (Tests whether the candidate can flatten nested logic and understands when decomposition aids maintainability.)