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
| Column | Type |
|---|---|
| project_id | int |
| usage_date | date |
| cost_usd | int |
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_id | usage_date | cost_usd |
|---|---|---|
| 1 | 2025-03-01 | 10 |
| 1 | 2025-03-02 | 12 |
| 1 | 2025-03-03 | 11 |
| 1 | 2025-03-04 | 30 |
| 1 | 2025-03-05 | 14 |
| 1 | 2025-03-07 | 40 |
| 2 | 2025-03-01 | 50 |
| 2 | 2025-03-02 | 100 |
| 2 | 2025-03-03 | 60 |
| 2 | 2025-03-04 | 140 |
Output
| project_id | usage_date | cost_usd | trailing_avg |
|---|---|---|---|
| 1 | 2025-03-04 | 30 | 11 |
| 1 | 2025-03-07 | 40 | 15.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_dateAnother 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_dateAnother 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 →