project 03 AI Platform Economics
Understand platform economics.
Connect usage, payments and costs to understand business performance.
- business question
- Calculate success rates and usage costs, prevent join fan-out, and reconcile split payments with refunds.
- you deliver
- Safe ratios, reconciled revenue and usage contribution margins. Exported as a report with your checked SQL, result evidence and written findings.
- skills
- Ratios · Reconciliation · Multi-table analysis
- scope
- 4 milestones · 6 tables · intermediate
Start the first milestone milestones
- 01Ratios, ROUND & NULLIFCalculate safe ratios and round your results.
- 02Multi-grain joins without fan-outCombine metrics at different levels without multiplying totals.
- 03Revenue & payment reconciliationReconcile revenue with payments and identify differences.
- 04AI usage cost & contribution marginCompare usage revenue with costs to calculate contribution margin.
data ModelWorks · 6 tables
Request usage, fictional model rates, invoices, payments, and refunds. Rates are invented for learning, not real provider prices.
ai_accounts4 columns · 6 rows
| idinteger · key | plantext | regiontext | signed_up_attext |
|---|
| 1 | pro | US | 2025-01-01 |
| 2 | free | US | 2025-01-02 |
| 3 | team | EU | 2025-01-02 |
| 4 | pro | IN | 2025-01-03 |
| 5 | free | EU | 2025-01-04 |
ai_requests7 columns · 12 rows
| idinteger · key | account_idinteger | modeltext | requested_attext | input_tokensinteger | output_tokensinteger | statustext |
|---|
| 1 | 1 | swift | 2025-01-01 10:00:00 | 1000 | 200 | success |
| 2 | 1 | reason | 2025-01-02 11:00:00 | 2000 | 600 | success |
| 3 | 2 | swift | 2025-01-02 12:00:00 | 500 | 0 | error |
| 4 | 3 | reason | 2025-01-02 13:00:00 | 8000 | 2000 | success |
| 5 | 3 | swift | 2025-01-03 14:00:00 | 4000 | 1000 | success |
model_rates3 columns · 2 rows
| modeltext · key | input_per_millionreal | output_per_millionreal |
|---|
| swift | 1 | 4 |
| reason | 3 | 12 |
invoices4 columns · 6 rows
| idinteger · key | account_idinteger | periodtext | billed_amountreal |
|---|
| 1 | 1 | 2025-01 | 20 |
| 2 | 3 | 2025-01 | 80 |
| 3 | 4 | 2025-01 | 20 |
| 4 | 6 | 2025-01 | 80 |
| 5 | 1 | 2025-02 | 20 |
payments3 columns · 7 rows
| idinteger · key | invoice_idinteger | amountreal |
|---|
| 1 | 1 | 10 |
| 2 | 1 | 10 |
| 3 | 2 | 80 |
| 4 | 3 | 15 |
| 5 | 4 | 80 |
refunds3 columns · 4 rows
| idinteger · key | invoice_idinteger | amountreal |
|---|
| 1 | 1 | 2 |
| 2 | 1 | 3 |
| 3 | 2 | 10 |
| 4 | 4 | 5 |
runtime