Write SQL and Python, run instantly in your browser, and track your progress.
You are a Finance Analyst at Salesforce. The team wants the freshest payment signal for every subscription. Your task is to return, for each subscription, the most recent successfully processed payment, including the payment identifier, processed time, method, provider, and amount. Use the invoicing link to attribute payments to subscriptions. Consider only the standard currency. Sort by the smallest subscription identifier; break ties by the smallest payment identifier.
| Column Name | Type |
|---|---|
| payment_id | INTEGER |
| invoice_id | INTEGER |
| method | TEXT |
You are a Finance Analyst at Salesforce. The team wants the freshest payment signal for every subscription. Your task is to return, for each subscription, the most recent successfully processed payment, including the payment identifier, processed time, method, provider, and amount. Use the invoicing link to attribute payments to subscriptions. Consider only the standard currency. Sort by the smallest subscription identifier; break ties by the smallest payment identifier.
| Column Name | Type |
|---|---|
| payment_id | INTEGER |
| invoice_id | INTEGER |
| method | TEXT |
| processor |
| processor |
| TEXT |
| TEXT |
| status | TEXT |
| status | TEXT |
| amount_cents | INTEGER |
| amount_cents | INTEGER |
| currency | TEXT |
| currency | TEXT |
| processed_at | TEXT |
| processed_at | TEXT |
| transaction_id | TEXT |
| transaction_id | TEXT |
| payment_id | invoice_id | method | processor | status | amount_cents | currency | processed_at | transaction_id |
|---|---|---|---|---|---|---|---|---|
| 1 | 1 | paypal | adyen | succeeded | 0 |
| payment_id | invoice_id | method | processor | status | amount_cents | currency | processed_at | transaction_id |
|---|---|---|---|---|---|---|---|---|
| 1 | 1 | paypal | adyen | succeeded | 0 |
| Column Name | Type |
|---|---|
| invoice_id | INTEGER |
| subscription_id | INTEGER |
| invoice_number | TEXT |
| period_start | TEXT |
| period_end | TEXT |
| issue_date | TEXT |
| Column Name | Type |
|---|---|
| invoice_id | INTEGER |
| subscription_id | INTEGER |
| invoice_number | TEXT |
| period_start | TEXT |
| period_end | TEXT |
| issue_date | 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 | payment_id | processed_at | amount_cents | method | processor | payment_rank |
|---|---|---|---|---|---|---|
| 1 | 2 | 2025-06-15 08:00:50 | 0 | wire | adyen | 1 |
| 2 | 6 | 2025-08-03 05:19:16 | 575553 | paypal | braintree |
| subscription_id | payment_id | processed_at | amount_cents | method | processor | payment_rank |
|---|---|---|---|---|---|---|
| 1 | 2 | 2025-06-15 08:00:50 | 0 | wire | adyen | 1 |
| 2 | 6 | 2025-08-03 05:19:16 | 575553 | paypal | braintree |
Showing first 5 of 41 rows. Latest successful payment per subscription.
Showing first 5 of 41 rows. Latest successful payment per subscription.
| USD |
| USD |
| 2025-03-27 21:54:39 |
| 2025-03-27 21:54:39 |
| TXN00000001 |
| TXN00000001 |
| 2 | 3 | wire | adyen | captured | 0 | USD | 2025-06-15 08:00:50 | TXN00000002 |
| 2 | 3 | wire | adyen | captured | 0 | USD | 2025-06-15 08:00:50 | TXN00000002 |
| 3 | 4 | card | stripe | failed | 251830 | USD | 2025-04-24 14:51:14 |
| 3 | 4 | card | stripe | failed | 251830 | USD | 2025-04-24 14:51:14 |
| 4 | 5 | card | internal | captured | 537900 | USD | 2025-06-01 05:45:57 | TXN00000004 |
| 4 | 5 | card | internal | captured | 537900 | USD | 2025-06-01 05:45:57 | TXN00000004 |
| 5 | 6 | card | braintree | chargeback | 537900 | USD | 2025-08-02 03:00:45 | TXN00000005 |
| 5 | 6 | card | braintree | chargeback | 537900 | USD | 2025-08-02 03:00:45 | TXN00000005 |
| 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 |
| 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 |
| 1 |
| 1 |
| 3 | 9 | 2025-08-19 04:19:48 | 21089 | paypal | stripe | 1 |
| 3 | 9 | 2025-08-19 04:19:48 | 21089 | paypal | stripe | 1 |
| 4 | 11 | 2025-05-17 04:51:52 | 243015 | ach | braintree | 1 |
| 4 | 11 | 2025-05-17 04:51:52 | 243015 | ach | braintree | 1 |
| 5 | 12 | 2025-08-14 10:12:00 | 556632 | paypal | internal | 1 |
| 5 | 12 | 2025-08-14 10:12:00 | 556632 | paypal | internal | 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 |