🚀 SQL Project Series #13

Ride-Sharing Analytics 🚖

Analyze trips, drivers, riders, earnings, cancellations, and customer behavior using SQL to improve operational efficiency and customer experience.

🎯 Business Objectives

✅ Analyze trip demand

✅ Track driver performance

✅ Measure customer activity

✅ Identify peak travel hours

✅ Analyze cancellations

✅ Monitor driver earnings

✅ Calculate trip efficiency

✅ Build operational dashboards

📂 Step 1: Create Database

CREATE DATABASE ride_sharing_db;
USE ride_sharing_db;

📂 Step 2: Create Riders Table

CREATE TABLE riders (
    rider_id INT PRIMARY KEY,
    rider_name VARCHAR(100),
    city VARCHAR(50),
    signup_date DATE
);

📂 Step 3: Create Drivers Table

CREATE TABLE drivers (
    driver_id INT PRIMARY KEY,
    driver_name VARCHAR(100),
    vehicle_type VARCHAR(30),
    city VARCHAR(50),
    joining_date DATE
);

📂 Step 4: Create Trips Table

CREATE TABLE trips (
    trip_id INT PRIMARY KEY,
    rider_id INT,
    driver_id INT,
    trip_date TIMESTAMP,
    pickup_location VARCHAR(100),
    drop_location VARCHAR(100),
    distance_km DECIMAL(6,2),
    fare DECIMAL(10,2),
    trip_status VARCHAR(20),
    payment_method VARCHAR(20),
    FOREIGN KEY (rider_id) REFERENCES riders(rider_id),
    FOREIGN KEY (driver_id) REFERENCES drivers(driver_id)
);

📂 Step 5: Insert Sample Riders

INSERT INTO riders VALUES
(1,'Rahul','Mumbai','2024-01-10'),
(2,'Priya','Delhi','2024-02-15'),
(3,'Amit','Pune','2024-03-08'),
(4,'Sneha','Bangalore','2024-03-20'),
(5,'Rohan','Hyderabad','2024-04-01');

📂 Step 6: Insert Sample Drivers

INSERT INTO drivers VALUES
(101,'Arjun','Sedan','Mumbai','2023-05-10'),
(102,'Karan','SUV','Delhi','2023-07-18'),
(103,'Vijay','Bike','Pune','2023-08-25'),
(104,'Ramesh','Sedan','Bangalore','2023-10-12');

📂 Step 7: Insert Sample Trips

INSERT INTO trips VALUES
(1001,1,101,'2025-01-05 09:15:00','Andheri','Bandra',12.5,420,'Completed','UPI'),
(1002,2,102,'2025-01-05 18:30:00','Connaught Place','Noida',18.0,650,'Completed','Card'),
(1003,3,103,'2025-01-06 08:45:00','Hinjewadi','Shivajinagar',15.2,390,'Cancelled','Cash'),
(1004,4,104,'2025-01-06 20:10:00','Whitefield','MG Road',20.5,720,'Completed','UPI'),
(1005,5,101,'2025-01-07 14:20:00','Banjara Hills','Gachibowli',10.8,340,'Completed','Cash');

🧠 SQL Concepts You'll Practice

✔ DDL & DML

✔ Joins

✔ Aggregate Functions

✔ GROUP BY

✔ HAVING

✔ CASE WHEN

✔ Window Functions

✔ Common Table Expressions (CTEs)

✔ Ranking Functions

✔ Date & Time Functions

📊 Business KPIs You Can Build

📈 Total Trips

📈 Completed Trips

📈 Cancelled Trips

📈 Cancellation Rate

📈 Total Revenue

📈 Average Trip Fare

📈 Average Trip Distance

📈 Revenue by City

📈 Revenue by Driver

📈 Driver Earnings

📈 Trips per Driver

📈 Most Active Riders

📈 Rider Retention Rate

📈 Peak Booking Hour

📈 Peak Booking Day

📈 Average Trip Duration

📈 Payment Method Distribution

📈 Revenue by Vehicle Type

📈 Top Pickup Locations

📈 Top Drop Locations

📈 Highest Revenue Routes

📈 Average Fare per Kilometer

📈 Driver Utilization Rate

📈 City-wise Demand Analysis

📈 Executive Operations Dashboard

🎯 This project reflects real-world SQL analysis performed by Data Analysts, Operations Analysts, Growth Analysts, and Business Intelligence teams at companies like Uber, Ola, Lyft, and Rapido.

💡 Double Tap ❤️ For More