Amazon: Savings Plan Coverage of Each AWS Account

Medium
586.

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

ColumnType
account_idint
hourly_commitmentint

Primary key: account_id.

At most one active plan per account; the commitment is in dollars per hour.

Table: ComputeUsage

ColumnType
account_idint
usage_hourdatetime
eligible_spendint

Primary key (together): account_id, usage_hour.

One row per account per hour with plan-eligible compute spend, in whole dollars.

Example

The rows may come back in any order; the columns must be the ones shown, in that order.

SavingsPlan

account_idhourly_commitment
100110
10025
10048

ComputeUsage

account_idusage_houreligible_spend
10012025-09-01 00:00:0012
10012025-09-01 01:00:007
10012025-09-01 02:00:0010
10022025-09-01 00:00:003
10022025-09-01 01:00:009
10032025-09-01 00:00:006

Output

account_idcovered_spendon_demand_spend
1001272
100284
100306
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.

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

ColumnType
account_idint
hourly_commitmentint

Primary key: account_id.

At most one active plan per account; the commitment is in dollars per hour.

Table: ComputeUsage

ColumnType
account_idint
usage_hourdatetime
eligible_spendint

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_idhourly_commitment
100110
10025
10048

ComputeUsage

account_idusage_houreligible_spend
10012025-09-01 00:00:0012
10012025-09-01 01:00:007
10012025-09-01 02:00:0010
10022025-09-01 00:00:003
10022025-09-01 01:00:009
10032025-09-01 00:00:006

Output

account_idcovered_spendon_demand_spend
1001272
100284
100306

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_id

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