Write SQL and Python, run instantly in your browser, and track your progress.
You are a Revenue Analyst at Stripe. The Finance team needs to analyze invoice trends for each subscription by comparing current invoices to both previous and next invoices. Use shift with positive and negative values to create lag (previous) and lead (next) columns.
| Column Name | Type |
|---|---|
| invoice_id | int64 |
| subscription_id | int64 |
| invoice_number | object |
| issue_date |
You are a Revenue Analyst at Stripe. The Finance team needs to analyze invoice trends for each subscription by comparing current invoices to both previous and next invoices. Use shift with positive and negative values to create lag (previous) and lead (next) columns.
| Column Name | Type |
|---|---|
| invoice_id | int64 |
| subscription_id | int64 |
| invoice_number | object |
| issue_date |
| object |
| object |
| total_cents | int64 |
| total_cents | int64 |
| status | object |
| status | object |
| invoice_id | subscription_id | invoice_number | issue_date | total_cents | status |
|---|---|---|---|---|---|
| 1 | 100 | INV-001 | 2025-01-15 | 50000 | paid |
| 2 | 100 | INV-002 |
| invoice_id | subscription_id | invoice_number | issue_date | total_cents | status |
|---|---|---|---|---|---|
| 1 | 100 | INV-001 | 2025-01-15 | 50000 | paid |
| 2 | 100 | INV-002 |
| invoice_id | subscription_id | total_cents | prev_amount | next_amount |
|---|---|---|---|---|
| 1 | 1 | 0 | NaN | 0 |
| 2 | 1 | 0 | 0 | 0 |
| 3 | 1 | 0 | 0 | NaN |
| invoice_id | subscription_id | total_cents | prev_amount | next_amount |
|---|---|---|---|---|
| 1 | 1 | 0 | NaN | 0 |
| 2 | 1 | 0 | 0 | 0 |
| 3 | 1 | 0 | 0 | NaN |
Showing first 5 of 99 rows.
Showing first 5 of 99 rows.
1. Data Selection:
2. Window Functions:
3. Output:
1. Data Selection:
2. Window Functions:
3. Output:
| 2025-02-15 |
| 2025-02-15 |
| 55000 |
| 55000 |
| paid |
| paid |
| 3 | 100 | INV-003 | 2025-03-15 | 52000 | open |
| 3 | 100 | INV-003 | 2025-03-15 | 52000 | open |
| 4 | 200 | INV-004 | 2025-01-20 | 80000 | paid |
| 4 | 200 | INV-004 | 2025-01-20 | 80000 | paid |
| 5 | 200 | INV-005 | 2025-02-20 | 85000 | paid |
| 5 | 200 | INV-005 | 2025-02-20 | 85000 | paid |
| 4 |
| 4 |
| 2 |
| 2 |
| 591690 |
| 591690 |
| NaN |
| NaN |
| 537900 |
| 537900 |
| 5 | 2 | 537900 | 591690 | 537900 |
| 5 | 2 | 537900 | 591690 | 537900 |