# The Cloud Bill > Every provider sent an invoice. Every month tells a different story. Canonical URL: Domain: SQL · Difficulty: medium · Seniority: L3 ## Problem The finance team is reconciling cloud invoices and wants a monthly read on spend by provider, but the provider names arrive with inconsistent casing where 'aws' and 'AWS' both mean the same platform. Give the combined total for AWS, GCP, and Azure in each month, counting every billing entry, oldest month first. ## 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. SQLite has no native PIVOT, 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', '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. > **Trick to solving** > > There is no PIVOT keyword to reach for, and you cannot trust the raw text of provider. The pattern is `SUM(CASE WHEN UPPER(provider) = 'AWS' THEN amount ELSE 0 END)`, one expression per output column. UPPER (or LOWER) collapses 'aws' and 'AWS' into one bucket before the CASE decides which rows feed which column, and the surrounding SUM folds them into a single monthly figure. --- ### 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, and 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 **Provider-level conditional pivot with case normalization** ```sql 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 pitfall** > > Two mistakes sink this problem. First, matching provider literally ('AWS' only) drops every 'aws' row, so the column undercounts without any error to warn you. Second, inventing a filter (for example, dropping rows whose acct_id is missing) quietly removes whole months and changes the row count, even though nothing in the requirement asked you to exclude anything. Read what is asked, normalize the dimension, and count every entry. > **Interviewers watch for** > > Saying the grain out loud ("one row per month") before writing GROUP BY signals you reason about the shape of the output, not just syntax. Reaching for conditional aggregation instead of three stitched-together queries shows you know the idiomatic pivot, and spotting the mixed casing before it burns you is the tell of someone who has cleaned real vendor data. > **Cost analysis** > > This is a single sequential scan of 15M rows with a streaming aggregation: no join, no self-reference, and the GROUP BY collapses everything down to at most 36 monthly rows. The UPPER call is a cheap per-row function that does not change the plan. Because bill_date is the partition key, an engine can prune to the months requested and the whole thing stays cheap even at scale. --- ## 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.)_ ## Related - [All practice problems](https://datadriven.io/problems) - [Mock interview mode](https://datadriven.io/interview/the_cloud_bill) - [SQL Interview Questions](https://datadriven.io/sql-interview-questions) - [Data Engineering Interview Prep Guide](https://datadriven.io/data-engineer-interview-prep) - [Daily Challenge](https://datadriven.io/daily) --- Source: DataDriven (https://datadriven.io). DataDriven is the data engineering interview community. Live code execution in SQL, Python, and Spark sandboxes. Every feature is open to every member.