"SQL, a Messy CSV, and a CEO Waiting for Answers: The SafariConnect Project"

Introduction

A few weeks ago, as part of learning SQL, we were given a project built around a fictional company called SafariConnect, a Nairobi bus and matatu booking platform. The scenario: you're the newly hired Data Analyst, the company's been growing fast, and nobody actually knows which routes make money, which drivers are worth promoting, or why cancellations feel so high. All they have is a 290-row CSV export from a shared Excel sheet, and a board meeting on the calendar with the CEO expected to ask questions.

No pressure. Just clean the data, load it, analyse it, and have real answers ready.

The Mess

The CSV looked like it had been typed by six different people on six different days, because it basically had been. Same fields, wildly inconsistent values:

• Names in ALL CAPS, some in lowercase, some with random leading spaces
• Phone numbers as 0712-345-678, some as [phone]
• Three different date formats sitting in the same column
• Gender as 'male', 'MALE', 'M', and 'F'
• Fares stored as text, some with 'KES' glued to the front
• One booking_id duplicated, one row with -1 seats booked
• A handful of trip ratings that were somehow 0 or 6, on a 1-5 scale

Before touching any of it, I dumped everything into a staging table where every column was just TEXT. No point enforcing types on data I hadn't even looked at yet.

CREATE TABLE bookings_staging (
    booking_id       TEXT, passenger_name TEXT, passenger_phone TEXT,
    passenger_gender TEXT, passenger_city  TEXT, route_code      TEXT,
    departure_date   TEXT, seats_booked    TEXT, total_fare      TEXT,
    booking_status   TEXT, trip_rating     TEXT
    -- (plus the rest of the columns)
);

Fixing It

Names, cities, and driver names all got the same treatment:

UPDATE bookings_staging
SET passenger_name = INITCAP(TRIM(passenger_name))
WHERE passenger_name != INITCAP(TRIM(passenger_name));

The messy categorical columns (gender, payment method, seat class, booking status) all had the same problem: too many spellings of the same real value. CASE WHEN cleaned them all up the same way:

UPDATE bookings_staging
SET payment_method = CASE
    WHEN UPPER(TRIM(payment_method)) IN ('MPESA','M-PESA','M PESA') THEN 'M-Pesa'
    WHEN UPPER(TRIM(payment_method)) = 'CASH' THEN 'Cash'
    WHEN UPPER(TRIM(payment_method)) = 'CARD' THEN 'Card'
    ELSE payment_method
END;

The dates were the annoying one. Three formats, no flag saying which was which, so I had to separate them by shape first:

-- DD/MM/YYYY - has a slash
UPDATE bookings_staging
SET departure_date = TO_DATE(departure_date,'DD/MM/YYYY')::TEXT
WHERE departure_date LIKE '%/%';

-- MM-DD-YYYY - the "day" part is over 12, so it can't be DD-MM
UPDATE bookings_staging
SET departure_date = TO_DATE(departure_date,'MM-DD-YYYY')::TEXT
WHERE departure_date LIKE '%-%'
  AND LENGTH(departure_date) = 10
  AND SPLIT_PART(departure_date,'-',2)::INTEGER > 12;

That "day part over 12" trick was the only way to tell MM-DD-YYYY apart from DD-MM-YYYY, since for the first twelve days of any month, both formats look identical.

The duplicate booking_id and the negative-seats row both got dropped outright rather than fixed. Postgres doesn't let you compare a row to itself directly, so ctid (the row's physical location) became the tiebreaker for the duplicate:

DELETE FROM bookings_staging
WHERE ctid NOT IN
    (SELECT MIN(ctid) FROM bookings_staging GROUP BY booking_id);

Once everything was clean, it moved into a properly typed bookings table with real DATE, NUMERIC, and INTEGER columns, going from 290 messy rows down to 283 trustworthy ones.

Actually Answering the Business Questions

This is the part where the project actually clicked for me. All that cleaning wasn't the point, it was just the cost of entry. The real value showed up here: turning "which routes make money" and "should we promote this driver" into an actual SELECT statement, and getting a real, defensible answer back. That's the whole point of data analysis, not writing queries for their own sake, but using them to answer questions a business genuinely needs answered.

With clean data, the six business questions turned into short queries. Route performance, for instance:

SELECT route_code, route_from || ' → ' || route_to AS route,
       SUM(total_fare) AS total_revenue,
       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;

