carrier that improved the most (biggest decrease in avg transit days), assuming they shipped in both halves.Use these exact table and column names in your query. Open Schema & Data under the editor to preview sample rows.
customers
5 cols
customer_idINTEGERnameTEXTcityTEXTcountryTEXTsignup_dateDATE
products
4 cols
product_idINTEGERproduct_nameTEXTcategoryTEXTpriceREAL
orders
5 cols
order_idINTEGERcustomer_idINTEGERorder_dateDATEamountREALstatusTEXT
order_items
4 cols
order_idINTEGERproduct_idINTEGERquantityINTEGERunit_priceREAL
payments
5 cols
payment_idINTEGERorder_idINTEGERmethodTEXTamountREALpaid_dateDATE
employees
6 cols
employee_idINTEGERnameTEXTdepartmentTEXTmanager_idINTEGERsalaryREALhire_dateDATE
logins
2 cols
user_idINTEGERlogin_dateDATE
ratings
5 cols
rating_idINTEGERcustomer_idINTEGERproduct_idINTEGERratingINTEGERrating_dateDATE
subscriptions
6 cols
sub_idINTEGERcustomer_idINTEGERplanTEXTstart_dateDATEend_dateDATEmonthly_feeREAL
shipments
5 cols
shipment_idINTEGERorder_idINTEGERcarrierTEXTship_dateDATEdelivery_dateDATE
support_tickets
7 cols
ticket_idINTEGERcustomer_idINTEGERsubjectTEXTpriorityTEXTstatusTEXTopened_dateDATEclosed_dateDATE
coupons
5 cols
codeTEXTdiscount_pctREALmin_order_amountREALvalid_fromDATEvalid_toDATE
coupon_redemptions
4 cols
redemption_idINTEGERcodeTEXTorder_idINTEGERredeemed_dateDATE
Two CTEs (H1 avg, H2 avg) joined on carrier, then ORDER BY the improvement descending, LIMIT 1.
customers5 colscustomer_idINTEGERnameTEXTcityTEXTcountryTEXTsignup_dateDATE| customer_id | name | city | country | signup_date |
|---|---|---|---|---|
| 101 | Rahul | Delhi | India | 2023-01-12 |
| 102 | Aman | Mumbai | India | 2023-02-03 |
| 103 | Sara | Bengaluru | India | 2023-02-20 |
products4 colsproduct_idINTEGERproduct_nameTEXTcategoryTEXTpriceREAL| product_id | product_name | category | price |
|---|---|---|---|
| 1 | Wireless Mouse | Electronics | 799 |
| 2 | Office Chair | Furniture | 5499 |
| 3 | Notebook | Stationery | 149 |
orders5 colsorder_idINTEGERcustomer_idINTEGERorder_dateDATEamountREALstatusTEXT| order_id | customer_id | order_date | amount | status |
|---|---|---|---|---|
| 1001 | 101 | 2024-01-05 | 1598 | Delivered |
| 1002 | 102 | 2024-01-11 | 5499 | Delivered |
| 1003 | 101 | 2024-02-02 | 149 | Cancelled |
order_items4 colsorder_idINTEGERproduct_idINTEGERquantityINTEGERunit_priceREAL| order_id | product_id | quantity | unit_price |
|---|---|---|---|
| 1001 | 1 | 2 | 799 |
| 1002 | 2 | 1 | 5499 |
| 1004 | 3 | 5 | 149 |
payments5 colspayment_idINTEGERorder_idINTEGERmethodTEXTamountREALpaid_dateDATE| payment_id | order_id | method | amount | paid_date |
|---|---|---|---|---|
| 5001 | 1001 | UPI | 1598 | 2024-01-05 |
| 5002 | 1002 | Card | 5499 | 2024-01-11 |
| 5003 | 1005 | UPI | 999 | 2024-03-02 |
employees6 colsemployee_idINTEGERnameTEXTdepartmentTEXTmanager_idINTEGERsalaryREALhire_dateDATE| employee_id | name | department | manager_id | salary | hire_date |
|---|---|---|---|---|---|
| 1 | Meera | Analytics | NULL | 180000 | |
| 2 | Rohit | Analytics | 1 | 95000 | |
| 3 | Neha | Engineering | 1 | 120000 |
logins2 colsuser_idINTEGERlogin_dateDATE| user_id | login_date |
|---|---|
| 101 | 2024-06-01 |
| 101 | 2024-06-02 |
| 102 | 2024-06-01 |
ratings5 colsrating_idINTEGERcustomer_idINTEGERproduct_idINTEGERratingINTEGERrating_dateDATE| rating_id | customer_id | product_id | rating | rating_date |
|---|---|---|---|---|
| 1 | 101 | 1 | 5 | 2024-01-10 |
| 2 | 101 | 4 | 4 | 2024-03-12 |
| 3 | 102 | 2 | 5 | 2024-01-15 |
subscriptions6 colssub_idINTEGERcustomer_idINTEGERplanTEXTstart_dateDATEend_dateDATEmonthly_feeREAL| sub_id | customer_id | plan | start_date | end_date | monthly_fee |
|---|---|---|---|---|---|
| 1 | 101 | Basic | 2023-02-01 | 2023-07-31 | 499 |
| 2 | 101 | Pro | 2023-08-01 | NULL | 999 |
| 3 | 102 | Pro | 2023-03-15 | 2024-03-14 | 999 |
shipments5 colsshipment_idINTEGERorder_idINTEGERcarrierTEXTship_dateDATEdelivery_dateDATE| shipment_id | order_id | carrier | ship_date | delivery_date |
|---|---|---|---|---|
| 1 | 1001 | Delhivery | 2024-01-06 | 2024-01-09 |
| 2 | 1002 | BlueDart | 2024-01-12 | 2024-01-14 |
| 15 | 1018 | Ekart | 2024-09-16 | NULL |
support_tickets7 colsticket_idINTEGERcustomer_idINTEGERsubjectTEXTpriorityTEXTstatusTEXTopened_dateDATEclosed_dateDATE| ticket_id | customer_id | subject | priority | status | opened_date | closed_date |
|---|---|---|---|---|---|---|
| 1 | 101 | Late delivery | High | Closed | 2024-01-10 | 2024-01-11 |
| 2 | 102 | Wrong item received | High | Closed | 2024-01-20 | 2024-01-23 |
| 10 | 110 | Wrong address delivered | Urgent | Open | 2024-05-01 | NULL |
coupons5 colscodeTEXTdiscount_pctREALmin_order_amountREALvalid_fromDATEvalid_toDATE| code | discount_pct | min_order_amount | valid_from | valid_to |
|---|---|---|---|---|
| WELCOME10 | 10 | 500 | 2024-01-01 | 2024-12-31 |
| SAVE20 | 20 | 2000 | 2024-03-01 | 2024-06-30 |
| FLASH50 | 50 | 1000 | 2024-04-01 | 2024-04-15 |
coupon_redemptions4 colsredemption_idINTEGERcodeTEXTorder_idINTEGERredeemed_dateDATE| redemption_id | code | order_id | redeemed_date |
|---|---|---|---|
| 1 | WELCOME10 | 1001 | 2024-01-05 |
| 2 | SAVE20 | 1006 | 2024-03-09 |
| 3 | WELCOME10 | 1013 | 2024-06-25 |