Ride-Sharing Trip Analytics
Download all 5 tables with complete messy data (50+ trips, 15 riders, 10 drivers)
| trip_id | rider_id | driver_id | pickup_time | dropoff_time | distance_km | fare_amount | status |
|---|---|---|---|---|---|---|---|
| T001 | R101 | D201 | 2024-01-15 08:30:00 | 2024-01-15 09:15:00 | 12.5 | 250.00 | completed |
| t002 | r102 | D202 | 15/01/2024 10:00 | NULL | NULL | NULL | CANCELLED |
| T003 | R103 | d203 | 2024-01-15 14:30:00 | 2024-01-15 15:10:00 | 8.3 | 175.50 | Completed |
| T004 | r104 | D204 | 16/01/2024 07:45 | 16/01/2024 08:20 | 15.2 | NULL | completed |
| t005 | R105 | D201 | 2024-01-16 18:00:00 | 2024-01-16 18:45:00 | 10.8 | 320.00 | COMPLETED |
| T006 | r106 | D205 | 2024-01-16 09:00:00 | 2024-01-16 09:40:00 | 7.2 | 145.00 | completed |
| t007 | R107 | d206 | 17/01/2024 11:30 | 17/01/2024 12:15 | 11.5 | 230.00 | Completed |
| T008 | R108 | D207 | 2024-01-17 13:00:00 | NULL | NULL | NULL | CANCELLED |
| t009 | r109 | D208 | 18/01/2024 08:15 | 18/01/2024 08:50 | 9.8 | 196.00 | completed |
| T010 | R110 | D209 | 2024-01-18 17:30:00 | 2024-01-18 18:20:00 | 13.4 | NULL | COMPLETED |
| t011 | R111 | D210 | 19/01/2024 07:00 | 19/01/2024 07:35 | 6.5 | 130.00 | completed |
| T012 | r101 | D201 | 2024-01-19 08:30:00 | 2024-01-19 09:20:00 | 14.2 | 400.00 | Completed |
| t013 | R112 | d202 | 20/01/2024 10:00 | 20/01/2024 10:45 | 10.1 | 202.00 | COMPLETED |
| T014 | R113 | D203 | 2024-01-20 14:00:00 | 2024-01-20 14:35:00 | 7.8 | 156.00 | completed |
| t015 | R114 | D204 | 21/01/2024 08:00 | NULL | NULL | NULL | CANCELLED |
| T016 | r115 | D205 | 2024-01-21 12:30:00 | 2024-01-21 13:10:00 | 9.5 | 190.00 | Completed |
| t017 | R101 | D206 | 22/01/2024 07:45 | 22/01/2024 08:25 | 11.3 | 226.00 | completed |
| T018 | R102 | d207 | 2024-01-22 18:15:00 | 2024-01-22 19:00:00 | 12.7 | 380.00 | COMPLETED |
| t019 | R103 | D208 | 23/01/2024 09:00 | 23/01/2024 09:40 | 8.9 | 178.00 | completed |
| T020 | r104 | D209 | 2024-01-23 15:30:00 | 2024-01-23 16:15:00 | 10.6 | NULL | Completed |
| t021 | R105 | D210 | 24/01/2024 08:30 | 24/01/2024 09:10 | 11.8 | 236.00 | COMPLETED |
| T022 | R106 | D201 | 2024-01-24 13:00:00 | NULL | NULL | NULL | CANCELLED |
| t023 | r107 | d202 | 25/01/2024 08:45 | 25/01/2024 09:30 | 13.5 | 450.00 | completed |
| T024 | R108 | D203 | 2024-01-25 11:00:00 | 2024-01-25 11:45:00 | 9.2 | 184.00 | Completed |
| t025 | R109 | D204 | 26/01/2024 07:30 | 26/01/2024 08:05 | 7.5 | 150.00 | COMPLETED |
| T026 | R110 | D205 | 2024-01-26 17:45:00 | 2024-01-26 18:30:00 | 12.1 | NULL | completed |
| t027 | r111 | D206 | 27/01/2024 09:15 | 27/01/2024 10:00 | 10.4 | 208.00 | Completed |
| T028 | R112 | d207 | 2024-01-27 14:30:00 | 2024-01-27 15:10:00 | 8.7 | 174.00 | COMPLETED |
| t029 | R113 | D208 | 28/01/2024 18:00 | 28/01/2024 18:50 | 14.3 | 350.00 | completed |
| T030 | R114 | D209 | 2024-01-28 08:00:00 | 2024-01-28 08:40:00 | 9.6 | 192.00 | Completed |
| t031 | r115 | D210 | 29/01/2024 12:00 | NULL | NULL | NULL | CANCELLED |
| T032 | R101 | D201 | 2024-01-29 07:30:00 | 2024-01-29 08:15:00 | 11.9 | 238.00 | completed |
| t033 | R102 | d202 | 30/01/2024 10:30 | 30/01/2024 11:10 | 8.4 | 168.00 | COMPLETED |
| T034 | R103 | D203 | 2024-01-30 15:00:00 | 2024-01-30 15:45:00 | 10.7 | NULL | Completed |
| t035 | r104 | D204 | 31/01/2024 08:45 | 31/01/2024 09:25 | 12.3 | 246.00 | completed |
| T036 | R105 | D205 | 2024-01-31 13:30:00 | 2024-01-31 14:15:00 | 9.8 | 196.00 | COMPLETED |
| t037 | R106 | D206 | 01/02/2024 07:00 | 01/02/2024 07:40 | 8.1 | 162.00 | Completed |
| T038 | r107 | d207 | 2024-02-01 11:00:00 | 2024-02-01 11:50:00 | 13.8 | 276.00 | completed |
| t039 | R108 | D208 | 02/02/2024 17:30 | 02/02/2024 18:20 | 15.1 | NULL | COMPLETED |
| T040 | R109 | D209 | 2024-02-02 08:15:00 | 2024-02-02 09:00:00 | 10.9 | 218.00 | completed |
| t041 | R110 | D210 | 03/02/2024 14:00 | 03/02/2024 14:40 | 7.6 | 152.00 | Completed |
| T042 | r111 | D201 | 2024-02-03 09:30:00 | 2024-02-03 10:15:00 | 11.4 | 228.00 | COMPLETED |
| t043 | R112 | d202 | 04/02/2024 12:00 | NULL | NULL | NULL | CANCELLED |
| T044 | R113 | D203 | 2024-02-04 07:45:00 | 2024-02-04 08:30:00 | 9.3 | 186.00 | completed |
| t045 | R114 | D204 | 05/02/2024 16:00 | 05/02/2024 16:50 | 14.6 | NULL | COMPLETED |
| T046 | R115 | D205 | 2024-02-05 08:00:00 | 2024-02-05 08:45:00 | 10.2 | 204.00 | Completed |
| t047 | r101 | D206 | 06/02/2024 11:30 | 06/02/2024 12:10 | 8.8 | 176.00 | completed |
| T048 | R102 | d207 | 2024-02-06 15:30:00 | 2024-02-06 16:15:00 | 11.7 | 234.00 | COMPLETED |
| t049 | R103 | D208 | 07/02/2024 09:00 | 07/02/2024 09:50 | 13.2 | 264.00 | Completed |
| T050 | r104 | D209 | 2024-02-07 13:00:00 | 2024-02-07 13:45:00 | 9.9 | 198.00 | completed |
| driver_id | driver_name | join_date | vehicle_type | rating | total_trips |
|---|---|---|---|---|---|
| D201 | amit sharma | 2023-05-10 | sedan | 4.8 | 523 |
| d202 | PRIYA KUMAR | 15/06/2023 | SUV | NULL | 412 |
| D203 | rajesh patel | 2023-07-22 | SEDAN | 4.5 | NULL |
| D204 | NEHA SINGH | 10-08-2023 | suv | 4.9 | 678 |
| d205 | VIKRAM RAO | 2023-09-15 | Sedan | 4.7 | 345 |
| D206 | kavita desai | 2023-10-20 | sedan | 4.6 | NULL |
| d207 | RAHUL MEHTA | 05-11-2023 | SUV | NULL | 289 |
| D208 | ANJALI VERMA | 2023-12-01 | sedan | 4.8 | 398 |
| d209 | suresh kumar | 15/12/2023 | Sedan | 4.4 | NULL |
| D210 | POOJA SHAH | 2024-01-05 | SUV | 4.9 | 156 |
| rider_id | rider_name | signup_date | city | total_trips |
|---|---|---|---|---|
| R101 | john doe | 2024-01-01 | mumbai | 15 |
| r102 | SARAH SMITH | 05/01/2024 | Delhi | NULL |
| R103 | MIKE WILSON | 2024-01-08 | bangalore | 22 |
| R104 | anna garcia | 10-01-2024 | MUMBAI | 8 |
| r105 | Bob Martin | 2024-01-12 | Delhi | 12 |
| R106 | EMMA BROWN | 15/01/2024 | bangalore | NULL |
| r107 | david johnson | 2024-01-18 | mumbai | 18 |
| R108 | LISA ANDERSON | 20-01-2024 | Delhi | 10 |
| r109 | james taylor | 2024-01-22 | BANGALORE | NULL |
| R110 | MARIA MARTINEZ | 25/01/2024 | Mumbai | 14 |
| r111 | william davis | 2024-01-28 | delhi | 9 |
| R112 | sophia rodriguez | 30-01-2024 | Bangalore | NULL |
| r113 | ROBERT WILSON | 2024-02-01 | MUMBAI | 16 |
| R114 | jennifer lee | 03/02/2024 | delhi | 7 |
| r115 | MICHAEL THOMAS | 2024-02-05 | bangalore | NULL |
| surge_id | trip_id | surge_multiplier | surge_start | surge_end |
|---|---|---|---|---|
| S001 | T005 | 2.5 | 2024-01-16 17:30:00 | 2024-01-16 19:30:00 |
| S002 | t012 | 1.8 | 15/01/2024 08:00 | 15/01/2024 10:00 |
| S003 | T018 | NULL | 2024-01-17 18:00:00 | 2024-01-17 20:00:00 |
| S004 | T023 | 2.0 | 2024-01-18 08:30:00 | 2024-01-18 10:00:00 |
| S005 | t029 | 1.5 | 19/01/2024 17:00 | 19/01/2024 19:00 |
| S006 | T012 | 2.2 | 2024-01-19 08:00:00 | 2024-01-19 10:00:00 |
| S007 | t018 | 1.9 | 22/01/2024 17:30 | 22/01/2024 19:30 |
| S008 | T023 | NULL | 2024-01-25 08:15:00 | 2024-01-25 10:15:00 |
| S009 | T029 | 1.7 | 28/01/2024 17:45 | 28/01/2024 19:45 |
| S010 | t005 | 2.8 | 2024-01-16 18:00:00 | 2024-01-16 20:00:00 |
| S011 | T010 | 2.1 | 18/01/2024 17:00 | 18/01/2024 19:00 |
| S012 | t026 | NULL | 2024-01-26 17:30:00 | 2024-01-26 19:30:00 |
| S013 | T018 | 1.6 | 22/01/2024 18:00 | 22/01/2024 20:00 |
| S014 | T029 | 2.3 | 2024-01-28 17:30:00 | 2024-01-28 19:30:00 |
| S015 | t039 | 1.9 | 02/02/2024 17:00 | 02/02/2024 19:00 |
| S016 | T012 | NULL | 2024-01-19 08:30:00 | 2024-01-19 10:30:00 |
| S017 | T023 | 2.4 | 25/01/2024 08:00 | 25/01/2024 10:00 |
| S018 | t005 | 2.0 | 16/01/2024 17:30 | 16/01/2024 19:30 |
| S019 | T026 | 1.8 | 2024-01-26 17:15:00 | 2024-01-26 19:15:00 |
| S020 | t039 | NULL | 02/02/2024 18:00 | 02/02/2024 20:00 |
| cancel_id | trip_id | cancelled_by | cancel_reason | cancel_time |
|---|---|---|---|---|
| C001 | T002 | rider | driver too far | 2024-01-15 10:05:00 |
| C002 | t008 | DRIVER | NULL | 16/01/2024 12:30 |
| C003 | T015 | rider | CHANGE OF PLANS | 2024-01-17 08:15:00 |
| C004 | t022 | Driver | traffic jam | 18-01-2024 14:45 |
| C005 | T031 | RIDER | long wait time | 2024-01-20 11:30:00 |
| C006 | t002 | rider | DRIVER TOO FAR | 15/01/2024 10:10 |
| C007 | T008 | DRIVER | NULL | 2024-01-17 13:05:00 |
| C008 | t015 | Rider | changed my mind | 21-01-2024 08:10 |
| C009 | T022 | driver | EMERGENCY | 2024-01-24 13:10:00 |
| C010 | T031 | RIDER | found another ride | 29/01/2024 12:05 |
| C011 | t043 | driver | NULL | 2024-02-04 12:08:00 |
| C012 | T002 | RIDER | too expensive | 15-01-2024 10:03 |
| C013 | t008 | Driver | VEHICLE ISSUE | 2024-01-17 13:02:00 |
| C014 | T043 | rider | long wait | 04/02/2024 12:10 |
| C015 | t031 | DRIVER | bad weather | 2024-01-29 12:03:00 |
MySQL:
1. Create database: CREATE DATABASE rideshare;
2. Import CSV: LOAD DATA INFILE 'path/trips.csv' INTO TABLE trips FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n' IGNORE 1 ROWS;
PostgreSQL:
1. Create database: CREATE DATABASE rideshare;
2. Import CSV: \COPY trips FROM 'path/trips.csv' WITH (FORMAT csv, HEADER true);
Note: Create tables first with appropriate column types before importing!