Write SQL and Python, run instantly in your browser, and track your progress.
You are a Regional Analytics Manager at Walmart. The team wants to see the top completed purchases in each U.S. state for benchmarking. Your task is to provide, for each state, the three purchases with the highest billed totals. Show the state, purchase identifier, billed amount, and purchase time, and include a rank of 1–3 within each state. Consider only completed purchases with successful payment and a U.S. shipping destination. Sort by state alphabetically; within each state, show the highest billed first and break ties by the smallest purchase identifier.
| Column Name | Type |
|---|---|
| order_id | INTEGER |
| order_number | TEXT |
| customer_id | INTEGER |
You are a Regional Analytics Manager at Walmart. The team wants to see the top completed purchases in each U.S. state for benchmarking. Your task is to provide, for each state, the three purchases with the highest billed totals. Show the state, purchase identifier, billed amount, and purchase time, and include a rank of 1–3 within each state. Consider only completed purchases with successful payment and a U.S. shipping destination. Sort by state alphabetically; within each state, show the highest billed first and break ties by the smallest purchase identifier.
| Column Name | Type |
|---|---|
| order_id | INTEGER |
| order_number | TEXT |
| customer_id | INTEGER |
| order_datetime | TEXT |
| order_datetime | 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 |
|---|
| ship_state | order_id | order_datetime | total_amount | state_rank |
|---|---|---|---|---|
| IL | 68 | 2025-04-14 10:49:21 | 581.84 | 1 |
| IL | 28 | 2024-10-21 09:33:19 | 492.19 | 2 |
| IL | 15 | 2025-08-21 08:37:53 | 131.01 | 3 |
| ship_state | order_id | order_datetime | total_amount | state_rank |
|---|---|---|---|---|
| IL | 68 | 2025-04-14 10:49:21 | 581.84 | 1 |
| IL | 28 | 2024-10-21 09:33:19 | 492.19 | 2 |
| IL | 15 | 2025-08-21 08:37:53 | 131.01 | 3 |
Showing first 5 of 7 rows. Top 3 delivered purchases per US state.
Showing first 5 of 7 rows. Top 3 delivered purchases per US state.
| 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 |
| MA | 31 | 2024-12-27 07:43:16 | 85.68 | 1 |
| MA | 31 | 2024-12-27 07:43:16 | 85.68 | 1 |
| NV | 46 | 2024-12-30 18:31:28 | 261.92 | 1 |
| NV | 46 | 2024-12-30 18:31:28 | 261.92 | 1 |
| 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 |