Google: Cloud Cost Spikes Against the Trailing Week

Medium
689.

Google: Cloud Cost Spikes Against the Trailing Week

MediumWindow Functions

MySQL

Google Cloud's billing alerts compare each day's spend with what a project normally spends. A FinOps engineer is prototyping the rule in SQL.

For each project, compare every billed day with its previous up to 7 billed days (earlier rows of the same project, not calendar days). A day is a spike when it has at least 3 such previous days and its cost_usd is strictly more than twice their average. Return project_id, usage_date, cost_usd and trailing_avg (that average rounded to 2 decimals) for every spike. Order by project_id, then usage_date.

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: ProjectDailyCost

ColumnType
project_idint
usage_datedate
cost_usdint

Primary key (together): project_id, usage_date.

A project's total cost for one day; days without usage have no row.

Example

The rows must come back in the order shown.

ProjectDailyCost

project_idusage_datecost_usd
12025-03-0110
12025-03-0212
12025-03-0311
12025-03-0430
12025-03-0514
12025-03-0740
22025-03-0150
22025-03-02100
22025-03-0360
22025-03-04140

Output

project_idusage_datecost_usdtrailing_avg
12025-03-043011
12025-03-074015.4
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: Cloud Cost Spikes Against the Trailing Week

Google Cloud's billing alerts compare each day's spend with what a project normally spends. A FinOps engineer is prototyping the rule in SQL.

  • Difficulty: Medium
  • Topics: Window Functions
  • Dialect: MySQL
  • Problem: #689

Problem statement

Google Cloud's billing alerts compare each day's spend with what a project normally spends. A FinOps engineer is prototyping the rule in SQL.

For each project, compare every billed day with its previous up to 7 billed days (earlier rows of the same project, not calendar days). A day is a spike when it has at least 3 such previous days and its cost_usd is strictly more than twice their average. Return project_id, usage_date, cost_usd and trailing_avg (that average rounded to 2 decimals) for every spike. Order by project_id, then usage_date.

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: ProjectDailyCost

ColumnType
project_idint
usage_datedate
cost_usdint

Primary key: project_id, usage_date.

A project's total cost for one day; days without usage have no row.

Examples

Example 1

ProjectDailyCost

project_idusage_datecost_usd
12025-03-0110
12025-03-0212
12025-03-0311
12025-03-0430
12025-03-0514
12025-03-0740
22025-03-0150
22025-03-02100
22025-03-0360
22025-03-04140

Output

project_idusage_datecost_usdtrailing_avg
12025-03-043011
12025-03-074015.4

How to solve Google: Cloud Cost Spikes Against the Trailing Week

The baseline for a day is the average of the rows before it, so the frame must stop one row short of the current one: ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING, partitioned by project and ordered by date. Because the frame counts rows, gaps in the calendar (a day without usage) are skipped naturally — the baseline is the previous seven billed days, as stated.

Early rows have short frames; the very first has an empty one, where AVG is NULL. A COUNT(*) over the same frame tells how many days the baseline really covers, and the rule needs at least three, so a project's first three days can never be spikes. The spike test is strict: a day at exactly twice the average is not flagged.

The first alternative avoids comparing with a fractional average by cross-multiplying: cost × n > 2 × sum. The second rebuilds the frame with a self join on row numbers (p.rn from c.rn − 7 to c.rn − 1) and aggregates — the pre-window way. The window version sorts each project once.

Reference solution (MySQL)

WITH w AS (
  SELECT project_id, usage_date, cost_usd,
         AVG(cost_usd) OVER (PARTITION BY project_id ORDER BY usage_date
                             ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) AS prev_avg,
         COUNT(*) OVER (PARTITION BY project_id ORDER BY usage_date
                        ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) AS prior_days
  FROM ProjectDailyCost
)
SELECT project_id, usage_date, cost_usd, ROUND(prev_avg, 2) AS trailing_avg
FROM w
WHERE prior_days >= 3
  AND cost_usd > 2 * prev_avg
ORDER BY project_id, usage_date

Another way

WITH w AS (SELECT project_id, usage_date, cost_usd, SUM(cost_usd) OVER (PARTITION BY project_id ORDER BY usage_date ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) AS s, COUNT(*) OVER (PARTITION BY project_id ORDER BY usage_date ROWS BETWEEN 7 PRECEDING AND 1 PRECEDING) AS n FROM ProjectDailyCost) SELECT project_id, usage_date, cost_usd, ROUND(s / n, 2) AS trailing_avg FROM w WHERE n >= 3 AND cost_usd * n > 2 * s ORDER BY project_id, usage_date

Another way

WITH r AS (SELECT project_id, usage_date, cost_usd, ROW_NUMBER() OVER (PARTITION BY project_id ORDER BY usage_date) AS rn FROM ProjectDailyCost) SELECT c.project_id, c.usage_date, c.cost_usd, ROUND(AVG(p.cost_usd), 2) AS trailing_avg FROM r c JOIN r p ON p.project_id = c.project_id AND p.rn < c.rn AND p.rn + 7 >= c.rn GROUP BY c.project_id, c.usage_date, c.cost_usd HAVING COUNT(*) >= 3 AND c.cost_usd * COUNT(*) > 2 * SUM(p.cost_usd) ORDER BY c.project_id, c.usage_date

← Google: Longest Heartbeat Gap of Each Nest Device · Google: Meet Calls Where Guests Waited for a Late Host →