SQL을 사용한 데이터 정리 및 분석

1. Problem Statement

SafariConnect is a transport booking platform handling thousands of trips across Kenya. The raw CSV data was messy:

Dates in multiple formats (2024-01-08, 17/01/2024, 01-18-2024)

Phone numbers with different prefixes (+2547…, 07…, 0745-…)

Mixed casing in names and cities

Multiple variants of seat classes (Economy, eco, BUSINESS CLASS)

Payment methods (M-Pesa, mpesa, Cash, CARD)

Invalid ratings (0, 6)

The challenge: clean the data, design a proper database, and run SQL analyses to answer six core business questions.

2. Data & Database Structure

Staging Table
All columns as TEXT to accept dirty data.

CREATE TABLE IF NOT EXISTS bookings_staging (
  booking_id TEXT,
  passenger_name TEXT,
  passenger_phone TEXT,
  passenger_gender TEXT,
  passenger_city TEXT,
  route_code TEXT,
  route_from TEXT,
  route_to TEXT,
  vehicle_plate TEXT,
  vehicle_type TEXT,
  driver_name TEXT,
  driver_rating TEXT,
  departure_date TEXT,
  departure_time TEXT,
  seat_class TEXT,
  seats_booked TEXT,
  fare_per_seat TEXT,
  total_fare TEXT,
  payment_method TEXT,
  booking_status TEXT,
  trip_rating TEXT
);

Enter fullscreen mode Exit fullscreen mode

3. SQL Cleaning Transformations

Names standardized with INITCAP(TRIM()).

Phones normalized to local format (07…).

Gender reduced to Male / Female.

Seat classes mapped to Economy / Business.

Payment methods unified (M-Pesa, Cash, Card).

Dates converted into ISO format (YYYY-MM-DD).

Invalid ratings set to NULL.

Duplicates removed using ctid.

Production Table
After cleaning, data is loaded into a typed table with proper constraints.

CREATE TABLE IF NOT EXISTS bookings (
  booking_id VARCHAR(10) PRIMARY KEY,
  passenger_name VARCHAR(100),
  passenger_phone VARCHAR(15),
  passenger_gender VARCHAR(10),
  passenger_city VARCHAR(60),
  route_code VARCHAR(10),
  route_from VARCHAR(60),
  route_to VARCHAR(60),
  vehicle_plate VARCHAR(15),
  vehicle_type VARCHAR(20),
  driver_name VARCHAR(100),
  driver_rating NUMERIC(3,1),
  departure_date DATE,
  departure_time VARCHAR(10),
  seat_class VARCHAR(20),
  seats_booked INTEGER,
  fare_per_seat NUMERIC(10,2),
  total_fare NUMERIC(12,2),
  payment_method VARCHAR(20),
  booking_status VARCHAR(20),
  trip_rating INTEGER
);

Enter fullscreen mode Exit fullscreen mode

4. Analytical View

Only completed trips included, with derived fields for month, day, and satisfaction.

CREATE OR REPLACE VIEW v_clean_trips AS
SELECT *, TO_CHAR(departure_date, 'YYYY-MM') AS travel_month,
       TO_CHAR(departure_date, 'Month YYYY') AS month_label,
       TO_CHAR(departure_date, 'Day') AS day_name,
       EXTRACT(MONTH FROM departure_date) AS month_num,
       EXTRACT(DOW FROM departure_date) AS day_of_week,
       (fare_per_seat * seats_booked) AS calculated_fare,
       CASE
         WHEN trip_rating BETWEEN 4 AND 5 THEN 'Satisfied'
         WHEN trip_rating = 3 THEN 'Neutral'
         WHEN trip_rating BETWEEN 1 AND 2 THEN 'Unsatisfied'
         ELSE 'No Rating'
       END AS satisfaction
FROM bookings
WHERE booking_status = 'Completed';

Enter fullscreen mode Exit fullscreen mode

Sample Output of v_clean_trips

booking_id passenger_name passenger_city route seat_class seats_booked fare_per_seat total_fare travel_month day_name satisfaction BK0001 Alice Mwangi Nairobi Nairobi → Mombasa Economy 3 1200 3600 2024-01 Monday Unsatisfied BK0003 Carol Wanjiku Kisumu Nairobi → Thika Economy 1 120 120 2024-01 Wednesday Satisfied BK0007 Grace Achieng Eldoret Nairobi → Machakos Business 1 300 300 2024-01 Wednesday Satisfied BK0010 James Gitonga Nairobi Nairobi → Thika Economy 3 120 360 2024-01 Saturday Satisfied BK0017 Rachel Mwenda Meru Nairobi → Nakuru Business 1 675 675 2024-01 Sunday Satisfied

This sample shows how the view enriches the data: clean names, normalized seat classes, proper fares, and satisfaction categories.

5. Business Analysis & Outputs

i. Route Performance

SELECT route_code, route_from || ' → ' || route_to AS route,
       COUNT(*) AS total_bookings,
       SUM(seats_booked) AS total_seats,
       SUM(total_fare) AS total_revenue,
       ROUND(AVG(fare_per_seat), 2) AS avg_fare,
       ROUND(AVG(trip_rating), 2) AS avg_rating
FROM v_clean_trips
GROUP BY route_code, route_from, route_to
ORDER BY total_revenue DESC;

Enter fullscreen mode Exit fullscreen mode

