Write SQL and Python, run instantly in your browser, and track your progress.
You are a Revenue Analyst at Salesforce. The Finance team needs a comprehensive view of subscription performance by status. For each status, calculate the count of subscriptions, total seats, and average seats per subscription.
| Column Name | Type |
|---|---|
| subscription_id | int64 |
| org_id | int64 |
| plan_id | int64 |
| start_date | object |
You are a Revenue Analyst at Salesforce. The Finance team needs a comprehensive view of subscription performance by status. For each status, calculate the count of subscriptions, total seats, and average seats per subscription.
| 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_purchased | int64 |
| seats_purchased | int64 |
| auto_renew | int64 |
| auto_renew | int64 |
| payment_method | object |
| payment_method | object |
| discount_percent | int64 |
| discount_percent | int64 |
| sales_rep | object |
| sales_rep | object |
| created_at | object |
| created_at | object |
| subscription_id | org_id | plan_id | start_date | end_date | status | seats_purchased | auto_renew | payment_method | discount_percent | sales_rep | created_at |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 4 |
| subscription_id | org_id | plan_id | start_date | end_date | status | seats_purchased | auto_renew | payment_method | discount_percent | sales_rep | created_at |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 4 |
| status | sub_count | total_seats | avg_seats |
|---|---|---|---|
| active | 33 | 8266 | 250.48 |
| canceled | 8 | 768 | 96 |
| trialing | 8 | 1843 | 230.38 |
| suspended | 6 | 406 | 67.67 |
| status | sub_count | total_seats | avg_seats |
|---|---|---|---|
| active | 33 | 8266 | 250.48 |
| canceled | 8 | 768 | 96 |
| trialing | 8 | 1843 | 230.38 |
| suspended | 6 | 406 | 67.67 |
5 rows returned.
5 rows returned.
1. Grouping:
2. Aggregation:
3. Output:
1. Grouping:
2. Aggregation:
3. Output:
| 1 |
| 1 |
| 2025-03-21 |
| 2025-03-21 |
| trialing |
| trialing |
| 906 |
| 906 |
| 1 |
| 1 |
| card |
| card |
| 25 |
| 25 |
| 2025-03-21 |
| 2025-03-21 |
| 2 | 34 | 4 | 2025-04-24 | 2025-07-01 | canceled | 212 | 0 | invoice | 0 | 2025-04-24 |
| 2 | 34 | 4 | 2025-04-24 | 2025-07-01 | canceled | 212 | 0 | invoice | 0 | 2025-04-24 |
| 3 | 7 | 6 | 2025-06-05 | 2025-07-15 | suspended | 2 | 1 | card | 10 | 2025-06-05 |
| 3 | 7 | 6 | 2025-06-05 | 2025-07-15 | suspended | 2 | 1 | card | 10 | 2025-06-05 |
| 4 | 7 | 12 | 2025-01-10 | active | 16 | 1 | card | 15 | Alex Thompson | 2025-01-10 |
| 4 | 7 | 12 | 2025-01-10 | active | 16 | 1 | card | 15 | Alex Thompson | 2025-01-10 |
| 5 | 1 | 12 | 2025-06-28 | 2025-08-24 | canceled | 43 | 1 | card | 0 | Jordan Lee | 2025-06-28 |
| 5 | 1 | 12 | 2025-06-28 | 2025-08-24 | canceled | 43 | 1 | card | 0 | Jordan Lee | 2025-06-28 |
| past_due | 5 | 603 | 120.60 |
| past_due | 5 | 603 | 120.60 |