Google: March Cloud Bill for Every Project

Easy
677.

Google: March Cloud Bill for Every Project

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

ColumnType
project_idint
project_namevarchar
billing_accountvarchar

Primary key: project_id.

One row per project; project_name is unique.

Table: UsageCost

ColumnType
line_idint
project_idint
servicevarchar
usage_datedate
cost_centsint

Primary key: line_id.

One billing line. A negative cost_cents is a credit (a free-tier or promotional discount).

Example

The rows must come back in the order shown.

CloudProject

project_idproject_namebilling_account
1maps-tiles-prodBA-01
2yt-transcodeBA-01
3cloud-demo-sandboxBA-02
4search-logs-etlBA-02

UsageCost

line_idproject_idserviceusage_datecost_cents
11Compute Engine2025-03-0142000
21Cloud Storage2025-03-318000
31Compute Engine2025-04-0139000
42Transcoder API2025-03-1461000
52Free-tier credit2025-03-14-11000
63Compute Engine2025-02-281500
74BigQuery2025-03-2050000

Output

project_namemarch_cost_cents
maps-tiles-prod50000
search-logs-etl50000
yt-transcode50000
cloud-demo-sandbox0
Read the hints, the editorial and the reference solution
-- Write your MySQL query below

Sign in to write, run and submit queries

A free account — no card, no trial clock. You come straight back here.

Run your query to see its result next to the expected one, example by example.

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

ColumnType
project_idint
project_namevarchar
billing_accountvarchar

Primary key: project_id.

One row per project; project_name is unique.

Table: UsageCost

ColumnType
line_idint
project_idint
servicevarchar
usage_datedate
cost_centsint

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_idproject_namebilling_account
1maps-tiles-prodBA-01
2yt-transcodeBA-01
3cloud-demo-sandboxBA-02
4search-logs-etlBA-02

UsageCost

line_idproject_idserviceusage_datecost_cents
11Compute Engine2025-03-0142000
21Cloud Storage2025-03-318000
31Compute Engine2025-04-0139000
42Transcoder API2025-03-1461000
52Free-tier credit2025-03-14-11000
63Compute Engine2025-02-281500
74BigQuery2025-03-2050000

Output

project_namemarch_cost_cents
maps-tiles-prod50000
search-logs-etl50000
yt-transcode50000
cloud-demo-sandbox0

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_name

Another 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_name

Another 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 →