The Cloud Bill

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

Microsoft

Problem

As we reconcile our cloud invoices, what do each month's billing entries in cloud_costs add up to for AWS, GCP, and Azure, side by side, oldest month first? Provider names in that table were never normalized, so 'aws' and 'AWS' are the same platform.

Every provider sent an invoice. Every month tells a different story.

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

This is a cross-tabulation wearing a finance costume, and it hides a dirty-data trap in plain sight. There is no PIVOT keyword to lean on, so the problem hinges on folding the provider dimension into columns with conditional sums. But look at the raw provider values: the same platform shows up as both 'aws' and 'AWS', and as both 'gcp' and 'GCP'. Match the literal case exactly and half of each provider's spend leaks into nowhere, leaving a column that reads 0 when real money was charged. The candidates who pass normalize the casing first. The ones who fail write a clean-looking pivot that quietly undercounts.

Building it

Step 1: Bucket by month

Collapse each bill_date to its year-month with STRFTIME('%Y-%m', bill_date). This is the output grain: one row per calendar month. Group on the same expression you select so the engine buckets consistently.

Step 2: One normalized conditional total per provider

For each provider, write a separate SUM(CASE WHEN UPPER(provider) = 'AWS' THEN amount ELSE 0 END). Two decisions matter here. UPPER normalizes the casing so every variant of a platform counts. ELSE 0 turns a month with no charges for that provider into a clean 0 rather than a NULL, which is what a finance report expects in the cell.

Step 3: Order chronologically

Sort on the month string. Because it is formatted as YYYY-MM, lexicographic order and chronological order coincide, so ORDER BY month alone gives oldest first with no extra casting.

The solution

SELECT STRFTIME('%Y-%m', bill_date) AS month,
    SUM(CASE WHEN UPPER(provider) = 'AWS' THEN amount ELSE 0 END) AS aws_total,
    SUM(CASE WHEN UPPER(provider) = 'GCP' THEN amount ELSE 0 END) AS gcp_total,
    SUM(CASE WHEN UPPER(provider) = 'AZURE' THEN amount ELSE 0 END) AS azure_total
FROM cloud_costs
GROUP BY STRFTIME('%Y-%m', bill_date)
ORDER BY month

Common follow-up questions

• A fourth provider starts billing next quarter. What happens to this query, and how would you make it adapt? (Tests whether the candidate spots that hard-coded `CASE` literals silently ignore a new provider until someone edits the query.)
• If casing chaos in `provider` is a recurring problem, where would you fix it so every downstream report does not have to normalize by hand? (Tests whether the candidate would push normalization upstream rather than repeating `UPPER` in every query.)
• Grouping on `STRFTIME` over 15M rows recomputes the month for every row. How would you keep this fast in production? (Tests understanding of grouping cost on a derived, non-indexable expression over a large table.)