Write SQL and Python, run instantly in your browser, and track your progress.
You are a logistics analyst at Wayfair, a large online retailer of home goods. To manage customer expectations and proactively handle any potential shipping issues, your team needs a priority list of high-value orders that are currently in the final stages of fulfillment (either shipped or already delivered). This list helps the support team focus on the most critical orders.
Your task is to query the orders table for all US orders that have a total_amount of $100 or more, a payment_status of 'captured', and an order status of either 'shipped' or 'delivered'.
The final list should be sorted by the order_datetime to show the most recent orders first.
| Column Name |
|---|
You are a logistics analyst at Wayfair, a large online retailer of home goods. To manage customer expectations and proactively handle any potential shipping issues, your team needs a priority list of high-value orders that are currently in the final stages of fulfillment (either shipped or already delivered). This list helps the support team focus on the most critical orders.
Your task is to query the orders table for all US orders that have a total_amount of $100 or more, a payment_status of 'captured', and an order status of either 'shipped' or 'delivered'.
The final list should be sorted by the order_datetime to show the most recent orders first.
| Column Name |
|---|
| Type |
|---|
| Type |
|---|
| order_id | INTEGER |
| order_number | TEXT |
| customer_id | INTEGER |
| order_datetime | TEXT |
| status | TEXT |
| fulfillment_type | TEXT |
| ship_city | TEXT |
| ship_state | TEXT |
| ship_country | TEXT |
| order_id | INTEGER |
| order_number | TEXT |
| customer_id | INTEGER |
| order_datetime | TEXT |
| status | TEXT |
| fulfillment_type | TEXT |
| ship_city | TEXT |
| ship_state | TEXT |
| ship_country | 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 |
|---|
| order_id | order_number | status | total_amount | order_datetime |
|---|---|---|---|---|
| 35 | ORD-10035 | shipped | 105.78 | 2025-08-31 15:38:05 |
| 54 | ORD-10054 | shipped | 118.76 | 2025-08-25 03:46:13 |
| 15 | ORD-10015 | delivered | 131.01 | 2025-08-21 08:37:53 |
| order_id | order_number | status | total_amount | order_datetime |
|---|---|---|---|---|
| 35 | ORD-10035 | shipped | 105.78 | 2025-08-31 15:38:05 |
| 54 | ORD-10054 | shipped | 118.76 | 2025-08-25 03:46:13 |
| 15 | ORD-10015 | delivered | 131.01 | 2025-08-21 08:37:53 |
Showing first 5 of 11 rows. High-value US orders shipped or delivered with captured payment.
Showing first 5 of 11 rows. High-value US orders shipped or delivered with captured payment.
Your final output should be a result set that meets the following requirements:
1. Filtering:
ship_country is 'US'.payment_status is 'captured'.total_amount greater than or equal to 100.status is either 'shipped' or 'delivered'.2. Columns:
order_idYour final output should be a result set that meets the following requirements:
1. Filtering:
ship_country is 'US'.payment_status is 'captured'.total_amount greater than or equal to 100.status is either 'shipped' or 'delivered'.2. Columns:
order_id| shipping_service_level |
| shipping_service_level |
| TEXT |
| 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 |
| payment_status |
|---|
| payment_status |
|---|
| created_at |
|---|
| created_at |
|---|
| 1 | ORD-10001 | 46 | 2025-08-20 07:01:34 | shipped | 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 | 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 |
| 63 | ORD-10063 | shipped | 285.46 | 2025-04-29 05:55:04 |
| 63 | ORD-10063 | shipped | 285.46 | 2025-04-29 05:55:04 |
| 68 | ORD-10068 | delivered | 581.84 | 2025-04-14 10:49:21 |
| 68 | ORD-10068 | delivered | 581.84 | 2025-04-14 10:49:21 |
order_numberorder_numberstatusstatustotal_amounttotal_amountorder_datetimeorder_datetime3. Ordering:
3. Ordering:
order_datetime in descending order (newest first).order_id in ascending order.order_datetime in descending order (newest first).order_id in ascending order.| 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 |