Write SQL and Python, run instantly in your browser, and track your progress.
You are a Logistics Analyst at Amazon. The Operations team needs to track in-transit shipments where payment has been successfully captured. These represent confirmed revenue in transit to customers.
Find all shipments that are currently in transit with a positive shipping fee, where the related order has a captured payment. Return the shipment identifier, order identifier, service level, and shipping fee. Sort by highest fee first; break ties by the smallest shipment identifier.
| Column Name | Type |
|---|---|
| shipment_id | INTEGER |
| order_id | INTEGER |
| warehouse_code | TEXT |
You are a Logistics Analyst at Amazon. The Operations team needs to track in-transit shipments where payment has been successfully captured. These represent confirmed revenue in transit to customers.
Find all shipments that are currently in transit with a positive shipping fee, where the related order has a captured payment. Return the shipment identifier, order identifier, service level, and shipping fee. Sort by highest fee first; break ties by the smallest shipment identifier.
| Column Name | Type |
|---|---|
| shipment_id | INTEGER |
| order_id | INTEGER |
| warehouse_code | TEXT |
| carrier_name | TEXT |
| carrier_name | TEXT |
| service_level | TEXT |
| service_level | TEXT |
| tracking_number | TEXT |
| tracking_number | TEXT |
| ship_date | TEXT |
| ship_date | TEXT |
| est_delivery_date | TEXT |
| est_delivery_date | TEXT |
| delivery_date | TEXT |
| delivery_date | TEXT |
| status | TEXT |
| status | TEXT |
| weight_kg | REAL |
| weight_kg | REAL |
| shipping_fee_charged | REAL |
| shipping_fee_charged | REAL |
| 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 |
| shipment_id | order_id | warehouse_code | carrier_name | service_level | tracking_number | ship_date | est_delivery_date | delivery_date | status | weight_kg | shipping_fee_charged |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 2 |
| shipment_id | order_id | warehouse_code | carrier_name | service_level | tracking_number | ship_date | est_delivery_date | delivery_date | status | weight_kg | shipping_fee_charged |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 2 |
| shipment_id | order_id | service_level | shipping_fee_charged |
|---|---|---|---|
| 43 | 54 | two_day | 19.99 |
| 2 | 3 | expedited | 14.99 |
| 6 | 7 | economy | 5.99 |
| 59 | 71 | standard | 5.0 |
| shipment_id | order_id | service_level | shipping_fee_charged |
|---|---|---|---|
| 43 | 54 | two_day | 19.99 |
| 2 | 3 | expedited | 14.99 |
| 6 | 7 | economy | 5.99 |
| 59 | 71 | standard | 5.0 |
5 rows returned.
5 rows returned.
1. Output Columns: - shipment identifier - order identifier - service level - shipping fee charged 2. Filtering: - Include only in-transit shipments with shipping_fee_charged > 0 - Must have a captured payment for the related order 3. Ordering: - Sort by shipping_fee_charged (descending) - Tie-breaker: shipment_id (ascending)
1. Output Columns: - shipment identifier - order identifier - service level - shipping fee charged 2. Filtering: - Include only in-transit shipments with shipping_fee_charged > 0 - Must have a captured payment for the related order 3. Ordering: - Sort by shipping_fee_charged (descending) - Tie-breaker: shipment_id (ascending)
| 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 |
| W3-Atlanta |
| W3-Atlanta |
| UPS |
| UPS |
| economy |
| economy |
| TRK100002 |
| TRK100002 |
| 2025-02-25 |
| 2025-02-25 |
| 2025-03-04 |
| 2025-03-04 |
| 2025-03-05 |
| 2025-03-05 |
| delivered |
| delivered |
| 3.04 |
| 3.04 |
| 5.99 |
| 5.99 |
| 2 | 3 | W4-Dallas | USPS | expedited | TRK100003 | 2025-02-15 | 2025-02-18 | in_transit | 9.2 | 14.99 |
| 2 | 3 | W4-Dallas | USPS | expedited | TRK100003 | 2025-02-15 | 2025-02-18 | in_transit | 9.2 | 14.99 |
| 3 | 4 | W2-Chicago | USPS | standard | TRK100004 | 2025-02-07 | 2025-02-12 | picked_up | 7.17 | 5.0 |
| 3 | 4 | W2-Chicago | USPS | standard | TRK100004 | 2025-02-07 | 2025-02-12 | picked_up | 7.17 | 5.0 |
| 4 | 5 | W3-Atlanta | UPS | standard | TRK100005 | 2025-06-26 | 2025-07-01 | return_to_sender | 6.35 | 5.0 |
| 4 | 5 | W3-Atlanta | UPS | standard | TRK100005 | 2025-06-26 | 2025-07-01 | return_to_sender | 6.35 | 5.0 |
| 5 | 6 | W3-Atlanta | UPS | standard | TRK100006 | 2025-04-08 | 2025-04-13 | 2025-04-12 | delivered | 4.66 | 9.99 |
| 5 | 6 | W3-Atlanta | UPS | standard | TRK100006 | 2025-04-08 | 2025-04-13 | 2025-04-12 | delivered | 4.66 | 9.99 |
| 18 | 22 | economy | 3.0 |
| 18 | 22 | economy | 3.0 |