Online Transportation

Data Analysis
SQL
Published

September 9, 2026

  1. Home
  2. Data Analytics Projects
  3. Online Transportation

Overview

This project explores the online transportation datasets using SQL to answer key analytical questions and uncover meaningful patterns in the data.

Download Datasets (.zip)

Tools & Technologies

  • PostgreSQL

  • SQL

  • Jupyter Notebook

  • JupySQL

  • SQLAlchemy

Analysis Workflow

  1. Preprocess the dataset and understand its structure.

  2. Create the table schema and insert the data into the table

  3. Formulate analytical questions.

  4. Query and manipulate data using SQL.

  5. Interpret query results and summarize key findings.

Install Packages

%pip install --upgrade -q jupysql psycopg2-binary python-dotenv sqlalchemy
%config SqlMagic.autopandas = True
%config SqlMagic.displaylimit = 50

Connect to The Database

import os
from dotenv import load_dotenv, find_dotenv

load_dotenv(find_dotenv())

# get credentials from .env
db_user = os.getenv("DB_USER")
db_password = os.getenv("DB_PASSWORD")
db_host = os.getenv("DB_HOST")
db_port = os.getenv("DB_PORT")
db_name = os.getenv("DB_NAME")

connection_string = f"postgresql://{db_user}:{db_password}@{db_host}:{db_port}/{db_name}"
os.environ['DATABASE_URL'] = connection_string
%load_ext sql
%sql

Prepare the Data

import pandas as pd

