📚 100 DAYS SQL
CODING CHALLENGE

DAY 18

Food Delivery Order Lifecycle Analysis

✨ Master SQL One Problem at a Time ✨

📊 Download Practice Dataset

Get all CSV files to practice this challenge:

📥 Download Day 18 Data

5 tables: orders, riders, restaurants, customers, order_status_log

🍕 Food Delivery Order Lifecycle

Build analytics for order tracking, delivery time optimization, and rider performance in food delivery platforms.

Table 1: orders (5 of 50 rows)

order_id customer_id restaurant_id rider_id order_time delivery_time order_value status
ORD001 C101 R201 RID3012024-02-10 12:30:002024-02-10 13:15:00450.00delivered
ord002 c102 R202NULL10/02/2024 13:00NULLNULLCANCELLED
ORD003C103r203RID3022024-02-10 14:15:002024-02-10 15:00:00680.50 Delivered
ORD004c104 R204 rid30311/02/2024 12:0011/02/2024 12:50320.00delivered
ord005C105R201RID3042024-02-11 18:30:002024-02-11 19:20:00550.00DELIVERED

...45 more rows in the download!

Table 2: riders (5 of 12 rows)

rider_idrider_namephonejoin_dateratingtotal_deliveries
RID301 amit kumar 98765432102023-08-154.7856
rid302PRIYA SINGH987654321115/09/2023NULL624
RID303rajesh patel98765432122023-10-054.5NULL
RID304 NEHA SHARMA 987654321310-11-20234.9789
rid305VIKRAM RAO98765432142023-12-204.6512

Table 3: restaurants (5 of 15 rows)

restaurant_idrestaurant_namecuisine_typecityratingavg_prep_time
R201pizza palaceItalian mumbai 4.325
r202 BURGER KING Fast FoodDelhiNULL15
R203SPICE GARDENIndianbangalore4.730
R204sushi hubJapanese MUMBAI 4.5NULL
r205Taco BellMexicanDelhi4.220

Table 4: customers (5 of 20 rows)

customer_idcustomer_namesignup_datecitytotal_orders
C101john doe2024-01-05 mumbai 28
c102 SARAH KHAN 10/01/2024DelhiNULL
C103MIKE WILSON2024-01-12bangalore35
C104anna garcia15-01-2024 MUMBAI 12
c105Bob Martin2024-01-18Delhi22

Table 5: order_status_log (5 of 30 rows)

log_idorder_idstatustimestampnotes
L001ORD001placed2024-02-10 12:30:00Order received
L002 ord001 CONFIRMED10/02/2024 12:35 restaurant confirmed
L003ORD001preparing2024-02-10 12:40:00NULL
L004ORD001OUT_FOR_DELIVERY2024-02-10 13:00:00Rider assigned
L005ord001delivered2024-02-10 13:15:00 delivered successfully

📋 Your Challenge

Build food delivery lifecycle analytics with order tracking, delivery time analysis, and rider performance metrics.

🧹 Data Cleaning:
  • Standardize all IDs (order_id, customer_id, restaurant_id, rider_id) - uppercase and trim
  • Clean names and cities - proper case formatting
  • Standardize all datetime formats to YYYY-MM-DD HH:MM:SS
  • Handle NULL order values (calculate from restaurant avg: ₹450)
  • Handle NULL ratings (use averages: 4.6 for riders, 4.3 for restaurants)
  • Standardize status values - lowercase
🔗 Complex JOINs & Data Enrichment:
  • JOIN orders to riders to get rider details
  • JOIN orders to restaurants to get restaurant info
  • JOIN orders to customers to get customer info
  • LEFT JOIN orders to order_status_log for lifecycle tracking
  • Calculate total delivery time in minutes using TIMESTAMPDIFF
  • Calculate preparation time and transit time separately
  • Count status transitions per order
📊 Analytics & Metrics:
  • Track order lifecycle: placed → confirmed → preparing → out_for_delivery → delivered
  • Identify peak ordering hours (Lunch Rush: 12-2 PM, Dinner Rush: 7-9 PM)
  • Calculate average delivery time by restaurant
  • Rank orders by delivery speed within each restaurant
  • Calculate running average daily delivery time
  • Analyze rider efficiency (deliveries per day, avg delivery time)
  • Track customer ordering patterns and frequency

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

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 food delivery analytics
Track order lifecycles
Optimize delivery times

Day 19 coming tomorrow!

Follow the 100 Days SQL Challenge

Master SQL one problem at a time!

Rahul Kanche — exploring data, decoding clarity!