🚀 SQL Project Series #8

Food Delivery Analytics 🍔

Analyze customer orders, restaurant performance, delivery efficiency, and rider operations using SQL.

🎯 Business Objectives

✅ Analyze food orders and revenue

✅ Measure delivery performance

✅ Evaluate restaurant performance

✅ Analyze customer ordering behavior

✅ Track delivery partner efficiency

✅ Identify peak order hours

✅ Reduce delivery delays

✅ Improve customer satisfaction

📂 Step 1: Create Database

CREATE DATABASE food_delivery_db;

USE food_delivery_db;

📂 Step 2: Create Customers Table

CREATE TABLE customers (
    customer_id INT PRIMARY KEY,
    customer_name VARCHAR(100),
    city VARCHAR(50),
    signup_date DATE
);

📂 Step 3: Create Restaurants Table

CREATE TABLE restaurants (
    restaurant_id INT PRIMARY KEY,
    restaurant_name VARCHAR(100),
    cuisine VARCHAR(50),
    city VARCHAR(50),
    rating DECIMAL(3,2)
);

📂 Step 4: Create Delivery Partners Table

CREATE TABLE delivery_partners (
    partner_id INT PRIMARY KEY,
    partner_name VARCHAR(100),
    vehicle_type VARCHAR(30)
);

📂 Step 5: Create Orders Table

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    customer_id INT,
    restaurant_id INT,
    partner_id INT,
    order_date TIMESTAMP,
    delivery_time_minutes INT,
    order_amount DECIMAL(10,2),
    order_status VARCHAR(20),
    FOREIGN KEY (customer_id) REFERENCES customers(customer_id),
    FOREIGN KEY (restaurant_id) REFERENCES restaurants(restaurant_id),
    FOREIGN KEY (partner_id) REFERENCES delivery_partners(partner_id)
);

📂 Step 6: Insert Sample Customers

INSERT INTO customers VALUES
(1,'Rahul','Mumbai','2025-01-10'),
(2,'Priya','Delhi','2025-01-12'),
(3,'Amit','Pune','2025-01-18'),
(4,'Sneha','Bangalore','2025-01-22'),
(5,'Rohan','Hyderabad','2025-01-25');

📂 Step 7: Insert Sample Restaurants

INSERT INTO restaurants VALUES
(101,'Pizza Hub','Italian','Mumbai',4.6),
(102,'Spice Villa','Indian','Delhi',4.4),
(103,'Burger Point','Fast Food','Pune',4.2),
(104,'Sushi World','Japanese','Bangalore',4.8);

📂 Step 8: Insert Sample Delivery Partners

INSERT INTO delivery_partners VALUES
(201,'Aman','Bike'),
(202,'Rohit','Scooter'),
(203,'Vikas','Bike'),
(204,'Ankit','Bicycle');

📂 Step 9: Insert Sample Orders

INSERT INTO orders VALUES
(1001,1,101,201,'2025-02-01 12:30:00',28,850,'Delivered'),
(1002,2,102,202,'2025-02-01 13:10:00',42,620,'Delivered'),
(1003,3,103,201,'2025-02-01 19:45:00',35,480,'Delivered'),
(1004,4,104,203,'2025-02-02 20:15:00',55,1200,'Delayed'),
(1005,5,101,204,'2025-02-03 18:20:00',25,760,'Delivered');