drivers_df = pd.read_csv('data/drivers.csv')
trips_df = pd.read_csv('data/trips.csv')
drivers_df
driver_id first_name last_name gender vehicle years_of_work age
0 11247 Rich Norrie Male Motorcycle 20 32
1 11246 Krystyna Bicker Female Motorcycle 21 34
2 11256 Tallulah Eveling Female Car 11 28
3 11257 Agnola Oaks Female Motorcycle 21 35
4 11252 Katine Skittle Female Car 6 35
5 11253 Foss Ricioppo Female Motorcycle 10 24
6 11233 Martica Burvill Female Motorcycle 13 34
7 11249 Cobb Eicke Male Motorcycle 21 32
8 11259 Hobard Zannelli Male Motorcycle 3 22
9 11244 Stanfield Thresher Male Motorcycle 10 22
10 11251 Felike Brenneke Male Car 7 34
11 11245 Clemmie Tulley Female Motorcycle 13 35
12 11239 Trey Liffey Male Car 17 23
13 11254 Hew Radki Male Motorcycle 12 33
14 11232 Halley De Antoni Female Car 22 21
15 11238 Kerrin Cavanagh Female Motorcycle 3 33
16 11231 Petronilla Shilling Female Motorcycle 1 37
17 11240 Wylma Legister Female Motorcycle 9 24
18 11235 Waldo Jeanesson Male Car 19 25
19 11258 Sheila-kathryn Deetlof Female Motorcycle 21 30
20 11250 Danny Shevelin Male Car 20 24
21 11237 Addi Danels Female Motorcycle 13 24
22 11241 Carri Codeman Female Motorcycle 6 40
23 11248 Herby Mengo Male Car 10 33
24 11242 Gawen Kebbell Male Motorcycle 5 23
25 11236 Zolly Chesley Male Car 17 31
26 11243 Carlie Chattoe Male Car 20 22
27 11234 Emmanuel Moxsom Female Car 12 35
28 11255 Shirlene Biers Female Motorcycle 13 25 
trips_df
driver_id order_id final_charge create_date order_time pickup_time finished_time service_type city order_path delievery_path pickup_address drop_address distance number_of_stops rating
0 11247 143967233989 10220 2023-12-01 07:15:12 07:25:00 07:42:18 Motorcycle Bandung Coblong Cicendo Jl. Cihampelas No.193 A, Cipaganti, Kecamatan ... Balai Besar Survei dan Pemetaan Geologi Kelaut... 4.39 1 0
1 11246 142177234930 10629 2023-12-01 07:20:06 07:30:00 07:49:58 Motorcycle Bandung Coblong Sumur Bandung Jl. Cihampelas No.193 A, Cipaganti, Kecamatan ... Jl. Menado No.2, Merdeka, Kec. Sumur Bandung, ... 4.51 1 5
2 11256 148577233960 9402 2023-12-01 07:32:49 07:42:00 07:58:02 Car Bandung Bandung Wetan Coblong Jl. Cihampelas No.112, Tamansari, Kec. Bandung... Jl. Cihampelas No.193 A, Cipaganti, Kecamatan ... 4.00 1 2
3 11257 144677236980 9402 2023-12-01 07:35:54 07:45:00 08:01:55 Motorcycle Bandung Sukasari Cidadap Setiabudi •  Antar Jemput Bandung, Jalan Dokt... Grand Serela Setiabudhi Bandung, Jalan Hegarma... 4.00 1 5
4 11247 147177231952 11446 2023-12-01 08:08:35 08:16:00 08:44:46 Motorcycle Bandung Lengkong Cibeunying Kidul Jl. Asia Afrika No.140, Paledang, Kec. Lengkon... Jl. A. Yani No.827, Padasuka, Kec. Kiaracondon... 4.96 1 1
... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ... ...
961 11233 157029932807 12979 2023-12-31 16:54:48 17:05:06 17:19:20 Motorcycle Bandung Regol Regol Kembar 8, Jalan Kembar VIII, Cigereleng, Kota... Jl.Rd.H.A.Kanti No.4, Ancol, Kota Bandung, Jaw... 4.00 1 0
962 11234 159939934822 12979 2023-12-31 18:29:11 18:39:29 19:36:23 Car Bandung Coblong Sukajadi Jl. Cihampelas No.193 A, Cipaganti, Kecamatan ... Jl. Dr. Setiabudi No.35, Pasteur, Kec. Sukajad... 4.00 1 4
963 11253 151839932853 12979 2023-12-31 18:49:37 18:59:55 19:20:25 Motorcycle Bandung Coblong Coblong Jl. Cihampelas No.193 A, Cipaganti, Kecamatan ... Sensa Hotel Bandung, Jalan Cihampelas, Cipagan... 4.00 1 2
964 11247 158139935824 17885 2023-12-31 18:56:16 19:06:34 19:49:14 Motorcycle Bandung Coblong Cidadap Jl. Cihampelas No.193 A, Cipaganti, Kecamatan ... GH Universal Hotel, Jl. Dr. Setiabudi No.376, ... 6.35 1 0
965 11258 157539932814 9402 2023-12-31 19:06:11 19:16:29 19:33:19 Motorcycle Bandung Coblong Cidadap Jl. Cihampelas No.193 A, Cipaganti, Kecamatan ... Gg. Bp. Aleh No.84/155a, Hegarmanah, Kota Band... 4.00 1 4

966 rows × 16 columns

drivers_df.info()
<class 'pandas.DataFrame'>
RangeIndex: 29 entries, 0 to 28
Data columns (total 7 columns):
 #   Column         Non-Null Count  Dtype
---  ------         --------------  -----
 0   driver_id      29 non-null     int64
 1   first_name     29 non-null     str  
 2   last_name      29 non-null     str  
 3   gender         29 non-null     str  
 4   vehicle        29 non-null     str  
 5   years_of_work  29 non-null     int64
 6   age            29 non-null     int64
dtypes: int64(3), str(4)
memory usage: 1.7 KB
trips_df.info()
<class 'pandas.DataFrame'>
RangeIndex: 966 entries, 0 to 965
Data columns (total 16 columns):
 #   Column           Non-Null Count  Dtype  
---  ------           --------------  -----  
 0   driver_id        966 non-null    int64  
 1   order_id         966 non-null    int64  
 2   final_charge     966 non-null    int64  
 3   create_date      966 non-null    str    
 4   order_time       966 non-null    str    
 5   pickup_time      966 non-null    str    
 6   finished_time    966 non-null    str    
 7   service_type     966 non-null    str    
 8   city             966 non-null    str    
 9   order_path       966 non-null    str    
 10  delivery_path    966 non-null    str    
 11  pickup_address   966 non-null    str    
 12  drop_address     966 non-null    str    
 13  distance         966 non-null    float64
 14  number_of_stops  966 non-null    int64  
 15  rating           966 non-null    int64  