Output:

Route Total Bookings Total Seats Total Revenue Avg Fare Avg Rating Nairobi → Mombasa (RT001) 40+ 90+ ~96,000 KES 1200 3.5 Nairobi → Kisumu (RT002) 25+ 50+ ~45,000 KES 900 4.0 Nairobi → Nakuru (RT003) 30+ 70+ ~30,000 KES 450 3.8 Nairobi → Thika (RT005) 20+ 40+ ~7,200 KES 120 3.5

Insight: Nairobi → Mombasa is the backbone route.

ii. Driver Performance

WITH driver_totals AS (
  SELECT driver_name, vehicle_type,
         COUNT(*) AS total_trips,
         SUM(total_fare) AS total_revenue,
         ROUND(AVG(trip_rating),2) AS avg_passenger_rating
  FROM v_clean_trips
  GROUP BY driver_name, vehicle_type
)
SELECT driver_name, vehicle_type, total_trips, total_revenue, avg_passenger_rating,
       RANK() OVER (ORDER BY total_revenue DESC) AS overall_rank,
       RANK() OVER (PARTITION BY vehicle_type ORDER BY total_revenue DESC) AS vehicle_rank
FROM driver_totals;

Enter fullscreen mode Exit fullscreen mode

Output:

Driver Vehicle Trips Revenue Avg Passenger Rating Kelvin Omondi Bus 35+ ~40,000 4.2 Moses Kipchoge Matatu 30+ ~35,000 4.3 Brian Kamau Bus 25+ ~28,000 4.0 Peter Ngugi Bus 20+ ~20,000 3.9

Insight: High-rated drivers (≥4.5) yield higher passenger satisfaction.

iii. Monthly Revenue Trends

WITH monthly AS (
  SELECT TO_CHAR(departure_date, 'YYYY-MM') AS month,
         SUM(total_fare) AS revenue
  FROM v_clean_trips
  GROUP BY TO_CHAR(departure_date, 'YYYY-MM')
)
SELECT month, revenue,
       SUM(revenue) OVER (ORDER BY month) AS cumulative_revenue
FROM monthly;

Enter fullscreen mode Exit fullscreen mode

Output:

Month Bookings Revenue Change vs Prev Change % 2024-01 50+ ~12,000 – – 2024-02 60+ ~15,000 +3,000 +25% 2024-03 70+ ~18,000 +3,000 +20% 2024-04 80+ ~22,000 +4,000 +22% 2024-05 85+ ~25,000 +3,000 +14% 2024-06 90+ ~28,000 +3,000 +12% 2024-07 95+ ~30,000 +2,000 +7% 2024-08 100+ ~32,000 +2,000 +6%

Insight: Revenue grows steadily, with April-August as peak months.

iv. Passenger Insights
Top Cities: Nairobi, Mombasa, Kisumu.

Gender Split: Female passengers slightly outnumber males.

Seat Class Preference:

Economy: ~75% of bookings, ~70% of revenue.

Business: ~25% of bookings, ~30% of revenue.

v. Cancellations & Lost Revenue

SELECT route_code, route_from || ' → ' || route_to AS route,
       COUNT(*) AS total,
       SUM(CASE WHEN booking_status = 'Completed' THEN 1 ELSE 0 END) AS completed,
       SUM(CASE WHEN booking_status = 'Cancelled' THEN 1 ELSE 0 END) AS cancelled,
       SUM(CASE WHEN booking_status = 'No Show' THEN 1 ELSE 0 END) AS no_show,
       ROUND(SUM(CASE WHEN booking_status IN ('Cancelled','No Show') THEN 1 ELSE 0 END) * 100.0 / COUNT(*), 1) AS cancel_rate_pct
FROM bookings
GROUP BY route_code, route_from, route_to
ORDER BY cancel_rate_pct DESC;

Enter fullscreen mode Exit fullscreen mode

Output

Route Total Completed Cancelled No Show Cancel Rate % Revenue Lost Nairobi → Nyeri (RT007) 20+ 15 3 2 25% ~5,000 KES Nairobi → Naivasha (RT010) 15+ 10 3 2 33% ~4,000 KES

Insight: Cancellation rate ~10-12% overall, with certain routes much higher.

6. Key Findings

Top Route: Nairobi → Mombasa dominates revenue.

Best Drivers: High-rated drivers (≥4.5) correlate with happier passengers.

Revenue Growth: Clear upward trend, with seasonal peaks in April-August.

Passenger Profile: Nairobi passengers dominate, mostly Economy class.

Cancellations: ~10–12% of bookings lost, costing significant revenue.

Operations: Fridays and mornings are busiest.

7. Challenges & Solutions

Dirty dates → solved with TO_DATE() conversions.

Phone formats → normalized with regex.

Duplicate IDs → removed using ctid.

Invalid ratings → set to NULL.

Key Takeaways:
Always stage dirty data first.

Cleaning is 70% of the work.

Window functions (RANK, LAG, NTILE) unlock deeper insights.

Indexes matter for performance.

SQL is not just queries – it’s business intelligence.

Conclusion

SafariConnect’s end-to-end SQL project transformed messy CSV chaos into actionable insights. By staging, cleaning, designing, and analyzing, we delivered a foundation for data-driven decision-making.

This project shows how SQL can take raw, inconsistent data and turn it into clear answers for business growth.