Amazon: Savings Plan Coverage of Each AWS Account
A cloud FinOps analyst is reviewing compute Savings Plans. A plan commits an account to a fixed spend per hour: in each hour, eligible spend up to the…
- Difficulty: Medium
- Topics: Aggregation, Joins, Conditional Logic
- Dialect: MySQL
- Problem: #586
Problem statement
A cloud FinOps analyst is reviewing compute Savings Plans. A plan commits an account to a fixed spend per hour: in each hour, eligible spend up to the commitment is covered by the plan, and anything above it is billed on demand. Unused commitment in one hour does not carry to another.
For every account in ComputeUsage, return account_id, covered_spend (the sum over its hours of the spend the plan covered) and on_demand_spend (the sum of the spend above the commitment). An account with no Savings Plan has a commitment of 0. Return the rows in any order.
A practice scenario set at Amazon: the tables and data are invented, and this is not a question from, or endorsed by, Amazon.
Tables
Table: SavingsPlan
| Column | Type |
|---|---|
| account_id | int |
| hourly_commitment | int |
Primary key: account_id.
At most one active plan per account; the commitment is in dollars per hour.
Table: ComputeUsage
| Column | Type |
|---|---|
| account_id | int |
| usage_hour | datetime |
| eligible_spend | int |
Primary key: account_id, usage_hour.
One row per account per hour with plan-eligible compute spend, in whole dollars.
Examples
Example 1
SavingsPlan
| account_id | hourly_commitment |
|---|---|
| 1001 | 10 |
| 1002 | 5 |
| 1004 | 8 |
ComputeUsage
| account_id | usage_hour | eligible_spend |
|---|---|---|
| 1001 | 2025-09-01 00:00:00 | 12 |
| 1001 | 2025-09-01 01:00:00 | 7 |
| 1001 | 2025-09-01 02:00:00 | 10 |
| 1002 | 2025-09-01 00:00:00 | 3 |
| 1002 | 2025-09-01 01:00:00 | 9 |
| 1003 | 2025-09-01 00:00:00 | 6 |
Output
| account_id | covered_spend | on_demand_spend |
|---|---|---|
| 1001 | 27 | 2 |
| 1002 | 8 | 4 |
| 1003 | 0 | 6 |
How to solve Amazon: Savings Plan Coverage of Each AWS Account
Because the commitment resets every hour, coverage must be computed per hour and only then summed: LEAST(spend, commitment) is what the plan paid for in that hour and GREATEST(spend - commitment, 0) is the on-demand overflow. Summing spend first and comparing it with the total commitment would let a quiet hour's unused commitment cover a busy hour, which a plan does not do.
The commitment comes from SavingsPlan with a LEFT JOIN, because accounts with no plan still have usage to report; COALESCE(…, 0) turns their missing commitment into 0, so all their spend is on demand and their covered spend is 0. An hour with spend exactly equal to the commitment is fully covered with nothing on demand. For every row, covered plus on-demand equals the hour's spend, a quick self-check.
The alternative fetches the commitment with a scalar subquery and splits the spend with CASE expressions instead of LEAST/GREATEST. Both make a single grouped pass over the usage table.
Reference solution (MySQL)
SELECT u.account_id,
SUM(LEAST(u.eligible_spend, COALESCE(p.hourly_commitment, 0))) AS covered_spend,
SUM(GREATEST(u.eligible_spend - COALESCE(p.hourly_commitment, 0), 0)) AS on_demand_spend
FROM ComputeUsage u
LEFT JOIN SavingsPlan p ON p.account_id = u.account_id
GROUP BY u.account_idAnother way
SELECT account_id, SUM(CASE WHEN eligible_spend < c THEN eligible_spend ELSE c END) AS covered_spend,
SUM(CASE WHEN eligible_spend > c THEN eligible_spend - c ELSE 0 END) AS on_demand_spend
FROM (SELECT u.account_id, u.eligible_spend, IFNULL((SELECT hourly_commitment FROM SavingsPlan p WHERE p.account_id = u.account_id), 0) AS c FROM ComputeUsage u) t
GROUP BY account_id← Amazon: Kindle Reading Sessions That Ended Behind the Last One · Amazon: Route Stops Delivered Out of the Planned Order →