Write SQL and Python, run instantly in your browser, and track your progress.
For delivered orders that shipped, Merch Ops wants to analyze the single highest-value line item per order. Provide the order identifier, line identifier, product identifier, and the line value. Consider only delivered orders that have shipped. Sort by highest line value first; break ties by the smallest order identifier.
| Column Name | Type |
|---|---|
| order_id | INTEGER |
| order_number | TEXT |
| customer_id | INTEGER |
| order_datetime | TEXT |
For delivered orders that shipped, Merch Ops wants to analyze the single highest-value line item per order. Provide the order identifier, line identifier, product identifier, and the line value. Consider only delivered orders that have shipped. Sort by highest line value first; break ties by the smallest order identifier.
| Column Name | Type |
|---|---|
| order_id | INTEGER |
| order_number | TEXT |
| customer_id | INTEGER |
| 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 |
| Column Name | Type |
|---|---|
| shipment_id | INTEGER |
| order_id | INTEGER |
| warehouse_code | TEXT |
| carrier_name | TEXT |
| service_level | TEXT |
| tracking_number | TEXT |
| Column Name | Type |
|---|---|
| shipment_id | INTEGER |
| order_id | INTEGER |
| warehouse_code | TEXT |
| carrier_name | TEXT |
| service_level | TEXT |
| tracking_number | TEXT |
| Column Name | Type |
|---|---|
| order_item_id | INTEGER |
| order_id | INTEGER |
| product_id | INTEGER |
| quantity | INTEGER |
| unit_price | REAL |
| line_subtotal | REAL |
| Column Name | Type |
|---|---|
| order_item_id | INTEGER |
| order_id | INTEGER |
| product_id | INTEGER |
| quantity | INTEGER |
| unit_price | REAL |
| line_subtotal | REAL |
| order_item_id | order_id | product_id | quantity | unit_price | line_subtotal | tax_amount | discount_amount | fulfillment_status | return_requested_at | return_reason |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 10 | 1 |
| order_item_id | order_id | product_id | quantity | unit_price | line_subtotal | tax_amount | discount_amount | fulfillment_status | return_requested_at | return_reason |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | 1 | 10 | 1 |
| 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 |
|---|
| 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 |
| order_id | order_item_id | product_id | line_subtotal |
|---|---|---|---|
| 67 | 109 | 31 | 834.6 |
| 29 | 47 | 28 | 823.65 |
| 75 | 121 | 35 | 754.71 |
| 2 | 2 | 8 | 601.53 |
| order_id | order_item_id | product_id | line_subtotal |
|---|---|---|---|
| 67 | 109 | 31 | 834.6 |
| 29 | 47 | 28 | 823.65 |
| 75 | 121 | 35 | 754.71 |
| 2 | 2 | 8 | 601.53 |
21 rows returned.
21 rows returned.
1. Output Columns:
2. Filtering:
3. Ordering:
1. Output Columns:
2. Filtering:
3. Ordering:
| 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 |
| tax_amount | REAL |
| tax_amount | REAL |
| discount_amount | REAL |
| discount_amount | REAL |
| fulfillment_status | TEXT |
| fulfillment_status | TEXT |
| return_requested_at | TEXT |
| return_requested_at | TEXT |
| return_reason | TEXT |
| return_reason | TEXT |
| 50.96 |
| 50.96 |
| 50.96 |
| 50.96 |
| 4.14 |
| 4.14 |
| 0.0 |
| 0.0 |
| shipped |
| shipped |
| 2 | 2 | 8 | 3 | 200.51 | 601.53 | 53.2 | 0.0 | delivered |
| 2 | 2 | 8 | 3 | 200.51 | 601.53 | 53.2 | 0.0 | delivered |
| 3 | 3 | 44 | 2 | 34.13 | 68.26 | 6.25 | 0.99 | shipped |
| 3 | 3 | 44 | 2 | 34.13 | 68.26 | 6.25 | 0.99 | shipped |
| 4 | 4 | 47 | 1 | 290.99 | 290.99 | 23.29 | 0.0 | shipped |
| 4 | 4 | 47 | 1 | 290.99 | 290.99 | 23.29 | 0.0 | shipped |
| 5 | 5 | 36 | 3 | 189.87 | 569.61 | 46.78 | 79.38 | backordered |
| 5 | 5 | 36 | 3 | 189.87 | 569.61 | 46.78 | 79.38 | backordered |
| 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 |
| 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 |
| 68 | 113 | 30 | 548.25 |
| 68 | 113 | 30 | 548.25 |
| 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 |