dtypes: float64(1), int64(5), str(10)
memory usage: 120.9 KB
drivers_df['driver_id'] = drivers_df['driver_id'].astype(str)
trips_df['driver_id'] = trips_df['driver_id'].astype(str)
trips_df['order_id'] = trips_df['order_id'].astype(str)
drivers_df.info()
<class 'pandas.DataFrame'>
RangeIndex: 29 entries, 0 to 28
Data columns (total 7 columns):
 #   Column         Non-Null Count  Dtype
---  ------         --------------  -----
 0   driver_id      29 non-null     str  
 1   first_name     29 non-null     str  
 2   last_name      29 non-null     str  
 3   gender         29 non-null     str  
 4   vehicle        29 non-null     str  
 5   years_of_work  29 non-null     int64
 6   age            29 non-null     int64
dtypes: int64(2), str(5)
memory usage: 1.7 KB
trips_df.info()
<class 'pandas.DataFrame'>
RangeIndex: 966 entries, 0 to 965
Data columns (total 16 columns):
 #   Column           Non-Null Count  Dtype  
---  ------           --------------  -----  
 0   driver_id        966 non-null    str    
 1   order_id         966 non-null    str    
 2   final_charge     966 non-null    int64  
 3   create_date      966 non-null    str    
 4   order_time       966 non-null    str    
 5   pickup_time      966 non-null    str    
 6   finished_time    966 non-null    str    
 7   service_type     966 non-null    str    
 8   city             966 non-null    str    
 9   order_path       966 non-null    str    
 10  delivery_path    966 non-null    str    
 11  pickup_address   966 non-null    str    
 12  drop_address     966 non-null    str    
 13  distance         966 non-null    float64
 14  number_of_stops  966 non-null    int64  
 15  rating           966 non-null    int64  
dtypes: float64(1), int64(3), str(12)
memory usage: 120.9 KB

Create The Table Schema

%%sql
-- drivers table
CREATE TABLE IF NOT EXISTS drivers (
    driver_id VARCHAR(10) PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    gender VARCHAR(10) NOT NULL,
    vehicle VARCHAR(25) NOT NULL,
    years_of_work INT NOT NULL,
    age INT NOT NULL
);

-- trips table
CREATE TABLE IF NOT EXISTS trips (
    driver_id VARCHAR(10) REFERENCES drivers(driver_id),
    order_id VARCHAR(25) PRIMARY KEY,
    final_charge INT NOT NULL,
    create_date DATE NOT NULL,
    order_time TIME NOT NULL,
    pickup_time TIME NOT NULL,
    finished_time TIME NOT NULL,
    service_type VARCHAR(25) NOT NULL,
    city VARCHAR(25) NOT NULL,
    order_path VARCHAR(25) NOT NULL,
    delivery_path VARCHAR(25) NOT NULL,
    pickup_address VARCHAR(200) NOT NULL,
    drop_address VARCHAR(200) NOT NULL,
    distance NUMERIC(4, 2) NOT NULL,
    number_of_stops INT NOT NULL,
    rating INT NOT NULL
);
Running query in 'postgresql://postgres:***@localhost:5432/sql_projects_db'

Insert The Data Into The Table

def insert_new_data(df, table_name, engine, key_cols):
    '''Insert only new data from DataFrame to the table in the SQL database.

    :param df: pandas DataFrame.
    :param table_name: a string of table name destination in the SQL database.
    :param engine: SQLAlchemy engine connection object.
    :param key_cols: string or list of primary key/composite keys
    '''

    if df.empty:
        print("DataFrame is empty.")
        return

    # make the key_cols is always a list
    if isinstance(key_cols, str):
        keys = [key_cols]
    else:
        keys = key_cols

    cols_str = ", ".join(keys)
    query = f"SELECT {cols_str} FROM {table_name}"
    existing_df = pd.read_sql(query, con=engine)
    
    
    if len(keys) == 1:
        # only 1 primary key
        key_col = keys[0]
        existing_keys = existing_df[key_col].tolist()
        new_df = df[~df[key_col].isin(existing_keys)].copy()
    else:
        # composite key
        merged = df.merge(existing_df[keys].drop_duplicates(), on=keys, how="left", indicator=True)
        new_df = merged[merged["_merge"] == "left_only"].drop(columns=["_merge"]).copy()

    if not new_df.empty:
        new_df.to_sql(table_name, con=engine, if_exists="append", index=False)
        print(f"Successfully inserted {len(new_df)} new rows of data into the table '{table_name}'.")
    else:
        print(f"All datas have been saved to the table '{table_name}'.")
