Write SQL and Python, run instantly in your browser, and track your progress.
You are a Sales Operations Analyst at Salesforce. Sales leadership wants to see which subscriptions drive the most recent billed revenue in each organization. For the last 180 days, your task is to identify each subscription's latest invoice and rank subscriptions within the same organization by that invoice's billed amount. Return the top two per organization. Show the organization identifier, subscription identifier, latest invoice identifier, latest invoice billed amount, and a rank within the organization. Sort by the smallest organization identifier; within each organization, show highest billed first and break ties by the smallest subscription identifier.
| Column Name | Type |
|---|---|
| invoice_id | INTEGER |
| subscription_id | INTEGER |
| invoice_number |
You are a Sales Operations Analyst at Salesforce. Sales leadership wants to see which subscriptions drive the most recent billed revenue in each organization. For the last 180 days, your task is to identify each subscription's latest invoice and rank subscriptions within the same organization by that invoice's billed amount. Return the top two per organization. Show the organization identifier, subscription identifier, latest invoice identifier, latest invoice billed amount, and a rank within the organization. Sort by the smallest organization identifier; within each organization, show highest billed first and break ties by the smallest subscription identifier.
| Column Name | Type |
|---|---|
| invoice_id | INTEGER |
| subscription_id | INTEGER |
| invoice_number |
| TEXT |
| TEXT |
| period_start | TEXT |
| period_start | TEXT |
| period_end | TEXT |
| period_end | TEXT |
| issue_date | TEXT |
| issue_date | TEXT |
| due_date | TEXT |
| due_date | TEXT |
| status | TEXT |
| status | TEXT |
| subtotal_cents | INTEGER |
| subtotal_cents | INTEGER |
| tax_cents | INTEGER |
| tax_cents | INTEGER |
| discount_cents | INTEGER |
| discount_cents | INTEGER |
| total_cents | INTEGER |
| total_cents | INTEGER |
| amount_due_cents | INTEGER |
| amount_due_cents | INTEGER |
| amount_paid_cents | INTEGER |
| amount_paid_cents | INTEGER |
| currency | TEXT |
| currency | TEXT |
| invoice_id | subscription_id | invoice_number | period_start | period_end | issue_date | due_date | status | subtotal_cents | tax_cents | discount_cents | total_cents | amount_due_cents | amount_paid_cents |
|---|
| invoice_id | subscription_id | invoice_number | period_start | period_end | issue_date | due_date | status | subtotal_cents | tax_cents | discount_cents | total_cents | amount_due_cents | amount_paid_cents |
|---|
| Column Name | Type |
|---|---|
| subscription_id | INTEGER |
| org_id | INTEGER |
| plan_id | INTEGER |
| start_date | TEXT |
| end_date | TEXT |
| status | TEXT |
| Column Name | Type |
|---|---|
| subscription_id | INTEGER |
| org_id | INTEGER |
| plan_id | INTEGER |
| start_date | TEXT |
| end_date | TEXT |
| status | TEXT |
| 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 |
| org_id | subscription_id | invoice_id | total_cents | org_rank |
|---|---|---|---|---|
| 1 | 28 | 50 | 1321950 | 1 |
| 1 | 5 | 13 | 556632 | 2 |
| 2 | 10 | 21 | 1365000 | 1 |
| org_id | subscription_id | invoice_id | total_cents | org_rank |
|---|---|---|---|---|
| 1 | 28 | 50 | 1321950 | 1 |
| 1 | 5 | 13 | 556632 | 2 |
| 2 | 10 | 21 | 1365000 | 1 |
Showing first 5 of 35 rows. Top 2 subscriptions per org by latest invoice.
Showing first 5 of 35 rows. Top 2 subscriptions per org by latest invoice.
| currency |
|---|
| currency |
|---|
| 1 | 1 | INV-2025000001 | 2025-03-21 | 2025-04-19 | 2025-03-26 | 2025-04-25 | paid | 0 | 0 | 0 | 0 | 0 | 0 | USD |
| 2 | 1 | INV-2025000002 | 2025-04-20 | 2025-05-19 | 2025-04-20 | 2025-05-20 | open |
| 1 | 1 | INV-2025000001 | 2025-03-21 | 2025-04-19 | 2025-03-26 | 2025-04-25 | paid | 0 | 0 | 0 | 0 | 0 | 0 | USD |
| 2 | 1 | INV-2025000002 | 2025-04-20 | 2025-05-19 | 2025-04-20 | 2025-05-20 | open |
| seats_purchased | INTEGER |
| seats_purchased | INTEGER |
| auto_renew | INTEGER |
| auto_renew | INTEGER |
| payment_method | TEXT |
| payment_method | TEXT |
| discount_percent | INTEGER |
| discount_percent | INTEGER |
| sales_rep | TEXT |
| sales_rep | TEXT |
| created_at | TEXT |
| created_at | TEXT |
| 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 |
| 3 | 23 | 43 | 351750 | 1 |
| 3 | 23 | 43 | 351750 | 1 |
| 5 | 25 | 47 | 4284500 | 1 |
| 5 | 25 | 47 | 4284500 | 1 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| USD |
| USD |
| 3 | 1 | INV-2025000003 | 2025-05-20 | 2025-06-18 | 2025-05-23 | 2025-06-07 | paid | 0 | 0 | 0 | 0 | 0 | 0 | USD |
| 3 | 1 | INV-2025000003 | 2025-05-20 | 2025-06-18 | 2025-05-23 | 2025-06-07 | paid | 0 | 0 | 0 | 0 | 0 | 0 | USD |
| 4 | 2 | INV-2025000004 | 2025-04-24 | 2025-05-23 | 2025-04-24 | 2025-05-24 | past_due | 537900 | 53790 | 0 | 591690 | 339860 | 251830 | USD |
| 4 | 2 | INV-2025000004 | 2025-04-24 | 2025-05-23 | 2025-04-24 | 2025-05-24 | past_due | 537900 | 53790 | 0 | 591690 | 339860 | 251830 | USD |
| 5 | 2 | INV-2025000005 | 2025-05-24 | 2025-06-22 | 2025-05-25 | 2025-06-09 | paid | 537900 | 0 | 0 | 537900 | 0 | 537900 | USD |
| 5 | 2 | INV-2025000005 | 2025-05-24 | 2025-06-22 | 2025-05-25 | 2025-06-09 | paid | 537900 | 0 | 0 | 537900 | 0 | 537900 | USD |