Write SQL and Python, run instantly in your browser, and track your progress.
You are a Revenue Operations Analyst at Stripe. The Finance team needs to understand revenue collection performance across different subscription statuses. Merge subscription data with invoices and calculate billing metrics by subscription status.
| Column Name | Type |
|---|---|
| subscription_id | int64 |
| org_id | int64 |
| plan_id | int64 |
| start_date | object |
You are a Revenue Operations Analyst at Stripe. The Finance team needs to understand revenue collection performance across different subscription statuses. Merge subscription data with invoices and calculate billing metrics by subscription status.
| Column Name | Type |
|---|---|
| subscription_id | int64 |
| org_id | int64 |
| plan_id | int64 |
| start_date | object |
| end_date | object |
| end_date | object |
| status | object |
| status | object |
| seats | int64 |
| seats | int64 |
| Column Name | Type |
|---|---|
| invoice_id | int64 |
| subscription_id | int64 |
| invoice_number | object |
| status | object |
| total_cents | int64 |
| amount_paid_cents | int64 |
| Column Name | Type |
|---|---|
| invoice_id | int64 |
| subscription_id | int64 |
| invoice_number | object |
| status | object |
| total_cents | int64 |
| amount_paid_cents | int64 |
| subscription_id | org_id | plan_id | start_date | end_date | status | seats |
|---|---|---|---|---|---|---|
| 1 | 4 | 1 | 2025-03-21 | trialing | 906 | |
| 2 |
| subscription_id | org_id | plan_id | start_date | end_date | status | seats |
|---|---|---|---|---|---|---|
| 1 | 4 | 1 | 2025-03-21 | trialing | 906 | |
| 2 |
| invoice_id | subscription_id | invoice_number | status | total_cents | amount_paid_cents |
|---|---|---|---|---|---|
| 1 | 1 | INV-2025000001 | paid | 0 | 0 |
| 2 | 1 | INV-2025000002 |
| invoice_id | subscription_id | invoice_number | status | total_cents | amount_paid_cents |
|---|---|---|---|---|---|
| 1 | 1 | INV-2025000001 | paid | 0 | 0 |
| 2 | 1 | INV-2025000002 |
| subscription_status | subscription_count | invoice_count | total_billed | total_paid | collection_rate |
|---|---|---|---|---|---|
| active | 33 | 53 | 485766.24 | 302333.27 | 62.20 |
| trialing | 8 | 15 | 83096.24 | 18063.25 | 21.70 |
| canceled | 8 |
| subscription_status | subscription_count | invoice_count | total_billed | total_paid | collection_rate |
|---|---|---|---|---|---|
| active | 33 | 53 | 485766.24 | 302333.27 | 62.20 |
| trialing | 8 | 15 | 83096.24 | 18063.25 | 21.70 |
| canceled | 8 |
5 rows returned.
5 rows returned.
1. Data Selection:
2. Join:
3. Aggregation:
4. Output:
1. Data Selection:
2. Join:
3. Aggregation:
4. Output:
| 34 |
| 34 |
| 4 |
| 4 |
| 2025-04-24 |
| 2025-04-24 |
| 2025-07-01 |
| 2025-07-01 |
| canceled |
| canceled |
| 212 |
| 212 |
| 3 | 7 | 6 | 2025-06-05 | 2025-07-15 | suspended | 2 |
| 3 | 7 | 6 | 2025-06-05 | 2025-07-15 | suspended | 2 |
| 4 | 7 | 12 | 2025-01-10 | active | 16 |
| 4 | 7 | 12 | 2025-01-10 | active | 16 |
| 5 | 1 | 12 | 2025-06-28 | 2025-08-24 | canceled | 43 |
| 5 | 1 | 12 | 2025-06-28 | 2025-08-24 | canceled | 43 |
| open |
| open |
| 0 |
| 0 |
| 0 |
| 0 |
| 3 | 1 | INV-2025000003 | paid | 0 | 0 |
| 3 | 1 | INV-2025000003 | paid | 0 | 0 |
| 4 | 2 | INV-2025000004 | past_due | 591690 | 339860 |
| 4 | 2 | INV-2025000004 | past_due | 591690 | 339860 |
| 5 | 2 | INV-2025000005 | paid | 537900 | 537900 |
| 5 | 2 | INV-2025000005 | paid | 537900 | 537900 |
| 14 |
| 14 |
| 82964.77 |
| 82964.77 |
| 70329.34 |
| 70329.34 |
| 84.80 |
| 84.80 |
| past_due | 5 | 7 | 43838.57 | 32556.57 | 74.30 |
| past_due | 5 | 7 | 43838.57 | 32556.57 | 74.30 |
| suspended | 6 | 10 | 36141.95 | 36141.95 | 100 |
| suspended | 6 | 10 | 36141.95 | 36141.95 | 100 |