from sqlalchemy import create_engine

engine = create_engine(connection_string)

# insert drivers data
insert_new_data(df=drivers_df, table_name='drivers', engine=engine, key_cols='driver_id')

# insert trips data
insert_new_data(df=trips_df, table_name='trips', engine=engine, key_cols='order_id')
Successfully inserted 29 new rows of data into the table 'drivers'.
Successfully inserted 966 new rows of data into the table 'trips'.
%%sql
SELECT *
FROM drivers
LIMIT 5;
Running query in 'postgresql://postgres:***@localhost:5432/sql_projects_db'
5 rows affected.
driver_id first_name last_name gender vehicle years_of_work age
0 11247 Rich Norrie Male Motorcycle 20 32
1 11246 Krystyna Bicker Female Motorcycle 21 34
2 11256 Tallulah Eveling Female Car 11 28
3 11257 Agnola Oaks Female Motorcycle 21 35
4 11252 Katine Skittle Female Car 6 35 
%%sql
SELECT *
FROM trips
LIMIT 5;
Running query in 'postgresql://postgres:***@localhost:5432/sql_projects_db'
5 rows affected.
driver_id order_id final_charge create_date order_time pickup_time finished_time service_type city order_path delivery_path pickup_address drop_address distance number_of_stops rating
0 11247 143967233989 10220 2023-12-01 07:15:12 07:25:00 07:42:18 Motorcycle Bandung Coblong Cicendo Jl. Cihampelas No.193 A, Cipaganti, Kecamatan ... Balai Besar Survei dan Pemetaan Geologi Kelaut... 4.39 1 0
1 11246 142177234930 10629 2023-12-01 07:20:06 07:30:00 07:49:58 Motorcycle Bandung Coblong Sumur Bandung Jl. Cihampelas No.193 A, Cipaganti, Kecamatan ... Jl. Menado No.2, Merdeka, Kec. Sumur Bandung, ... 4.51 1 5
2 11256 148577233960 9402 2023-12-01 07:32:49 07:42:00 07:58:02 Car Bandung Bandung Wetan Coblong Jl. Cihampelas No.112, Tamansari, Kec. Bandung... Jl. Cihampelas No.193 A, Cipaganti, Kecamatan ... 4.00 1 2
3 11257 144677236980 9402 2023-12-01 07:35:54 07:45:00 08:01:55 Motorcycle Bandung Sukasari Cidadap Setiabudi •  Antar Jemput Bandung, Jalan Dokt... Grand Serela Setiabudhi Bandung, Jalan Hegarma... 4.00 1 5
4 11247 147177231952 11446 2023-12-01 08:08:35 08:16:00 08:44:46 Motorcycle Bandung Lengkong Cibeunying Kidul Jl. Asia Afrika No.140, Paledang, Kec. Lengkon... Jl. A. Yani No.827, Padasuka, Kec. Kiaracondon... 4.96 1 1

Business Questions

Question 1

How does trip volume and total revenue change over time?

%%sql
SELECT
    create_date,
    COUNT(*) AS total_order,
    SUM(final_charge) AS total_revenue,
    ROUND(AVG(final_charge), 2) AS avg_revenue