Running that query against the cleaned data gave a clear leader and a clear laggard: Nairobi to Mombasa pulled in KES 51,600, close to a quarter of total company revenue, while Nairobi to Machakos brought in just KES 6,900. Same query, same columns, wildly different outcomes depending on the route, which is exactly the kind of gap a director can act on. A follow-up version of the same query, dividing revenue by seats sold instead of just summing it, told a slightly different story too: RT001 earned the most per seat at KES 1,258.54, while RT005 barely cleared KES 122.90, a reminder that total revenue and efficiency per seat aren't always the same route. Sliced by vehicle type instead of route, matatus came out as the most profitable overall at KES 85,135, edging out buses despite carrying fewer total seats.

I wrapped each business question in a view (v_route_performance, v_driver_performance, v_monthly_revenue, and so on) so the analysis wasn't a one-time thing, it's queryable going forward.

The month-over-month revenue trend needed a window function, since "compare this month to last month" isn't something a plain GROUP BY can do on its own:

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,
       LAG(revenue) OVER (ORDER BY month) AS prev_month,
       ROUND((revenue - LAG(revenue) OVER (ORDER BY month))
           / NULLIF(LAG(revenue) OVER (ORDER BY month), 0) * 100, 1) AS change_pct
FROM monthly
ORDER BY month;

LAG() just reaches back one row to grab the previous month's revenue, sitting right next to the current one, which made the percentage change a one-line calculation instead of a separate query.

What the Queries Actually Turned Up

Total net revenue across the network landed at Ksh223.97K from 248 completed bookings, KES 223,970 that only existed as scattered, dirty rows a few queries earlier.

The one that actually surprised me: drivers the platform rated 4.5 and above averaged a lower passenger satisfaction score (3.34) than "standard" drivers did (3.64). The company's own rating system wasn't predicting happier passengers. If anything, it was pointing the wrong way. That's the kind of thing you'd never catch just skimming the spreadsheet, and exactly the kind of finding a director actually wants walking into a board meeting.

The driver question needed more than a simple GROUP BY too, since "who's the best driver" isn't just about overall revenue, it's also about how each driver stacks up within their own vehicle type. RANK() with PARTITION BY handled both at once:

WITH driver_totals AS (
    SELECT driver_name, vehicle_type,
           COUNT(*) AS total_trips,
           SUM(total_fare) AS total_revenue
    FROM v_clean_trips
    GROUP BY driver_name, vehicle_type
)
SELECT driver_name, vehicle_type, total_trips, total_revenue,
       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
ORDER BY overall_rank;

PARTITION BY restarts the ranking within each group instead of across the whole table, so a driver could be the top performer for matatus specifically, even without being the top driver network-wide. Overall, Isaac Korir came out on top by revenue at KES 32,505, while Hassan Abdi had the best average passenger rating at 3.9, two different drivers topping two different leaderboards, which is exactly why "who should we promote" needed more than one query to answer properly.

Cancellations turned out to be a real problem, not just a feeling. A view built on booking_status made it easy to check per route:

SELECT route_code,
       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
ORDER BY cancel_rate_pct DESC;

Across the network, the overall cancellation rate sat at 12.37%, and that translated to Ksh17.24K in lost revenue. RT005 (Nairobi-Thika) and RT006 (Mombasa-Malindi) had the highest combined cancellations and no-shows, five apiece. But the one that actually mattered most in KES terms was RT001, the network's single best-performing route by revenue also lost the most money to cancellations, at KES 5,169. Being the top earner didn't make it immune, if anything it meant every cancellation there cost more.

Booking patterns by day filled in the operational side, and this one was a straightforward EXTRACT and COUNT:

SELECT TO_CHAR(departure_date, 'Day') AS day_name,
       COUNT(*) AS total_bookings
FROM v_clean_trips
GROUP BY TO_CHAR(departure_date, 'Day'), EXTRACT(DOW FROM departure_date)
ORDER BY EXTRACT(DOW FROM departure_date);

Tuesday was the busiest day on the network at 49 bookings, followed by Monday at 45 and Thursday at 43. Sunday was the quietest by a wide margin, just 10 bookings, which on its own is a fairly clear signal for where NOT to over-allocate vehicles. Economy seats made up the vast majority of bookings too, 79% against 21% for Business, so most of the fleet's day-to-day demand is coming from the budget end.

