📚 100 DAYS SQL
CODING CHALLENGE

DAY 17

Ride-Sharing Trip Analytics

✨ Master SQL One Problem at a Time ✨

📊 Download Practice Dataset

Get all CSV files to practice this challenge:

📥 Download Day 17 Data

5 tables: trips, drivers, riders, surge_pricing, cancellations

🚗 Ride-Sharing Trip Analytics

Build a comprehensive ride-sharing analytics system with surge pricing, cancellation tracking, and driver performance metrics.

Table 1: trips (5 of 50 rows)

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

...45 more rows in the download!

Table 2: drivers (5 of 10 rows)

driver_id driver_name join_date vehicle_type rating total_trips
D201 amit sharma 2023-05-10sedan4.8523
d202PRIYA KUMAR15/06/2023 SUV NULL412
D203rajesh patel2023-07-22SEDAN4.5NULL
D204 NEHA SINGH 10-08-2023suv4.9678
d205VIKRAM RAO2023-09-15Sedan4.7345

Table 3: riders (5 of 15 rows)

rider_id rider_name signup_date city total_trips
R101john doe2024-01-01 mumbai 15
r102 SARAH SMITH 05/01/2024DelhiNULL
R103MIKE WILSON2024-01-08bangalore22
R104anna garcia10-01-2024 MUMBAI 8
r105Bob Martin2024-01-12Delhi12

Table 4: surge_pricing (5 of 20 rows)

surge_id trip_id surge_multiplier surge_start surge_end
S001T0052.52024-01-16 17:30:002024-01-16 19:30:00
S002 t012 1.815/01/2024 08:0015/01/2024 10:00
S003T018NULL2024-01-17 18:00:002024-01-17 20:00:00
S004T0232.02024-01-18 08:30:002024-01-18 10:00:00
S005t0291.519/01/2024 17:0019/01/2024 19:00

Table 5: cancellations (5 of 15 rows)

cancel_id trip_id cancelled_by cancel_reason cancel_time
C001T002 rider driver too far2024-01-15 10:05:00
C002t008DRIVER NULL 16/01/2024 12:30
C003T015riderCHANGE OF PLANS2024-01-17 08:15:00
C004t022 Driver traffic jam18-01-2024 14:45
C005T031RIDERlong wait time2024-01-20 11:30:00

📋 Your Challenge

Build ride-sharing analytics with surge analysis, cancellation tracking, and driver performance metrics.

🧹 Data Cleaning:
  • Standardize all IDs (trip_id, rider_id, driver_id) - uppercase and trim
  • Clean names and cities - proper case formatting
  • Standardize all datetime formats to YYYY-MM-DD HH:MM:SS
  • Handle NULL fare values (calculate from distance: ₹20/km)
  • Handle NULL ratings (use average: 4.5)
  • Standardize status values - lowercase
🔗 Complex JOINs & Data Enrichment:
  • JOIN trips to drivers to get driver details
  • JOIN trips to riders to get rider details
  • LEFT JOIN trips to surge_pricing for surge analysis
  • LEFT JOIN trips to cancellations for cancelled trips
  • Calculate trip duration in minutes using TIMESTAMPDIFF
  • Calculate average fare per km for each trip
  • Identify surge vs normal trips
📊 Analytics & Metrics:
  • Calculate driver earnings (80% of fare goes to driver)
  • Identify peak hours (Morning Rush: 7-9 AM, Evening Rush: 5-8 PM)
  • Calculate surge impact on final fare
  • Rank driver trips by fare using window functions
  • Calculate running daily revenue
  • Track cancellation patterns (by rider vs driver)
  • Analyze cancellation reasons

💡 Key SQL Concepts: Window Functions (RANK, PARTITION BY), TIMESTAMPDIFF, Complex JOINs, CTEs, CASE WHEN!

🎯 Ready to Solve?

Download the full challenge PDF with complete solution, explanations, and step-by-step hints!

🔗 Connect & Get Solutions

Follow for complete solutions and all 100 challenges:

Complete solutions posted on LinkedIn • All data on GitHub

💪 Keep Practicing!

Master ride-sharing analytics
Track surge pricing patterns
Analyze driver performance

Day 18 coming tomorrow!

Follow the 100 Days SQL Challenge

Master SQL one problem at a time!

Rahul Kanche — exploring data, decoding clarity!