FROM trips
GROUP BY create_date
ORDER BY create_date;
Running query in 'postgresql://postgres:***@localhost:5432/sql_projects_db'
31 rows affected.
create_date total_order total_revenue avg_revenue
0 2023-12-01 23 275423 11974.91
1 2023-12-02 32 325703 10178.22
2 2023-12-03 30 354933 11831.10
3 2023-12-04 27 346145 12820.19
4 2023-12-05 17 189373 11139.59
5 2023-12-06 26 280942 10805.46
6 2023-12-07 20 202556 10127.80
7 2023-12-08 37 412266 11142.32
8 2023-12-09 29 317629 10952.72
9 2023-12-10 37 405518 10959.95
10 2023-12-11 18 207053 11502.94
11 2023-12-12 29 314974 10861.17
12 2023-12-13 41 452837 11044.80
13 2023-12-14 35 494027 14115.06
14 2023-12-15 47 487075 10363.30
15 2023-12-16 31 337051 10872.61
16 2023-12-17 41 408177 9955.54
17 2023-12-18 29 309249 10663.76
18 2023-12-19 42 450896 10735.62
19 2023-12-20 43 532860 12392.09
20 2023-12-21 42 519165 12361.07
21 2023-12-22 40 502513 12562.83
22 2023-12-23 31 393978 12708.97
23 2023-12-24 44 535317 12166.30
24 2023-12-25 16 168422 10526.38
25 2023-12-26 28 356979 12749.25
26 2023-12-27 31 357185 11522.10
27 2023-12-28 36 399389 11094.14
28 2023-12-29 22 274809 12491.32
29 2023-12-30 29 318651 10987.97
30 2023-12-31 13 142667 10974.38 
%%sql daily_trip_revenue << 
SELECT
    create_date,
    COUNT(*) AS total_order,
    SUM(final_charge) AS total_revenue,
    ROUND(AVG(final_charge), 2) AS avg_revenue
FROM trips
GROUP BY create_date
ORDER BY create_date;
Running query in 'postgresql://postgres:***@localhost:5432/sql_projects_db'
31 rows affected. 
import matplotlib.pyplot as plt

plt.figure(figsize=(10,5))
plt.plot(daily_trip_revenue['create_date'], daily_trip_revenue['total_revenue'], color='blue')
plt.title('Daily Reveneue Over Time')
plt.xlabel('Date')
plt.ylabel('Revenue')
plt.show()

Daily revenue showed significant fluctuations throughout December 2023.

Peak revenue occurred around the middle and end of the month (approaching 550,000), while the sharpest decline was recorded on December 25.

Question 2

Which cities generate the highest trip volume and revenue?

%%sql
SELECT
    city,
    COUNT(*) AS total_trips,
    SUM(final_charge) AS total_revenue,
    ROUND(AVG(final_charge), 2) AS avg_revenue,
    ROUND(
        100.0 * COUNT(order_id)
        / SUM(COUNT(order_id)) OVER (),
        2
    ) AS trip_share_pct
FROM trips
GROUP BY city
ORDER BY total_revenue DESC;
Running query in 'postgresql://postgres:***@localhost:5432/sql_projects_db'
2 rows affected.
city total_trips total_revenue avg_revenue trip_share_pct
0 Bandung 933 10738658 11509.82 96.58
1 Jakarta 33 335104 10154.67 3.42

Bandung dominates the market share, contributing 96.58% of trips (933 trips) and generating total revenue of approximately 10.7 million.

Jakarta accounts for a much smaller volume—just 3.42% (33 trips)—with total revenue of approximately 335 thousand.

Question 3

Which drivers generate the highest revenue and complete the most trips?

%%sql
SELECT
    driver_id,
    SUM(final_charge) AS total_revenue,
    COUNT(*) AS total_trip,
    ROUND(AVG(final_charge), 2) AS avg_revenue_per_trip
FROM trips 
GROUP BY driver_id
ORDER BY total_revenue DESC;
Running query in 'postgresql://postgres:***@localhost:5432/sql_projects_db'
29 rows affected.
driver_id total_revenue total_trip avg_revenue_per_trip
0 11257 521008 40 13025.20
1 11245 513236 46 11157.30
2 11259 453557 39 11629.67
3 11237 449469 38 11828.13
4 11255 444666 36 12351.83
5 11240 437305 39 11212.95
6 11244 434238 37 11736.16
7 11236 432911 37 11700.30
8 11231 429131 35 12260.89
9 11238 428721 39 10992.85
10 11251 428516 36 11903.22
11 11241 426267 34 12537.26
12 11253 424529 37 11473.76
13 11246 395301 38 10402.66
14 11258 377519 33 11439.97
15 11239 367504 32 11484.50
16 11234 359842 28 12851.50
17 11233 357999 33 10848.45
18 11248 356872 33 10814.30
19 11249 351967 33 10665.67
20 11242 347165 29 11971.21
21 11252 338277 31 10912.16
22 11254 326726 28 11668.79
23 11250 323253 30 10775.10
24 11247 290652 26 11178.92
25 11235 288912 27 10700.44
26 11243 269190 23 11703.91
27 11256 257233 25 10289.32
28 11232 241796 24 10074.83

