Write SQL and Python, run instantly in your browser, and track your progress.
You are a Revenue Operations Analyst at Salesforce. The team needs to analyze recent billing activity across customer subscriptions to identify revenue recognition patterns and optimize cash flow forecasting. For each active subscription, your task is to identify the most recent invoice issued within the last 180 days to support dunning processes, renewal predictions, and customer health scoring in Salesforce's Customer 360 platform.
| Column Name | Type |
|---|---|
| invoice_id | INTEGER |
| subscription_id | INTEGER |
| invoice_number | TEXT |
You are a Revenue Operations Analyst at Salesforce. The team needs to analyze recent billing activity across customer subscriptions to identify revenue recognition patterns and optimize cash flow forecasting. For each active subscription, your task is to identify the most recent invoice issued within the last 180 days to support dunning processes, renewal predictions, and customer health scoring in Salesforce's Customer 360 platform.
| Column Name | Type |
|---|---|
| invoice_id | INTEGER |
| subscription_id | INTEGER |
| invoice_number | TEXT |
| period_start |
| period_start |
| TEXT |
| 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 |
|---|
| subscription_id | invoice_id | issue_date | total_cents | invoice_rank |
|---|---|---|---|---|
| 2 | 7 | 2025-07-23 | 575553 | 1 |
| 3 | 10 | 2025-08-04 | 21089 | 1 |
| 5 | 13 | 2025-07-02 | 556632 | 1 |
| subscription_id | invoice_id | issue_date | total_cents | invoice_rank |
|---|---|---|---|---|
| 2 | 7 | 2025-07-23 | 575553 | 1 |
| 3 | 10 | 2025-08-04 | 21089 | 1 |
| 5 | 13 | 2025-07-02 | 556632 | 1 |
Showing first 5 of 42 rows. Latest invoice per subscription (last 180 days).
Showing first 5 of 42 rows. Latest invoice per subscription (last 180 days).
Output Requirements:
Business Logic:
Time Window & Filters:
Output Requirements:
Business Logic:
Time Window & Filters:
| 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 |
| 7 | 18 | 2025-08-27 | 47861 | 1 |
| 7 | 18 | 2025-08-27 | 47861 | 1 |
| 8 | 19 | 2025-06-30 | 1054000 | 1 |
| 8 | 19 | 2025-06-30 | 1054000 | 1 |
Sorting Requirements:
Sorting Requirements:
| 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 |