Passenger patterns filled in the rest, and this is where HAVING earned its keep, filtering the grouped results down to cities with enough bookings to actually mean something:

SELECT passenger_city, COUNT(*) AS total_bookings,
       SUM(total_fare) AS total_revenue
FROM v_clean_trips
GROUP BY passenger_city
HAVING COUNT(*) >= 3
ORDER BY total_revenue DESC;

Nairobi dominated as a home city by a wide margin over Mombasa, Kisumu, and Thika, generating KES 110,410 on its own. M-Pesa was the clear preferred payment method at 53% of transactions, with cash and card splitting the remainder. And revenue by gender came out almost even, 53.97% male to 46.03% female, nothing dramatic enough to change a business decision on its own, but useful context for the passenger profile the director asked for.

What I'd Tell Someone Starting This

• Load messy data into a TEXT-only staging table first. Don't fight the types until you've seen the mess.
• Run SELECT DISTINCT on every categorical column before assuming you know what's in it.
• If two date formats look identical for part of the month, find the one field that breaks the tie (day > 12, in this case) instead of guessing.
• Wrap your answers in views. The director didn't just want six numbers, they wanted queries they could re-run next month.

Cleaning took longer than the actual analysis. But the analysis was only trustworthy because of it, and "trustworthy" is the whole job when there's a CEO in the room asking where the numbers came from. Every finding here fed straight into a set of actual recommendations, root-causing why RT009 underperforms instead of just flagging it, tightening cancellation policy on the worst offenders, and figuring out whether Peter Ngugi's lower numbers are a driver problem or a route problem before deciding how to respond.

That's the bit that actually stuck with me from this project. It's easy to think of SQL as just a way to retrieve data. This was the first time it clearly showed me the other half: SQL as the actual mechanism for answering a business question, GROUP BY turning raw rows into a route ranking, CASE WHEN turning a numeric score into a driver category, a window function turning a flat table into a month-over-month trend. None of it was retrieval for its own sake. Every query existed because someone in a boardroom needed a specific question answered, and that, more than any single command, is the whole point of data analysis.

All of this eventually fed into a dashboard the director could actually hand to the board:

More on how that got built in a follow-up post.

Comments

[Raknaos]

The staging-table-with-everything-as-TEXT decision is the right instinct and doesn't get said enough. Typing the data before you've seen it means Postgres rejects rows on formats you haven't catalogued yet, and then you're debugging the import instead of the data.

The SPLIT_PART day-over-12 trick is a clever resolution of the MM-DD vs DD-MM ambiguity — with the honest caveat baked in that for the first twelve days of any month both parses are valid, so those rows are disambiguated by convention rather than evidence. If the source file ever matters (it usually does for reconciliation), that's the kind of guess worth keeping in a provenance column.

For the next run of the same import, one pattern that paid off for us: once the data moves into the typed bookings table, restate your cleaning rules as CHECK constraints (rating BETWEEN 1 AND 5, seats_booked > 0, status IN (...)) so a re-import that regresses fails loudly instead of quietly re-polluting the analytics.

Out of curiosity, what did you do with the 0 and 6 ratings — NULL them, clamp, or drop the rows? Ratings are the worst kind of dirty value because they look numeric and clean right up until you average them.

[Neema Kirui]

Really appreciate this, especially the provenance column idea, that hadn't occurred to me at all.
Same with the CHECK constraints suggestion. I treated the cleanup as a one-time pass, but you're right that without something enforcing rating BETWEEN 1 AND 5 or seats_booked > 0 at the table level, a future re-import could reintroduce the exact same mess and I wouldn't know until someone noticed the numbers looked off again. Fixing it once isn't the same as making it stay fixed.

To answer the ratings question: NULL. A 0 or 6 isn't really "close to" a valid rating, it's just not a rating, so clamping it to 1 or 5 felt like inventing an opinion the passenger never gave. Dropping the row felt worse, since the rest of that booking (fare, route, driver) was still perfectly valid data, no reason to lose all of it over one bad field. NULL let the rest of the row survive and just meant AVG(trip_rating) correctly ignored those rows instead of quietly dragging the average toward a fake number.

And agreed on staging-as-TEXT, that one felt obvious in hindsight but wasn't obvious going in. My first instinct was to type the staging table too, and the load just started failing on rows I hadn't even looked at yet. Untyping it first turned "fix the import" into "look at the data," which was a much better place to actually start debugging from.