The driver with ID 11257 generated the highest total revenue of 521,008 from 40 trips.

Most top drivers maintained an average revenue per trip in the range of 10,000 to 13,000.

Question 4

How does driver experience relate to trip volume, revenue, and customer ratings?

%%sql
WITH experience_group AS (
    SELECT
        driver_id,
        CASE
            WHEN years_of_work <= 2 THEN '0-2 years'
            WHEN years_of_work <= 5 THEN '3-5 years'
            WHEN years_of_work <= 10 THEN '6-10 years'
            ELSE '> 10 years'
        END AS experience
    FROM drivers
)
SELECT
    e.experience,
    COUNT(*) AS total_trip,
    SUM(t.final_charge) AS total_revenue,
    ROUND(AVG(rating), 2) AS avg_rating
FROM trips AS t
JOIN experience_group AS e
    ON t.driver_id = e.driver_id
GROUP BY e.experience
ORDER BY 
    CASE
        WHEN e.experience = '0-2 years' THEN 1
        WHEN e.experience = '3-5 years' THEN 2
        WHEN e.experience = '6-10 years' THEN 3
        ELSE 4
    END;
Running query in 'postgresql://postgres:***@localhost:5432/sql_projects_db'
4 rows affected.
experience total_trip total_revenue avg_rating
0 0-2 years 35 429131 2.60
1 3-5 years 107 1229443 2.72
2 6-10 years 247 2846004 2.43
3 > 10 years 577 6569184 2.45

Drivers with over 10 years of experience handled the highest number of trips (577) and generated the highest total revenue.

Interestingly, average ratings across experience groups remained relatively consistent, ranging from 2.4 to 2.7. In fact, drivers in the 0–2 year group held a slight rating advantage (2.60) over the senior group.

Question 5

How do the types of vehicles used by drivers compare?

%%sql
SELECT
    vehicle,
    COUNT(*) AS total_vehicle
FROM drivers
GROUP BY vehicle;
Running query in 'postgresql://postgres:***@localhost:5432/sql_projects_db'
2 rows affected.
vehicle total_vehicle
0 Car 11
1 Motorcycle 18

The fleet of motorcycles (18 units) is larger than that of cars (11 units).

Question 6

Which service types are the most popular, generate the highest revenue and average revenue, and best ratings?

%%sql
SELECT
    service_type,
    COUNT(*) AS total_trip,
    SUM(final_charge) AS total_revenue,
    ROUND(AVG(final_charge), 2) AS avg_revenue,
    ROUND(AVG(rating), 2) AS avg_rating
FROM trips 
GROUP BY service_type;
Running query in 'postgresql://postgres:***@localhost:5432/sql_projects_db'
2 rows affected.
service_type total_trip total_revenue avg_revenue avg_rating
0 Car 326 3664306 11240.20 2.41
1 Motorcycle 640 7409456 11577.28 2.51

The motorcycle service recorded a total number of trips (640) and revenue (7.4 million) that were more than double those of the car service (326 trips, 3.6 million).

The average satisfaction rating for the motorcycle service (2.51) was slightly higher than that for the car service (2.41).

Question 7

How do trip distance and number of stops relate to final charges?

%%sql
WITH distance_stops_charges AS (
    SELECT
        order_id,
        distance,
        CASE
            WHEN distance <= 5 THEN 'Short (<= 5)'
            WHEN distance <= 10 THEN 'Medium (5-10)'
            ELSE 'Long (> 10)'
        END AS distance_group,
        number_of_stops,
        final_charge
    FROM trips
)
SELECT
    distance_group,
    number_of_stops,
    COUNT(order_id) AS total_trips,
    ROUND(AVG(distance), 2) AS avg_distance,
    ROUND(AVG(final_charge), 2) AS avg_charge,
    SUM(final_charge) AS total_charges
