Annual Cloud Spend

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

Zillow

Problem

Each row of cloud_costs is one cloud charge, dated in bill_date to the first day of its billing month. Total the spend for each billing year across all providers, earliest year first, for a year-over-year view.

One year of cloud bills. The total.

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

This is a re-graining problem in a budget costume. cloud_costs has no year column: bill_date is stamped with the first day of each billing month, so the year you are asked to total by does not exist until you build it. Everyone writes SUM(amount) correctly. What separates candidates is whether they group by the year they derived or by the column they can see. Group by bill_date and the query runs without complaint and returns 57 monthly rows instead of five annual totals, which is exactly the fragmented view a year-over-year comparison cannot use.

Getting to the answer

Step 1: Check the grain of bill_date

Before writing anything, read the sample rows: every bill_date ends in '-01', one value per billing month. On this data that is 57 distinct dates across five years, and no year is stored anywhere. The grain you need has to be computed.

Step 2: Derive the year and group on it

strftime('%Y', bill_date) returns the four-digit year as text, '2022' through '2026'. Group on that expression, not on bill_date, so every month of a year folds into one bucket.

Step 3: Total the amount and keep every row

Take SUM(amount) per year and filter nothing. Negative amounts are credits that net against spend, and the twelve rows with no amount contribute nothing because SUM skips NULLs.

Step 4: Sort earliest year first

A year-over-year view reads chronologically, so order by year ascending. Each year appears once, so no tie rule is needed.

SELECT strftime('%Y', bill_date) AS year, SUM(amount) AS total_spend FROM cloud_costs GROUP BY strftime('%Y', bill_date) ORDER BY year ASC

SELECT bill_date, SUM(amount) AS total_spend FROM cloud_costs GROUP BY bill_date ORDER BY bill_date

Common follow-up questions

• The same view is now wanted per `provider`. What do you do about 'aws' versus 'AWS'? (Tests noticing the casing dirt in `provider` and normalizing it before grouping.)
• How would you add each year's percentage change from the previous year? (Tests reading the prior year's total within an ordered result.)
• The current year only covers nine billing months. How do you make it comparable to full years? (Tests recognizing a partial period and comparing year-to-date or annualizing.)
• What changes if `bill_date` were a UTC timestamp and billing years followed the company's local time zone? (Tests converting time zones before deriving the year.)