Write SQL and Python, run instantly in your browser, and track your progress.
You are a Sales Analyst at Amazon. Sales leadership wants a short list of the highest-value customers from the last 90 days (ending August 30, 2025). Identify customers whose delivered orders have the largest total captured payments. Return the top 5 with their rank. Sort by highest captured total; break ties by smallest customer ID.
| Column Name | Type |
|---|---|
| order_id | INTEGER |
| order_number | TEXT |
| customer_id | INTEGER |
| order_datetime |
You are a Sales Analyst at Amazon. Sales leadership wants a short list of the highest-value customers from the last 90 days (ending August 30, 2025). Identify customers whose delivered orders have the largest total captured payments. Return the top 5 with their rank. Sort by highest captured total; break ties by smallest customer ID.
| Column Name | Type |
|---|---|
| order_id | INTEGER |
| order_number | TEXT |
| customer_id | INTEGER |
| order_datetime |
| TEXT |
| TEXT |
| status | TEXT |
| status | TEXT |
| fulfillment_type | TEXT |
| fulfillment_type | TEXT |
| ship_city | TEXT |
| ship_city | TEXT |
| ship_state | TEXT |
| ship_state | TEXT |
| ship_country | TEXT |
| ship_country | TEXT |
| shipping_service_level | TEXT |
| shipping_service_level | TEXT |
| subtotal | REAL |
| subtotal | REAL |
| shipping_fee | REAL |
| shipping_fee | REAL |
| tax | REAL |
| tax | REAL |
| discount | REAL |
| discount | REAL |
| total_amount | REAL |
| total_amount | REAL |
| payment_status | TEXT |
| payment_status | TEXT |
| created_at | TEXT |
| created_at | TEXT |
| order_id | order_number | customer_id | order_datetime | status | fulfillment_type | ship_city | ship_state | ship_country | shipping_service_level | subtotal | shipping_fee | tax | discount | total_amount |
|---|
| order_id | order_number | customer_id | order_datetime | status | fulfillment_type | ship_city | ship_state | ship_country | shipping_service_level | subtotal | shipping_fee | tax | discount | total_amount |
|---|
| Column Name | Type |
|---|---|
| payment_id | INTEGER |
| order_id | INTEGER |
| method | TEXT |
| processor | TEXT |
| amount | REAL |
| currency | TEXT |
| Column Name | Type |
|---|---|
| payment_id | INTEGER |
| order_id | INTEGER |
| method | TEXT |
| processor | TEXT |
| amount | REAL |
| currency | TEXT |
| payment_id | order_id | method | processor | amount | currency | status | transaction_id | auth_time | capture_time | refund_time |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | apple_pay | stripe |
| payment_id | order_id | method | processor | amount | currency | status | transaction_id | auth_time | capture_time | refund_time |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | apple_pay | stripe |
| customer_id | total_captured_amount | rn |
|---|---|---|
| 12 | 444.56 | 1 |
| 44 | 131.01 | 2 |
| customer_id | total_captured_amount | rn |
|---|---|---|
| 12 | 444.56 | 1 |
| 44 | 131.01 | 2 |
2 rows returned. Customer 12 ranks first with $444.56 in captured payments from delivered orders. Only captured payments from delivered orders within the 90-day window are included.
2 rows returned. Customer 12 ranks first with $444.56 in captured payments from delivered orders. Only captured payments from delivered orders within the 90-day window are included.
| payment_status |
|---|
| payment_status |
|---|
| created_at |
|---|
| created_at |
|---|
| 1 | ORD-10001 | 46 | 2025-08-20 07:01:34 | shipped | pickup | pickup | 50.96 | 0.0 | 4.36 | 0.0 | 55.32 | captured | 2025-08-20 07:01:34 | |||
| 2 | ORD-10002 | 19 | 2025-02-24 04:56:03 | delivered | ship |
| 1 | ORD-10001 | 46 | 2025-08-20 07:01:34 | shipped | pickup | pickup | 50.96 | 0.0 | 4.36 | 0.0 | 55.32 | captured | 2025-08-20 07:01:34 | |||
| 2 | ORD-10002 | 19 | 2025-02-24 04:56:03 | delivered | ship |
| status | TEXT |
| status | TEXT |
| transaction_id | TEXT |
| transaction_id | TEXT |
| auth_time | TEXT |
| auth_time | TEXT |
| capture_time | TEXT |
| capture_time | TEXT |
| refund_time | TEXT |
| refund_time | TEXT |
| 55.32 |
| 55.32 |
| USD |
| USD |
| captured |
| captured |
| TX200002 |
| TX200002 |
| 2025-08-20 07:01:34 |
| 2025-08-20 07:01:34 |
| 2025-08-20 15:01:34 |
| 2025-08-20 15:01:34 |
| 2 | 2 | google_pay | square | 662.48 | USD | partial_refund | TX200003 | 2025-02-24 04:56:03 | 2025-02-26 06:56:03 | 2025-03-04 06:56:03 |
| 2 | 2 | google_pay | square | 662.48 | USD | partial_refund | TX200003 | 2025-02-24 04:56:03 | 2025-02-26 06:56:03 | 2025-03-04 06:56:03 |
| 3 | 3 | bank_transfer | internal | 88.66 | USD | captured | TX200004 | 2025-02-13 17:43:43 | 2025-02-14 16:43:43 |
| 3 | 3 | bank_transfer | internal | 88.66 | USD | captured | TX200004 | 2025-02-13 17:43:43 | 2025-02-14 16:43:43 |
| 4 | 4 | card | adyen | 322.87 | USD | captured | TX200005 | 2025-02-07 13:00:50 | 2025-02-09 11:00:50 |
| 4 | 4 | card | adyen | 322.87 | USD | captured | TX200005 | 2025-02-07 13:00:50 | 2025-02-09 11:00:50 |
| 5 | 5 | bank_transfer | stripe | 629.34 | USD | captured | TX200006 | 2025-06-24 03:59:58 | 2025-06-26 01:59:58 |
| 5 | 5 | bank_transfer | stripe | 629.34 | USD | captured | TX200006 | 2025-06-24 03:59:58 | 2025-06-26 01:59:58 |
| Austin |
| Austin |
| TX |
| TX |
| US |
| US |
| economy |
| economy |
| 601.53 |
| 601.53 |
| 5.99 |
| 5.99 |
| 54.96 |
| 54.96 |
| 0.0 |
| 0.0 |
| 662.48 |
| 662.48 |
| partial_refund |
| partial_refund |
| 2025-02-24 04:56:03 |
| 2025-02-24 04:56:03 |
| 3 | ORD-10003 | 21 | 2025-02-13 17:43:43 | shipped | ship | London | ENG | UK | expedited | 68.26 | 14.99 | 5.41 | 0.0 | 88.66 | captured | 2025-02-13 17:43:43 |
| 3 | ORD-10003 | 21 | 2025-02-13 17:43:43 | shipped | ship | London | ENG | UK | expedited | 68.26 | 14.99 | 5.41 | 0.0 | 88.66 | captured | 2025-02-13 17:43:43 |
| 4 | ORD-10004 | 8 | 2025-02-07 13:00:50 | shipped | ship | Calgary | AB | CA | standard | 290.99 | 5.0 | 26.88 | 0.0 | 322.87 | captured | 2025-02-07 13:00:50 |
| 4 | ORD-10004 | 8 | 2025-02-07 13:00:50 | shipped | ship | Calgary | AB | CA | standard | 290.99 | 5.0 | 26.88 | 0.0 | 322.87 | captured | 2025-02-07 13:00:50 |
| 5 | ORD-10005 | 8 | 2025-06-24 03:59:58 | packed | ship | Edmonton | AB | CA | standard | 569.61 | 5.0 | 54.73 | 0.0 | 629.34 | captured | 2025-06-24 03:59:58 |
| 5 | ORD-10005 | 8 | 2025-06-24 03:59:58 | packed | ship | Edmonton | AB | CA | standard | 569.61 | 5.0 | 54.73 | 0.0 | 629.34 | captured | 2025-06-24 03:59:58 |