FROM distance_stops_charges
GROUP BY 
    distance_group, 
    number_of_stops
ORDER BY
    CASE
        WHEN distance_group = 'Short (<= 5)' THEN 1
        WHEN distance_group = 'Medium (5-10)' THEN 2
        ElSE 3
    END,
    number_of_stops;
Running query in 'postgresql://postgres:***@localhost:5432/sql_projects_db'
6 rows affected.
distance_group number_of_stops total_trips avg_distance avg_charge total_charges
0 Short (<= 5) 1 711 4.10 9847.69 7001709
1 Short (<= 5) 2 6 4.45 14580.33 87482
2 Medium (5-10) 1 228 6.36 14911.78 3399885
3 Medium (5-10) 2 7 7.13 19826.71 138787
4 Long (> 10) 1 12 13.92 31843.83 382126
5 Long (> 10) 2 2 12.98 31886.50 63773

The majority of trips fall into the short-distance category (≤ 5 units) with a single stop, totaling 711 trips.

As the distance and number of stops increase, the average cost (avg_charge) rises linearly (reaching approximately 31,886 for long-distance trips with two stops).

Question 8

How long do trips take from order creation to pickup and completion by service type?

%%sql
WITH trip_durations AS (
    SELECT
        order_id,
        service_type,
        (pickup_time - order_time) AS waiting_duration,
        (finished_time - pickup_time) AS trip_duration
    FROM trips
)
SELECT
    service_type,
    COUNT(order_id) AS total_trips,
    AVG(waiting_duration)::time AS avg_waiting_duration,
    AVG(trip_duration)::time AS avg_trip_duration
FROM trip_durations
GROUP BY service_type
ORDER BY avg_trip_duration DESC;
Running query in 'postgresql://postgres:***@localhost:5432/sql_projects_db'
2 rows affected.
service_type total_trips avg_waiting_duration avg_trip_duration
0 Motorcycle 640 00:09:56.757812 00:23:48.443750
1 Car 326 00:09:54.073620 00:23:24.251534

The average waiting time (avg_waiting_duration) for cars and motorcycles is nearly identical, falling in the range of just over 9 minutes.

The average trip duration (avg_trip_duration) shows a figure of approximately 23 minutes.

Question 9

How long do trips take from order creation to pickup and completion by city?

%%sql
WITH trip_durations AS (
    SELECT
        order_id,
        city,
        (pickup_time - order_time) AS waiting_duration,
        (finished_time - pickup_time) AS trip_duration
    FROM trips
)
SELECT
    city,
    COUNT(order_id) AS total_trips,
    AVG(waiting_duration)::time AS avg_waiting_duration,
    AVG(trip_duration)::time AS avg_trip_duration
FROM trip_durations
GROUP BY city
ORDER BY avg_trip_duration DESC;
Running query in 'postgresql://postgres:***@localhost:5432/sql_projects_db'
2 rows affected.
city total_trips avg_waiting_duration avg_trip_duration
0 Jakarta 33 00:10:04.121212 00:27:37.090909
1 Bandung 933 00:09:55.559486 00:23:31.903537

Jakarta recorded a slightly longer average waiting time (10 minutes 4 seconds) and a longer travel duration (27 minutes 37 seconds) compared to Bandung (9 minutes 55 seconds and 23 minutes 31 seconds).

Conclusions

The operational data reveals that Bandung serves as the primary core market, driving over 96% of total trips and revenue, with Motorcycle services vastly outperforming cars in volume and total earnings. While veteran drivers with over 10 years of experience handle the highest share of workload, customer satisfaction ratings remain uniformly distributed across all experience tiers. Furthermore, short-distance trips dominate the platform usage, and traffic conditions in Jakarta introduce slightly higher waiting and trip durations compared to Bandung. Overall, the business shows strong reliance on high-frequency motorcycle commutes in regional hubs, highlighting an opportunity to optimize vehicle allocation and retention strategies for top-performing drivers.