Microsoft: Azure Prepayment Balance and Overage Month by Month

Hard
794.

Microsoft: Azure Prepayment Balance and Overage Month by Month

An enterprise customer prepays a fixed Azure credit every month. A Microsoft billing analyst replays the agreement to explain each invoice.

Each month the available credit is that month's monthly_prepayment plus whatever was carried forward from the month before (nothing before month 1). Usage draws the credit down; usage beyond the available credit is billed as overage. Unused credit carries into the next month, capped at one month's prepayment — anything above the cap expires. Return agreement_id, month_no, available_credit, overage and carried_forward for every month of usage, ordered by agreement_id, then month_no.

A practice scenario set at Microsoft: the tables and data are invented, and this is not a question from, or endorsed by, Microsoft.

Tables

Table: Agreement

ColumnType
agreement_idint
customer_namevarchar
monthly_prepaymentint

Primary key: agreement_id.

One row per agreement; amounts in US dollars.

Table: AzureMonthUsage

ColumnType
agreement_idint
month_noint
usage_amountint

Primary key (together): agreement_id, month_no.

Azure usage per agreement and month of the term. Months are numbered from 1 with no gaps.

Example

The rows must come back in the order shown.

Agreement

agreement_idcustomer_namemonthly_prepayment
1Contoso10000
2Fabrikam5000
3Litware20000

AzureMonthUsage

agreement_idmonth_nousage_amount
116000
123000
1325000
1410000
217000
221000
230

Output

agreement_idmonth_noavailable_creditoveragecarried_forward
111000004000
1214000010000
132000050000
141000000
21500020000
22500004000
23900005000
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.

Microsoft: Azure Prepayment Balance and Overage Month by Month

An enterprise customer prepays a fixed Azure credit every month. A Microsoft billing analyst replays the agreement to explain each invoice.

  • Difficulty: Hard
  • Topics: Subqueries, Conditional Logic
  • Dialect: MySQL
  • Problem: #794

Problem statement

An enterprise customer prepays a fixed Azure credit every month. A Microsoft billing analyst replays the agreement to explain each invoice.

Each month the available credit is that month's monthly_prepayment plus whatever was carried forward from the month before (nothing before month 1). Usage draws the credit down; usage beyond the available credit is billed as overage. Unused credit carries into the next month, capped at one month's prepayment — anything above the cap expires. Return agreement_id, month_no, available_credit, overage and carried_forward for every month of usage, ordered by agreement_id, then month_no.

A practice scenario set at Microsoft: the tables and data are invented, and this is not a question from, or endorsed by, Microsoft.

Tables

Table: Agreement

ColumnType
agreement_idint
customer_namevarchar
monthly_prepaymentint

Primary key: agreement_id.

One row per agreement; amounts in US dollars.

Table: AzureMonthUsage

ColumnType
agreement_idint
month_noint
usage_amountint

Primary key: agreement_id, month_no.

Azure usage per agreement and month of the term. Months are numbered from 1 with no gaps.

Examples

Example 1

Agreement

agreement_idcustomer_namemonthly_prepayment
1Contoso10000
2Fabrikam5000
3Litware20000

AzureMonthUsage

agreement_idmonth_nousage_amount
116000
123000
1325000
1410000
217000
221000
230

Output

agreement_idmonth_noavailable_creditoveragecarried_forward
111000004000
1214000010000
132000050000
141000000
21500020000
22500004000
23900005000

How to solve Microsoft: Azure Prepayment Balance and Overage Month by Month

Without the cap this would be a running total. The cap makes it path-dependent: what carries into month 3 depends on how much was clipped in month 2, which depends on month 1. Running sums and window functions cannot express "clip, then continue", so the ledger is replayed one month at a time with a recursive CTE.

The anchor is month 1, where the available credit is just the prepayment. Each recursive step joins the next month's usage to the previous ledger row and computes the new available credit as prepayment + LEAST(GREATEST(previous available − previous usage, 0), cap). For Contoso: month 1 leaves 4,000 (all carried); month 2 has 14,000, leaves 11,000, of which only 10,000 carries; month 3 has 20,000 against 25,000 of usage — 5,000 overage and nothing carried. The final SELECT derives overage and carry-forward from each row. An agreement with no usage rows (Litware) has no anchor and produces nothing.

The alternative recurses on the carry alone, using CASE instead of GREATEST/LEAST, and computes the rest in the outer query. Both visit each month once.

Reference solution (MySQL)

WITH RECURSIVE ledger AS (
  SELECT u.agreement_id, u.month_no, a.monthly_prepayment AS available_credit,
         u.usage_amount, a.monthly_prepayment AS cap
  FROM AzureMonthUsage u
  JOIN Agreement a ON a.agreement_id = u.agreement_id
  WHERE u.month_no = 1
  UNION ALL
  SELECT u.agreement_id, u.month_no,
         l.cap + LEAST(GREATEST(l.available_credit - l.usage_amount, 0), l.cap),
         u.usage_amount, l.cap
  FROM ledger l
  JOIN AzureMonthUsage u ON u.agreement_id = l.agreement_id AND u.month_no = l.month_no + 1
)
SELECT agreement_id, month_no, available_credit,
       GREATEST(usage_amount - available_credit, 0) AS overage,
       LEAST(GREATEST(available_credit - usage_amount, 0), cap) AS carried_forward
FROM ledger
ORDER BY agreement_id, month_no

Another way

WITH RECURSIVE carry AS (
  SELECT agreement_id, month_no, 0 AS carry_in FROM AzureMonthUsage WHERE month_no = 1
  UNION ALL
  SELECT c.agreement_id, c.month_no + 1,
         CASE WHEN a.monthly_prepayment + c.carry_in - u.usage_amount <= 0 THEN 0
              WHEN a.monthly_prepayment + c.carry_in - u.usage_amount > a.monthly_prepayment THEN a.monthly_prepayment
              ELSE a.monthly_prepayment + c.carry_in - u.usage_amount END
  FROM carry c
  JOIN Agreement a ON a.agreement_id = c.agreement_id
  JOIN AzureMonthUsage u ON u.agreement_id = c.agreement_id AND u.month_no = c.month_no
  JOIN AzureMonthUsage nx ON nx.agreement_id = c.agreement_id AND nx.month_no = c.month_no + 1
)
SELECT c.agreement_id, c.month_no, a.monthly_prepayment + c.carry_in AS available_credit,
       CASE WHEN u.usage_amount > a.monthly_prepayment + c.carry_in THEN u.usage_amount - a.monthly_prepayment - c.carry_in ELSE 0 END AS overage,
       CASE WHEN a.monthly_prepayment + c.carry_in - u.usage_amount <= 0 THEN 0
            WHEN a.monthly_prepayment + c.carry_in - u.usage_amount > a.monthly_prepayment THEN a.monthly_prepayment
            ELSE a.monthly_prepayment + c.carry_in - u.usage_amount END AS carried_forward
FROM carry c
JOIN Agreement a ON a.agreement_id = c.agreement_id
JOIN AzureMonthUsage u ON u.agreement_id = c.agreement_id AND u.month_no = c.month_no
ORDER BY c.agreement_id, c.month_no

← Microsoft: Management Groups Whose Rolled-Up Azure Spend Tops Budget · Microsoft: Xbox Hunters With the Most Rare Achievements →