Google: March Cloud Bill for Every Project — SQL Problem
A FinOps analyst on Google Cloud prepares the March 2025 bill review and wants every project on the list — including idle ones, so their owners can be asked…
- Difficulty: Easy
- Topics: Joins, Aggregation, Dates
- Dialect: MySQL
- Problem: #677
Problem statement
A FinOps analyst on Google Cloud prepares the March 2025 bill review and wants every project on the list — including idle ones, so their owners can be asked to shut them down.
Return project_name and march_cost_cents, the sum of the project's cost_cents for usage dated 2025-03-01 to 2025-03-31 inclusive (credits are negative lines and reduce the total). A project with no March lines shows 0. Order by march_cost_cents descending, then project_name ascending.
A practice scenario set at Google: the tables and data are invented, and this is not a question from, or endorsed by, Google.
Tables
Table: CloudProject
| Column | Type |
|---|---|
| project_id | int |
| project_name | varchar |
| billing_account | varchar |
Primary key: project_id.
One row per project; project_name is unique.
Table: UsageCost
| Column | Type |
|---|---|
| line_id | int |
| project_id | int |
| service | varchar |
| usage_date | date |
| cost_cents | int |
Primary key: line_id.
One billing line. A negative cost_cents is a credit (a free-tier or promotional discount).
Examples
Example 1
CloudProject
| project_id | project_name | billing_account |
|---|---|---|
| 1 | maps-tiles-prod | BA-01 |
| 2 | yt-transcode | BA-01 |
| 3 | cloud-demo-sandbox | BA-02 |
| 4 | search-logs-etl | BA-02 |
UsageCost
| line_id | project_id | service | usage_date | cost_cents |
|---|---|---|---|---|
| 1 | 1 | Compute Engine | 2025-03-01 | 42000 |
| 2 | 1 | Cloud Storage | 2025-03-31 | 8000 |
| 3 | 1 | Compute Engine | 2025-04-01 | 39000 |
| 4 | 2 | Transcoder API | 2025-03-14 | 61000 |
| 5 | 2 | Free-tier credit | 2025-03-14 | -11000 |
| 6 | 3 | Compute Engine | 2025-02-28 | 1500 |
| 7 | 4 | BigQuery | 2025-03-20 | 50000 |
Output
| project_name | march_cost_cents |
|---|---|
| maps-tiles-prod | 50000 |
| search-logs-etl | 50000 |
| yt-transcode | 50000 |
| cloud-demo-sandbox | 0 |
How to solve Google: March Cloud Bill for Every Project
Every project belongs in the answer, so the query starts from CloudProject and LEFT JOINs the billing lines. The subtle part is where the date filter goes. Placed in the ON clause, it limits which lines are attached; a project with no March lines still survives with NULL cost columns. Placed in WHERE, u.usage_date BETWEEN … would be NULL for those rows and silently turn the LEFT JOIN back into an inner join, dropping the idle projects the analyst wants to see.
SUM over only NULLs is NULL, so COALESCE(…, 0) reports idle projects as 0. Credits are ordinary negative numbers and the sum nets them off without special handling. BETWEEN is inclusive at both ends, which keeps lines on the 1st and the 31st.
Alternatives: a correlated scalar subquery per project, or keep every line in the join and sum only March lines with a CASE. The order is fully determined by the cost and then the unique project name.
Reference solution (MySQL)
SELECT p.project_name, COALESCE(SUM(u.cost_cents), 0) AS march_cost_cents
FROM CloudProject p
LEFT JOIN UsageCost u
ON u.project_id = p.project_id
AND u.usage_date BETWEEN '2025-03-01' AND '2025-03-31'
GROUP BY p.project_id, p.project_name
ORDER BY march_cost_cents DESC, p.project_nameAnother way
SELECT project_name, (SELECT COALESCE(SUM(cost_cents), 0) FROM UsageCost u WHERE u.project_id = p.project_id AND YEAR(u.usage_date) = 2025 AND MONTH(u.usage_date) = 3) AS march_cost_cents FROM CloudProject p ORDER BY march_cost_cents DESC, project_nameAnother way
SELECT p.project_name, SUM(CASE WHEN u.usage_date >= '2025-03-01' AND u.usage_date < '2025-04-01' THEN u.cost_cents ELSE 0 END) AS march_cost_cents FROM CloudProject p LEFT JOIN UsageCost u ON u.project_id = p.project_id GROUP BY p.project_id, p.project_name ORDER BY march_cost_cents DESC, p.project_name← Google: Well-Rated Places With Enough Reviews · Google: Pixel Units Sold and Returned per Model →