Food Delivery Order Lifecycle Analysis
Download all 5 tables with complete messy data (50+ orders, 20 customers, 12 riders)
| order_id | customer_id | restaurant_id | rider_id | order_time | delivery_time | order_value | status |
|---|---|---|---|---|---|---|---|
| ORD001 | C101 | R201 | RID301 | 2024-02-10 12:30:00 | 2024-02-10 13:15:00 | 450.00 | delivered |
| ord002 | c102 | R202 | NULL | 10/02/2024 13:00 | NULL | NULL | CANCELLED |
| ORD003 | C103 | r203 | RID302 | 2024-02-10 14:15:00 | 2024-02-10 15:00:00 | 680.50 | Delivered |
| ORD004 | c104 | R204 | rid303 | 11/02/2024 12:00 | 11/02/2024 12:50 | 320.00 | delivered |
| ord005 | C105 | R201 | RID304 | 2024-02-11 18:30:00 | 2024-02-11 19:20:00 | 550.00 | DELIVERED |
| ORD006 | C106 | R205 | RID305 | 2024-02-11 12:15:00 | 2024-02-11 13:00:00 | 380.00 | delivered |
| ord007 | c107 | R206 | RID306 | 12/02/2024 13:30 | 12/02/2024 14:20 | NULL | Delivered |
| ORD008 | C108 | R207 | rid307 | 2024-02-12 19:00:00 | 2024-02-12 19:55:00 | 720.00 | DELIVERED |
| ORD009 | c109 | R208 | RID308 | 13/02/2024 11:45 | 13/02/2024 12:30 | 290.00 | delivered |
| ord010 | C110 | R209 | NULL | 2024-02-13 14:00:00 | NULL | NULL | CANCELLED |
| ORD011 | C111 | R210 | RID309 | 2024-02-13 18:15:00 | 2024-02-13 19:10:00 | 630.00 | delivered |
| ord012 | c112 | R201 | RID310 | 14/02/2024 12:45 | 14/02/2024 13:35 | 470.00 | Delivered |
| ORD013 | C113 | R202 | rid311 | 2024-02-14 13:20:00 | 2024-02-14 14:05:00 | NULL | DELIVERED |
| ORD014 | c114 | R203 | RID312 | 15/02/2024 19:30 | 15/02/2024 20:25 | 580.00 | delivered |
| ord015 | C115 | R204 | NULL | 2024-02-15 12:00:00 | NULL | NULL | CANCELLED |
| ORD016 | C116 | R205 | RID301 | 2024-02-16 11:30:00 | 2024-02-16 12:20:00 | 340.00 | delivered |
| ord017 | c117 | R206 | rid302 | 16/02/2024 18:45 | 16/02/2024 19:40 | 690.00 | Delivered |
| ORD018 | C118 | R207 | RID303 | 2024-02-17 12:15:00 | 2024-02-17 13:05:00 | NULL | DELIVERED |
| ORD019 | c119 | R208 | RID304 | 17/02/2024 13:00 | 17/02/2024 13:50 | 410.00 | delivered |
| ord020 | C120 | R209 | rid305 | 2024-02-18 19:15:00 | 2024-02-18 20:10:00 | 540.00 | Delivered |
| ORD021 | C101 | R210 | RID306 | 2024-02-18 12:30:00 | 2024-02-18 13:20:00 | 480.00 | DELIVERED |
| ord022 | c102 | R201 | NULL | 19/02/2024 14:00 | NULL | NULL | CANCELLED |
| ORD023 | C103 | R202 | RID307 | 2024-02-19 18:00:00 | 2024-02-19 18:55:00 | 610.00 | delivered |
| ORD024 | c104 | R203 | rid308 | 20/02/2024 11:45 | 20/02/2024 12:35 | NULL | Delivered |
| ord025 | C105 | R204 | RID309 | 2024-02-20 13:15:00 | 2024-02-20 14:05:00 | 370.00 | DELIVERED |
| ORD026 | C106 | R205 | RID310 | 2024-02-21 19:30:00 | 2024-02-21 20:20:00 | 650.00 | delivered |
| ord027 | c107 | R206 | rid311 | 21/02/2024 12:00 | 21/02/2024 12:50 | 310.00 | Delivered |
| ORD028 | C108 | R207 | RID312 | 2024-02-22 13:30:00 | 2024-02-22 14:25:00 | NULL | DELIVERED |
| ORD029 | c109 | R208 | RID301 | 22/02/2024 18:15 | 22/02/2024 19:05 | 570.00 | delivered |
| ord030 | C110 | R209 | NULL | 2024-02-23 12:45:00 | NULL | NULL | CANCELLED |
| ORD031 | C111 | R210 | RID302 | 2024-02-23 13:00:00 | 2024-02-23 13:50:00 | 420.00 | delivered |
| ord032 | c112 | R201 | rid303 | 24/02/2024 19:00 | 24/02/2024 19:50 | NULL | Delivered |
| ORD033 | C113 | R202 | RID304 | 2024-02-24 11:30:00 | 2024-02-24 12:20:00 | 390.00 | DELIVERED |
| ORD034 | c114 | R203 | RID305 | 25/02/2024 14:15 | 25/02/2024 15:05 | 600.00 | delivered |
| ord035 | C115 | R204 | rid306 | 2024-02-25 18:30:00 | 2024-02-25 19:25:00 | 520.00 | Delivered |
| ORD036 | C116 | R205 | RID307 | 2024-02-26 12:00:00 | 2024-02-26 12:50:00 | NULL | DELIVERED |
| ord037 | c117 | R206 | NULL | 26/02/2024 13:45 | NULL | NULL | CANCELLED |
| ORD038 | C118 | R207 | RID308 | 2024-02-27 19:15:00 | 2024-02-27 20:10:00 | 680.00 | delivered |
| ORD039 | c119 | R208 | rid309 | 27/02/2024 11:45 | 27/02/2024 12:35 | 360.00 | Delivered |
| ord040 | C120 | R209 | RID310 | 2024-02-28 13:20:00 | 2024-02-28 14:15:00 | NULL | DELIVERED |
| ORD041 | C101 | R210 | RID311 | 2024-02-28 18:00:00 | 2024-02-28 18:50:00 | 490.00 | delivered |
| ord042 | c102 | R201 | rid312 | 29/02/2024 12:30 | 29/02/2024 13:20 | 430.00 | Delivered |
| ORD043 | C103 | R202 | RID301 | 2024-02-29 13:45:00 | 2024-02-29 14:35:00 | NULL | DELIVERED |
| ORD044 | c104 | R203 | RID302 | 01/03/2024 19:30 | 01/03/2024 20:20 | 590.00 | delivered |
| ord045 | C105 | R204 | NULL | 2024-03-01 11:00:00 | NULL | NULL | CANCELLED |
| ORD046 | C106 | R205 | RID303 | 2024-03-02 12:15:00 | 2024-03-02 13:05:00 | 400.00 | delivered |
| ord047 | c107 | R206 | rid304 | 02/03/2024 18:45 | 02/03/2024 19:40 | 660.00 | Delivered |
| ORD048 | C108 | R207 | RID305 | 2024-03-03 13:00:00 | 2024-03-03 13:50:00 | NULL | DELIVERED |
| ORD049 | c109 | R208 | RID306 | 03/03/2024 12:30 | 03/03/2024 13:25 | 510.00 | delivered |
| ord050 | C110 | R209 | rid307 | 2024-03-04 19:00:00 | 2024-03-04 19:55:00 | 440.00 | Delivered |
| rider_id | rider_name | phone | join_date | rating | total_deliveries |
|---|---|---|---|---|---|
| RID301 | amit kumar | 9876543210 | 2023-08-15 | 4.7 | 856 |
| rid302 | PRIYA SINGH | 9876543211 | 15/09/2023 | NULL | 624 |
| RID303 | rajesh patel | 9876543212 | 2023-10-05 | 4.5 | NULL |
| RID304 | NEHA SHARMA | 9876543213 | 10-11-2023 | 4.9 | 789 |
| rid305 | VIKRAM RAO | 9876543214 | 2023-12-20 | 4.6 | 512 |
| RID306 | kavita desai | 9876543215 | 2024-01-10 | 4.8 | NULL |
| rid307 | RAHUL MEHTA | 9876543216 | 15-01-2024 | NULL | 423 |
| RID308 | ANJALI VERMA | 9876543217 | 2024-01-25 | 4.7 | 567 |
| rid309 | suresh kumar | 9876543218 | 01/02/2024 | 4.4 | NULL |
| RID310 | POOJA SHAH | 9876543219 | 2024-02-05 | 4.9 | 298 |
| rid311 | manoj tiwari | 9876543220 | 10-02-2024 | 4.6 | NULL |
| RID312 | REKHA PILLAI | 9876543221 | 2024-02-15 | 4.8 | 189 |
| restaurant_id | restaurant_name | cuisine_type | city | rating | avg_prep_time |
|---|---|---|---|---|---|
| R201 | pizza palace | Italian | mumbai | 4.3 | 25 |
| r202 | BURGER KING | Fast Food | Delhi | NULL | 15 |
| R203 | SPICE GARDEN | Indian | bangalore | 4.7 | 30 |
| R204 | sushi hub | Japanese | MUMBAI | 4.5 | NULL |
| r205 | Taco Bell | Mexican | Delhi | 4.2 | 20 |
| R206 | CHINESE WOK | Chinese | bangalore | 4.4 | NULL |
| r207 | dominos pizza | Italian | mumbai | 4.1 | 22 |
| R208 | KFC | Fast Food | Delhi | NULL | 18 |
| R209 | mcdonald's | Fast Food | BANGALORE | 4.0 | NULL |
| r210 | SUBWAY | Fast Food | Mumbai | 4.3 | 12 |
| R211 | biryani house | Indian | delhi | 4.6 | 35 |
| R212 | PASTA POINT | Italian | bangalore | NULL | 28 |
| r213 | THAI CURRY | Thai | Mumbai | 4.5 | NULL |
| R214 | starbucks | Cafe | Delhi | 4.2 | 10 |
| r215 | CAFE COFFEE DAY | Cafe | BANGALORE | 4.1 | NULL |
| customer_id | customer_name | signup_date | city | total_orders |
|---|---|---|---|---|
| C101 | john doe | 2024-01-05 | mumbai | 28 |
| c102 | SARAH KHAN | 10/01/2024 | Delhi | NULL |
| C103 | MIKE WILSON | 2024-01-12 | bangalore | 35 |
| C104 | anna garcia | 15-01-2024 | MUMBAI | 12 |
| c105 | Bob Martin | 2024-01-18 | Delhi | 22 |
| C106 | EMMA BROWN | 20/01/2024 | bangalore | NULL |
| c107 | david johnson | 2024-01-22 | mumbai | 19 |
| C108 | LISA ANDERSON | 25-01-2024 | Delhi | 31 |
| c109 | james taylor | 2024-01-28 | BANGALORE | NULL |
| C110 | MARIA MARTINEZ | 01/02/2024 | Mumbai | 16 |
| c111 | william davis | 2024-02-03 | delhi | 24 |
| C112 | sophia rodriguez | 05-02-2024 | Bangalore | NULL |
| c113 | ROBERT WILSON | 2024-02-07 | MUMBAI | 27 |
| C114 | jennifer lee | 09/02/2024 | delhi | 18 |
| c115 | MICHAEL THOMAS | 2024-02-11 | bangalore | NULL |
| C116 | emily white | 13-02-2024 | Mumbai | 21 |
| C117 | DANIEL HARRIS | 2024-02-15 | Delhi | NULL |
| c118 | OLIVIA MOORE | 2024-02-17 | BANGALORE | 29 |
| C119 | christopher clark | 19-02-2024 | mumbai | NULL |
| c120 | MADISON LEWIS | 2024-02-21 | Delhi | 23 |
| log_id | order_id | status | timestamp | notes |
|---|---|---|---|---|
| L001 | ORD001 | placed | 2024-02-10 12:30:00 | Order received |
| L002 | ord001 | CONFIRMED | 10/02/2024 12:35 | restaurant confirmed |
| L003 | ORD001 | preparing | 2024-02-10 12:40:00 | NULL |
| L004 | ORD001 | OUT_FOR_DELIVERY | 2024-02-10 13:00:00 | Rider assigned |
| L005 | ord001 | delivered | 2024-02-10 13:15:00 | delivered successfully |
| L006 | ORD003 | placed | 2024-02-10 14:15:00 | Order received |
| L007 | ORD003 | confirmed | 10/02/2024 14:20 | NULL |
| L008 | ord003 | PREPARING | 2024-02-10 14:25:00 | cooking started |
| L009 | ORD003 | out_for_delivery | 2024-02-10 14:45:00 | Rider picked up |
| L010 | ORD003 | DELIVERED | 10/02/2024 15:00 | completed |
| L011 | ORD004 | placed | 2024-02-11 12:00:00 | Order received |
| L012 | ord004 | CONFIRMED | 11/02/2024 12:05 | NULL |
| L013 | ORD004 | preparing | 2024-02-11 12:10:00 | in kitchen |
| L014 | ord004 | OUT_FOR_DELIVERY | 11/02/2024 12:30 | Rider assigned |
| L015 | ORD004 | delivered | 2024-02-11 12:50:00 | delivered |
| L016 | ORD005 | PLACED | 2024-02-11 18:30:00 | Order received |
| L017 | ord005 | confirmed | 11/02/2024 18:35 | NULL |
| L018 | ORD005 | PREPARING | 2024-02-11 18:40:00 | preparing food |
| L019 | ord005 | out_for_delivery | 11/02/2024 19:00 | Rider on way |
| L020 | ORD005 | DELIVERED | 2024-02-11 19:20:00 | successfully delivered |
| L021 | ORD006 | placed | 2024-02-11 12:15:00 | Order received |
| L022 | ORD006 | CONFIRMED | 11/02/2024 12:20 | NULL |
| L023 | ord006 | preparing | 2024-02-11 12:25:00 | cooking |
| L024 | ORD006 | OUT_FOR_DELIVERY | 11/02/2024 12:45 | Rider picked up |
| L025 | ORD006 | delivered | 2024-02-11 13:00:00 | delivered |
| L026 | ORD008 | PLACED | 2024-02-12 19:00:00 | Order received |
| L027 | ord008 | confirmed | 12/02/2024 19:05 | NULL |
| L028 | ORD008 | PREPARING | 2024-02-12 19:10:00 | in preparation |
| L029 | ord008 | out_for_delivery | 12/02/2024 19:35 | Rider assigned |
| L030 | ORD008 | DELIVERED | 2024-02-12 19:55:00 | completed successfully |
| L031 | ORD011 | placed | 2024-02-13 18:15:00 | Order received |
| L032 | ORD011 | CONFIRMED | 13/02/2024 18:20 | NULL |
| L033 | ord011 | preparing | 2024-02-13 18:25:00 | food being prepared |
| L034 | ORD011 | OUT_FOR_DELIVERY | 13/02/2024 18:50 | Rider on way |
| L035 | ORD011 | delivered | 2024-02-13 19:10:00 | delivered |
MySQL:
1. Create database: CREATE DATABASE food_delivery;
2. Import CSV: LOAD DATA INFILE 'path/orders.csv' INTO TABLE orders FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;
PostgreSQL:
1. Create database: CREATE DATABASE food_delivery;
2. Import CSV: \COPY orders FROM 'path/orders.csv' WITH (FORMAT csv, HEADER true);
Note: Create tables first with appropriate column types before importing!