Write SQL and Python, run instantly in your browser, and track your progress.
You are a Merchandising Analyst at Amazon. The team wants quick visibility into which items dominate the bill within each completed purchase. For each qualifying purchase, your task is to show the two highest billed line items, including their amounts and a rank that indicates first and second. Consider only line items that were successfully fulfilled and have nonnegative taxes; exclude anything else. Sort by the smallest purchase identifier; within each purchase, show the highest billed line first and break ties by the smallest line identifier.
| Column Name | Type |
|---|---|
| order_item_id | INTEGER |
| order_id | INTEGER |
| product_id | INTEGER |
You are a Merchandising Analyst at Amazon. The team wants quick visibility into which items dominate the bill within each completed purchase. For each qualifying purchase, your task is to show the two highest billed line items, including their amounts and a rank that indicates first and second. Consider only line items that were successfully fulfilled and have nonnegative taxes; exclude anything else. Sort by the smallest purchase identifier; within each purchase, show the highest billed line first and break ties by the smallest line identifier.
| Column Name | Type |
|---|---|
| order_item_id | INTEGER |
| order_id | INTEGER |
| product_id | INTEGER |
| quantity | INTEGER |
| quantity | INTEGER |
| unit_price | REAL |
| unit_price | REAL |
| line_subtotal | REAL |
| line_subtotal | 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 |
| 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_item_id | line_subtotal | item_rank |
|---|---|---|---|
| 2 | 2 | 601.53 | 1 |
| 6 | 6 | 227.64 | 1 |
| 10 | 12 | 230.12 | 1 |
| 11 | 13 | 418.24 | 1 |
| order_id | order_item_id | line_subtotal | item_rank |
|---|---|---|---|
| 2 | 2 | 601.53 | 1 |
| 6 | 6 | 227.64 | 1 |
| 10 | 12 | 230.12 | 1 |
| 11 | 13 | 418.24 | 1 |
Showing first 5 of 33 rows. Top 2 line items per purchase by subtotal.
Showing first 5 of 33 rows. Top 2 line items per purchase by subtotal.
| 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 |
| 15 | 20 | 118.34 | 1 |
| 15 | 20 | 118.34 | 1 |