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
| Column | Type |
|---|---|
| agreement_id | int |
| customer_name | varchar |
| monthly_prepayment | int |
Primary key: agreement_id.
One row per agreement; amounts in US dollars.
Table: AzureMonthUsage
| Column | Type |
|---|---|
| agreement_id | int |
| month_no | int |
| usage_amount | int |
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_id | customer_name | monthly_prepayment |
|---|---|---|
| 1 | Contoso | 10000 |
| 2 | Fabrikam | 5000 |
| 3 | Litware | 20000 |
AzureMonthUsage
| agreement_id | month_no | usage_amount |
|---|---|---|
| 1 | 1 | 6000 |
| 1 | 2 | 3000 |
| 1 | 3 | 25000 |
| 1 | 4 | 10000 |
| 2 | 1 | 7000 |
| 2 | 2 | 1000 |
| 2 | 3 | 0 |
Output
| agreement_id | month_no | available_credit | overage | carried_forward |
|---|---|---|---|---|
| 1 | 1 | 10000 | 0 | 4000 |
| 1 | 2 | 14000 | 0 | 10000 |
| 1 | 3 | 20000 | 5000 | 0 |
| 1 | 4 | 10000 | 0 | 0 |
| 2 | 1 | 5000 | 2000 | 0 |
| 2 | 2 | 5000 | 0 | 4000 |
| 2 | 3 | 9000 | 0 | 5000 |
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_noAnother 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 →