DEV Community: David Mwandairo

Python Loops at the Nairobi Animal Orphanage

David Mwandairo — Thu, 08 Oct 2026 14:10:01 +0000

Nairobi is one of the few capitals with a national park inside its limits. Lions hunt on the plains while the city skyline stands in view. Near the Main Gate on Lang'ata Road, the Kenya Wildlife Service runs the Nairobi Animal Orphanage, which takes in orphaned, injured and abandoned animals, treats them, and gives them a chance to return to the wild.

Now picture a day on the job. The park gates open at 6:00 a.m. Before they do, Wanjiru, a ranger at the orphanage, has a clipboard and a problem: dozens of small checks, and every one is the same. Look, tick, move on. A person does this with a pencil and a sore back. A programmer writes a loop.

A loop tells Python to run one block of code more than once. Python has two kinds, for and while. It also has two controls that change a loop mid-flight, break and continue, and a built-in helper, enumerate(), that does your counting for you.

One note before we start: Wanjiru, the enclosure codes and every number below are invented for teaching. They are not real KWS procedures. Each example is a complete script, so save it, run it, and compare your terminal with the output shown.

Note: "Python" in this article has been used to denote the programming language, not the snake.

The for loop: you have the items, so visit each one

A for loop takes a source of items and walks through them in order. On each pass, Python puts the next item into a variable you name, runs the indented block beneath, and moves on. When the items run out, the loop ends and the program carries on below it.

for item in source:
    # this indented block runs once per item

Two sources cover most of what you will do early on: range(), which hands out numbers, and a string, which hands out its characters one at a time.

Counting with range()

The cheetah trough fills in five pump strokes, and each stroke adds 50 litres. Wanjiru wants a running total.

total_litres = 0

for pump in range(1, 6):
    total_litres += 50
    print(f"Pump {pump}: cheetah trough holds {total_litres} litres")

print("Cheetah trough is full.")

Run it:

$ python trough.py
Pump 1: cheetah trough holds 50 litres
Pump 2: cheetah trough holds 100 litres
Pump 3: cheetah trough holds 150 litres
Pump 4: cheetah trough holds 200 litres
Pump 5: cheetah trough holds 250 litres
Cheetah trough is full.

Two things to notice. The f before the quotes lets you drop a variable into text by wrapping it in braces. And range(1, 6) produces 1, 2, 3, 4, 5. It starts at the first number and stops before the second. That "stops before" rule trips up nearly everyone once. If you want to count to 5, the stop value is 6.

Give range() a third number and it counts in steps. The orphanage hosts ranger talks for school groups, one every 20 minutes during the first hour:

for minute in range(0, 60, 20):
    print(f"Ranger talk for school groups: {minute} minutes after opening")

$ python talks.py
Ranger talk for school groups: 0 minutes after opening
Ranger talk for school groups: 20 minutes after opening
Ranger talk for school groups: 40 minutes after opening

range(0, 60, 20) starts at 0, adds 20 each time, and stops before it reaches 60. If you give range() a single number, as in range(5), it starts at 0 and stops before that number.

Walking through a string

Six pens, labeled A to F, each hold a water bowl that needs a morning check. A string is a row of characters, so a for loop can visit each label directly.

for pen in "ABCDEF":
    print(f"Pen {pen}: water bowl checked")

$ python pens.py
Pen A: water bowl checked
Pen B: water bowl checked
Pen C: water bowl checked
Pen D: water bowl checked
Pen E: water bowl checked
Pen F: water bowl checked

The loop variable, pen, holds one letter per pass. You choose the name, so pick one that says what the item is. for x in "ABCDEF" works, but for pen in "ABCDEF" explains itself six months later.

The while loop: you have a condition, so repeat until it changes

A for loop knows how many passes it will make before it starts. Sometimes you don't. A rescued chick needs a warm brooder, and a heat lamp raises the temperature a few degrees at a time. Nobody can say in advance how many cycles it takes. You only know the goal.

A while loop fits that case. It checks a condition before every pass and runs the block only if the condition is true.

temperature = 22
target = 30

while temperature < target:
    temperature += 3
    print(f"Heat lamp cycle: brooder is at {temperature}°C")

print("Brooder ready for the rescued chick.")

$ python brooder.py
Heat lamp cycle: brooder is at 25°C
Heat lamp cycle: brooder is at 28°C
Heat lamp cycle: brooder is at 31°C
Brooder ready for the rescued chick.

Follow the numbers. The brooder starts at 22°C. The check 22 < 30 is true, so the lamp adds 3 and the temperature reads 25. Then 28, which still passes the check. Then 31. Now 31 < 30 is false, so the loop stops. The condition is tested before each pass, never during one, so the final value can overshoot the target. If overshooting matters, test for it inside the loop.

The trap: a loop that never ends

Every while loop needs something inside the block that moves the condition toward false. Take that line out and the loop runs forever. Here is the brooder script with the temperature += 3 line deleted:

temperature = 22
target = 30

while temperature < target:
    print(f"Brooder is at {temperature}°C")

$ python stuck.py
Brooder is at 22°C
Brooder is at 22°C
Brooder is at 22°C
...

The temperature stays at 22, so 22 < 30 stays true, and the terminal fills with that line until you press Ctrl+C. Whenever you write a while loop, ask yourself one question: what changes on each pass to make this stop?

Which loop do I pick?

Use for when you have a set of things to visit or a number of passes to make. Use while when you have a goal and no idea how many steps it takes. If you can do it with for, prefer for. It has no counter to forget and no way to run forever.

break and continue: change the loop from the inside

Sometimes the plan changes halfway through. Two keywords give you control.

break ends the whole loop

The side of the park that faces the city is fenced, which keeps wildlife off the roads and out of the neighborhoods. Rangers walk the fence checkpoint by checkpoint. The moment they find a breach, they call the response team. Checking the rest of the fence can wait.

In the status string below, I means intact and B means breached.

checkpoint = 0

for fence in "IIIBII":
    checkpoint += 1
    if fence == "B":
        print(f"Checkpoint {checkpoint}: fence BREACHED. Calling the response team.")
        break
    print(f"Checkpoint {checkpoint}: fence intact.")

print("Patrol ended.")

$ python fence.py
Checkpoint 1: fence intact.
Checkpoint 2: fence intact.
Checkpoint 3: fence intact.
Checkpoint 4: fence BREACHED. Calling the response team.
Patrol ended.

At checkpoint 4, break fires. Python leaves the loop at once, so checkpoints 5 and 6 are never checked. The last print sits outside the loop, so it runs anyway.

continue ends only the current pass

The morning feeding round is different. Hungry animals get a meal. A sleeping animal gets left alone, but the ranger still walks on to the next enclosure. Here H means hungry and S means sleeping.

enclosure = 0
meals_served = 0

for mood in "HHSHSH":
    enclosure += 1
    if mood == "S":
        print(f"Enclosure {enclosure}: animal is asleep, skipping.")
        continue
    meals_served += 1
    print(f"Enclosure {enclosure}: fed.")

print(f"Meals served: {meals_served}")

$ python feeding.py
Enclosure 1: fed.
Enclosure 2: fed.
Enclosure 3: animal is asleep, skipping.
Enclosure 4: fed.
Enclosure 5: animal is asleep, skipping.
Enclosure 6: fed.
Meals served: 4

When the mood is S, continue jumps back to the top of the loop for the next item. It skips the two lines below it, so the meal count stays put for sleepers. Enclosures 3 and 5 are skipped, and four meals go out.

Compare the two: break leaves the loop for good, while continue abandons one pass and keeps looping.

Using both in a while True loop

A while True loop has a condition that is always true, so only a break can end it. That sounds dangerous, but it is a common pattern when the exit rule is easier to state in the middle of the block than at the top.

Park tickets are cashless and bought in advance, so the Main Gate runs on a scanner. This script follows the scanner through the afternoon. It goes offline at 15:00 for a shift change, and the gate closes at 18:00:

hour = 12

while True:
    hour += 1
    if hour == 15:
        print("15:00 Shift change. Scanner offline.")
        continue
    if hour == 18:
        print("18:00 Last entry has passed. Gate closed.")
        break
    print(f"{hour}:00 Main Gate scanner reading tickets")

$ python booth.py
13:00 Main Gate scanner reading tickets
14:00 Main Gate scanner reading tickets
15:00 Shift change. Scanner offline.
16:00 Main Gate scanner reading tickets
17:00 Main Gate scanner reading tickets
18:00 Last entry has passed. Gate closed.

Look at where hour += 1 sits: first line of the loop. That placement is deliberate. At 15:00, continue skips the rest of the block and goes back to the top. Had the hour += 1 line sat below the continue, the hour would stay at 15 forever, and the loop would print the shift-change message until you killed it. In a while loop, put the update before any continue.

enumerate(): let Python do the counting

Look back at the fence patrol and the feeding round. Both carry a hand-made counter, checkpoint += 1 and enclosure += 1, whose only job is to say which item we are on. This is such a common need that Python built a tool for it.

enumerate() wraps your source of items and hands you two things on every pass: a count, then the item. You catch both by writing two names before in.

Three cheetahs wear colored tags, R, G and B. Here is what enumerate() gives you with no extra settings:

for index, tag in enumerate("RGB"):
    print(f"Index {index}: cheetah with {tag} tag")

$ python enum_default.py
Index 0: cheetah with R tag
Index 1: cheetah with G tag
Index 2: cheetah with B tag

The count starts at 0 by default. That suits programmers, but Wanjiru's clipboard starts at checkpoint 1. Pass start=1 and the count follows. Here is the fence patrol again:

for checkpoint, fence in enumerate("IIIBII", start=1):
    if fence == "B":
        print(f"Checkpoint {checkpoint}: fence BREACHED. Calling the response team.")
        break
    print(f"Checkpoint {checkpoint}: fence intact.")

print("Patrol ended.")

$ python fence_enum.py
Checkpoint 1: fence intact.
Checkpoint 2: fence intact.
Checkpoint 3: fence intact.
Checkpoint 4: fence BREACHED. Calling the response team.
Patrol ended.

The output matches the earlier version line for line, and the checkpoint = 0 and checkpoint += 1 lines are gone. Fewer lines mean fewer places for a counter bug to hide. Whenever you catch yourself writing i += 1 inside a for loop, reach for enumerate() instead.

Morning round at the orphanage: all four at once

Here is the full picture. Wanjiru pushes a food cart that holds up to 3 meals and starts with 2. She walks eight enclosures, each coded H (hungry), S (sleeping) or X (emergency). Sleepers get skipped. An emergency ends the round so she can call the vet. When the cart runs out, she restocks it before feeding.

cart = 2
fed = 0

for enclosure, status in enumerate("HSHHSXHH", start=1):
    if status == "X":
        print(f"Enclosure {enclosure}: emergency! Calling the vet. Round over.")
        break
    if status == "S":
        print(f"Enclosure {enclosure}: asleep, skipping.")
        continue
    if cart == 0:
        print("  Cart empty. Restocking...")
        while cart < 3:
            cart += 1
            print(f"  Cart holds {cart} meal(s)")
    cart -= 1
    fed += 1
    print(f"Enclosure {enclosure}: fed. Cart now holds {cart}.")

print(f"Animals fed: {fed}")

$ python morning_round.py
Enclosure 1: fed. Cart now holds 1.
Enclosure 2: asleep, skipping.
Enclosure 3: fed. Cart now holds 0.
  Cart empty. Restocking...
  Cart holds 1 meal(s)
  Cart holds 2 meal(s)
  Cart holds 3 meal(s)
Enclosure 4: fed. Cart now holds 2.
Enclosure 5: asleep, skipping.
Enclosure 6: emergency! Calling the vet. Round over.
Animals fed: 3

Trace it against the output:

enumerate(..., start=1) numbers the enclosures from 1, so no counter variable exists.
Enclosure 2 is S, so continue skips the feeding code and the round moves on.
After enclosure 3, the cart holds 0. At enclosure 4 the if cart == 0 check triggers a while loop that adds one meal per pass until the cart holds 3. The number of passes depends on the cart's level, which is exactly the situation while is for.
Enclosure 6 is X, so break ends the for loop. Enclosures 7 and 8 are never visited, and the final count reports 3 animals fed.

Each tool does one job. for visits the enclosures, enumerate() numbers them, continue skips the sleepers, break answers the emergency, and while handles the restock.

Try it yourself

Copy the morning round script and change one thing at a time. Predict the output before you run it.

Move the X to the last position. How many animals get fed now?
Change start=1 to start=10. Which lines of output change?
Set the starting cart to 0. What prints first?
Add a second X earlier in the string. Does the second one ever fire?

Predicting, then running, then reading the gap between the two is how loops stop feeling like magic. If you rebuild the example around another Kenyan setting, such as a tea factory in Kericho, a matatu stage, or an M-Pesa agent's day, share it in the comments.

Next time you catch yourself pasting the same line three times, stop and ask which loop wants the job.

Building the Tembo Hotel Dashboard in Power BI

David Mwandairo — Wed, 07 Oct 2026 14:36:20 +0000

The Hotel Director's message ended with its shortest instruction: "Make it look professional - use Power BI for the visuals."

Professional is hard to test, so I use three tests. The numbers must tie to the database. The labels must say what they mean. A busy manager must find an answer in under a minute. This article builds the Tembo Hotel dashboard, then grades it against all three.

I'll also set you a puzzle. Page three of the dashboard contains a mistake that sits in plain sight. Look at the Staff Performance table when you reach it, and see whether you spot it before I explain.

The brief in one breath

Tembo Hotel & Suites is a mid-range business hotel in Nairobi. Its Director wanted four answers: which rooms make the most money, which months are busiest, how the staff are performing, and whether guests are happy. The raw material was a booking export with 286 rows and a long list of defects. I cleaned it in PostgreSQL first and then built the dashboard on the clean result.

3 quick reminders before building the dashboard:

To go through the step-by-step analysis that led to this Power BI report, go through this article.
For a refresher on how to connect Power BI to a local database, check out this article.
To access the files used to build this project, check out my Github repository.

1. How I prepared the data for BI

A dashboard does not clean data. It draws whatever it receives, so a misspelt city becomes a bar and a duplicate booking becomes double revenue. The cleaning therefore came first, in SQL, and Power BI only ever saw the finished result: 285 bookings after one exact duplicate was removed.

Import the view, not the table

Power BI imports one object, the view v_clean_bookings in the tembo_hotel schema of the PostgreSQL database. It does not touch the staging table or the raw file. The model is a single flat table of 285 rows, with 25 measures on top.

The view carries four columns built for reporting:

Column	What it holds
`realised_revenue`	The booking amount for `Checked Out` bookings, and zero for the rest
`lost_revenue`	The booking amount for `Cancelled` and `No Show` bookings, and zero for the rest
`stay_month`	The first day of the check-in month
`month_label`	The same month as text, such as `2023-06`

Moving the two money rules into SQL was deliberate. Power BI then sums a column, and anything else that reads the view gets the same answer.

Three signs the dashboard reads clean data

The first card says 285 bookings, so the duplicate is gone.
The guest-city chart has a bar called Unknown. That is the label my cleaning gave to blank cities. It has no bar called Thikax, a typo the cleaning corrected to Thika.
Every chart that splits by category shows exactly the allowed values: four payment methods, four room types, three booking statuses. There is no mpesa, Std or DLX.

Four rules the numbers rest on

Cleaning fixes spelling. It cannot decide what "revenue" means. Before building a chart, I fixed four rules:

Term	Rule	Where it lives
Realised revenue	`Checked Out` bookings only	SQL column `realised_revenue`
Lost value	`Cancelled` and `No Show` bookings	SQL column `lost_revenue`
Completed stay	A booking with status `Checked Out`	DAX measure
Average rating	Completed stays only; blank ratings stay blank	DAX measure

Section 3 shows the DAX for both rules, and for every other measure on the three pages. AVERAGE skips blank cells, so the NULL ratings left by the cleaning step never count as zeros. Nobody rates a night they did not spend, so ratings on cancelled bookings stay out of the average too.

I sorted the 25 measures into numbered display folders (Booking Volume, Revenue, Room Demand, Guest Satisfaction, Staff Performance, Booking Losses, Monthly Trend) and gave each one a one-line description. When someone asks what "Lost Revenue" means, the model answers.

Does the dashboard agree with the database?

I do not trust a dashboard until its totals match a query I wrote by hand. This query returns the headline numbers straight from the view:

SELECT COUNT(*) AS total_bookings,
       COUNT(*) FILTER (WHERE booking_status = 'Checked Out') AS completed_stays,
       SUM(total_amount) FILTER (WHERE booking_status = 'Checked Out') AS realised_revenue,
       COUNT(*) FILTER (WHERE booking_status IN ('Cancelled', 'No Show')) AS lost_bookings,
       SUM(total_amount) FILTER (WHERE booking_status IN ('Cancelled', 'No Show')) AS lost_value,
       ROUND(AVG(guest_rating) FILTER (WHERE booking_status = 'Checked Out'), 2) AS avg_rating
FROM tembo_hotel.v_clean_bookings;

Result grid: 1 row fetched

total_bookings	completed_stays	realised_revenue	lost_bookings	lost_value	avg_rating
285	253	7752400.00	32	1175300.00	3.01

Measure	Dashboard	SQL	Match
Total bookings	285	285	Yes
Completed stays	253	253	Yes
Realised revenue	7,752K on the card, 7,752,400 in the staff table total	7,752,400	Yes
Lost bookings	32	32	Yes
Lost value	1,175K on the card, 1,175,300 in the payment table	1,175,300	Yes
Average rating	3.01	3.01	Yes

Every figure ties. That is test one passed.

2. The dashboard I built

I grouped the Director's four questions into three pages. Each page has one job.

Page	Job	Visuals
Executive Overview	Money and trend at a glance	Five KPI cards, monthly realised revenue (line), revenue by room type (bars), revenue by payment method (donut), revenue share by department (bars), booked value by status (donut)
Rooms & Guests	Who stays, for how long, and how they feel	Completed stays by room type, average stay length by room type, completed stays by guest city, guest rating distribution, average rating by room type
Staff & Risk	People and lost money	Staff performance table, realised revenue by department, cancellation and no-show rate by room type, lost bookings by payment method

The look is deliberate and plain: a cream background, one teal for every bar, a serif title in warm brown, and four warm colours kept for the donuts, where slices must be told apart. The page tabs use the nouns the Director already uses.

The second page answers "who stays and are they happy". The rating distribution sits beside the average on purpose, since the average alone would mislead.

The third page holds the staff table, and the puzzle. Look at the Rank column before you read on.

3. The DAX behind each page

Every figure on the dashboard comes from a measure. The model has one table, named tembo_hotel v_clean_bookings, and 25 measures grouped in display folders. The code below is copied from the model and reformatted for reading, with the logic unchanged. For each page I list the visuals and the measure that feeds each one, then show the code for the measures that page introduces. A measure used on an earlier page is not repeated.

Two display details apply everywhere. The measures keep plain number formats (#,##0 for counts and money, 0.00 for ratings and average nights, 0.0% for shares and rates). The "K" on the cards and the payment donut comes from the visual's label display units, set to Thousands, so 7,752,400 shows as 7,752K.

Page 1: Executive Overview

Visual	Title on the page	Axis	Measure
Card	(none, a caption sits below)	none	`[Total Bookings]`
Card	Realised Revenue (KES)	none	`[Realised Revenue]`
Card	Average Guest Rating / 5	none	`[Average Guest Rating]`
Card	At-Risk Booking Value (KES)	none	`[Lost Revenue]`
Card	Completed Stays	none	`[Completed Stays]`
Line	Monthly Realised Revenue (KES)	`month_label`	`[Realised Revenue]`
Bars	Revenue by Room Type (KES)	`room_type`	`[Realised Revenue]`
Donut	Revenue by Payment Method (KES)	`payment_method`	`[Realised Revenue]`
Bars	Revenue Share by Department	`staff_department`	`[Department Revenue Share]`
Donut	Booked Value by Status	`booking_status`	`[Booking Status Value Share]`

The first block holds the volume and money measures. Two of them, Realised Revenue and Lost Revenue, simply sum the reporting columns that the SQL view built, so the rule sits in the database and DAX stays short.

Total Bookings =
COUNTROWS('tembo_hotel v_clean_bookings')

Completed Stays =
CALCULATE([Total Bookings], 'tembo_hotel v_clean_bookings'[booking_status] = "Checked Out")

Realised Revenue =
SUM('tembo_hotel v_clean_bookings'[realised_revenue])

Lost Revenue =
SUM('tembo_hotel v_clean_bookings'[lost_revenue])

Booked Value =
SUM('tembo_hotel v_clean_bookings'[total_amount])

Average Guest Rating =
CALCULATE(AVERAGE('tembo_hotel v_clean_bookings'[guest_rating]), 'tembo_hotel v_clean_bookings'[booking_status] = "Checked Out")

The two share charts need a different shape. The numerator is the bar's own value. The denominator is the same value with the filter on the bar's own category removed, which gives the total across all categories. DIVIDE returns a blank on a zero denominator and avoids an error.

Department Revenue Share =
DIVIDE(
    [Realised Revenue],
    CALCULATE([Realised Revenue], REMOVEFILTERS('tembo_hotel v_clean_bookings'[staff_department]))
)

Booking Status Value Share =
DIVIDE(
    [Booked Value],
    CALCULATE([Booked Value], REMOVEFILTERS('tembo_hotel v_clean_bookings'[booking_status]))
)

Page 2: Rooms & Guests

Visual	Title on the page	Axis	Measure
Columns	Completed Stays by Room Type	`room_type`	`[Completed Stays]`
Bars	Average Stay Length by Room Type	`room_type`	`[Average Stay Nights]`
Bars	Completed Stays by Guest City	`guest_city`	`[Completed Stays]`
Columns	Guest Rating Distribution	`guest_rating`	`[Rating Distribution Share]`
Bars	Average Rating by Room Type	`room_type`	`[Average Guest Rating]`

Page two reuses Completed Stays and Average Guest Rating from page one and adds three measures of its own. Average Stay Nights is built from a reusable Room Nights measure, so the two can be checked separately. Rated Stays counts the completed stays that carry a rating, and Rating Distribution Share divides each rating's count by the total across all ratings.

Room Nights =
CALCULATE(SUM('tembo_hotel v_clean_bookings'[nights_stayed]), 'tembo_hotel v_clean_bookings'[booking_status] = "Checked Out")

Average Stay Nights =
DIVIDE([Room Nights], [Completed Stays])

Rated Stays =
CALCULATE(COUNT('tembo_hotel v_clean_bookings'[guest_rating]), 'tembo_hotel v_clean_bookings'[booking_status] = "Checked Out")

Rating Distribution Share =
DIVIDE(
    [Rated Stays],
    CALCULATE([Rated Stays], REMOVEFILTERS('tembo_hotel v_clean_bookings'[guest_rating]))
)

COUNT skips blank cells, so Rated Stays counts only the 238 completed stays that have a rating. The unrated ones never produce a bar of their own. The rating chart's format string, 0.0%;-0.0%;0.0%, makes an empty share show as 0.0%.

Page 3: Staff & Risk

Visual	Title on the page	Fields	Measures
Table	Staff Performance	`staff_name`, `staff_department`	`[Staff Booking Rank]`, `[Bookings Handled]`, `[Completed Stays]`, `[Lost Bookings]`, `[Realised Revenue]`
Bars	Realised Revenue by Department (KES)	`staff_department`	`[Realised Revenue]`
Bars	Cancellation and No-Show Rate by Room Type	`room_type`	`[Lost Booking Rate]`
Table	Lost Bookings by Payment Method	`payment_method`	`[Lost Bookings]`, `[Lost Booking Rate]` as Loss Rate, `[Lost Revenue]` as At-Risk Value, `[Staff Handling Count]`

Page three adds the staff and loss measures. COALESCE turns an empty result into a zero, which is why Card, Cash and M-Pesa show 0 in the payment table and not a blank. DIVIDE(..., 0) does the same for the rate. DISTINCTCOUNT counts each person once, so the total row shows 8 even though the four payment methods add up to more.

Bookings Handled =
[Total Bookings]

Lost Bookings =
COALESCE(
    CALCULATE([Total Bookings], 'tembo_hotel v_clean_bookings'[booking_status] IN {"Cancelled", "No Show"}),
    0
)

Lost Booking Rate =
DIVIDE([Lost Bookings], [Total Bookings], 0)

Staff Handling Count =
DISTINCTCOUNT('tembo_hotel v_clean_bookings'[staff_name])

Staff Booking Rank =
RANKX(ALL('tembo_hotel v_clean_bookings'[staff_name]), [Bookings Handled], , DESC, DENSE)

The last measure, Staff Booking Rank, feeds the Rank column in the staff table. It is the one to watch. Section 7 shows what it does wrong and how to fix it.

4. Key insights

Each number below comes from the cleaned data and appears on the dashboard.

The hotel realised 7,752,400 KES from 253 completed stays. That is 253 of 285 bookings, and 86.8% of everything booked by value.
Suites earn the most, Standard rooms fill the most. Suites brought in 2,659,600 KES (34.3%) from 54 stays. Standard rooms had 97 stays and 21.0% of revenue. One Penthouse stay is worth 64,611 KES on average, nearly four times a Standard stay at 16,789 KES.
Revenue stepped up in January 2024, then wobbled. Monthly revenue grew from 78,000 KES in June 2023 to 630,500 KES in January 2024. It peaks in April 2024 at 704,500 KES, drops to 437,700 KES in May (38% below April), and falls to its 2024 low of 402,500 KES in August before recovering. The 2023 months hold only 34 stays between them, against 18 in a typical 2024 month, so the early "growth" may reflect better record-keeping as much as better trade.
The 3.01 average rating hides a split. Of 238 ratings, 39.5% are a 1 or a 2, 43.7% are a 4 or a 5, and 16.8% are a 3. Average rating by room type runs from 2.94 (Deluxe) to 3.17 (Penthouse), too narrow a gap to act on.
Nairobi supplies 111 of the 253 stays, 43.9%. Kisumu (29) and Eldoret (28) follow far behind.
Volume and value rank different people first. Kelvin Omondi handled the most bookings (38). Brenda Achieng brought in the most revenue (1,283,800 KES). By department, Front Desk earns 30.4% of revenue, Restaurant 26.3%, Housekeeping 20.3%, Security 13.8% and Management 9.2%.
All the lost money sits in one payment method. Thirty-two bookings worth 1,175,300 KES were cancelled or never showed, which is 13.2% of booked value and 11.2% of bookings. Every one was paid by bank transfer, so 46.4% of bank-transfer bookings (32 of 69) failed. Cash, card and M-Pesa lost none. By room type, the Penthouse loses 24.0%, Standard 14.9%, Deluxe 7.8% and Suite 3.6%.

The seventh finding needs care. This query rebuilds the payment table from page three, with its last column:

SELECT payment_method,
       COUNT(*) FILTER (WHERE booking_status IN ('Cancelled', 'No Show')) AS lost_bookings,
       ROUND(100.0 * COUNT(*) FILTER (WHERE booking_status IN ('Cancelled', 'No Show'))
             / COUNT(*), 1) AS loss_rate_pct,
       COALESCE(SUM(total_amount) FILTER (WHERE booking_status IN ('Cancelled', 'No Show')), 0) AS at_risk_value,
       COUNT(DISTINCT staff_name) AS staff_handling
FROM tembo_hotel.v_clean_bookings
GROUP BY payment_method
ORDER BY payment_method;

Result grid: 4 rows fetched

payment_method	lost_bookings	loss_rate_pct	at_risk_value	staff_handling
Bank Transfer	32	46.4	1175300.00	2
Card	0	0.0	0	2
Cash	0	0.0	0	3
M-Pesa	0	0.0	0	3

The last column shows how many staff handle each method: two for bank transfer, two for card, three for cash, three for M-Pesa. Staff and payment method travel together in this file, so the data cannot say whether the cause is the payment method or the way two colleagues record cancellations. The dashboard shows the pattern. It does not claim a cause.

5. How I communicated the findings

I followed the Director's own words. His four questions became three pages, and the page names are plain nouns he already uses: Executive Overview, Rooms & Guests, Staff & Risk.

I put the answer before the evidence. The five cards across the top give the scale of the business in five seconds: 285 bookings, 7,752K realised, 3.01 out of 5, 1,175K lost, 253 completed stays. A reader who stops there still leaves with the shape of the hotel.

I let one colour do the work. Every bar is the same teal, so no bar competes with another for attention. The eye goes to length, which is the thing a bar chart exists to show.

I showed the spread beside the average. The rating page puts the full 1-to-5 distribution next to the average. Without it, 3.01 reads as "fine".

I presented the risk finding as a question. The "Staff Handling" column sits inside the lost-bookings table so that the reader sees the confound at the same moment as the pattern. A verdict would have been easier to say and harder to defend.

I checked the numbers against SQL first. Every headline ties to a query, so when someone asks "where does this come from?", the answer is a script and not a shrug.

6. What I learned from presenting the project

Define the word before showing the number. "Revenue" can mean booked, collected or profit. A one-line definition on the page, "realised revenue means checked-out stays only", costs ten seconds and heads off an argument.

Show the exact figure. The cards say 7,752K and 1,175K, which is precise enough to defend. The payment donut's centre says "8M", which rounds away 248,000 KES and contradicts the card beside it. One chart undoes what the other earned.

An average opens a conversation. It does not close one. The 3.01 rating is true and nearly useless until you see the split behind it.

Say what the data cannot tell you. The bank-transfer pattern is the most useful finding in the project, and the most dangerous to oversell. Naming its limit made the finding stronger.

Read your own dashboard like a stranger. Some defects are invisible to the person who built the page, because the builder knows what each label was meant to say. The Rank column, below, is one of them.

Small samples call for plain language. Nineteen months and 285 bookings can show a pattern. They cannot prove one. I try to write "suggests" where I would like to write "shows".

7. What I would do differently

First, what I would keep: the three-page structure, the single bar colour, the rating distribution beside the average, the thousands format on the cards, the honest "Staff Handling" column, and the model's tidy habits: display folders, a description on every measure, and the money rules kept in the database. The rest of this section is the repair list, sorted by how much each item matters.

Fix first: things that are wrong or misleading

The Rank column is wrong. That was the puzzle. On the Staff Performance table, six of eight people are ranked 1. Kelvin Omondi handled 38 bookings, two more than anyone, and four people tied on 36. Ranking the same data two ways in SQL shows the cause:

SELECT staff_name,
       staff_department,
       COUNT(*) AS bookings,
       DENSE_RANK() OVER (ORDER BY COUNT(*) DESC) AS hotel_rank,
       DENSE_RANK() OVER (PARTITION BY staff_department ORDER BY COUNT(*) DESC) AS rank_in_department
FROM tembo_hotel.v_clean_bookings
GROUP BY staff_name, staff_department
ORDER BY hotel_rank, staff_name;

Result grid: 8 rows fetched

staff_name	staff_department	bookings	hotel_rank	rank_in_department
Kelvin Omondi	Front Desk	38	1	1
Brenda Achieng	Front Desk	36	2	2
Fatuma Hassan	Housekeeping	36	2	1
Moses Kipchoge	Restaurant	36	2	1
Tony Karanja	Housekeeping	36	2	1
Amina Juma	Restaurant	35	3	2
Joy Otieno	Management	34	4	1
Peter Ngugi	Security	34	4	1

The dashboard's column matches rank_in_department: Amina 2, Brenda 2, Fatuma 1, Joy 1, Kelvin 1, Moses 1, Peter 1, Tony 1. The Staff Booking Rank measure from section 3 explains why. ALL on staff_name lifts the filter on the name, but the department column in the same table row still filters the calculation. Each person is ranked only against colleagues in their own department, and the total row, with no department filter, ranks 285 against eight smaller numbers and returns 1. The fix is to remove the department filter as well, and to show a rank only on a row that holds one person:

Staff Booking Rank =
IF(
    HASONEVALUE('tembo_hotel v_clean_bookings'[staff_name]),
    RANKX(
        ALL(
            'tembo_hotel v_clean_bookings'[staff_name],
            'tembo_hotel v_clean_bookings'[staff_department]
        ),
        [Bookings Handled], , DESC, DENSE
    )
)

Check the result against the hotel_rank column above, with dense ties: 1, 2, 2, 2, 2, 3, 4, 4. Then sort the table by bookings, highest first. The total row will show a blank rank, which is correct.

One idea has two names, and two rates have none. The card is titled "At-Risk Booking Value" and captioned "Lost Revenue", and the payment table says "At-Risk Value". These bookings are already lost, so "at risk" is wrong, and one name should win. Elsewhere, the status donut implies 13.2% lost (by value) while the staff page shows 11.2% (by count). Both are right. Each needs a label that says "of booked value" or "of bookings".

Two donuts carry filler at the centre. The payment donut shows "8M", which contradicts the 7,752K card. The status donut shows "100.0%", which tells the reader nothing. Use the space for the figure that matters, such as "13.2% lost".

The status donut repeats itself. Its labels read "10.2% (10.2%)", "3.0% (3.0%)" and "86.8% (86.8%)", because the measure Booking Status Value Share is already a share, and the donut adds its own percentage label on top. Show the category name and the value once.

Fix next: things that make the page hard to read

Sort bars by value. Room types, departments, cities and payment methods all appear in alphabetical order, so the eye must hunt for the biggest bar. The brief also asked for the top 10 cities, and the city chart shows ten in alphabetical order, Unknown among them.
Add data labels. Without them, the reader guesses at bar lengths. This matters most on the lost-rate chart, where a reader cannot tell 3.6% from 7.8% by eye.
Rescale the rating chart. Bars that start at zero make 2.94 and 3.17 look identical. Show the share of 1-and-2 ratings against 4-and-5 ratings for each room type, which carries the real story.
Put a title on pages two and three. Only the first page names itself, and each page should state its takeaway in a sentence.
Add slicers. A year, a room type and a status filter would let the Director test a hunch without asking me.
Remove the duplicate. Department revenue appears twice, once as a share on page one and once in KES on page three. Page three also has an empty lower half beside the staff table and under the payment table. A better use of that space: lost bookings by month.
Show the trend honestly. The line shows the May and August dips clearly, which is good. Mark April as the best month and August as the worst of 2024, and shade the thin 2023 months so nobody reads them as growth.

Use the measures already built

Seven finished measures, plus the helper behind one of them, sit in the model and appear on no visual. Each answers a question the brief or the dashboard raises, and each is ready to drop onto a page:

Measure	What it would add	Value in this data
`Revenue Month-over-Month Growth` (with `Previous Month Revenue`)	The month-over-month growth the brief asked for. Put it on a column chart beside the line, with `month_label` on the axis.	May 2024: -37.9%. January 2024: +88.1%
`Revenue Month Rank`	Highlight the best and worst months without hunting along the line	April 2024 ranks 1, January 2024 ranks 2
`Guest City Stay Rank`	The Top 10 filter the brief asked for. Set it as a visual filter: rank less than or equal to 10.	Nairobi ranks 1
`Low Rating Share`	A single number for unhappy guests, to sit beside the 3.01 average	39.5%
`Lost Value Share`	The "13.2% of booked value" figure, labelled as such	13.2%
`Room Type Revenue Share`, `Payment Method Revenue Share`	Percent labels for the room-type bars and payment donut without relying on the visual's own percentage	Suite 34.3%, Cash 29.8%

The growth and city-rank measures are the two I would add first. Here is their code, copied from the model:

Previous Month Revenue =
VAR CurrentMonth = MAX('tembo_hotel v_clean_bookings'[stay_month])
RETURN
    CALCULATE(
        [Realised Revenue],
        REMOVEFILTERS('tembo_hotel v_clean_bookings'[stay_month]),
        REMOVEFILTERS('tembo_hotel v_clean_bookings'[month_label]),
        'tembo_hotel v_clean_bookings'[stay_month] = EDATE(CurrentMonth, -1)
    )

Revenue Month-over-Month Growth =
DIVIDE([Realised Revenue] - [Previous Month Revenue], [Previous Month Revenue])

Guest City Stay Rank =
RANKX(ALL('tembo_hotel v_clean_bookings'[guest_city]), [Completed Stays], , DESC, DENSE)

EDATE steps back one month from the month in view, so the measure works on the text month axis without a separate date table. One caution on the growth measure. In the total row, MAX(stay_month) is the last month, so the grand total would show December's growth. Put it only on a month axis, or wrap it in HASONEVALUE.

Do next time: the process

Commit the view the model depends on. The money rules live in the database view, and that was the right call. The risk is that the repository does not record how those columns are built. Put the full CREATE VIEW statement for v_clean_bookings, with realised_revenue, lost_revenue, month_label and stay_month, in the scripts folder, so a reader can rebuild the model's source from the repository alone.

Keep the reconciliation in the routine. Keep the query from section 1 in the repository and run it each time the data refreshes. If the dashboard and the query disagree, you learn that one of them is wrong before anyone else sees the page.

Guard the project file before you share it. The Power BI project stores the database host and port in plain text, and its local cache holds a copy of the data, guest names and phone numbers included. Keep the cache and local settings out of version control, and replace the host with a parameter.

Add the number a hotelier expects. An occupancy rate, nights sold against nights available, is the first figure most hoteliers look for. The brief's "occupancy" question asks which room types are booked most, and the dashboard answers that. The true rate would be a sixth card.

Test the page on a stranger. Ask someone who has never seen it to find the busiest month, the best room type and the riskiest payment method. Time them. Anything over a minute is a design bug.

What comes next

The repair list is short: fix the rank, name each loss rate, empty the donut centres, sort the bars, add slicers, and put the seven unused measures to work. After that the dashboard is ready for the question it could not yet answer, which is why bank-transfer bookings fail. That answer lives outside the data, in how the hotel takes deposits and records cancellations. The dashboard did its job when it showed where to look.

The data, the SQL and the full cleaning walk-through are in this repository. If you spot a defect I missed, tell me. A rank bug can hide on a finished page, and there may be others.

Python Operators and Conditionals at the Airport Check-in Desk

David Mwandairo — Thu, 01 Oct 2026 13:20:57 +0000

Zuri is flying to Lisbon. Her suitcase is full, the airline allows 23 kg, and she has no idea what the bag weighs. At the desk, an agent will put it on a scale and make a decision in about two seconds: pass it, charge for it, or send her away to repack.

That agent runs a small program in their head. It adds up weights, compares the total to a limit, combines a few yes/no questions, and picks one of three outcomes. Python does the same work with two tools: operators, which calculate and compare, and conditionals, which choose what happens next.

This article builds the agent's program one piece at a time. Each snippet was run on Python 3.12, and each output block shows what the terminal printed.

Arithmetic operators: weighing the bag

Python has seven arithmetic operators. Five behave like the keys on a phone calculator. Two, // and %, do work a calculator hides.

Operator	Name	At the desk
`+`	addition	add up everything in the bag
`-`	subtraction	room left before the limit
`*`	multiplication	six shirts at 350 g each
`/`	true division	grams to kilograms (always gives a decimal)
`//`	floor division	how many whole items still fit
`%`	remainder (modulo)	what is left after that
`**`	power	volume of a cube-shaped packing box

Zuri weighs her things in grams. Whole numbers keep the arithmetic clean, a habit worth copying whenever the quantities allow it:

# Everything in grams, so the numbers stay whole
empty_bag = 3500
shirts = 6
shirt = 350
shoes = 1200
laptop = 2100
limit = 23000

total = empty_bag + shirts * shirt + shoes + laptop
spare = limit - total
more_shirts = spare // shirt
left_over = spare % shirt

print("Packed weight:", total, "g")
print("Spare room:", spare, "g")
print("Extra shirts that fit:", more_shirts)
print("Gap after that:", left_over, "g")
print("Packed weight in kilograms:", total / 1000)

$ python packing.py
Packed weight: 8900 g
Spare room: 14100 g
Extra shirts that fit: 40
Gap after that: 100 g
Packed weight in kilograms: 8.9

The bag weighs 3,500 + 6 × 350 + 1,200 + 2,100 = 8,900 g. That leaves 14,100 g of room. Dividing 14,100 by 350 gives about 40.3, and // keeps only the 40 whole shirts. The % operator gives the part that did not fit: 100 g. The two operators work as a pair. One counts the full units, the other reports the remainder.

The last line uses /, which always returns a decimal number, even when the division comes out even.

Floor division rounds toward negative infinity, not toward zero. Positive numbers hide this, and negative ones show it:

print("7 / 2   =", 7 / 2)
print("7 // 2  =", 7 // 2)
print("7 % 2   =", 7 % 2)
print("2 ** 10 =", 2 ** 10)
print("-7 // 2 =", -7 // 2)
print("-7 % 2  =", -7 % 2)
print("35 ** 3 =", 35 ** 3)

$ python operators.py
7 / 2   = 3.5
7 // 2  = 3
7 % 2   = 1
2 ** 10 = 1024
-7 // 2 = -4
-7 % 2  = 1
35 ** 3 = 42875

The answer to -7 // 2 is -4, not -3. The pair still holds together, because (a // b) * b + a % b always equals a: (-4 × 2) + 1 = -7. The last line is the volume in cubic centimetres of a packing cube 35 cm on each side.

Precedence: multiplication goes first

Python follows school arithmetic. Power goes first, then *, /, // and %, then + and -. A misplaced parenthesis changes the answer without any warning:

empty_bag = 3500
shirts = 6
shirt = 350

wrong = (empty_bag + shirts) * shirt
right = empty_bag + shirts * shirt

print("wrong:", wrong)
print("right:", right)

$ python precedence.py
wrong: 1227100
right: 5600

The wrong line adds the bag weight to the shirt count (3,500 + 6, a meaningless sum) and then multiplies by 350. The right line lets * run first, which is what Zuri meant. When you doubt the order, add parentheses. They cost nothing and show the next reader what you meant.

Comparison operators: asking the scale a question

A comparison asks a yes/no question and answers with True or False. Python has six: ==, !=, <, >, <=, >=.

A single = stores a value. A double == asks whether two values are equal. Confusing the two is the most common typo in the language, and we will watch Python catch it at the end.

Python also lets you chain comparisons the way you would write them on paper:

total = 8900
limit = 23000

print("total <= limit:", total <= limit)
print("total == limit:", total == limit)
print("total != limit:", total != limit)
print("0 < total <= limit:", 0 < total <= limit)
print("0.1 + 0.2 =", 0.1 + 0.2)
print("0.1 + 0.2 == 0.3:", 0.1 + 0.2 == 0.3)
print('"apple" < "banana":', "apple" < "banana")
print('"Banana" < "apple":', "Banana" < "apple")

$ python comparisons.py
total <= limit: True
total == limit: False
total != limit: True
0 < total <= limit: True
0.1 + 0.2 = 0.30000000000000004
0.1 + 0.2 == 0.3: False
"apple" < "banana": True
"Banana" < "apple": True

Line by line:

8900 <= 23000 is True. The bag is under the limit.
8900 == 23000 is False. It is not exactly at the limit.
8900 != 23000 is True. The mirror image of the line above.
0 < total <= limit is True. A chain reads like the inequality from a maths lesson. Python treats it as 0 < total and total <= limit, and it reads total only once.
0.1 + 0.2 prints as 0.30000000000000004. Computers store decimals in binary, and 0.1 has no exact binary form, so a tiny error creeps in.
That error is why 0.1 + 0.2 == 0.3 is False. Avoid testing decimals for exact equality when they came from arithmetic. This is one reason Zuri weighed everything in whole grams.
"apple" < "banana" is True. Text compares letter by letter, so a comes before b.
"Banana" < "apple" is also True, which surprises people. Python compares character codes, and every capital letter has a lower code than every small letter. Convert text to one case before you compare it.

Logical operators: combining questions

One comparison rarely settles a decision. Zuri's carry-on must weigh under 7 kg and fit either under the seat or in the overhead bin. That is three questions joined together.

Python has three logical operators. and is true when both sides are true. or is true when at least one side is. not flips an answer. When they mix, not binds first, then and, then or.

carry_on = 6200
fits_under_seat = False
fits_in_bin = True

light_enough = carry_on <= 7000
fits_somewhere = fits_under_seat or fits_in_bin

print("Light enough:", light_enough)
print("Fits somewhere:", fits_somewhere)
print("Carry-on accepted:", light_enough and fits_somewhere)
print("Not under the seat:", not fits_under_seat)

$ python logic.py
Light enough: True
Fits somewhere: True
Carry-on accepted: True
Not under the seat: True

The carry-on weighs 6,200 g, so light_enough is True. It does not fit under the seat but it does fit in the bin, so or gives True for fits_somewhere. Both halves of the final and are True, so the bag is accepted. The last line shows not turning False into True.

Short-circuit evaluation

Python reads and from left to right and stops at the first False, because nothing on the right can change the result. The same applies to or, which stops at the first True. That makes and a guard:

total_g = 8900
bags = 0

print("Safe check:", bags != 0 and total_g / bags > 20000)
print("Unsafe check:", total_g / bags > 20000)

$ python short_circuit.py
Safe check: False
Traceback (most recent call last):
  File "/home/you/short_circuit.py", line 5, in <module>
    print("Unsafe check:", total_g / bags > 20000)
                           ~~~~~~~~^~~~~~
ZeroDivisionError: division by zero

Suppose a bag count of zero slips in. In the safe check, bags != 0 is False, so Python never evaluates the division on the right, and the program prints False. In the unsafe check, Python tries to divide by zero and stops with a ZeroDivisionError. Put the cheap, protective test first, and let the risky one follow.

`and` and `or` return a value, not only True or False

First, a word on what "truthy" means. Python does not need a True or False to make a decision. In a condition, every value counts as one or the other. A value that counts as False is called falsy. A value that counts as True is called truthy. The falsy values are few: 0, 0.0, the empty string "", and False itself. Everything else is truthy, including negative numbers and any string with at least one character in it.

bags = 0
if bags:
    print("Bags counted:", bags)
else:
    print("No bags to check")

name = "Zuri"
if name:
    print("Hello,", name)
else:
    print("No name given")

change = -5
if change:
    print("-5 is truthy")

$ python truthy.py
No bags to check
Hello, Zuri
-5 is truthy

A bag count of 0 is falsy, so if bags: reads as "if there are any bags" and the else branch runs. A name with text in it is truthy. The surprise is -5: it is not zero, so it is truthy. Among numbers, only zero is falsy.

Now back to and and or. They hand back one of their operands, and they use truthiness to choose which. or gives back the first truthy value, or the last value if none is truthy. and gives back the first falsy value, or the last value if all are truthy. This gives a neat way to supply a default:

name = ""
print(name or "Passenger")

name = "Zuri"
print(name or "Passenger")

$ python default_name.py
Passenger
Zuri

The empty name is falsy, so or moves on to "Passenger". The name "Zuri" is truthy, so or stops there and returns it.

The bug nearly every beginner writes once

Zuri wants to know what to pack. She writes what she would say aloud: "if the city is Lisbon or Porto, take a light jacket." She is going to Oslo:

city = "Oslo"

if city == "Lisbon" or "Porto":
    print("Pack a light jacket")
else:
    print("Pack a warm coat")

if "Porto":
    print("A non-empty string counts as True")

if city == "Lisbon" or city == "Porto":
    print("Pack a light jacket")
else:
    print("Pack a warm coat")

$ python jacket.py
Pack a light jacket
A non-empty string counts as True
Pack a warm coat

Oslo is cold, and the first check still says to pack a light jacket. Python reads city == "Lisbon" or "Porto" as (city == "Lisbon") or ("Porto"). The left side is False, so or moves on to "Porto", a non-empty string, which is truthy. The condition passes for every city on Earth. The second if proves the point: a bare non-empty string passes as True. The fix is the third block, where each side of the or makes its own full comparison.

if, elif, else: choosing an outcome

A conditional statement tells Python which block of code to run. Python tests the conditions from top to bottom. It runs the first block whose condition is true, skips the rest, and moves on. The else block runs only if nothing above it matched. Exactly one block runs. Indentation marks which lines belong to which branch.

Here is the agent's decision. Up to 23 kg is free, 23 to 32 kg costs $12 for each extra kilo, and anything heavier is refused. Zuri's brother Baraka has a 26 kg bag:

weight_kg = 26

if weight_kg <= 23:
    print("Free")
elif weight_kg <= 32:
    extra = weight_kg - 23
    print("Extra kilos:", extra)
    print("Fee in dollars:", extra * 12)
else:
    print("Refused: repack")

$ python check_bag.py
Extra kilos: 3
Fee in dollars: 36

Trace it. Is 26 at most 23? No. Is it at most 32? Yes, so Python works out 26 - 23 = 3 extra kilos, prints the fee of 3 × 12 = 36, and never looks at else. Change the first line and run the script again to see the other branches:

# weight_kg = 18
$ python check_bag.py
Free

# weight_kg = 23
$ python check_bag.py
Free

# weight_kg = 32
$ python check_bag.py
Extra kilos: 9
Fee in dollars: 108

# weight_kg = 35
$ python check_bag.py
Refused: repack

Note the boundaries. A bag of exactly 23 kg is free, and one of exactly 32 kg still pays, because both tests use <=. Whether the limit itself counts as in or out is a decision, and the operator you choose makes it.

The second test does not say "more than 23 and at most 32". It can say less, because by the time Python reaches it, the first test has already failed. The order of the tests carries information. Swap two of them and the program changes meaning:

weight_kg = 26

if weight_kg <= 32:
    extra = weight_kg - 23
    print("Extra kilos:", extra)
    print("Fee in dollars:", extra * 12)
elif weight_kg <= 23:
    print("Free")
else:
    print("Refused: repack")

# weight_kg = 26
$ python broken_check.py
Extra kilos: 3
Fee in dollars: 36

# weight_kg = 18
$ python broken_check.py
Extra kilos: -5
Fee in dollars: -60

The 26 kg bag still gets the right answer, which is how this bug survives testing. The 18 kg bag does not: it is charged minus $60. The test weight_kg <= 32 is true for every light bag, so the chain stops there and the free case never gets its turn.

The rule: when you test with < or <=, start with the smallest limit. When you test with > or >=, start with the largest.

elif is not the same as a second if

A chain of elif picks one winner. A row of separate if statements checks each condition on its own, so more than one block can run. Both forms are correct. They answer different questions:

weight_kg = 30

print("Two separate if statements:")
if weight_kg > 23:
    print("  over the free limit")
if weight_kg > 25:
    print("  over the warning line")

print("One if/elif chain:")
if weight_kg > 25:
    print("  over the warning line")
elif weight_kg > 23:
    print("  over the free limit")

$ python if_vs_elif.py
Two separate if statements:
  over the free limit
  over the warning line
One if/elif chain:
  over the warning line

A 30 kg bag is over both lines. The separate if statements report both. The chain stops at the first match and reports one. Use separate if statements when the cases can overlap and you want every match. Use elif when the cases compete and only one should apply.

Putting it together: the check-in desk

Now the full program for one passenger. Frequent flyers get 5 extra kilos on the checked bag. Each passenger has a checked bag and a carry-on, and the desk reports the fee and whether the passenger can move on to security. It uses all three kinds of operator and every form of if.

name = "Zuri"
checked_kg = 18
carry_on_kg = 6
is_member = False

free_kg = 23
max_kg = 32
fee_per_kg = 12
carry_on_max_kg = 7
member_bonus_kg = 5

allowed_kg = free_kg
if is_member:
    allowed_kg = free_kg + member_bonus_kg

extra_kg = checked_kg - allowed_kg
refused = checked_kg > max_kg
carry_ok = carry_on_kg <= carry_on_max_kg

print("Passenger:", name or "Unnamed")
print("Checked bag allowance:", allowed_kg, "kg")

if refused:
    print("Checked bag: refused, repack")
elif extra_kg > 0:
    print("Checked bag: extra", extra_kg, "kg, fee in dollars:", extra_kg * fee_per_kg)
else:
    print("Checked bag: free")

if carry_ok:
    print("Carry-on: OK")
else:
    print("Carry-on: too heavy by", carry_on_kg - carry_on_max_kg, "kg")

if not refused and carry_ok:
    print("Result: go to security")
else:
    print("Result: fix the problems above first")

$ python checkin.py
Passenger: Zuri
Checked bag allowance: 23 kg
Checked bag: free
Carry-on: OK
Result: go to security

Zuri's bag is under the limit and her carry-on is under 7 kg, so she goes through. To see the other branches, change the first four lines and run the script again. Baraka has a 26 kg bag and no membership:

# name = "Baraka", checked_kg = 26, carry_on_kg = 5
$ python checkin.py
Passenger: Baraka
Checked bag allowance: 23 kg
Checked bag: extra 3 kg, fee in dollars: 36
Carry-on: OK
Result: go to security

Baraka pays for three extra kilos, and a fee is a cost, not a failure, so he still goes to security. Next comes Imani, a member with the same 26 kg bag but an 8 kg carry-on. Last is a passenger who left the name blank and brought a 35 kg bag:

# name = "Imani", checked_kg = 26, carry_on_kg = 8, is_member = True
$ python checkin.py
Passenger: Imani
Checked bag allowance: 28 kg
Checked bag: free
Carry-on: too heavy by 1 kg
Result: fix the problems above first

# name = "", checked_kg = 35, carry_on_kg = 4
$ python checkin.py
Passenger: Unnamed
Checked bag allowance: 23 kg
Checked bag: refused, repack
Carry-on: OK
Result: fix the problems above first

Imani's allowance is 28 kg, so her 26 kg bag is free, but her carry-on is one kilo over, so she must fix that first. The blank name falls back to "Unnamed" through or. At 35 kg, the bag is over the 32 kg maximum and gets refused.

Each tool did its own job. Arithmetic (+, -, *) produced the extra kilos and the fee. Comparison (<=, >) turned numbers into yes/no answers. Logic (not, and, or) combined those answers: not refused and carry_ok is the whole test for "go to security". The if/elif/else blocks turned the answers into actions.

What Python says when you slip

I promised to show what happens when = replaces ==:

weight_kg = 23
if weight_kg = 23:
    print("exactly at the limit")

$ python typo.py
  File "/home/you/typo.py", line 2
    if weight_kg = 23:
       ^^^^^^^^^^^^^^
SyntaxError: invalid syntax. Maybe you meant '==' or ':=' instead of '='?

Python refuses to run the file and points at the exact expression. Recent versions even suggest the fix. The harder mistakes run without complaint: an or that is always true, a chain in the wrong order, a decimal compared with ==. No error message flags them. You find them by testing the edge cases, which is what the exercises below ask you to do.

Try it yourself

Copy checkin.py and change it.

Set checked_kg to exactly 23 and predict what the program prints, then run it. Now change one operator so that 23 kg costs a fee, and check that nothing else breaks.
Add a rule: a carry-on counts as too heavy above 7 kg, unless the passenger is a member, who may carry 10 kg. Combine and and or in one condition, and add parentheses to show your intent.
Set checked_kg to 32 and carry_on_kg to 7. Which messages do you expect? Run it and check whether the order of your if/elif chain gives them.

If you build something with these tools, such as a parking meter, a bus fare calculator or a school canteen till, tell me in the comments what decision it makes. And tell me which of the bugs above you have already written yourself.

Python Basics in the Matatu Context

David Mwandairo — Tue, 29 Sep 2026 19:51:18 +0000

Watch a matatu conductor work for one trip and you will see a programmer at full speed. He counts heads. He knows the fare. He takes a KSh 500 note from someone who owes KSh 70 and hands back the exact change without a pause. He also decides, by feel, when the vehicle is full enough to leave.

Listen to what he tracks: a number plate, a seat count, a fuel bill and a yes or no on whether the vehicle is full. That is text, a whole number, a decimal number and a true-or-false answer. Python cares about that difference, and once you see why, the basics stop looking like rules to memorise.

This article covers variables, four data types (string, integer, float and boolean), input and output, and the syntax that binds them together. Every example follows one fictional conductor working for the Mwananchi Express sacco. The fares, fuel prices and plate number are invented, so change them to match real life. Each code block shows the output you will see in your terminal.

Variables: names, not boxes

Most tutorials say a variable is a box that holds a value. The picture fails quickly, so use this one. A variable is a sticky note with a name on it. Python sticks the note onto a value. You can peel the note off and stick it on something else, and you can put two notes on one value.

plate = "KDD 482P"
seats = 14
fare_kes = 100
is_full = True

print(plate, seats, fare_kes, is_full)

$ python matatu.py
KDD 482P 14 100 True

The = sign does not mean "equals" the way it does in maths. It means "stick this name on that value." Python works out the right side first, then attaches the name on the left.

That order explains a line that would be nonsense in algebra. Say the sacco raises the fare by KSh 20:

fare_kes = fare_kes + 20
print(fare_kes)

Python takes the current value of fare_kes (100), adds 20, and moves the sticky note onto the result. Nothing was changed in place. The note moved.

Here is the two-notes idea at work:

x = 14
y = x        # y sits on the same value as x
x = 13       # x moves to a new value; y stays put
print(x, y)

13 14

Changing x did not touch y, because y never pointed at x. It pointed at the value 14.

Naming rules

A name can hold letters, digits and underscores. It cannot start with a digit, and it cannot be a word Python already uses, such as if or True. Names are case sensitive, so Seats and seats are two different notes.

Seats = 14
print(seats)

Traceback (most recent call last):
  File "matatu.py", line 2, in <module>
    print(seats)
          ^^^^^
NameError: name 'seats' is not defined. Did you mean: 'Seats'?

Read the error from the bottom. Python names the problem (NameError), names the culprit (seats), and guesses what you meant. Most Python errors help you this much once you learn to read them.

The community habit is snake_case: lowercase words joined by underscores. Put the unit in the name (fare_kes, fuel_litres) and you prevent a whole class of mistakes later. Values that should never change get capital letters, like SEATS. Python will let you change them anyway. The capitals are a note to the next reader.

The four data types

Every value has a type, and the type decides what you can do with it. Ask Python with type():

print(type(plate))
print(type(seats))
print(type(38.5))
print(type(is_full))

<class 'str'>
<class 'int'>
<class 'float'>
<class 'bool'>

Strings (`str`)

A string is text inside quotes. Single or double quotes both work. Pick one style and keep it.

sacco = "Mwananchi Express"

print(len(sacco))
print(sacco.upper())
print(sacco + " Sacco")
print(sacco[0])
print(sacco[:9])
print("Karibu! " * 2)

17
MWANANCHI EXPRESS
Mwananchi Express Sacco
M
Mwananchi
Karibu! Karibu!

len() counts characters, spaces included. The + sign glues strings together. Counting starts at zero, so sacco[0] is the first letter, and sacco[:9] means "everything before position 9." Multiplying a string by a number repeats it.

Strings suit phone numbers well. A Safaricom number is text, not a number, because nobody adds two phone numbers together and the leading zero matters. Here is how to turn a local number into the international form that M-Pesa integrations expect:

local_number = "0712345678"

print("+254" + local_number[1:])
print(len(local_number))
print(local_number.startswith("07"))

+254712345678
10
True

The slice [1:] means "everything from position 1 onward," which drops the leading zero. The startswith() method asks a question about the text, and the answer comes back as a boolean. We will meet booleans shortly.

A string that looks like a number is still a string. "14" and 14 are different things, and Python refuses to mix them:

print("Seats: " + 14)

Traceback (most recent call last):
  File "matatu.py", line 1, in <module>
    print("Seats: " + 14)
          ~~~~~~~~~~^~~~
TypeError: can only concatenate str (not "int") to str

You have to convert the number first. We cover that in a moment.

Integers (`int`)

An integer is a whole number. Python has three division-related operators, and beginners mix them up:

riders = 47

print(riders // 14)
print(riders % 14)
print(2 ** 10)
print(7 / 2)

The // operator divides and drops the remainder. A 14-seater carrying 47 riders in a day needs 3 full loads. The % operator keeps only the remainder: 5 riders are left for a fourth, partly empty trip. Together they answer "how many full loads, and what is left?", a question that turns up all the time. The ** operator raises a number to a power. The single / always returns a float, even when the answer is whole:

print(type(8 / 2), 8 / 2)

<class 'float'> 4.0

Floats (`float`)

A float is a number with a decimal point. Fuel is measured in litres and priced to the cent, so floats fit. Mixing an integer with a float gives a float:

print(3 * 1.5)

4.5

Floats have one quirk that startles everyone once:

print(0.1 + 0.2)
print(round(0.1 + 0.2, 2))

0.30000000000000004
0.3

Computers store numbers in binary. One tenth has no exact binary form, just as one third has no exact decimal form, so the machine stores a very close neighbour. The error is tiny, and it appears when you print raw results. Two habits protect you. Use round() or a format specifier when you display a float, and never test two floats for exact equality.

Booleans (`bool`)

A boolean is True or False, with a capital first letter. You will seldom type them yourself. Comparisons produce them:

boarded = 14
is_full = boarded >= seats
traffic_heavy = False

print(is_full)
print(is_full and not traffic_heavy)

True
True

The keywords and, or and not combine booleans. The expression is_full and not traffic_heavy asks: is the vehicle full, and is the road clear? Both hold, so the answer is True. Set traffic_heavy to True and the answer flips to False.

Converting between types

Python will not guess when you mix types, so you convert on purpose with int(), float(), str() and bool():

print(int("120") + 30)
print(float("38.5") * 2)
print(str(14) + " seats")
print(int(9.99))

150
77.0
14 seats
9

Notice that int(9.99) gives 9. Converting to an integer cuts off the decimals. It does not round.

Converting to a boolean has a trap:

print(bool(0), bool(1), bool(""), bool("no"))
print(bool("False"))

False True False True
True

bool() asks one question: is there anything here? Zero and the empty string count as nothing, so they become False. Everything else becomes True, including the text "False", which is a non-empty string. If you read the word "false" from a user, compare it with the text yourself. Do not pass it to bool().

Input and output

Python talks through print() and listens through input().

Output with `print()`

print() takes several values and puts a space between them. Two options change that: sep sets the separator, and end sets what closes the line (a newline by default).

print("Nairobi", "Thika", "Kenol", sep=" > ")
print("Counting heads...", end=" ")
print("done")

Nairobi > Thika > Kenol
Counting heads... done

For anything longer, use an f-string. Put an f before the opening quote and write expressions inside curly braces. After a colon you control the format:

fuel_cost = 5.5 * 190
print(f"{sacco} spent KSh {fuel_cost:.2f} on fuel")
print(f"KSh {1234567.891:,.2f}")

Mwananchi Express spent KSh 1045.00 on fuel
KSh 1,234,567.89

The .2f means "two digits after the decimal point," and the comma adds thousands separators. F-strings also fix the "Seats: " + 14 problem, because they convert values to text for you.

Input with `input()`

input() shows a prompt, waits for the user to type and press Enter, then hands you what they typed. In the transcripts below, the text after each prompt is what the user typed.

name = input("Conductor name: ")
print("Karibu,", name)

Conductor name: Maxwell
Karibu, Maxwell

Remember this rule: input() always returns a string. If the user types 3, you receive the text "3".

passengers = input("Passengers: ")
print(passengers + 1)

Passengers: 3
Traceback (most recent call last):
  File "matatu.py", line 2, in <module>
    print(passengers + 1)
          ~~~~~~~~~~~^~~
TypeError: can only concatenate str (not "int") to str

The fix is to convert the moment the value arrives: passengers = int(input("Passengers: ")). If the user types something that is not a number, int() raises a ValueError. Handling that needs try and except, which sit outside this article. For now, know that it can happen.

Syntax rules that hold it together

Python's syntax is small. Five rules cover most of what you will write this month.

Indentation is part of the language. Where other languages use braces, Python uses the four spaces at the start of a line to show what belongs inside an if. Leave them out and Python stops you:

seats = 14
if seats > 10:
print("big")

  File "matatu.py", line 3
    print("big")
    ^
IndentationError: expected an indented block after 'if' statement on line 2

A colon ends the line that opens a block (if, elif, else). Comments start with #, and Python ignores them, so use them to explain why you did something. A line break ends a statement, so you need no semicolons. And names are case sensitive, as you saw with Seats.

The if statement is the one piece of flow control we need. It runs a block only when a boolean is True. The second and third projects below use it.

Practical example 1: the conductor's change counter

A passenger owes KSh 70 and hands over a KSh 500 note. The conductor must return the right change, fast, and sometimes the passenger has paid too little. This program uses integers, division, a boolean and an if statement.

# change_counter.py
fare_kes = int(input("Fare (KSh): "))
paid_kes = int(input("Passenger paid (KSh): "))

enough = paid_kes >= fare_kes

if enough:
    change = paid_kes - fare_kes
    hundreds = change // 100
    small_change = change % 100
    print(f"Change due: KSh {change}")
    print(f"Give back: {hundreds} x KSh 100, plus KSh {small_change} in smaller notes or coins")
else:
    print(f"Short by KSh {fare_kes - paid_kes}. Ask for the rest.")

Two runs, one where the passenger overpays and one where they come up short:

$ python change_counter.py
Fare (KSh): 70
Passenger paid (KSh): 500
Change due: KSh 430
Give back: 4 x KSh 100, plus KSh 30 in smaller notes or coins

$ python change_counter.py
Fare (KSh): 70
Passenger paid (KSh): 50
Short by KSh 20. Ask for the rest.

Follow the arithmetic: 500 minus 70 is 430. Then 430 // 100 is 4, the number of hundred-shilling notes, and 430 % 100 is 30, what remains. The same // and % pair that counted full loads now counts notes.

Practical example 2: the trip profit calculator

At the end of a trip, the owner wants to know whether the trip paid for itself. Income is the passenger count times the fare. Costs are the fuel and a flat sacco fee. This program mixes integers and floats and ends on a boolean.

# trip_profit.py
FARE_KES = 100
SACCO_FEE_KES = 150

passengers = int(input("Passengers on this trip: "))
fuel_litres = float(input("Fuel used (litres): "))
price_per_litre = float(input("Fuel price (KSh per litre): "))

income = passengers * FARE_KES
fuel_cost = fuel_litres * price_per_litre
profit = income - fuel_cost - SACCO_FEE_KES
made_profit = profit > 0

print()
print(f"Income:    KSh {income:,.2f}")
print(f"Fuel:      KSh {fuel_cost:,.2f}")
print(f"Sacco fee: KSh {SACCO_FEE_KES:,.2f}")
print(f"Profit:    KSh {profit:,.2f}")
print("Trip paid for itself?", made_profit)

A full vehicle on one run, and a half-empty one on another:

$ python trip_profit.py
Passengers on this trip: 14
Fuel used (litres): 5.5
Fuel price (KSh per litre): 190

Income:    KSh 1,400.00
Fuel:      KSh 1,045.00
Sacco fee: KSh 150.00
Profit:    KSh 205.00
Trip paid for itself? True

$ python trip_profit.py
Passengers on this trip: 9
Fuel used (litres): 5.5
Fuel price (KSh per litre): 190

Income:    KSh 900.00
Fuel:      KSh 1,045.00
Sacco fee: KSh 150.00
Profit:    KSh -295.00
Trip paid for itself? False

The numbers show why conductors hate leaving half empty. Five fewer passengers turn a KSh 205 profit into a KSh 295 loss. Look at the types: passengers is an integer, fuel_litres is a float, and multiplying the integer by the integer constant gives an integer, while any product with a float gives a float. The :,.2f format makes every amount line up with two decimals and a thousands separator.

Practical example 3: the departure call

The hardest decision on the route is when to go. Leave too early and you lose fares. Wait too long and passengers get off. This program takes the number of people on board and the minutes spent waiting, turns them into three booleans, and decides.

# departure.py
SEATS = 14

boarded = int(input("Passengers on board: "))
minutes_waiting = int(input("Minutes waiting: "))

is_full = boarded >= SEATS
waited_long = minutes_waiting >= 20
half_full = boarded >= SEATS / 2

if is_full:
    print("Decision: full. Close the door and go.")
elif waited_long and half_full:
    print("Decision: enough people and enough waiting. Leave now.")
elif waited_long:
    print("Decision: long wait, few riders. Keep calling, but tell them the delay.")
else:
    print("Decision: keep calling for passengers.")

Four situations, four decisions:

$ python departure.py
Passengers on board: 14
Minutes waiting: 5
Decision: full. Close the door and go.

$ python departure.py
Passengers on board: 9
Minutes waiting: 25
Decision: enough people and enough waiting. Leave now.

$ python departure.py
Passengers on board: 4
Minutes waiting: 30
Decision: long wait, few riders. Keep calling, but tell them the delay.

$ python departure.py
Passengers on board: 6
Minutes waiting: 8
Decision: keep calling for passengers.

Python checks the if and elif lines from top to bottom and runs only the first block whose condition is true. That is why the full vehicle leaves even after five minutes: is_full is checked first. Notice that SEATS / 2 is a float (7.0), and comparing an integer with a float works without any conversion.

Try it yourself

You now have every piece for tools like these. Pick one and bend it:

Extend the change counter to hand back KSh 50 notes as well as KSh 100 notes, using another // and % step.
Change the profit calculator to ask for the fare, so it works for a route that charges KSh 60 or KSh 150.
Add a fourth rule to the departure call: if the road is blocked, tell the passengers and stop counting.

Then pick a small problem from your own week. Splitting a shopping bill, converting shillings to dollars and tracking your data bundle all follow the same steps: ask for input, convert it, calculate, and print the answer. Each program you finish teaches you more than another hour of reading.

Cleaning and Analyzing Tembo Hotel's Bookings

David Mwandairo — Tue, 29 Sep 2026 12:32:52 +0000

One guest at Tembo Hotel & Suites checked out three days before checking in. The spreadsheet says so. It also spells Nairobi as Nairobi and as NAIROBI, files a town called Thikax under guest cities, and writes January dates as 10/01/2024, 01-12-2024 and 28-01-24, three formats in one column.

That spreadsheet is the whole booking history of a mid-range business hotel in Nairobi, open since 2023. The Hotel Director wants to know which rooms earn the most, which months are busiest, how the staff are doing and whether guests are happy. Nobody can answer those questions from a file like this. Any total you add up is wrong by an amount you can't measure.

This article follows the project from the raw CSV to the finished answers. I load the file into PostgreSQL, fix the damage one defect at a time, and then run the six analyses the Director asked for. I ran every query below, in order, against the real file on PostgreSQL 16. The output under each query is what came back, laid out the way DBeaver shows it: a result grid for SELECT, a statistics line for everything else.

The brief in plain words

The Director's message boils down to four worries: money, busy periods, staff and guest happiness. The brief turns them into six groups of questions:

Revenue by month, by room type and by payment method.
Which room types are booked most, and how many nights a guest stays on average.
The top 10 cities guests come from, and the average rating per room type.
Which staff member handled the most bookings, and which department earns the most.
Month-over-month revenue growth (with a window function), and the busiest and quietest months.
The cancellation rate per room type, and the revenue lost to cancellations and no-shows.

The file is tembo_hotel_dirty.csv: 286 rows and 20 columns, one row per booking. Each row carries the guest, the room, the dates, the staff member who handled it, how the guest paid, any extra service (spa, laundry, airport pickup) and the guest's rating.

Note: To gain access to the files used in this project, check out this Github repository.

The plan: register first, get a room second

A guest doesn't walk straight to a room. They stop at the front desk, where staff check the details, and only then get a key. The database work follows the same order:

tembo_hotel_dirty.csv
        |
        v
staging_bookings      every column is TEXT, so nothing is rejected on arrival
        |   fix each defect with UPDATE / DELETE
        v
clean_bookings        real types and CHECK constraints, so bad data can't get in
        |
        v
v_clean_bookings      a view that adds a month column for the analysis
        |
        v
six business questions

The staging table exists because a strict table would refuse the file at the first KES 34000 in a numeric column. With everything as text, the load always succeeds, and the cleaning happens where I can watch it, one column at a time.

Step 1: Ingest the CSV without losing a row

I keep the project in its own schema so it never collides with anything else in the database.

CREATE SCHEMA IF NOT EXISTS tembo;
SET search_path TO tembo;

DROP TABLE IF EXISTS tembo.staging_bookings;
CREATE TABLE tembo.staging_bookings (
    booking_id          TEXT,
    guest_name          TEXT,
    guest_phone         TEXT,
    guest_city          TEXT,
    guest_nationality   TEXT,
    room_no             TEXT,
    room_type           TEXT,
    room_rate_per_night TEXT,
    check_in_date       TEXT,
    check_out_date      TEXT,
    nights_stayed       TEXT,
    staff_name          TEXT,
    staff_department    TEXT,
    staff_salary        TEXT,
    payment_method      TEXT,
    booking_status      TEXT,
    total_amount        TEXT,
    service_used        TEXT,
    service_price       TEXT,
    guest_rating        TEXT
);

Statistics tab: Updated Rows = 0

In DBeaver you can right-click the table and choose Import Data, then point it at the CSV. If you prefer a script, PostgreSQL's COPY does the same job in one line:

COPY tembo.staging_bookings
FROM '/path/to/tembo_hotel_dirty.csv'
WITH (FORMAT csv, HEADER true);

Statistics tab: Updated Rows = 286

COPY reads the file on the database server, so the path must be visible to the server and your role needs the right privileges. From a client machine, use \copy in psql or the DBeaver wizard.

Next, count the rows and look at the first few. This is the sanity check I run after every load.

SELECT COUNT(*) AS rows_loaded
FROM tembo.staging_bookings;

Result grid: 1 row fetched

rows_loaded
286

SELECT booking_id, guest_name, guest_phone, guest_city, room_type,
       check_in_date, total_amount
FROM tembo.staging_bookings
LIMIT 8;

Result grid: 8 rows fetched

booking_id	guest_name	guest_phone	guest_city	room_type	check_in_date	total_amount
BK0001	ALICE MWANGI	0712345678	Nairobi	Standard	2024-01-05	6000
BK0002	brian otieno	0723456789	Mombasa	Standard	2024-01-06	5500
BK0003	Carol Wanjiku	0734567890	Kisumu	Deluxe	2024-01-07	25500
BK0004	David Kamau	0745-678-901	Nairobi	Deluxe	2024-01-08	18200
BK0005	Esther Njoroge	+254756789012	Nakuru	Suite	2024-01-09	60000
BK0006	Felix Hassan	0767890123	Nairobi	Standard	10/01/2024	27500
BK0007	Grace Achieng	0778901234	Eldoret	Deluxe	01-12-2024	43000
BK0008	Henry Korir	0789012345	[NULL]	Suite	2024-01-13	30000

286 rows, as promised. Look at the grid, though. Row 3 has stray spaces around a name, row 4 has dashes in a phone number, row 5 has a +254 prefix instead of a leading zero, row 7 has a date in month-day-year order, and row 8 has an empty city. We are just getting started.

Step 2: Audit the damage before touching anything

I don't fix by feel. One query counts every kind of damage I can test for, so I know the size of the job and can prove afterwards that it's done. Each line is a rule the clean data must obey, tested against the raw text.

SELECT 'Duplicate booking_id rows' AS problem,
       COUNT(*) - COUNT(DISTINCT booking_id) AS rows_affected
FROM tembo.staging_bookings
UNION ALL SELECT 'Names with stray spaces or odd casing', COUNT(*)
  FROM tembo.staging_bookings WHERE guest_name <> INITCAP(TRIM(guest_name))
UNION ALL SELECT 'Phones with dashes, +254 or padding', COUNT(*)
  FROM tembo.staging_bookings WHERE guest_phone !~ '^0[0-9]{9}$'
UNION ALL SELECT 'Cities blank, misspelt or oddly cased', COUNT(*)
  FROM tembo.staging_bookings
  WHERE guest_city IS NULL OR guest_city !~ '^[A-Z][a-z]+$' OR guest_city = 'Thikax'
UNION ALL SELECT 'Nationality casing', COUNT(*)
  FROM tembo.staging_bookings WHERE guest_nationality <> 'Kenyan'
UNION ALL SELECT 'Room type aliases (Std, DLX, lower case)', COUNT(*)
  FROM tembo.staging_bookings WHERE room_type NOT IN ('Standard','Deluxe','Suite','Penthouse')
UNION ALL SELECT 'Payment method aliases', COUNT(*)
  FROM tembo.staging_bookings WHERE payment_method NOT IN ('Cash','Card','Bank Transfer','M-Pesa')
UNION ALL SELECT 'Booking status casing', COUNT(*)
  FROM tembo.staging_bookings WHERE booking_status NOT IN ('Checked Out','Cancelled','No Show')
UNION ALL SELECT 'Dates not in YYYY-MM-DD', COUNT(*)
  FROM tembo.staging_bookings
  WHERE check_in_date !~ '^\d{4}-\d{2}-\d{2}$' OR check_out_date !~ '^\d{4}-\d{2}-\d{2}$'
UNION ALL SELECT 'Check-out before check-in', COUNT(*)
  FROM tembo.staging_bookings
  WHERE check_in_date ~ '^\d{4}-\d{2}-\d{2}$' AND check_out_date ~ '^\d{4}-\d{2}-\d{2}$'
    AND check_out_date < check_in_date
UNION ALL SELECT 'Nights stayed below 1', COUNT(*)
  FROM tembo.staging_bookings WHERE nights_stayed::INT < 1
UNION ALL SELECT 'Staff salary unreadable', COUNT(*)
  FROM tembo.staging_bookings WHERE staff_salary !~ '^\d+$'
UNION ALL SELECT 'Total amount blank or not a plain number', COUNT(*)
  FROM tembo.staging_bookings WHERE total_amount IS NULL OR total_amount !~ '^\d+$'
UNION ALL SELECT 'Rating padded or outside 1 to 5', COUNT(*)
  FROM tembo.staging_bookings WHERE guest_rating !~ '^[1-5]$';

Result grid: 14 rows fetched

problem	rows_affected
Duplicate booking_id rows	1
Names with stray spaces or odd casing	45
Phones with dashes, +254 or padding	29
Cities blank, misspelt or oddly cased	45
Nationality casing	1
Room type aliases (Std, DLX, lower case)	29
Payment method aliases	15
Booking status casing	15
Dates not in YYYY-MM-DD	44
Check-out before check-in	2
Nights stayed below 1	1
Staff salary unreadable	14
Total amount blank or not a plain number	30
Rating padded or outside 1 to 5	17

Fourteen kinds of damage. (Missing phone numbers and ratings are not on the list, because a value that was never recorded is a gap, not a defect.) Some are cosmetic (casing), some silently change an answer (a duplicate booking counts twice), and some make an answer impossible (a total that is just a comma). The !~ operator means "does not match this regular expression", which is the fastest way to find everything that breaks a rule I can describe.

Step 3: Fix the data, one defect at a time

Every fix follows the same rhythm. Look at the problem, write the UPDATE, then run a check that proves it worked. I keep the WHERE clause on each update so the "Updated Rows" count tells me how many rows I really touched. An update with no WHERE clause always reports 285, which teaches you nothing.

3.1 Duplicate bookings

SELECT booking_id, guest_name, check_in_date, total_amount, COUNT(*) AS copies
FROM tembo.staging_bookings
GROUP BY booking_id, guest_name, check_in_date, total_amount
HAVING COUNT(*) > 1;

Result grid: 1 row fetched

booking_id	guest_name	check_in_date	total_amount	copies
BK0006	Felix Hassan	10/01/2024	27500	2

Booking BK0006 appears twice, identical in every column. Revenue counted twice is the classic quiet error. In PostgreSQL every row has a hidden physical address called ctid. I keep the lowest address for each booking_id and delete the rest.

DELETE FROM tembo.staging_bookings
WHERE ctid NOT IN (
    SELECT MIN(ctid)
    FROM tembo.staging_bookings
    GROUP BY booking_id
);

Statistics tab: Updated Rows = 1

SELECT COUNT(*) AS rows_left, COUNT(DISTINCT booking_id) AS unique_ids
FROM tembo.staging_bookings;

Result grid: 1 row fetched

rows_left	unique_ids
285	285

285 rows and 285 unique IDs. (MIN(ctid) needs PostgreSQL 14 or newer. On older versions, use ROW_NUMBER() in a CTE.)

3.2 Guest names

The LENGTH column exposes what the eye can't see: trailing spaces.

SELECT guest_name, LENGTH(guest_name) AS chars, COUNT(*) AS rows_affected
FROM tembo.staging_bookings
WHERE guest_name <> INITCAP(TRIM(guest_name))
GROUP BY guest_name
ORDER BY rows_affected DESC;

Result grid: 5 rows fetched

guest_name	chars	rows_affected
ALICE MWANGI	12	15
brian otieno	12	14
Carol Wanjiku	17	14
grace wanjiru	13	1
PETER MWANGI	12	1

INITCAP capitalises each word and lower-cases the rest. TRIM strips the spaces.

UPDATE tembo.staging_bookings
SET guest_name = INITCAP(TRIM(guest_name))
WHERE guest_name <> INITCAP(TRIM(guest_name));

Statistics tab: Updated Rows = 45

The check is the same query as before. It should return nothing.

SELECT guest_name, LENGTH(guest_name) AS chars, COUNT(*) AS rows_affected
FROM tembo.staging_bookings
WHERE guest_name <> INITCAP(TRIM(guest_name))
GROUP BY guest_name;

Result grid: 0 rows fetched

guest_name	chars	rows_affected

3.3 Phone numbers

QUOTE_LITERAL wraps each value in quotes, so blanks and padding become visible.

SELECT QUOTE_LITERAL(guest_phone) AS phone_as_typed, COUNT(*) AS rows_affected
FROM tembo.staging_bookings
WHERE guest_phone !~ '^0[0-9]{9}$'
GROUP BY guest_phone
ORDER BY rows_affected DESC;

Result grid: 3 rows fetched

phone_as_typed	rows_affected
'+254756789012'	14
'0745-678-901'	14
' 0700000004 '	1

Three shapes: dashes, a +254 prefix and padding. The 14 blank phones don't appear, because COPY loads an empty CSV field as NULL. Some import tools load an empty string instead, so the CASE below turns those into NULL as well. The target is the local ten-digit form, 07xxxxxxxx: strip everything that isn't a digit, and turn a leading 254 into 0.

UPDATE tembo.staging_bookings
SET guest_phone = CASE
    WHEN NULLIF(TRIM(guest_phone), '') IS NULL THEN NULL
    WHEN TRIM(guest_phone) LIKE '+254%'
        THEN '0' || SUBSTRING(REGEXP_REPLACE(guest_phone, '[^0-9]', '', 'g') FROM 4)
    ELSE REGEXP_REPLACE(guest_phone, '[^0-9]', '', 'g')
END
WHERE guest_phone !~ '^0[0-9]{9}$';

Statistics tab: Updated Rows = 29

SELECT COUNT(*) FILTER (WHERE guest_phone IS NULL) AS missing_phones,
       COUNT(*) FILTER (WHERE guest_phone !~ '^0[0-9]{9}$') AS malformed_phones
FROM tembo.staging_bookings;

Result grid: 1 row fetched

missing_phones	malformed_phones
14	0

3.4 Cities

SELECT QUOTE_LITERAL(guest_city) AS city_as_typed, COUNT(*) AS bookings
FROM tembo.staging_bookings
GROUP BY guest_city
ORDER BY bookings DESC;

Result grid: 12 rows fetched

city_as_typed	bookings
'Nairobi'	114
'Eldoret'	28
'Kisumu'	27
'Mombasa'	15
'NAIROBI'	15
'Nakuru'	14
'Machakos'	14
'Nyeri'	14
'Meru'	14
[NULL]	14
'Thikax'	14
'kisumu'	2

Nairobi has three spellings. Thikax is a typo for Thika, which is the only reading that fits: it appears exactly as often as the other small towns and no such place exists. That is an assumption, and I say so here so a reader can overrule it. Blank cities become Unknown rather than staying NULL, so the label is visible in every report and the Director can see how much is missing.

UPDATE tembo.staging_bookings
SET guest_city = CASE
    WHEN NULLIF(TRIM(guest_city), '') IS NULL THEN 'Unknown'
    WHEN LOWER(TRIM(guest_city)) = 'thikax' THEN 'Thika'
    ELSE INITCAP(TRIM(guest_city))
END
WHERE guest_city IS NULL OR guest_city !~ '^[A-Z][a-z]+$' OR guest_city = 'Thikax';

Statistics tab: Updated Rows = 45

SELECT guest_city, COUNT(*) AS bookings
FROM tembo.staging_bookings
GROUP BY guest_city
ORDER BY bookings DESC;

Result grid: 10 rows fetched

guest_city	bookings
Nairobi	129
Kisumu	29
Eldoret	28
Mombasa	15
Meru	14
Unknown	14
Nakuru	14
Thika	14
Machakos	14
Nyeri	14

3.5 Short vocabularies: room type, payment, status, nationality

These four columns hold a handful of allowed values each, so one pattern fits all: map every known variant to one spelling. The room types hide the most, since Std and DLX are abbreviations that no casing function can repair.

UPDATE tembo.staging_bookings
SET room_type = CASE
    WHEN UPPER(TRIM(room_type)) IN ('STANDARD', 'STD') THEN 'Standard'
    WHEN UPPER(TRIM(room_type)) IN ('DELUXE', 'DLX')   THEN 'Deluxe'
    WHEN UPPER(TRIM(room_type)) = 'SUITE'              THEN 'Suite'
    WHEN UPPER(TRIM(room_type)) = 'PENTHOUSE'          THEN 'Penthouse'
    ELSE room_type
END
WHERE room_type NOT IN ('Standard', 'Deluxe', 'Suite', 'Penthouse');

Statistics tab: Updated Rows = 29

UPDATE tembo.staging_bookings
SET payment_method = 'M-Pesa'
WHERE UPPER(TRIM(payment_method)) IN ('MPESA', 'M PESA')
  AND payment_method <> 'M-Pesa';

Statistics tab: Updated Rows = 15

UPDATE tembo.staging_bookings
SET booking_status = INITCAP(TRIM(booking_status))
WHERE booking_status <> INITCAP(TRIM(booking_status));

Statistics tab: Updated Rows = 15

UPDATE tembo.staging_bookings
SET guest_nationality = INITCAP(TRIM(guest_nationality))
WHERE guest_nationality <> INITCAP(TRIM(guest_nationality));

Statistics tab: Updated Rows = 1

One query verifies all four columns at once:

SELECT 'room_type' AS column_name, room_type AS value, COUNT(*) AS bookings
FROM tembo.staging_bookings GROUP BY room_type
UNION ALL
SELECT 'payment_method', payment_method, COUNT(*)
FROM tembo.staging_bookings GROUP BY payment_method
UNION ALL
SELECT 'booking_status', booking_status, COUNT(*)
FROM tembo.staging_bookings GROUP BY booking_status
UNION ALL
SELECT 'guest_nationality', guest_nationality, COUNT(*)
FROM tembo.staging_bookings GROUP BY guest_nationality
ORDER BY column_name, bookings DESC;

Result grid: 12 rows fetched

column_name	value	bookings
booking_status	Checked Out	253
booking_status	Cancelled	23
booking_status	No Show	9
guest_nationality	Kenyan	285
payment_method	M-Pesa	74
payment_method	Cash	72
payment_method	Card	70
payment_method	Bank Transfer	69
room_type	Standard	114
room_type	Deluxe	90
room_type	Suite	56
room_type	Penthouse	25

3.6 Dates, the hard one

Dates need the most care, because a wrong guess is invisible. 05/03/2024 is either 5 March or 3 May, and nothing in the text says which. First, sort the check-in dates by shape:

SELECT CASE
           WHEN check_in_date ~ '^\d{4}-\d{2}-\d{2}$' THEN 'YYYY-MM-DD'
           WHEN check_in_date ~ '^\d{2}/\d{2}/\d{4}$' THEN 'NN/NN/YYYY'
           WHEN check_in_date ~ '^\d{2}-\d{2}-\d{2}$' THEN 'NN-NN-YY'
           WHEN check_in_date ~ '^\d{2}-\d{2}-\d{4}$' THEN 'NN-NN-YYYY'
       END AS date_shape,
       COUNT(*) AS bookings,
       MIN(check_in_date) AS example
FROM tembo.staging_bookings
GROUP BY 1
ORDER BY bookings DESC;

Result grid: 4 rows fetched

date_shape	bookings	example
YYYY-MM-DD	242	2023-06-11
NN-NN-YYYY	15	01-12-2024
NN/NN/YYYY	15	05/03/2024
NN-NN-YY	13	05-10-24

Four shapes. My rules for the three odd ones:

NN/NN/YYYY is day first, as is usual in Kenya.
NN-NN-YY is day first with a two-digit year.
NN-NN-YYYY is month first (01-12-2024 for 12 January), unless the first number is above 12, in which case it must be day first.

Each of those rules is a guess. So I use the nights_stayed column as a witness: after parsing, the gap between check-in and check-out must equal the nights on record. If a rule is wrong, the check will show it. A small function keeps the rules in one place:

CREATE OR REPLACE FUNCTION tembo.fix_date(d TEXT) RETURNS DATE AS $$
    SELECT CASE
        WHEN d ~ '^\d{4}-\d{2}-\d{2}$' THEN d::DATE
        WHEN d ~ '^\d{2}/\d{2}/\d{4}$' THEN TO_DATE(d, 'DD/MM/YYYY')
        WHEN d ~ '^\d{2}-\d{2}-\d{2}$' THEN TO_DATE(d, 'DD-MM-YY')
        WHEN d ~ '^\d{2}-\d{2}-\d{4}$' AND SPLIT_PART(d, '-', 1)::INT > 12
            THEN TO_DATE(d, 'DD-MM-YYYY')
        WHEN d ~ '^\d{2}-\d{2}-\d{4}$' THEN TO_DATE(d, 'MM-DD-YYYY')
    END
$$ LANGUAGE sql IMMUTABLE;

Statistics tab: Updated Rows = 0

A trial run on one booking of each shape, before anything changes:

SELECT booking_id,
       check_in_date  AS raw_in,
       check_out_date AS raw_out,
       tembo.fix_date(check_in_date)  AS parsed_in,
       tembo.fix_date(check_out_date) AS parsed_out,
       nights_stayed
FROM tembo.staging_bookings
WHERE booking_id IN ('BK0003', 'BK0006', 'BK0007', 'BK0020', 'BK9005')
ORDER BY booking_id;

Result grid: 5 rows fetched

booking_id	raw_in	raw_out	parsed_in	parsed_out	nights_stayed
BK0003	2024-01-07	2024-01-10	2024-01-07	2024-01-10	3
BK0006	10/01/2024	15/01/2024	2024-01-10	2024-01-15	5
BK0007	01-12-2024	01-17-2024	2024-01-12	2024-01-17	5
BK0020	28-01-24	29-01-24	2024-01-28	2024-01-29	1
BK9005	15-11-2024	17-11-2024	2024-11-15	2024-11-17	2

Each parsed gap matches its nights_stayed. Now apply the function to both date columns:

UPDATE tembo.staging_bookings
SET check_in_date  = tembo.fix_date(check_in_date)::TEXT,
    check_out_date = tembo.fix_date(check_out_date)::TEXT
WHERE check_in_date  !~ '^\d{4}-\d{2}-\d{2}$'
   OR check_out_date !~ '^\d{4}-\d{2}-\d{2}$';

Statistics tab: Updated Rows = 43

Now the time traveller. Two bookings check out before they check in. In both, the gap between the two dates equals the recorded nights (or its absolute value), so the two dates were entered in the wrong boxes. Swap them. In a single UPDATE, PostgreSQL evaluates every right-hand side against the old row, so the swap needs no temporary column.

UPDATE tembo.staging_bookings
SET check_in_date  = check_out_date,
    check_out_date = check_in_date
WHERE check_out_date::DATE < check_in_date::DATE;

Statistics tab: Updated Rows = 2

One booking still carries -3 nights. The dates now say three nights, so the dates win, and the rule is written into SQL: recompute nights wherever they disagree with the calendar.

UPDATE tembo.staging_bookings
SET nights_stayed = (check_out_date::DATE - check_in_date::DATE)::TEXT
WHERE nights_stayed::INT <> (check_out_date::DATE - check_in_date::DATE);

Statistics tab: Updated Rows = 1

The proof: every one of the 285 bookings must agree with the calendar.

SELECT COUNT(*) AS bookings,
       COUNT(*) FILTER (
           WHERE check_out_date::DATE - check_in_date::DATE = nights_stayed::INT
       ) AS dates_agree_with_nights,
       MIN(check_in_date) AS first_check_in,
       MAX(check_in_date) AS last_check_in
FROM tembo.staging_bookings;

Result grid: 1 row fetched

bookings	dates_agree_with_nights	first_check_in	last_check_in
285	285	2023-06-10	2024-12-31

All 285 agree, so both guessed rules survived the test. Without that test, I would be trusting my own reading of an ambiguous string.

3.7 Staff salary

SELECT staff_name, staff_salary, COUNT(*) AS rows_affected
FROM tembo.staging_bookings
GROUP BY staff_name, staff_salary
ORDER BY staff_name, staff_salary;

Result grid: 11 rows fetched

staff_name	staff_salary	rows_affected
Amina Juma	65000	35
Brenda Achieng	42000	36
Fatuma Hassan	48000	36
Joy Otieno	120000	34
Kelvin Omondi	42000	38
Moses Kipchoge	35000	35
Moses Kipchoge	KES ,	1
Peter Ngugi	30000	28
Peter Ngugi	KES ,	6
Tony Karanja	32000	29
Tony Karanja	KES ,	7

Three staff members have some rows with KES , in place of a salary. Each person has one true salary elsewhere in the file, so the value can be recovered. First blank the junk, then copy the known salary from the same person's other rows.

UPDATE tembo.staging_bookings
SET staff_salary = NULL
WHERE staff_salary !~ '^\d+$';

Statistics tab: Updated Rows = 14

UPDATE tembo.staging_bookings s
SET staff_salary = k.salary
FROM (
    SELECT staff_name, MAX(staff_salary) AS salary
    FROM tembo.staging_bookings
    WHERE staff_salary IS NOT NULL
    GROUP BY staff_name
) k
WHERE s.staff_name = k.staff_name
  AND s.staff_salary IS NULL;

Statistics tab: Updated Rows = 14

SELECT staff_name, staff_department, staff_salary, COUNT(*) AS bookings
FROM tembo.staging_bookings
GROUP BY staff_name, staff_department, staff_salary
ORDER BY staff_name;

Result grid: 8 rows fetched

staff_name	staff_department	staff_salary	bookings
Amina Juma	Restaurant	65000	35
Brenda Achieng	Front Desk	42000	36
Fatuma Hassan	Housekeeping	48000	36
Joy Otieno	Management	120000	34
Kelvin Omondi	Front Desk	42000	38
Moses Kipchoge	Restaurant	35000	36
Peter Ngugi	Security	30000	34
Tony Karanja	Housekeeping	32000	36

Eight staff, eight salaries, one row each.

3.8 Money: totals and service prices

SELECT booking_id, room_rate_per_night AS rate, nights_stayed AS nights,
       service_price, QUOTE_LITERAL(total_amount) AS total_as_typed
FROM tembo.staging_bookings
WHERE total_amount !~ '^\d+$'
ORDER BY booking_id
LIMIT 8;

Result grid: 8 rows fetched

booking_id	rate	nights	service_price	total_as_typed
BK0014	8500	4	[NULL]	'KES 34000'
BK0015	15000	3	[NULL]	','
BK0034	8500	4	15000	'KES 49000'
BK0035	15000	2	[NULL]	','
BK0054	8500	4	[NULL]	'KES 34000'
BK0055	15000	4	15000	','
BK0074	5500	2	[NULL]	'KES 11000'
BK0075	8500	4	[NULL]	','

Some totals carry a KES prefix, some use a thousands comma (42,500), and some are a lone comma or empty. The first two only need stripping. The last group has lost the figure, but the figure can be rebuilt, because the file follows a rule: total = rate per night x nights + service price. Check BK0001 in the raw data: 5,500 x 1 + 500 = 6,000, and that is what the row says.

Blank service fields first, so a missing service is NULL and never an empty string. This reports 0 rows here, because COPY had already loaded the blanks as NULL. With an import tool that loads empty strings, it would catch them. Then strip the symbols, then rebuild what is still missing.

UPDATE tembo.staging_bookings
SET service_used  = NULLIF(TRIM(service_used), ''),
    service_price = NULLIF(TRIM(service_price), '')
WHERE service_used = '' OR service_price = '';

Statistics tab: Updated Rows = 0

UPDATE tembo.staging_bookings
SET total_amount = NULLIF(REGEXP_REPLACE(total_amount, '[^0-9]', '', 'g'), '')
WHERE total_amount !~ '^\d+$';

Statistics tab: Updated Rows = 29

UPDATE tembo.staging_bookings
SET total_amount = (room_rate_per_night::INT * nights_stayed::INT
                    + COALESCE(service_price::INT, 0))::TEXT
WHERE total_amount IS NULL;

Statistics tab: Updated Rows = 11

Now the audit. How many of the 285 totals obey the rule?

SELECT COUNT(*) AS bookings,
       COUNT(*) FILTER (
           WHERE total_amount::INT = room_rate_per_night::INT * nights_stayed::INT
                                     + COALESCE(service_price::INT, 0)
       ) AS totals_that_add_up
FROM tembo.staging_bookings;

Result grid: 1 row fetched

bookings	totals_that_add_up
285	283

283 of 285. The two that fail deserve a look, not a silent fix.

SELECT booking_id, service_used, service_price, total_amount,
       room_rate_per_night::INT * nights_stayed::INT
           + COALESCE(service_price::INT, 0) AS rule_says
FROM tembo.staging_bookings
WHERE total_amount::INT <> room_rate_per_night::INT * nights_stayed::INT
                           + COALESCE(service_price::INT, 0);

Result grid: 2 rows fetched

booking_id	service_used	service_price	total_amount	rule_says
BK9007	Breakfast Buffet	1200	25500	26700
BK9004	Laundry	800	25500	26300

In both rows the stored total equals the room charge alone, with the service left out. The service may have been billed separately, or someone forgot to add it. I can't tell from the file, so I leave the stored figures alone and list the two bookings as a question for the hotel. Together they come to 2,000 KES.

3.9 Ratings

SELECT QUOTE_LITERAL(guest_rating) AS rating_as_typed, COUNT(*) AS bookings
FROM tembo.staging_bookings
GROUP BY guest_rating
ORDER BY guest_rating;

Result grid: 10 rows fetched

rating_as_typed	bookings
' 3'	1
' 4'	2
'0'	6
'1'	60
'2'	45
'3'	45
'4'	65
'5'	52
'6'	8
[NULL]	1

The scale is 1 to 5. The file also holds 0, 6, padded values like ' 4', and blanks. A 0 or a 6 is impossible, so it becomes NULL: an honest gap is better than an invented score. First strip the padding and turn blanks into NULL.

UPDATE tembo.staging_bookings
SET guest_rating = NULLIF(TRIM(guest_rating), '')
WHERE guest_rating <> TRIM(guest_rating) OR guest_rating = '';

Statistics tab: Updated Rows = 3

UPDATE tembo.staging_bookings
SET guest_rating = NULL
WHERE guest_rating::INT NOT BETWEEN 1 AND 5;

Statistics tab: Updated Rows = 14

SELECT guest_rating, COUNT(*) AS bookings
FROM tembo.staging_bookings
GROUP BY guest_rating
ORDER BY guest_rating;

Result grid: 6 rows fetched

guest_rating	bookings
1	60
2	45
3	46
4	67
5	52
[NULL]	15

Step 4: Move the clean rows into a table that says no

The staging table is tidy now, but it's still all text, and text will happily accept banana as a date. The real table uses proper types and CHECK constraints. If a bad row ever reaches it, the database itself refuses the row.

DROP TABLE IF EXISTS tembo.clean_bookings CASCADE;

CREATE TABLE tembo.clean_bookings (
    booking_id          VARCHAR(10) PRIMARY KEY,
    guest_name          VARCHAR(100),
    guest_phone         VARCHAR(10),
    guest_city          VARCHAR(60),
    guest_nationality   VARCHAR(30),
    room_no             VARCHAR(5),
    room_type           VARCHAR(20),
    room_rate_per_night NUMERIC(10,2),
    check_in_date       DATE,
    check_out_date      DATE,
    nights_stayed       INTEGER CHECK (nights_stayed > 0),
    staff_name          VARCHAR(100),
    staff_department    VARCHAR(30),
    staff_salary        NUMERIC(10,2),
    payment_method      VARCHAR(20),
    booking_status      VARCHAR(20),
    total_amount        NUMERIC(10,2),
    service_used        VARCHAR(50),
    service_price       NUMERIC(10,2),
    guest_rating        INTEGER CHECK (guest_rating BETWEEN 1 AND 5),
    CHECK (check_out_date > check_in_date)
);

Statistics tab: Updated Rows = 0

INSERT INTO tembo.clean_bookings
SELECT booking_id, guest_name, guest_phone, guest_city, guest_nationality,
       room_no, room_type, room_rate_per_night::NUMERIC,
       check_in_date::DATE, check_out_date::DATE, nights_stayed::INT,
       staff_name, staff_department, staff_salary::NUMERIC,
       payment_method, booking_status, total_amount::NUMERIC,
       service_used, service_price::NUMERIC, guest_rating::INT
FROM tembo.staging_bookings;

Statistics tab: Updated Rows = 285

All 285 rows went through without a single complaint, which is the real test of the cleaning. To see what the constraints protect against, try to insert a rating of 6:

INSERT INTO tembo.clean_bookings
    (booking_id, guest_name, room_type, check_in_date, check_out_date, nights_stayed, guest_rating)
VALUES
    ('BK9999', 'Test Guest', 'Suite', '2025-01-10', '2025-01-12', 2, 6);

Error: SQL Error [23514]: ERROR: new row for relation "clean_bookings" violates check constraint "clean_bookings_guest_rating_check"

Last, a view. Every analysis below groups by month, so the view adds a stay_month column once instead of repeating DATE_TRUNC in every query. I assign each stay to the month of check-in.

CREATE OR REPLACE VIEW tembo.v_clean_bookings AS
SELECT b.*,
       DATE_TRUNC('month', check_in_date)::DATE AS stay_month
FROM tembo.clean_bookings b;

Statistics tab: Updated Rows = 0

SELECT COUNT(*) AS bookings,
       MIN(check_in_date) AS first_check_in,
       MAX(check_in_date) AS last_check_in,
       COUNT(DISTINCT room_no) AS rooms,
       COUNT(*) FILTER (WHERE guest_rating IS NULL) AS unrated
FROM tembo.v_clean_bookings;

Result grid: 1 row fetched

bookings	first_check_in	last_check_in	rooms	unrated
285	2023-06-10	2024-12-31	10	15

The file covers June 2023 to December 2024, ten rooms, and 285 bookings. Before the first business question, one definition matters. Only bookings with status Checked Out earned money. Cancelled and No Show rows still carry a total_amount, but it's a price the hotel never collected. So revenue queries filter on Checked Out, and the cancellation question (6) is the one place the other two statuses take centre stage.

Step 5: Answer the Director's questions

Q1. Where does the money come from?

By month:

SELECT TO_CHAR(stay_month, 'YYYY-MM') AS month,
       COUNT(*)          AS stays,
       SUM(total_amount) AS revenue
FROM tembo.v_clean_bookings
WHERE booking_status = 'Checked Out'
GROUP BY stay_month
ORDER BY stay_month;

Result grid: 19 rows fetched

month	stays	revenue
2023-06	3	78000.00
2023-07	6	182300.00
2023-08	3	110000.00
2023-09	5	152200.00
2023-10	3	92000.00
2023-11	5	142300.00
2023-12	9	335200.00
2024-01	18	630500.00
2024-02	19	608000.00
2024-03	19	614000.00
2024-04	18	704500.00
2024-05	15	437700.00
2024-06	19	476000.00
2024-07	17	528200.00
2024-08	17	402500.00
2024-09	18	509000.00
2024-10	21	583000.00
2024-11	19	576500.00
2024-12	19	590500.00

By room type, with each type's share of the total. SUM(SUM(...)) OVER () is a window function wrapped around an aggregate. It divides each group's revenue by the grand total without a second query.

SELECT room_type,
       COUNT(*)          AS stays,
       SUM(total_amount) AS revenue,
       ROUND(100.0 * SUM(total_amount) / SUM(SUM(total_amount)) OVER (), 1) AS revenue_share_pct
FROM tembo.v_clean_bookings
WHERE booking_status = 'Checked Out'
GROUP BY room_type
ORDER BY revenue DESC;

Result grid: 4 rows fetched

room_type	stays	revenue	revenue_share_pct
Suite	54	2659600.00	34.3
Deluxe	83	2236700.00	28.9
Standard	97	1628500.00	21.0
Penthouse	19	1227600.00	15.8

By payment method, with the same share calculation:

SELECT payment_method,
       COUNT(*)          AS stays,
       SUM(total_amount) AS revenue,
       ROUND(100.0 * SUM(total_amount) / SUM(SUM(total_amount)) OVER (), 1) AS revenue_share_pct
FROM tembo.v_clean_bookings
WHERE booking_status = 'Checked Out'
GROUP BY payment_method
ORDER BY revenue DESC;

Result grid: 4 rows fetched

payment_method	stays	revenue	revenue_share_pct
Cash	72	2309500.00	29.8
M-Pesa	74	2108700.00	27.2
Card	70	2043000.00	26.4
Bank Transfer	37	1291200.00	16.7

Suites earn the most, 34.3% of revenue from only 54 stays, while Standard rooms take the most bookings and bring in 21%. The Penthouse has the fewest stays (19) and still takes a 15.8% share. On payment, cash leads at 29.8%, with M-Pesa and cards close behind. Bank transfers are the rarest method (37 stays), but the guests who use them spend more per stay than the rest.

Q2. Which rooms are booked most, and for how long?

SELECT room_type,
       COUNT(*)                    AS stays,
       SUM(nights_stayed)          AS room_nights,
       ROUND(AVG(nights_stayed), 2) AS avg_nights
FROM tembo.v_clean_bookings
WHERE booking_status = 'Checked Out'
GROUP BY room_type
ORDER BY stays DESC;

Result grid: 4 rows fetched

room_type	stays	room_nights	avg_nights
Standard	97	279	2.88
Deluxe	83	252	3.04
Suite	54	172	3.19
Penthouse	19	48	2.53

Standard is the most booked, with 97 stays. Longer stays go the other way: Suite guests stay 3.19 nights on average against 2.88 for Standard. The Penthouse has the shortest stays (2.53 nights), which fits a room people book for an occasion.

Q3. Who are our guests, and are they happy?

The top 10 cities:

SELECT guest_city,
       COUNT(*)          AS stays,
       SUM(total_amount) AS revenue
FROM tembo.v_clean_bookings
WHERE booking_status = 'Checked Out'
GROUP BY guest_city
ORDER BY stays DESC, revenue DESC
LIMIT 10;

Result grid: 10 rows fetched

guest_city	stays	revenue
Nairobi	111	3248100.00
Kisumu	29	737100.00
Eldoret	28	983700.00
Mombasa	15	303800.00
Meru	14	574300.00
Nakuru	14	555000.00
Thika	14	373200.00
Unknown	10	474000.00
Nyeri	9	336500.00
Machakos	9	166700.00

Nairobi supplies 111 of the 253 stays, about 44%, and Eldoret earns more per stay than Kisumu. The file only names nine real cities, so the tenth row is Unknown, which is the honest name for the blank cells. It is worth telling the front desk to stop leaving that field empty.

Average rating per room type:

SELECT room_type,
       ROUND(AVG(guest_rating), 2) AS avg_rating,
       COUNT(guest_rating)         AS ratings_counted
FROM tembo.v_clean_bookings
WHERE booking_status = 'Checked Out'
GROUP BY room_type
ORDER BY avg_rating DESC;

Result grid: 4 rows fetched

room_type	avg_rating	ratings_counted
Penthouse	3.17	18
Standard	3.07	91
Suite	2.96	47
Deluxe	2.94	82

I count ratings only from guests who actually stayed. Nobody can rate a night they never spent, yet the raw file holds ratings on cancelled bookings. COUNT(guest_rating) skips NULLs, so the cleaned blanks don't drag the average down.

Every room type sits between 2.94 and 3.17 out of 5. That is a mediocre score, and an average can hide the shape behind it. One more query shows the spread:

SELECT guest_rating,
       COUNT(*) AS stays,
       ROUND(100.0 * COUNT(*) / SUM(COUNT(*)) OVER (), 1) AS share_pct
FROM tembo.v_clean_bookings
WHERE booking_status = 'Checked Out'
  AND guest_rating IS NOT NULL
GROUP BY guest_rating
ORDER BY guest_rating;

Result grid: 5 rows fetched

guest_rating	stays	share_pct
1	54	22.7
2	40	16.8
3	40	16.8
4	58	24.4
5	46	19.3

Guests split. Of the 238 ratings, 39.5% are a 1 or a 2 and 43.7% are a 4 or a 5, while only 16.8% picked the middle. One guest in five gave the lowest score. A hotel of quietly satisfied guests would score 3 out of 5 as well, so the average alone would never have told us this.

Q4. How is the staff performing?

A CTE gathers each person's numbers first, then RANK() orders them. COUNT(*) FILTER (WHERE ...) counts only the rows that meet a condition, which replaces the older SUM(CASE WHEN ...) trick.

WITH staff_stats AS (
    SELECT staff_name,
           staff_department,
           COUNT(*) AS bookings_handled,
           COUNT(*) FILTER (WHERE booking_status = 'Checked Out') AS completed_stays,
           COUNT(*) FILTER (WHERE booking_status IN ('Cancelled', 'No Show')) AS lost_bookings,
           SUM(total_amount) FILTER (WHERE booking_status = 'Checked Out') AS revenue
    FROM tembo.v_clean_bookings
    GROUP BY staff_name, staff_department
)
SELECT RANK() OVER (ORDER BY bookings_handled DESC) AS rank_by_bookings,
       staff_name,
       staff_department,
       bookings_handled,
       completed_stays,
       lost_bookings,
       revenue
FROM staff_stats
ORDER BY rank_by_bookings, revenue DESC;

Result grid: 8 rows fetched

rank_by_bookings	staff_name	staff_department	bookings_handled	completed_stays	lost_bookings	revenue
1	Kelvin Omondi	Front Desk	38	38	0	1073700.00
2	Brenda Achieng	Front Desk	36	36	0	1283800.00
2	Moses Kipchoge	Restaurant	36	36	0	1035000.00
2	Tony Karanja	Housekeeping	36	36	0	972700.00
2	Fatuma Hassan	Housekeeping	36	19	17	601200.00
6	Amina Juma	Restaurant	35	35	0	1000200.00
7	Peter Ngugi	Security	34	34	0	1070300.00
7	Joy Otieno	Management	34	19	15	715500.00

Kelvin Omondi handled the most bookings (38), but Brenda Achieng brought in the most revenue (1,283,800 KES) from fewer bookings. Volume and value are different rankings, and a bonus scheme should know which one it rewards.

The last two columns hold the surprise. Every one of the 32 cancellations and no-shows belongs to two people, Fatuma Hassan and Joy Otieno. The other six staff members have none. That's a pattern in the data, not a verdict on anyone, and Q6 shows that the staff name is only half of it. It's the first thing I would raise with the Director.

Which department earns the most?

SELECT staff_department,
       COUNT(*)          AS stays,
       SUM(total_amount) AS revenue,
       ROUND(100.0 * SUM(total_amount) / SUM(SUM(total_amount)) OVER (), 1) AS revenue_share_pct
FROM tembo.v_clean_bookings
WHERE booking_status = 'Checked Out'
GROUP BY staff_department
ORDER BY revenue DESC;

Result grid: 5 rows fetched

staff_department	stays	revenue	revenue_share_pct
Front Desk	74	2357500.00	30.4
Restaurant	71	2035200.00	26.3
Housekeeping	55	1573900.00	20.3
Security	34	1070300.00	13.8
Management	19	715500.00	9.2

Front Desk leads with 30.4%, which surprises nobody. Restaurant is second, and Security (a department with no obvious booking role) still records 13.8% of revenue. That's another oddity worth a question: the data suggests bookings are entered by people outside reception.

Q5. What is the trend?

The growth query compares each month with the one before. LAG() reaches back one row in the window, so no self-join is needed. A second window function, RANK(), orders the months from best to worst.

WITH monthly_revenue AS (
    SELECT stay_month,
           COUNT(*)          AS stays,
           SUM(total_amount) AS revenue
    FROM tembo.v_clean_bookings
    WHERE booking_status = 'Checked Out'
    GROUP BY stay_month
),
monthly_growth AS (
    SELECT stay_month, stays, revenue,
           LAG(revenue) OVER (ORDER BY stay_month) AS prev_revenue
    FROM monthly_revenue
)
SELECT TO_CHAR(stay_month, 'YYYY-MM') AS month,
       stays,
       revenue,
       prev_revenue,
       ROUND((revenue - prev_revenue) * 100.0 / prev_revenue, 2) AS growth_pct,
       RANK() OVER (ORDER BY revenue DESC) AS revenue_rank
FROM monthly_growth
ORDER BY stay_month;

Result grid: 19 rows fetched

month	stays	revenue	prev_revenue	growth_pct	revenue_rank
2023-06	3	78000.00	[NULL]	[NULL]	19
2023-07	6	182300.00	78000.00	133.72	14
2023-08	3	110000.00	182300.00	-39.66	17
2023-09	5	152200.00	110000.00	38.36	15
2023-10	3	92000.00	152200.00	-39.55	18
2023-11	5	142300.00	92000.00	54.67	16
2023-12	9	335200.00	142300.00	135.56	13
2024-01	18	630500.00	335200.00	88.10	2
2024-02	19	608000.00	630500.00	-3.57	4
2024-03	19	614000.00	608000.00	0.99	3
2024-04	18	704500.00	614000.00	14.74	1
2024-05	15	437700.00	704500.00	-37.87	11
2024-06	19	476000.00	437700.00	8.75	10
2024-07	17	528200.00	476000.00	10.97	8
2024-08	17	402500.00	528200.00	-23.80	12
2024-09	18	509000.00	402500.00	26.46	9
2024-10	21	583000.00	509000.00	14.54	6
2024-11	19	576500.00	583000.00	-1.11	7
2024-12	19	590500.00	576500.00	2.43	5

The first row has no previous month, so its prev_revenue and growth_pct are NULL. That's correct, and it's why the dividing expression never fails.

The 2023 months look wild (an 88% jump into January 2024, two months down by almost 40%), but the cause is thin data. Only 34 stays fall in the seven months of 2023 against roughly 18 in a typical month of 2024. Growth between such small numbers mostly measures noise. For busiest and quietest, I compare 2024 only, the one complete year:

(SELECT 'Busiest' AS label,
        TO_CHAR(stay_month, 'YYYY-MM') AS month,
        COUNT(*) AS stays,
        SUM(total_amount) AS revenue
 FROM tembo.v_clean_bookings
 WHERE booking_status = 'Checked Out' AND stay_month >= '2024-01-01'
 GROUP BY stay_month
 ORDER BY revenue DESC
 LIMIT 3)
UNION ALL
(SELECT 'Quietest',
        TO_CHAR(stay_month, 'YYYY-MM'),
        COUNT(*),
        SUM(total_amount)
 FROM tembo.v_clean_bookings
 WHERE booking_status = 'Checked Out' AND stay_month >= '2024-01-01'
 GROUP BY stay_month
 ORDER BY SUM(total_amount)
 LIMIT 3);

Result grid: 6 rows fetched

label	month	stays	revenue
Busiest	2024-04	18	704500.00
Busiest	2024-01	18	630500.00
Busiest	2024-03	19	614000.00
Quietest	2024-08	17	402500.00
Quietest	2024-05	15	437700.00
Quietest	2024-06	19	476000.00

April 2024 was the best month at 704,500 KES. August was the weakest by revenue (402,500 KES) even though it had 17 stays, and May had the fewest stays (15). Stays and revenue don't move together: June had 19 stays and earned 476,000 KES, while January earned 630,500 KES from 18. The mix of rooms matters more than the headcount, so a hotel needs both rankings, one for rostering staff and one for pricing.

Q6. What do cancellations cost?

SELECT room_type,
       COUNT(*) AS total_bookings,
       COUNT(*) FILTER (WHERE booking_status IN ('Cancelled', 'No Show')) AS lost_bookings,
       ROUND(100.0 * COUNT(*) FILTER (WHERE booking_status IN ('Cancelled', 'No Show'))
             / COUNT(*), 2) AS cancellation_rate_pct,
       SUM(total_amount) FILTER (WHERE booking_status IN ('Cancelled', 'No Show')) AS lost_revenue
FROM tembo.v_clean_bookings
GROUP BY room_type
ORDER BY cancellation_rate_pct DESC;

Result grid: 4 rows fetched

room_type	total_bookings	lost_bookings	cancellation_rate_pct	lost_revenue
Penthouse	25	6	24.00	565000.00
Standard	114	17	14.91	320300.00
Deluxe	90	7	7.78	170000.00
Suite	56	2	3.57	120000.00

The Penthouse loses almost one booking in four (24%), and that one room type accounts for 565,000 KES of lost revenue. Suites lose the fewest, 3.57%. Standard rooms are cancelled at 14.91%, which costs less per booking but happens far more often.

The overall bill, split by type of loss:

SELECT booking_status,
       COUNT(*)          AS bookings,
       SUM(total_amount) AS value,
       ROUND(100.0 * SUM(total_amount) / SUM(SUM(total_amount)) OVER (), 1) AS share_of_booked_value_pct
FROM tembo.v_clean_bookings
GROUP BY booking_status
ORDER BY value DESC;

Result grid: 3 rows fetched

booking_status	bookings	value	share_of_booked_value_pct
Checked Out	253	7752400.00	86.8
Cancelled	23	910500.00	10.2
No Show	9	264800.00	3.0

Cancellations and no-shows together removed 1,175,300 KES, about 13.2% of everything booked. Cancellations cost far more than no-shows (910,500 KES against 264,800 KES).

One more cut, because the staff table in Q4 hinted at a pattern. Does the payment method say anything about who cancels?

SELECT payment_method,
       COUNT(*) AS bookings,
       COUNT(*) FILTER (WHERE booking_status IN ('Cancelled', 'No Show')) AS lost_bookings,
       ROUND(100.0 * COUNT(*) FILTER (WHERE booking_status IN ('Cancelled', 'No Show'))
             / COUNT(*), 1) AS lost_rate_pct,
       COUNT(DISTINCT staff_name) AS staff_handling
FROM tembo.v_clean_bookings
GROUP BY payment_method
ORDER BY lost_rate_pct DESC;

Result grid: 4 rows fetched

payment_method	bookings	lost_bookings	lost_rate_pct	staff_handling
Bank Transfer	69	32	46.4	2
Card	70	0	0.0	2
Cash	72	0	0.0	3
M-Pesa	74	0	0.0	3

This is the most useful result in the project. Every one of the 32 lost bookings was paid by bank transfer, so 46% of bank-transfer bookings never became revenue, and cash, card and M-Pesa lost none. The last column ties it to Q4: only two staff members, Fatuma Hassan and Joy Otieno, ever handle a bank-transfer booking. The data can't separate the two causes. It may be that transfers are reservations held without payment and left to lapse, or that these two colleagues log lapsed bookings and others don't. Either way it points at the fix: ask for a deposit before a bank-transfer booking holds a room, and check how cancellations get recorded.

What the Director gets

Put together, the answers fit in a short paragraph. Suites earn the most in total, the Penthouse earns the most per stay (about 64,600 KES), Standard rooms fill most often, and the Penthouse also loses the most bookings to cancellation. Revenue held steady at roughly 400,000 to 700,000 KES a month through 2024, with April the peak. Front Desk earns the biggest share and Brenda Achieng leads on revenue. Guests are polarised, with four in ten giving a 1 or a 2 and four in ten giving a 4 or a 5. About 13% of booked value never arrived, and all of it sits in bank-transfer bookings.

And there are four questions for the hotel: why only two colleagues handle bank transfers, why Security and Housekeeping staff enter bookings at all, why blank cities and phone numbers slip through, and what happened to the service charge on two bookings.

Pitfalls I hit, so you can skip them

A two-digit year and a four-digit format don't mix. TO_DATE('28-01-24', 'DD-MM-YYYY') returns a date in the year 24 AD, and gives no error. Match the format to the text: DD-MM-YY.
Ambiguous dates need a witness. Guessing MM-DD against DD-MM is only safe when another column, here nights_stayed, can confirm it.
An update with no WHERE clause hides its own effect. A guarded update reports the true number of rows changed, which becomes a free audit trail.
Clean in staging, constrain in the final table. The CHECK constraints turned "I think the data is clean" into "the database refused anything that wasn't".
Decide what "revenue" means before writing the first SUM. It changed every answer above.

Try it yourself

Load the file, run the audit query, and see if your count of damaged rows matches mine. Then pick a question the Director didn't ask, such as which room number is busiest or whether guests who pay with M-Pesa rate higher, and answer it with the view. The brief also asks for Power BI visuals. The views above are ready to connect, so that is the natural next step.

If you spot a cleaning rule I got wrong, or a better way to write any query here, tell me in the comments. I'd like to know.

Building the SafariConnect Dashboard

David Mwandairo — Thu, 24 Sep 2026 14:22:14 +0000

Wednesday's job was simple to state and hard to do: turn a PostgreSQL schema full of correct answers into something a CEO could read in ten seconds. By Monday we had clean bookings. By Tuesday we had answers to six business questions, buried in SQL output. By Thursday morning, someone with no interest in GROUP BY clauses needed to walk out of a meeting knowing which routes made money and which drivers to promote.

That gap between a correct query and a convincing dashboard is where this part of the project actually lived.

Getting the Data Ready for Power BI

Power BI doesn't want to see your cleaning process. It wants a table it can trust, so the first job was making sure v_clean_trips and clean_bookings deserved that trust. The reason we use both sources for the presentation is that v_clean_trips only contains the completed trips and clean_bookings contains both the completed and cancelled trips, which is a major presentation point. We'd already fixed the 23 data problems in the raw CSV such as the shouting names, the phone numbers with dashes and stray +254 prefixes, the seat classes spelled four different ways and the fares stored as text. Wednesday's task was different. It was about shape, not spelling.

2 quick reminders before getting into the dashboard building process:

To go through the step-by-step analysis that led to this Power BI report, go through this article.
For a refresher on how to connect Power BI to a local database, check out this article

A few things mattered more than I expected going in:

Grain. Power BI aggregates whatever you drag onto a visual, and it will do it happily even when the grain is wrong. Our view sits at one row per booking, so SUM(seats_booked) gives real passenger counts and COUNT(booking_id) gives real booking counts. Mixing those up produces numbers that look plausible and are wrong, which is worse than numbers that look obviously broken.
Date and time columns split out early. We pulled day_name and hour-of-day fields out of departure_date and departure_time in SQL rather than in Power Query, because a CTE with EXTRACT() is easier to check with a SELECT than a DAX measure buried in a report. Anything I can verify with a query, I verify with a query.
Indexes, because refresh time is part of the user experience. Seven indexes went onto the clean table before we connected Power BI, on the columns we knew we'd filter and join on constantly: route_code, driver_name, booking_status, departure_date. A dashboard that takes fifteen seconds to slice by route stops feeling like a dashboard and starts feeling like a problem.
One view, not five imports. Everything Power BI touches comes from v_clean_trips, clean_bookings or a small set of purpose-built views. That's a deliberate choice. When a number on a slide looks wrong, there's exactly one place to go check it, not five copies of the logic drifting apart across different report pages.

The Dashboard, Page by Page

We built five pages, ending in one summary view stitched together from the other four. Here's what each one carries.

Page 1 sets the scale of the business in six cards.

Total revenue of KSh 259.96K across 288 bookings, an average fare of KSh 903.10, an average driver rating of 4.27 against an average trip rating of only 3.53, and 35 cancelled bookings. Nothing fancy here on purpose. A board member glancing at a laptop from across a table should get the shape of the business before anyone says a word.

Page 2 is where the money and the people live.

A route table anchors the page on the left, followed by a revenue-by-route bar chart, a monthly revenue line, a passenger-city chart, a gender donut, and a driver rating bar chart with a trend line. RT001, Nairobi to Mombasa, sits clearly on top of the revenue bars, matching what the SQL had already told us on Tuesday. Good sign. If the dashboard had disagreed with the query, one of them would have been lying.

Page 3 covers what's costing us and when people actually travel.

A seat-class donut, a cancellation-rate card, a lost-revenue figure, a route-by-route lost revenue bar chart, and a month-by-day-of-week matrix of bookings. The cancellation rate reads as 0.12, which is 12%, and lost revenue lands at KSh 32.15K. That's real money walking out the door on bookings that never happened.

Page 4 is one focused chart.

Busiest hours by departure time, sorted from most booked to least. 6am leads. This page could have lived inside page 3, but a scheduling question this operational deserved its own screen rather than fighting for space with six other visuals.

The final page compresses all of it onto one screen for anyone who wants the whole story without clicking through tabs.

The DAX Behind Each Metric

Power BI doesn't inherit anything from the SQL side beyond the columns themselves, so every card and chart above needed its own measure. Below is the DAX that reproduces each one, written against clean_bookings as the table name, swap in whatever your model actually calls it.

KPI cards (Page 1 and the dashboard header)

Total Revenue = SUM(v_clean_trips[total_fare])

Total Bookings = DISTINCTCOUNT(v_clean_trips[booking_id])

Avg Fare = DIVIDE([Total Revenue], [Total Bookings])

Avg Driver Rating = AVERAGE(v_clean_trips[driver_rating])

Avg Trip Rating = AVERAGE(v_clean_trips[trip_rating])

Cancelled Bookings =
CALCULATE(
    DISTINCTCOUNT(clean_bookings[booking_id]),
    clean_bookings[booking_status] = "Cancelled"
)

Lost Revenue =
CALCULATE(
    SUM(clean_bookings[total_fare]),
    clean_bookings[booking_status] IN { "Cancelled", "No Show" }
)

Cancellation Rate % =
DIVIDE(
    CALCULATE(
        DISTINCTCOUNT(clean_bookings[booking_id]),
        clean_bookings[booking_status] IN { "Cancelled", "No Show" }
    ),
    [Total Bookings]
)

Revenue by Route and Revenue by Month (Page 2)

Revenue by Route =
CALCULATE(
    SUM(v_clean_trips[total_fare]),
    ALLEXCEPT(v_clean_trips, v_clean_trips[route_code])
)

Revenue by Month = SUM(v_clean_trips[total_fare])
-- placed on a matrix/line chart with departure_date grouped to Month,
-- or against a proper Date table:
Monthly Revenue = CALCULATE(SUM(v_clean_trips[total_fare]), DATESMTD('Date'[Date]))

Revenue by Route is really just Total Revenue dropped onto a bar chart with route_code on the axis; the ALLEXCEPT version is only needed if you want the route total to appear as a static comparison column alongside row-level detail.

Passenger Population and Gender split (Page 2)

Total Seats Booked = SUM(v_clean_trips[seats_booked])

Female Passengers =
CALCULATE([Total Seats Booked], v_clean_trips[passenger_gender] = "Female")

Male Passengers =
CALCULATE([Total Seats Booked], v_clean_trips[passenger_gender] = "Male")

Both donut and area chart use [Total Seats Booked], split by passenger_city for the area chart and by passenger_gender for the donut.

Driver Rating per Revenue (Page 2)

Driver Avg Rating = AVERAGE(v_clean_trips[driver_rating])

Driver Total Revenue = SUM(v_clean_trips[total_fare])

Both measures sit on the same combo chart with driver_name on the axis, one as columns, one as the line. The duplicate "Sum of driver_rating" legend entry flagged in the critique below is what happens when the second measure gets built as a copy of the first instead of a trip_rating average.

Seat Class Booked and Cancellation Rate (Page 3)

Bookings by Seat Class = COUNTROWS(clean_bookings)
-- on the donut, sliced by seat_class

Lost Revenue by Route =
CALCULATE(
    SUM(clean_bookings[total_fare]),
    clean_bookings[booking_status] IN { "Cancelled", "No Show" },
    ALLEXCEPT(clean_bookings, clean_bookings[route_code])
)

Busiest Days Booked by Seats and Busiest Days per Bookings (Page 3)

Seats Booked by Day =
SUM(v_clean_trips[seats_booked])
-- matrix: day_name on rows, Month(departure_date) on columns

Bookings by Day of Week =
COUNTROWS(v_clean_trips)
-- bar chart: WEEKDAY(v_clean_trips[departure_date]) on the axis

day_name itself is worth calculating once as a calculated column rather than re-deriving it in every visual:

day_name = FORMAT(v_clean_trips[departure_date], "dddd")
day_of_week = WEEKDAY(v_clean_trips[departure_date], 2)

Busiest Hours per Departure Time (Page 4)

Bookings by Departure Hour = COUNTROWS(v_clean_trips)
-- horizontal bar, departure_time on the axis, sorted by this measure descending

If departure_time came in as text (as it did through most of the SQL cleaning stage), Power Query needs to convert it to a proper time value before this sort works correctly, otherwise "06:00" and "16:00" sort as text rather than as clock time.

What the Numbers Actually Said

Strip away the chart types and a few things stood out clearly enough that we could say them out loud without hedging.

Nairobi-to-Mombasa (RT001) is the business's best route by revenue, and it's also the route bleeding the most money to cancellations, at roughly KSh 10K lost against a lost-revenue total of KSh 32.15K. That's not a contradiction. It's the same thing: your busiest, most valuable route is also your biggest exposure when a booking falls through.

Driver ratings sit meaningfully higher than trip ratings across the board, 4.27 against 3.53. Passengers aren't rating the drivers badly, but something about the overall trip experience, the vehicle, the timing, the route itself, isn't landing as well as the person behind the wheel. That gap is worth a follow-up question the dashboard alone can't answer.

Economy dominates seat class choice by a wide margin, which says more about price sensitivity in this market than anything else in the data. Monday is the single busiest day of the week, ahead of Wednesday and Tuesday, and Sunday is dramatically quiet. 6am is the most requested departure slot, which tells operations exactly where to put the extra vehicle if they only have one to spare.

Telling the Story to the Board

The brief was blunt about Thursday: ten minutes, live dashboard, every group member speaks, and the CEO chooses who answers each question. That last part changes how you prepare more than anything else. You can't assign yourself the easy slide and hope nobody asks about the hard one.

We walked the room through the dashboard in the same order it's built here: scale first, then routes and people, then costs and timing. Each number on screen had to trace back to a specific query from Tuesday, because "the dashboard says so" isn't an answer a CEO in that kind of room will accept. When the cancellation-rate card came up, the follow-up was immediate: which route, how much money, and what would fixing it require. Having the lost-revenue-by-route chart sitting right there, rather than a number we'd have to go calculate live, was the difference between a confident answer and an awkward pause.

What This Taught Me

A live filter is a live risk. Click the wrong slicer in front of an audience and your carefully rehearsed narrative shows a different number than the one you said thirty seconds ago. We tested every click path the night before, not just the happy one.
A chart is not an insight. "Revenue by route" is a chart. "RT001 makes the most money and loses the most to cancellations" is an insight, and it only exists because someone said it out loud while pointing at two different visuals on two different pages. The dashboard did the arithmetic. The presentation did the thinking.
Numbers need to match everywhere or they undermine themselves. The moment two pages showed a slightly different split for the same field, the room noticed before we did. A dashboard is a set of promises that every number agrees with every other number, and it only takes one mismatch to make people doubt all of them.

What I'd Build Differently Next Time

A few things would change if I rebuilt this dashboard today:

The seat-class donut on page 3 carries a (Blank) slice worth 10-13% of bookings, a leftover null that should have been resolved to a real value or an explicit "Unknown" label back in the SQL cleaning stage, not left to surface as an unlabelled wedge on a board slide. The same donut also disagrees slightly on the combined summary page versus its own dedicated page, a small inconsistency that's exactly the kind of thing point three above warned about.
The driver rating chart on page 2 shows two identical legend entries, both labelled "Sum of driver_rating." One of those measures was clearly meant to be something else, likely a trip-rating comparison, and it slipped through review. Labelling errors like that are easy to miss when you're staring at your own report and easy to spot when a CEO is staring at it fresh.
Several labels get cut off: the route table's route_from column, the passenger-city axis, a driver's name mid-word on the ratings chart. On a laptop screen that's a minor irritation. Projected on a boardroom wall, it reads as sloppiness that has nothing to do with the analysis underneath it.
Everything on this dashboard is the same shade of blue. That's a deliberate, calm choice, and it also means nothing visually flags the routes losing money versus the routes making it. A single accent color on the lost-revenue chart, red or amber against the house blue, would let a CEO's eye find the problem before anyone finished the sentence.
Given that the entire business is about routes between named cities, there's no map anywhere in the report. A route table with codes and city names asks the reader to do geography homework. A map does that work for them, and it's the one visual type this dataset was practically begging for.

None of these are failures of the analysis. The numbers underneath the dashboard held up under direct CEO questioning, which was the actual test. But a dashboard has two audiences, the analyst who trusts the query behind it, and the executive who only ever sees the screen, and building for the second audience is a distinct skill from the SQL that got us there.

The SafariConnect Data Story

David Mwandairo — Wed, 23 Sep 2026 12:43:56 +0000

How 290 rows of tangled booking data became six clear answers for a CEO who doesn't have time for excuses.

The Message That Started It All

Every data project has an origin story. This one starts with a WhatsApp-length message from an Operations Director who has run out of patience with a shared Excel file.

Safari Connect is a Nairobi-based bus and matatu booking platform. Think of it as the Bolt of long-distance travel. Passengers pick a route, choose a seat class, pay by M-Pesa, cash, or card, and turn up (mostly). Since 2024 the company has been growing fast, and its booking history has been growing right along with it, quietly, in a spreadsheet nobody has cleaned since the day it was created.

The brief was blunt: "I need you to clean this data, load it into our database, analyse it, and present your findings to the board... Make it look professional. Present on Friday. The CEO will be in the room."

No pressure.

What follows is the full journey of that data. From a 290-row CSV export riddled with 23 categories of mess, through a PostgreSQL cleaning pipeline, into a set of views, and finally into six business answers with real Kenyan-shilling figures behind them. Every query below is genuine SQL written for this project, and every result table underneath it is the actual output that query produces against the cleaned data, shown the way it would appear in a database GUI like DBeaver, row for row.

Note: To access the files I used in this project, check out this Github repository

Chapter 1: Landing the Raw Data

Before any cleaning can happen, the raw export has to land somewhere safe. The first move is to build a staging table, with every column typed as TEXT, so that a stray letter in a numeric column doesn't blow up the import.

-- Create safari_connect schema
CREATE SCHEMA IF NOT EXISTS safari_connect;

-- Set search_path to safari_connect schema
SET search_path TO safari_connect;

-- Set up staging table to match csv structure
-- Drop existing table if any
DROP TABLE IF EXISTS safari_connect.staging_bookings;

-- Create staging table with all columns as text to avoid type mismatches
CREATE TABLE safari_connect.staging_bookings (
    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
);
-- Sanity check for staging_bookings table
SELECT * FROM safari_connect.staging_bookings;

That "everything is text" decision looks lazy, but it's the opposite. It's the one rule that keeps a messy import from crashing on line one. 290 rows load without a single error. The mess gets dealt with afterwards, on the analyst's terms, not the file's.

With the raw data safely staged, a second table (clean_bookings), is created to receive the fixed version, with booking_id as a VARCHAR(10) PRIMARY KEY and every other column given a real type: NUMERIC for money and ratings, DATE for departure dates, INTEGER for seats and trip ratings. This table stays empty until the cleaning below earns its data.

--  Drop clean table if exists
DROP TABLE IF EXISTS safari_connect.clean_bookings;

-- Create clean table with appropriate data types
CREATE TABLE safari_connect.clean_bookings (
    booking_id VARCHAR(10) PRIMARY KEY,
    passenger_name VARCHAR(100),
    passenger_phone VARCHAR(10),
    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(2,1),
    departure_date DATE,
    departure_time VARCHAR(10),
    seat_class VARCHAR(20),
    seats_booked INTEGER,
    fare_per_seat NUMERIC(10,2),
    total_fare NUMERIC(10,2),
    payment_method VARCHAR(20),
    booking_status VARCHAR(20),
    trip_rating INTEGER
);
-- Santy check for clean table
SELECT * FROM safari_connect.clean_bookings;

Chapter 2: Twenty-Three Ways a Spreadsheet Can Lie to You

Upon further analysis, I found 23 categories of dirty data hiding in the export. This section shows how SQL was used to hunt each one down.

Names: shouting, whispering, and stray spaces

Passenger names arrive in three unhelpful states i.e. ALICE MWANGI, brian otieno, and Carol Wanjiku with leading and trailing whitespace baked in.

SELECT booking_id, passenger_name
FROM safari_connect.staging_bookings
WHERE passenger_name != INITCAP(TRIM(passenger_name));

Output:

booking_id	passenger_name
BK0001	ALICE MWANGI
BK0002	brian otieno
BK0003	Carol Wanjiku
BK0023	CAROL WANJIKU
BK0024	david kamau

One UPDATE fixes all three problems at once, because INITCAP(TRIM(...)) trims whitespace and title-cases in a single pass:

UPDATE safari_connect.staging_bookings
SET passenger_name = INITCAP(TRIM(passenger_name))
WHERE passenger_name != INITCAP(TRIM(passenger_name));

booking_id	passenger_name
BK0001	Alice Mwangi
BK0002	Brian Otieno
BK0003	Carol Wanjiku
BK0023	Carol Wanjiku
BK0024	David Kamau

Note: BK0003 and BK0023 converge on the same clean value from two different kinds of mess. This proves that one well-chosen function beats three separate patches.

Phone numbers: dashes, country codes, and blanks

Phone numbers show up with dashes (0745-678-901), a +254 international prefix (+254756789012), and in 13 other cases, nothing at all.

SELECT booking_id, passenger_phone
FROM safari_connect.staging_bookings
WHERE passenger_phone LIKE '%-%';

Output:
| booking_id | passenger_phone |
|---|---|
| BK0004 | 0745-678-901 |
| BK0005 | +254756789012 |
| BK0006 | |
| BK0026 | 0767-890-123 |
| BK0027 | +254778901234 |
| BK0028 | |

Three targeted updates handle each variant:

-- Strip dashes and any non-digits
UPDATE safari_connect.staging_bookings
SET passenger_phone = REGEXP_REPLACE(passenger_phone,'[^0-9]', '', 'g')
WHERE passenger_phone LIKE '%-%';

-- Swap the +254 country code for a local leading zero
UPDATE safari_connect.staging_bookings
SET passenger_phone = '0' || SUBSTRING(REGEXP_REPLACE(passenger_phone, '[^0-9]', '', 'g'), 4)
WHERE passenger_phone LIKE '%+254%';

-- Empty strings become real NULLs, not invisible blanks
UPDATE safari_connect.staging_bookings
SET passenger_phone = NULL
WHERE TRIM(passenger_phone) = '';

booking_id	passenger_phone
BK0004	0745678901
BK0005	0756789012
BK0006	NULL
BK0026	0767890123
BK0027	0778901234
BK0028	NULL

That third update matters more than it looks. An empty string and a NULL behave identically to the human eye but very differently to a database. COUNT(), joins, and WHERE clauses all treat them apart. Making the blank explicit is what lets later analysis say "13 bookings have no phone on file" instead of silently miscounting.

Gender: seven ways to say two things

SELECT DISTINCT(passenger_gender) FROM safari_connect.staging_bookings;

passenger_gender	row_count
Male	143
Female	134
FEMALE	4
F	3
MALE	2
female	2
M	1
male	1

Seven labels, two real categories. A CASE statement collapses them:

UPDATE safari_connect.staging_bookings
SET passenger_gender = CASE
    WHEN UPPER(TRIM(passenger_gender)) IN ('MALE', 'M') THEN 'Male'
    WHEN UPPER(TRIM(passenger_gender)) IN ('FEMALE', 'F') THEN 'Female'
    ELSE passenger_gender
END;

Output:
| passenger_gender | row_count |
|---|---|
| Male | 147 |
| Female | 141 |

Dates: three formats fighting for the same column

This is the one that separates a careful analyst from a hopeful one. The same column holds 2024-01-08, 17/01/2024, 01-18-2024, and 19-01-24 — four different shapes, and none of them announce which is which.

booking_id	departure_date
BK0001	2024-01-08
BK0007	17/01/2024
BK0008	01-18-2024
BK0009	19-01-24

The fix has to be sequenced carefully. Slash-separated dates first, then short year-dashes, then anything left over gets checked digit-by-digit (a month value over 12 can only mean the format was MM-DD-YYYY, not DD-MM-YYYY):

UPDATE safari_connect.staging_bookings
SET departure_date = TO_DATE(departure_date, 'DD/MM/YYYY')::TEXT
WHERE departure_date LIKE '%/%';

UPDATE safari_connect.staging_bookings
SET departure_date = TO_DATE(departure_date, 'DD-MM-YYYY')::TEXT
WHERE departure_date LIKE '%-%' AND LENGTH(departure_date) = 8;

UPDATE safari_connect.staging_bookings
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;

booking_id	departure_date
BK0001	2024-01-08
BK0007	2024-01-17
BK0008	2024-01-18
BK0009	2024-01-19

Every one of those four rows now speaks the same language: YYYY-MM-DD, ready to be cast to a real DATE type.

Money hiding behind the word "KES"

SELECT booking_id, fare_per_seat, total_fare FROM safari_connect.staging_bookings;

booking_id	fare_per_seat	total_fare
BK0016	1200	KES 2400
BK0017	KES 675	675
BK0038	900	KES 1800
BK0039	KES 200	600
BK0060	280	KES 840

27 rows across fare_per_seat and total_fare carry a KES prefix that turns a number into a string PostgreSQL can't do arithmetic on. REGEXP_REPLACE strips anything that isn't a digit:

UPDATE safari_connect.staging_bookings
SET total_fare = REGEXP_REPLACE(total_fare, '[^0-9]', '', 'g')
WHERE total_fare SIMILAR TO '%[^0-9]%';

booking_id	fare_per_seat	total_fare
BK0016	1,200.00	2,400.00
BK0017	675.00	675.00
BK0038	900.00	1,800.00
BK0039	200.00	600.00
BK0060	280.00	840.00

Ratings that shouldn't exist, seats that go negative, and a booking that shows up twice

Three more categories of trouble, each fixed with a different weapon:

Impossible ratings. A 1–5 scale should never contain a 0 or a 6. 13 rows did.

SELECT DISTINCT(trip_rating) FROM safari_connect.staging_bookings;

booking_id	trip_rating
BK0021	6
BK0043	6
BK0065	0
BK0087	6
BK0109	0
BK0131	6

UPDATE safari_connect.staging_bookings
SET trip_rating = NULL
WHERE TRIM(trip_rating) NOT IN ('1', '2', '3', '4', '5', '');

A rating that can't be trusted is worse than no rating at all. This turns it into an honest NULL rather than a lie that would quietly drag down an average.

Negative seats. A booking for -1 seats makes no physical sense. You can't un-book a seat that was never booked in the first place, so the project rule is to delete rather than guess:

booking_id	passenger_name	seats_booked
BK9002	JANE SMITH	-1

DELETE FROM safari_connect.staging_bookings
WHERE NULLIF(REGEXP_REPLACE(seats_booked, '[^0-9-]', '', 'g'), '')::INTEGER < 1;

An exact duplicate. BK0005 — Esther Njoroge, same date, same seats appears twice:

booking_id	passenger_name	departure_date	seats_booked
BK0005	Esther Njoroge	2024-01-15	2
BK0005	Esther Njoroge	2024-01-15	2

DELETE FROM safari_connect.staging_bookings
WHERE ctid NOT IN
    (SELECT MIN(ctid) FROM safari_connect.staging_bookings GROUP BY booking_id);

ctid is Postgres' own physical row address and it's what makes this command safe. booking_id is supposed to be unique, so you can't self-join on it to find "the other one" without ambiguity; ctid never lies about which physical row is which, so keeping the MIN(ctid) per booking_id keeps exactly one copy and drops the rest.

Loading the cleaned data

To load and verify the cleaned data into the clean_bookings table, we use the following script:

INSERT INTO safari_connect.clean_bookings
SELECT 
        booking_id, TRIM(passenger_name),
        NULLIF(TRIM(passenger_phone), ''),
        passenger_gender, COALESCE(NULLIF(TRIM(passenger_city),''), 'Unknown'),
        route_code, route_from, route_to, vehicle_plate,
        INITCAP(TRIM(vehicle_type)), TRIM(driver_name),
        NULLIF(REGEXP_REPLACE(driver_rating, '[^0-9.]', '', 'g'), '')::NUMERIC,
        departure_date::DATE, departure_time, seat_class,
        NULLIF(REGEXP_REPLACE(seats_booked, '[^0-9]', '', 'g'), '')::INTEGER,
        NULLIF(REGEXP_REPLACE(fare_per_seat, '[^0-9]', '', 'g'), '')::NUMERIC,
        NULLIF(REGEXP_REPLACE(total_fare, '[^0-9]', '', 'g'), '')::NUMERIC,
        payment_method, booking_status,
        NULLIF(trip_rating, '')::INTEGER 
FROM safari_connect.staging_bookings 
WHERE departure_date SIMILAR TO '[0-9]{4}-[0-9]{2}-[0-9]{2}'
AND NULLIF(REGEXP_REPLACE(seats_booked, '[^0-9]', '', 'g'), '')::INTEGER > 0;

-- Verify script
SELECT count(*) FROM safari_connect.clean_bookings;
SELECT DISTINCT booking_status FROM safari_connect.clean_bookings;

Once we verify the script, we get the following outputs respectively:

Between the negative-seat row and the duplicate, the table drops from 290 rows to 283. These were small, deliberate cuts, each backed by a SELECT that confirmed what was about to be deleted before it was deleted.

Casing was fixed the same INITCAP(TRIM()) way for passenger_city, driver_name, and vehicle_type; seat_class, payment_method, and booking_status each got their own CASE WHEN to collapse abbreviations like ECO/BUS/BUSINESS CLASS down to the two or three values the business actually recognises. The pattern repeats, but the logic behind each one is worth the same scrutiny as the flashier fixes above.

Chapter 3: The View That Everything Else Stands On

Once clean_bookings holds trustworthy data, a view(v_clean_trips) sits on top of it and does the last mile of preparation. It contains the data of the trips that were completed. Here is the script used to create v_clean_trips:

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 safari_connect.clean_bookings
WHERE booking_status = 'Completed';

-- Test v_clean_trips
SELECT count(*) FROM v_clean_trips;

After running the test script, the rows are further cleaned to give an output of 248 rows:

The v_clean_trips table is what will be used to create the Power BI presentation for this project. Every query(except Q5) in the next section reads from it.

Chapter 4: Six Questions, Six Answers

This is where the cleaning pays off. The Operations Director asked six things. Here is what the data actually said, query by query, output by output.

Q1. Which routes earn the most, which are most popular, and which are most efficient?

Three angles on the same ten routes, because "best" means different things to different people in the room.

Highest earner:

SELECT route_code, route_from, route_to, SUM(calculated_fare) AS total_fare_per_route
FROM v_clean_trips
GROUP BY route_code, route_from, route_to
ORDER BY total_fare_per_route DESC;

Output:

route_code	route_from	route_to	total_fare_per_route
RT001	Nairobi	Mombasa	62,400.00
RT004	Nairobi	Eldoret	44,800.00
RT002	Nairobi	Kisumu	43,650.00
RT007	Nairobi	Nyeri	25,000.00
RT003	Nairobi	Nakuru	23,850.00
RT006	Mombasa	Malindi	17,150.00
RT010	Nairobi	Naivasha	15,960.00
RT009	Nairobi	Machakos	9,300.00
RT008	Kisumu	Kakamega	9,090.00
RT005	Nairobi	Thika	8,760.00

Nairobi–Mombasa (RT001) is the clear top earner, pulling in KES 62,400.00 — nearly 20,000 more than the second-place route.

Most popular by demand:

SELECT route_code, route_from, route_to,
       COUNT(booking_id) AS num_bookings,
       SUM(seats_booked) AS total_seats_sold
FROM v_clean_trips
GROUP BY route_code, route_from, route_to
ORDER BY total_seats_sold DESC;

Output:
| route_code | route_from | route_to | num_bookings | total_seats_sold |
|---|---|---|---|---|
| RT005 | Nairobi | Thika | 35 | 71 |
| RT007 | Nairobi | Nyeri | 31 | 59 |
| RT010 | Nairobi | Naivasha | 27 | 55 |
| RT004 | Nairobi | Eldoret | 28 | 53 |
| RT001 | Nairobi | Mombasa | 30 | 50 |
| RT003 | Nairobi | Nakuru | 29 | 49 |
| RT008 | Kisumu | Kakamega | 29 | 47 |
| RT006 | Mombasa | Malindi | 27 | 47 |
| RT002 | Nairobi | Kisumu | 28 | 45 |
| RT009 | Nairobi | Machakos | 24 | 43 |

Here the story flips: Nairobi–Thika (RT005) wins on volume(the most bookings and the most seats sold), even though it earns the least revenue of all ten routes. It's a short, cheap, high-frequency commuter run, not a premium long-haul product, and the two metrics were never going to agree.

Most efficient per seat:

SELECT route_code, route_from, route_to,
       SUM(seats_booked) AS total_seats_sold,
       ROUND(AVG(trip_rating), 2) AS avg_rating,
       ROUND(SUM(calculated_fare) / NULLIF(SUM(seats_booked), 0), 2) AS revenue_per_seat
FROM v_clean_trips
GROUP BY route_code, route_from, route_to
ORDER BY revenue_per_seat DESC;

Output:
| route_code | route_from | route_to | total_seats_sold | avg_rating | revenue_per_seat |
|---|---|---|---|---|---|
| RT001 | Nairobi | Mombasa | 50 | 3.48 | 1,248.00 |
| RT002 | Nairobi | Kisumu | 45 | 4.0 | 970.00 |
| RT004 | Nairobi | Eldoret | 53 | 3.5 | 845.28 |
| RT003 | Nairobi | Nakuru | 49 | 3.59 | 486.73 |
| RT007 | Nairobi | Nyeri | 59 | 3.23 | 423.73 |
| RT006 | Mombasa | Malindi | 47 | 3.62 | 364.89 |
| RT010 | Nairobi | Naivasha | 55 | 3.33 | 290.18 |
| RT009 | Nairobi | Machakos | 43 | 3.57 | 216.28 |
| RT008 | Kisumu | Kakamega | 47 | 3.52 | 193.40 |
| RT005 | Nairobi | Thika | 71 | 3.5 | 123.38 |

RT001 wins a third time. It brings in the highest revenue, most efficient per seat. Nairobi–Mombasa isn't just the biggest number on the board; it's the healthiest one, generating more shillings per seat sold than any other route.

A window-function version pulls all three lenses into one CTE and adds a revenue-share column:

WITH route_metrics AS (
    SELECT route_code, route_from || ' - ' || route_to AS route,
           COUNT(DISTINCT booking_id) AS total_bookings,
           SUM(seats_booked) AS total_seats_sold,
           SUM(total_fare) AS total_revenue,
           SUM(total_fare) / NULLIF(SUM(seats_booked), 0) AS revenue_per_seat
    FROM v_clean_trips
    WHERE booking_status = 'Completed'
    GROUP BY route_code, route_from, route_to
)
SELECT route, total_revenue, total_seats_sold, total_bookings,
       ROUND(revenue_per_seat, 2) AS revenue_per_seat,
       ROUND(total_revenue * 100.0 / SUM(total_revenue) OVER (), 2) AS revenue_share_pct
FROM route_metrics
ORDER BY total_revenue DESC;

Output:
| route | total_revenue | total_seats_sold | total_bookings | revenue_per_seat | revenue_share_pct |
|---|---|---|---|---|---|
| Nairobi - Mombasa | 62,400.00 | 50 | 30 | 1,248.00 | 24.0 |
| Nairobi - Eldoret | 44,800.00 | 53 | 28 | 845.28 | 17.23 |
| Nairobi - Kisumu | 43,650.00 | 45 | 28 | 970.00 | 16.79 |
| Nairobi - Nyeri | 25,000.00 | 59 | 31 | 423.73 | 9.62 |
| Nairobi - Nakuru | 23,850.00 | 49 | 29 | 486.73 | 9.17 |
| Mombasa - Malindi | 17,150.00 | 47 | 27 | 364.89 | 6.6 |
| Nairobi - Naivasha | 15,960.00 | 55 | 27 | 290.18 | 6.14 |
| Nairobi - Machakos | 9,300.00 | 43 | 24 | 216.28 | 3.58 |
| Kisumu - Kakamega | 9,090.00 | 47 | 29 | 193.40 | 3.5 |
| Nairobi - Thika | 8,760.00 | 71 | 35 | 123.38 | 3.37 |

That last column — revenue_share_pct, built with SUM(...) OVER (), is what turns "RT001 earns a lot" into a boardroom sentence: RT001 alone accounts for roughly a quarter of all route revenue. That's the kind of dependency a CEO needs to hear out loud.

Q2. Who are the best drivers, and does their rating actually affect how happy passengers are?

WITH driver_stats AS (
    SELECT driver_name,
           COUNT(DISTINCT booking_id) AS total_completed_bookings,
           SUM(total_fare) AS total_revenue,
           ROUND(AVG(driver_rating), 2) AS avg_driver_rating,
           ROUND(AVG(trip_rating), 2) AS avg_trip_rating,
           CORR(driver_rating, trip_rating) AS rating_correlation
    FROM v_clean_trips
    WHERE booking_status = 'Completed'
      AND trip_rating IS NOT NULL
      AND driver_rating IS NOT NULL
    GROUP BY driver_name
)
SELECT * FROM driver_stats ORDER BY total_revenue DESC;

Output:
| driver_name | total_completed_bookings | total_revenue | avg_driver_rating | avg_trip_rating |
|---|---|---|---|---|
| Isaac Korir | 33 | 33,045.00 | 3.8 | 3.39 |
| Kelvin Omondi | 32 | 30,855.00 | 4.5 | 3.32 |
| Brian Kamau | 26 | 29,340.00 | 4.2 | 3.73 |
| Tony Karanja | 34 | 28,250.00 | 3.9 | 3.74 |
| Samuel Gitonga | 33 | 28,235.00 | 4.6 | 3.59 |
| Hassan Abdi | 30 | 27,575.00 | 4.1 | 3.9 |
| Moses Kipchoge | 30 | 26,630.00 | 4.8 | 3.19 |
| Peter Ngugi | 35 | 23,880.00 | 4.3 | 3.41 |

Isaac Korir generates the most revenue of any driver, despite carrying a mid-table 3.8 platform rating. This is proof that a driver's star rating and their commercial value to the company are not the same conversation. The CORR() window function makes that gap official: the correlation between a driver's platform rating and the trip ratings passengers actually leave comes out at -0.101(essentially no relationship). A high driver rating does not reliably buy a happier passenger, which means promotion decisions built purely on stars would be pointing at the wrong signal.

Q3. How is revenue trending, and what were the best and worst months?

WITH monthly_revenue AS (
    SELECT date_trunc('month', departure_date) AS month,
           SUM(total_fare) AS revenue,
           COUNT(DISTINCT booking_id) AS bookings
    FROM v_clean_trips
    GROUP BY date_trunc('month', departure_date)
),
monthly_growth AS (
    SELECT month, revenue,
           LAG(revenue) OVER (ORDER BY month) AS prev_monthly_revenue,
           ROUND(((revenue - LAG(revenue) OVER (ORDER BY month)) 
                  / LAG(revenue) OVER (ORDER BY month)) * 100, 2) AS growth_pct
    FROM monthly_revenue
)
SELECT to_char(month, 'YYYY-MM') AS month_label, revenue, growth_pct,
       RANK() OVER (ORDER BY revenue DESC) AS rank_month
FROM monthly_growth
ORDER BY month;

Output:
| month | revenue | bookings | growth_pct |
|---|---|---|---|
| 2024-01 | 18,475.00 | 20 | |
| 2024-02 | 19,945.00 | 22 | 7.96 |
| 2024-03 | 20,325.00 | 23 | 1.91 |
| 2024-04 | 18,390.00 | 23 | -9.52 |
| 2024-05 | 22,060.00 | 28 | 19.96 |
| 2024-06 | 23,425.00 | 25 | 6.19 |
| 2024-07 | 23,395.00 | 22 | -0.13 |
| 2024-08 | 16,740.00 | 18 | -28.45 |
| 2024-09 | 23,735.00 | 26 | 41.79 |
| 2024-10 | 25,230.00 | 28 | 6.3 |
| 2024-11 | 21,650.00 | 24 | -14.19 |
| 2024-12 | 22,205.00 | 25 | 2.56 |
| 2025-01 | 4,385.00 | 4 | -80.25 |

LAG() is doing the heavy lifting here. It reaches back one row (one month) without a self-join, which is exactly what month-over-month growth needs. The trend is choppy rather than a clean upward line. October is the standout month, September posts the sharpest single jump (+41.8%), and August is the year's quiet trough. The final row, January 2025, is a partial month in the export rather than a genuine collapse. This is worth flagging in the boardroom so nobody reads a half-month as a crisis.

Q4. Where do passengers come from, what do they book, and are they happy?

SELECT passenger_city, COUNT(DISTINCT booking_id) AS bookings, SUM(total_fare) AS revenue
FROM v_clean_trips
WHERE booking_status = 'Completed'
GROUP BY passenger_city
ORDER BY bookings DESC
LIMIT 10;

Output:
| passenger_city | bookings | revenue |
|---|---|---|
| Nairobi | 113 | 112,000.00 |
| Kisumu | 26 | 20,980.00 |
| Eldoret | 24 | 13,910.00 |
| Mombasa | 20 | 21,320.00 |
| Meru | 14 | 11,655.00 |
| Nakuru | 13 | 6,300.00 |
| Thika | 13 | 16,740.00 |
| Unknown | 11 | 9,425.00 |
| Nyeri | 10 | 8,220.00 |
| Machakos | 9 | 7,260.00 |

Nairobi dominates, contributing far more bookings and revenue than every other city combined, unsurprising for a Nairobi-headquartered platform, but worth quantifying rather than assuming.

Seat class tells its own story:

SELECT seat_class, COUNT(DISTINCT booking_id) AS bookings, SUM(total_fare) AS revenue,
       ROUND(AVG(trip_rating), 2) AS avg_rating
FROM v_clean_trips
WHERE booking_status = 'Completed'
GROUP BY seat_class;

Output:
| seat_class | bookings | revenue | avg_rating |
|---|---|---|---|
| Business | 52 | 38,210.00 | 3.65 |
| Economy | 201 | 189,600.00 | 3.5 |

Economy carries nearly four times the booking volume of Business, which is expected, but Business passengers rate their trips slightly higher on average, a small signal that the premium seat class is delivering on its premium.

Satisfaction, at a glance:

SELECT satisfaction, COUNT(booking_id) AS total_trips
FROM v_clean_trips
GROUP BY satisfaction
ORDER BY total_trips DESC;

Output:
| satisfaction | total_trips |
|---|---|
| Satisfied | 119 |
| Neutral | 74 |
| Unsatisfied | 50 |
| No Rating | 45 |

More passengers land in "Satisfied" than in any other bucket, but "Unsatisfied" and "No Rating" together are not a small slice of the pie. They're enough to be worth a follow-up on why so many trips go unrated at all.

And a cross-cut for completeness:

SELECT passenger_gender, seat_class, COUNT(booking_id) AS total_bookings
FROM v_clean_trips
GROUP BY passenger_gender, seat_class
ORDER BY passenger_gender, seat_class, total_bookings DESC;

Output:
| passenger_gender | seat_class | total_bookings |
|---|---|---|
| Female | Business | 29 |
| Female | Economy | 112 |
| Male | Business | 30 |
| Male | Economy | 117 |

There's no meaningful gender skew in seat class preference. Both groups book Economy at roughly the same rate. Sometimes the most useful finding is confirming that a variable doesn't matter, so nobody chases it later.

Q5. What's the cancellation rate per route, and what did it cost?

Note: we use the clean_bookings table for this query because v_clean_trips only contains completed trips

WITH route_totals AS (
    SELECT route_code,
           COUNT(*) AS total_bookings,
           COUNT(*) FILTER (WHERE booking_status IN ('Cancelled', 'No Show')) AS cancelled_bookings,
           SUM(total_fare) FILTER (WHERE booking_status IN ('Cancelled', 'No Show')) AS lost_revenue
    FROM safari_connect.clean_bookings
    GROUP BY route_from, route_to, route_code
)
SELECT route_code, total_bookings, cancelled_bookings,
       ROUND(cancelled_bookings * 100.0 / total_bookings, 2) AS cancellation_rate_pct,
       lost_revenue
FROM route_totals
ORDER BY cancellation_rate_pct DESC;

Output:
| route_code | total_bookings | cancelled_bookings | lost_revenue | cancellation_rate_pct |
|---|---|---|---|---|
| RT006 | 27 | 5 | 2,800.00 | 18.52 |
| RT009 | 24 | 4 | 2,000.00 | 16.67 |
| RT005 | 35 | 5 | 1,140.00 | 14.29 |
| RT003 | 29 | 4 | 3,150.00 | 13.79 |
| RT008 | 29 | 4 | 1,620.00 | 13.79 |
| RT001 | 30 | 4 | 10,800.00 | 13.33 |
| RT002 | 28 | 3 | 5,400.00 | 10.71 |
| RT007 | 31 | 3 | 2,800.00 | 9.68 |
| RT010 | 27 | 2 | 840.00 | 7.41 |
| RT004 | 28 | 1 | 1,600.00 | 3.57 |

SELECT SUM(total_fare) AS total_lost_revenue
FROM safari_connect.clean_bookings
WHERE booking_status IN ('Cancelled', 'No Show');

total_lost_revenue: 32,150.00

RT006 (Mombasa–Malindi) has the worst cancellation rate at over 18%, which is nearly one in five bookings on that route. But the bigger shilling figure sits with RT001: even a modest cancellation rate on the highest-earning route still bleeds real money, because 13% of a big number is bigger than 18% of a small one. Across the whole business, cancellations and no-shows cost Safari Connect KES 32,150.00, money that a firmer deposit or reminder policy could claw back.

Q6. When are we busiest, and when should we add more vehicles?

SELECT EXTRACT(HOUR FROM departure_time::time) AS hour_of_day,
       COUNT(*) AS total_bookings,
       SUM(seats_booked) AS total_passengers
FROM v_clean_trips
GROUP BY EXTRACT(HOUR FROM departure_time::time)
ORDER BY total_passengers DESC;

hour	total_bookings	total_passengers
10	27	51
6	29	50
9	27	48
8	25	46
19	25	46
12	24	44
7	24	43
16	21	42

10 a.m. is the single busiest departure hour, with 6 a.m. right behind it, exactly as you'd expect from a network anchored on Nairobi.

Breaking it down further by day and hour sharpens the picture:

SELECT day_name, EXTRACT(HOUR FROM departure_time::time) AS hour_of_day,
       COUNT(*) AS total_bookings,
       SUM(seats_booked) AS total_passengers
FROM v_clean_trips
GROUP BY day_name, EXTRACT(HOUR FROM departure_time::time)
ORDER BY total_passengers DESC;

day_name	hour	total_bookings	total_passengers
Thursday	10	9	20
Monday	19	9	16
Monday	12	7	15
Wednesday	8	9	15
Tuesday	6	10	14
Wednesday	17	7	13

Thursday at 10 a.m. is the single busiest slot in the entire dataset. If Safari Connect is going to add a vehicle anywhere, it's that slot, and the Monday evening rush right behind it is where it pays for itself fastest.

Chapter 5: What the CEO Actually Needs to Hear

Strip away the SQL and six sentences remain:

RT001 (Nairobi–Mombasa) is the crown jewel. It brings in the highest revenue, highest revenue-per-seat, and roughly a quarter of all route income. Protect it.
RT005 (Nairobi–Thika) is the volume play. It holds the most bookings, most seats, lowest revenue. It's a different business model wearing the same uniform so price it like one.
Driver star ratings don't predict passenger satisfaction (correlation ≈ -0.101). Promote on revenue and completed trips, not on stars alone.
Revenue is trending up but noisy, with October as the peak and August as the trough. It's not a straight line, so month-to-month swings shouldn't trigger panic on their own.
Cancellations cost KES 32,150.00 across the year, concentrated on RT006 and, in absolute terms, RT001. A deposit or reminder policy targeted at these two routes is the highest-leverage fix available.
Thursday 10 a.m. is the network's busiest single slot. That's where the next vehicle belongs.

The Part That Doesn't Show Up in a Query

None of the numbers above exist without the tedious work that came first i.e. the TRIM(), the REGEXP_REPLACE, the careful SELECT before every DELETE. It's tempting to treat data cleaning as a chore standing between an analyst and the "real" analysis. This project is a good argument against that instinct: every single business answer above; the top route, the best driver or the busiest hour would have been wrong, or at least unreliable, on the raw 290-row export. 99% of the credibility in this presentation was earned in Chapter 2, not Chapter 4.

Matatus and buses move millions of Kenyans every day. Somewhere behind the app, the M-Pesa prompt, and the seat selection screen, there is a table like this one, quietly deciding whether the person running the company can trust their own numbers. In this instance, for Safari Connect, it finally can.

Handling NULL Values in SQL

David Mwandairo — Tue, 22 Sep 2026 15:53:20 +0000

Walk into any record store and you'll find a shelf of vinyl waiting to be catalogued. Some sleeves have no price sticker. Some have no year stamped on the label. One has clearly been played a hundred times but nobody wrote down its condition. The store still owns these records. The data about them is just missing.

That's what a NULL is in SQL: not zero, not an empty string, not "we checked and there's nothing there." It means "we don't know." And if you don't handle that gap on purpose, your queries will handle it for you, usually in ways you didn't ask for.

Here's the database behind our example. A small shop called The Vinyl Vault keeps its stock in one table:

CREATE TABLE records (
    record_id      INT PRIMARY KEY,
    title           VARCHAR(100),
    artist          VARCHAR(100),
    pressing_year   INT,
    condition_grade VARCHAR(20),
    list_price      DECIMAL(6,2),
    sale_price      DECIMAL(6,2),
    units_in_stock  INT
);

The owner enters stock with an INSERT statement like this one, leaving a cell out entirely whenever that detail isn't known yet:

INSERT INTO records
    (record_id, title, artist, pressing_year, condition_grade, list_price, sale_price, units_in_stock)
VALUES
    (1, 'Blue Train', 'John Coltrane', 1957, 'VG+', 45.00, NULL, 3),
    (2, 'Songs in the Key of Life', 'Stevie Wonder', NULL, 'NM', 60.00, 48.00, 1),
    (3, 'Unknown Pleasures', 'Joy Division', 1979, NULL, 30.00, NULL, 0),
    (4, 'Blue', 'Joni Mitchell', 1971, 'VG', NULL, 22.00, 2),
    (5, 'Kind of Blue', 'Miles Davis', 1959, 'NM', 55.00, NULL, 4);

Notice that NULL here is a keyword, not a quoted string. Write NULL on its own, never 'NULL'. Quoting it would insert the four-character text "NULL" into the column, which is a value like any other and breaks every one of the checks below. Once that statement runs, the table looks like this:

record_id	title	artist	pressing_year	condition_grade	list_price	sale_price	units_in_stock
1	Blue Train	John Coltrane	1957	VG+	45.00	NULL	3
2	Songs in the Key of Life	Stevie Wonder	NULL	NM	60.00	48.00	1
3	Unknown Pleasures	Joy Division	1979	NULL	30.00	NULL	0
4	Blue	Joni Mitchell	1971	VG	NULL	22.00	2
5	Kind of Blue	Miles Davis	1959	NM	55.00	NULL	4

Four columns here have gaps: a missing pressing year, a missing condition grade, a missing list price, a missing sale price. Each gap tells a different story. Maybe the owner hasn't graded the sleeve yet. Maybe a record isn't on sale. Maybe nobody has looked up its original pressing date. SQL doesn't know or care why the value is missing, and that's exactly the trap.

Why this matters

Try to find every record with no listed price using the instinct most people bring from everyday logic:

SELECT title FROM records WHERE list_price = NULL;

This returns nothing. Not an error, not a warning. NULL isn't a value you can compare with =, because comparing "unknown" to anything, even to another unknown, produces "unknown". SQL treats an unknown result the same as false when deciding whether to include a row. The query runs, the shop owner sees an empty result set, and walks away thinking every record has a price while it doesn't.

Aggregate functions carry the same trap. Count the shop's inventory:

SELECT COUNT(*) AS total_records, COUNT(sale_price) AS records_on_sale
FROM records;

Output:
| total_records | records_on_sale |
|---|---|
| 5 | 2 |

COUNT(*) counts rows. COUNT(sale_price) counts only the rows where that column has an actual value as NULLs get skipped. Both numbers are correct for what they measure, but if you meant to ask "how many records are on sale" and wrote COUNT(*) by habit, you'd report five instead of two. Averages have the same quirk: AVG(sale_price) divides by the number of non-NULL rows, not by every row in the table. Miss that, and a small sale section can look like it's pulling in more revenue per item than it really is.

None of this is a bug. It's SQL being consistent about what "I don't know" means. The job is to decide, column by column, what should happen when the value isn't there and that's where a handful of functions earn their keep.

The toolkit

COALESCE: the standard fallback

COALESCE takes a list of values and returns the first one that isn't NULL. It works the same way across MySQL, PostgreSQL, SQL Server, SQLite, and Oracle, which makes it the one worth learning first.

Say the shop wants a clean price list for a printed flyer. A record on sale should show its sale price; anything else should show its list price. Anything with neither should read "Ask staff":

SELECT
    title,
    COALESCE(sale_price, list_price, 0) AS display_price
FROM records;

Output:
| title | display_price |
|---|---|
| Blue Train | 45.00 |
| Songs in the Key of Life | 48.00 |
| Unknown Pleasures | 30.00 |
| Blue | 22.00 |
| Kind of Blue | 55.00 |

For Coltrane's Blue Train, sale_price is NULL, so COALESCE moves to the next argument and prints the list price instead. For Stevie Wonder's record, the sale price exists, so that's what shows. This is the same logic as checking a stack of fallback options in order and stopping at the first one that's actually filled in. There's nothing more mysterious than that.

ISNULL and IFNULL: the single-fallback shortcut

Where COALESCE accepts any number of arguments, ISNULL (SQL Server) and IFNULL (MySQL, SQLite) take exactly two: a value and what to use if that value is NULL. They read a little more plainly for the simple case:

-- SQL Server
SELECT title, ISNULL(pressing_year, 0) AS year_known
FROM records;

-- MySQL / SQLite
SELECT title, IFNULL(pressing_year, 0) AS year_known
FROM records;

Output:
| title | year_known |
|---|---|
| Blue Train | 1957 |
| Songs in the Key of Life | 0 |
| Unknown Pleasures | 1979 |
| Blue | 1971 |
| Kind of Blue | 1959 |

Stevie Wonder's pressing year is unknown, so the placeholder 0 shows up instead of a blank cell. That's fine for a quick script. It's a poor choice for a customer-facing report, since a shopper reading "0" might think the record was pressed in the year zero rather than "we don't have that on file." A text placeholder communicates the gap far better:

SELECT title, IFNULL(condition_grade, 'Not yet graded') AS grade
FROM records;

Output:
| title | grade |
|---|---|
| Blue Train | VG+ |
| Songs in the Key of Life | NM |
| Unknown Pleasures | Not yet graded |
| Blue | VG |
| Kind of Blue | NM |

Because ISNULL/IFNULL only run on two engines a piece and only take two arguments, COALESCE stays the safer default for anything that needs to run on more than one database or needs more than one fallback.

NULLIF: turning a value into NULL on purpose

NULLIF runs the opposite direction. It compares two values, and if they match, it returns NULL instead of the value. This sounds backwards until you hit the one problem it exists to solve: division by zero.

The shop wants to know how many units of each record are still worth restocking, based on the ratio of stock sold to stock on hand. A record with zero units in stock would divide by zero and crash the report:

SELECT
    title,
    units_in_stock,
    list_price / NULLIF(units_in_stock, 0) AS price_per_unit
FROM records;

Output:
| title | units_in_stock | price_per_unit |
|---|---|---|
| Blue Train | 3 | 15.00 |
| Songs in the Key of Life | 1 | 60.00 |
| Unknown Pleasures | 0 | NULL |
| Blue | 2 | NULL |
| Kind of Blue | 4 | 13.75 |

For Unknown Pleasures, units_in_stock is 0, so NULLIF swaps it for NULL before the division runs. Dividing by NULL returns NULL rather than throwing an error, so the query finishes and simply reports "not applicable" for that row. Joni Mitchell's Blue returns NULL for a different reason: its list_price is missing, so the division has nothing to work with regardless of stock count. Two different gaps, same NULL result — which is exactly why it helps to combine tools rather than lean on just one.

Putting it together

Real reports rarely need just one of these functions. Here's the shop's actual end-of-week summary query, which prices each record for the shelf tag and flags anything still missing key details:

SELECT
    title,
    artist,
    COALESCE(CAST(pressing_year AS VARCHAR), 'year unknown') AS pressing_year,
    COALESCE(condition_grade, 'ungraded') AS condition_grade,
    COALESCE(sale_price, list_price, 0) AS shelf_price,
    CASE
        WHEN pressing_year IS NULL OR condition_grade IS NULL OR list_price IS NULL
        THEN 'Needs cataloguing'
        ELSE 'Ready'
    END AS status
FROM records;

Output:
| title | artist | pressing_year | condition_grade | shelf_price | status |
|---|---|---|---|---|---|
| Blue Train | John Coltrane | 1957 | VG+ | 45.00 | Ready |
| Songs in the Key of Life | Stevie Wonder | year unknown | NM | 48.00 | Needs cataloguing |
| Unknown Pleasures | Joy Division | 1979 | ungraded | 30.00 | Needs cataloguing |
| Blue | Joni Mitchell | 1971 | VG | 22.00 | Needs cataloguing |
| Kind of Blue | Miles Davis | 1959 | NM | 55.00 | Ready |

One query, four columns cleaned up, and a status flag built from IS NULL checks that tells the shop owner exactly which records still need attention before they go on the shelf. Nothing here is exotic. It's the same handful of functions, applied with a clear idea of what each blank cell is supposed to mean once it reaches a human reader.

That's really the whole discipline: NULL isn't a data quality failure to panic over, and it isn't something to paper over with a random default either. It's a fact about the world — a record whose year nobody wrote down, a price nobody set yet — and your query should say so, on purpose, in whatever way fits the report you're building.

Window Functions: Seeing the Group Without Losing the Row

David Mwandairo — Tue, 22 Sep 2026 13:25:31 +0000

GROUP BY has one serious limitation: the moment it collapses rows into a summary, the detail is gone. You can find out that hive 101 produced 36.5 kg this year, but not which harvest was the biggest, or how much honey flow grew between visits, not without a second query. Window functions exist to answer exactly that kind of question: calculations that need to see an entire group, but still need to hand back an answer for every row in it.

To have a better understanding of the examples used in this article, have a look at these articles:

The four original harvests stay exactly as they were. Four new ones were added so each hive has enough history for a window function to actually do something interesting with it.

INSERT INTO harvests (harvest_id, hive_id, harvest_date, honey_kg, notes) VALUES
('H5', 102, '2024-08-01', 11.0, 'Late summer harvest'),
('H6', 103, '2024-07-20', 6.5,  'First harvest from this hive'),
('H7', 103, '2024-09-10', 8.0,  'Second harvest, steady growth'),
('H8', 101, '2024-11-01', 15.0, 'End of season harvest, strong flow');

harvests, in full

harvest_id	hive_id	harvest_date	honey_kg
H1	101	2024-05-01	12.5
H2	101	2024-09-01	9.0
H3	102	2024-06-15	15.0
H4	999	2024-07-01	5.0
H5	102	2024-08-01	11.0
H6	103	2024-07-20	6.5
H7	103	2024-09-10	8.0
H8	101	2024-11-01	15.0

Hive 999 is still the orphaned hive_id from the joins article, with no matching row in hives. It's staying in this dataset on purpose. A window function doesn't care whether a row has a valid foreign key anywhere else, it just processes whatever rows the query gives it.

The Anatomy of a Window Function

Every window function follows the same shape: a function, followed by OVER (...), followed optionally by PARTITION BY and ORDER BY inside those parentheses. Each piece answers a different question.

PARTITION BY decides which rows belong together the same way GROUP BY would. ORDER BY inside OVER() decides the sequence within that group, which matters enormously for anything about position, ranking, or what came before. Leaving both of them out leads to the function treating the entire result set as one window.

Here's the clearest way to see PARTITION BY and ORDER BY working together: number each hive's harvests in the order they happened.

SELECT hive_id, harvest_id, harvest_date, honey_kg,
    ROW_NUMBER() OVER (PARTITION BY hive_id ORDER BY harvest_date) AS harvest_seq
FROM harvests
ORDER BY hive_id, harvest_date;

Result:

hive_id	harvest_id	harvest_date	honey_kg	harvest_seq
101	H1	2024-05-01	12.5	1
101	H2	2024-09-01	9.0	2
101	H8	2024-11-01	15.0	3
102	H3	2024-06-15	15.0	1
102	H5	2024-08-01	11.0	2
103	H6	2024-07-20	6.5	1
103	H7	2024-09-10	8.0	2
999	H4	2024-07-01	5.0	1

Eight rows went in, eight rows came out, nothing collapsed. PARTITION BY hive_id reset the counter for every hive, and ORDER BY harvest_date decided which harvest counts as number one within each. Filter this result to harvest_seq = 1 and you have a clean list of each hive's very first harvest, useful for a "new hive is now productive" milestone report, without a single subquery.

Ranking Functions: ROW_NUMBER, RANK, DENSE_RANK

ROW_NUMBER above never repeats a number, even when two rows are genuinely tied. RANK and DENSE_RANK handle ties differently, and the difference only shows up when a tie actually exists. Ranking the hives by their single best harvest produces one:

SELECT hive_id,
    MAX(honey_kg) AS best_harvest_kg,
    RANK()       OVER (ORDER BY MAX(honey_kg) DESC) AS honey_rank,
    DENSE_RANK() OVER (ORDER BY MAX(honey_kg) DESC) AS honey_dense_rank
FROM harvests
GROUP BY hive_id
ORDER BY best_harvest_kg DESC, hive_id;

Result:

hive_id	best_harvest_kg	honey_rank	honey_dense_rank
101	15.0	1	1
102	15.0	1	1
103	8.0	3	2
999	5.0	4	3

Hive 101's best single harvest, H8 at 15.0 kg, ties exactly with hive 102's H3. RANK gives both of them rank 1, then skips straight to rank 3 for the next hive, counting the two tied rows as if they'd occupied ranks 1 and 2. DENSE_RANK also gives both hives rank 1, but the next distinct value gets rank 2, no gap. Which one you want depends on the question: "top 3 hives by best harvest" reads differently depending on whether a tie for first should use up a slot.

NTILE takes a different approach to ranking: instead of ordering, it splits rows into a fixed number of equal-sized buckets. Say the co-op wants to flag its two weakest hives by total output for a closer inspection this winter:

SELECT hive_id, SUM(honey_kg) AS total_kg,
    NTILE(2) OVER (ORDER BY SUM(honey_kg) DESC) AS performance_tier
FROM harvests
GROUP BY hive_id
ORDER BY total_kg DESC;

Result:

hive_id	total_kg	performance_tier
101	36.5	1
102	26.0	1
103	14.5	2
999	5.0	2

NTILE(2) divides the four hives into two tiers of two. Hives 103 and 999 land in tier 2, the co-op's shortlist for a winter health check, generated without a single manually chosen cutoff value.

Offset Functions: LAG and LEAD

Ranking functions look at position. LAG and LEAD look sideways, at the row immediately before or after the current one, within the same partition and order. Hive 101 now has three harvests across the season, enough to ask how each one compares to its neighbors.

SELECT harvest_id, harvest_date, honey_kg,
    LAG(honey_kg)  OVER (PARTITION BY hive_id ORDER BY harvest_date) AS prev_kg,
    ROUND(honey_kg - LAG(honey_kg) OVER (PARTITION BY hive_id ORDER BY harvest_date), 1) AS change_kg,
    LEAD(honey_kg) OVER (PARTITION BY hive_id ORDER BY harvest_date) AS next_kg
FROM harvests
WHERE hive_id = 101
ORDER BY harvest_date;

Result:

harvest_id	harvest_date	honey_kg	prev_kg	change_kg	next_kg
H1	2024-05-01	12.5	NULL	NULL	9.0
H2	2024-09-01	9.0	12.5	-3.5	15.0
H8	2024-11-01	15.0	9.0	6.0	NULL

LAG reaches backward, LEAD reaches forward, and both return NULL at the edges of the partition, there's nothing before H1 or after H8 for this hive. The change_kg column, honey this time minus honey last time, is the real payoff: a dip of 3.5 kg in September followed by a jump of 6.0 kg by November tells the beekeeper more than the raw totals ever would, and it took no self-join and no subquery to produce.

Aggregate Functions as Window Functions: Frames

Any aggregate function, SUM, AVG, COUNT, can run as a window function too, and this is where the frame clause matters, the part of OVER() that defines exactly which neighboring rows a calculation includes. Left unspecified with an ORDER BY present, the default frame runs from the start of the partition to the current row, which is what produces a running total. Specify the frame explicitly, and you can build something more targeted, like a two-harvest moving average across the whole co-op's season:

SELECT harvest_id, harvest_date, honey_kg,
    ROUND(AVG(honey_kg) OVER (
        ORDER BY harvest_date
        ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
    ), 2) AS moving_avg_2
FROM harvests
ORDER BY harvest_date;

Result:

harvest_id	harvest_date	honey_kg	moving_avg_2
H1	2024-05-01	12.5	12.5
H3	2024-06-15	15.0	13.75
H4	2024-07-01	5.0	10.0
H6	2024-07-20	6.5	5.75
H5	2024-08-01	11.0	8.75
H2	2024-09-01	9.0	10.0
H7	2024-09-10	8.0	8.5
H8	2024-11-01	15.0	11.5

No PARTITION BY this time, so every harvest across every hive is treated as one continuous timeline, a co-op-wide honey flow trend rather than a per-hive one. ROWS BETWEEN 1 PRECEDING AND CURRENT ROW tells the database exactly which two rows to average at each step: the current one and the one right before it. The dip after H3's bumper 15.0 kg harvest and the climb back up toward H8 are both visible in the smoothed column in a way the raw honey_kg values alone don't show as clearly.

When to Reach for a Window Function

You need to...	Use
Number or sequence rows within a group, without collapsing it	`ROW_NUMBER()`
Rank rows, and gaps after ties are acceptable	`RANK()`
Rank rows, and ties shouldn't create gaps	`DENSE_RANK()`
Split rows into a fixed number of equal buckets	`NTILE(n)`
Compare a row to the one before or after it	`LAG()` / `LEAD()`
Get a running total, moving average, or other rolling calculation	Aggregate function `OVER (...)` with a frame

Try It Yourself

Take the moving-average query and change the frame to ROWS BETWEEN 2 PRECEDING AND CURRENT ROW, a three-harvest average instead of two. Watch how much smoother the trend line gets, and how much more it lags behind sudden changes like H3's bumper crop. That trade-off, smoothness against responsiveness, is the real decision behind every moving average, in a spreadsheet or in SQL.

SQL Functions: The Toolbox Hiding Inside Every SELECT

David Mwandairo — Tue, 22 Sep 2026 12:36:46 +0000

Most of what a database does to your data happens between SELECT and FROM: turning a name into an email address, a date into a month, a pile of raw numbers into an average worth reporting. That work is done by functions, and most SQL beginners use a handful of them for months, COUNT, SUM, maybe UPPER, without ever noticing that "function" is a real, well-defined idea with its own categories and rules.

We'll stay with the beekeeping co-op from the earlier articles I wrote here and here. The notes column below is new; everything else should look familiar. Here's the script that added it:

ALTER TABLE harvests ADD COLUMN notes VARCHAR(200);

UPDATE harvests SET notes = '  Extracted in the afternoon, slight rain  ' WHERE harvest_id = 'H1';
UPDATE harvests SET notes = 'Morning harvest, strong yield'                WHERE harvest_id = 'H2';
UPDATE harvests SET notes = '  BUMPER CROP after spring bloom  '           WHERE harvest_id = 'H3';
UPDATE harvests SET notes = 'Logged by hand, hive number unclear'         WHERE harvest_id = 'H4';

The Data, With One Addition

beekeepers

keeper_id	name	mentor_id
1	Amara Wanjiru	NULL
2	Brian Otieno	1
3	Chiara Mwangi	1
4	David Kimani	NULL
5	Grace Achieng	2

hives

hive_id	keeper_id	location	established_year
101	1	Ruiru Rooftop	2021
102	2	Thika Road Garden	2022
103	3	Kiambu Backyard	2023
104	99	Abandoned Lot	2020

harvests

harvest_id	hive_id	harvest_date	honey_kg	notes
H1	101	2024-05-01	12.5	`" Extracted in the afternoon, slight rain "`
H2	101	2024-09-01	9.0	`"Morning harvest, strong yield"`
H3	102	2024-06-15	15.0	`" BUMPER CROP after spring bloom "`
H4	999	2024-07-01	5.0	`"Logged by hand, hive number unclear"`

Whoever logs harvests by hand isn't consistent about spacing or capitalization. That's not an accident on my part, real free-text fields look exactly like this, and it gives the string functions below something honest to clean up.

What a Function Actually Is

A SQL function takes input, does something deterministic to it, and returns a value. That's the whole definition, but it hides an important split. Functions fall into three behaviors, and confusing them is where most beginner mistakes come from.

Scalar functions run once per row and return one value per row. UPPER('bee') doesn't care what else is in the table; feed it a string, it hands back a string, row by row.

Aggregate functions collapse many rows into one. SUM(honey_kg) doesn't return a value for each row, it consumes the whole group and returns a single number for it. This is why you can't mix a raw column with an aggregate function in the same SELECT without a GROUP BY, the database can't return one row of totals sitting next to twenty rows of detail at the same time.

Window functions are the middle ground: they look across a group of rows, like an aggregate does, but they return a value for every row instead of collapsing them. You'll see the difference directly in the examples below.

Scalar Functions: Cleaning and Shaping Text

The co-op needs a keeper@murchisonapiary.coop email address for everyone, generated from their name rather than typed in by hand.

SELECT name,
    LOWER(REPLACE(name, ' ', '.')) || '@murchisonapiary.coop' AS coop_email
FROM beekeepers;

Result:

name	coop_email
Amara Wanjiru	amara.wanjiru@murchisonapiary.coop
Brian Otieno	brian.otieno@murchisonapiary.coop
Chiara Mwangi	chiara.mwangi@murchisonapiary.coop
David Kimani	david.kimani@murchisonapiary.coop
Grace Achieng	grace.achieng@murchisonapiary.coop

Three scalar functions, nested inside each other, run on every row: REPLACE swaps the space for a period, LOWER normalizes the case, and || glues the domain on the end. (MySQL doesn't support || for concatenation by default; use CONCAT() there instead.)

Now clean up the messy notes field:

SELECT harvest_id, TRIM(notes) AS clean_note, LENGTH(TRIM(notes)) AS note_length
FROM harvests;

Result:

harvest_id	clean_note	note_length
H1	Extracted in the afternoon, slight rain	39
H2	Morning harvest, strong yield	29
H3	BUMPER CROP after spring bloom	30
H4	Logged by hand, hive number unclear	35

TRIM removes the leading and trailing spaces someone left when they typed H1 and H3 by hand; LENGTH then measures the cleaned-up result. Run LENGTH before TRIM and H1 would report 43, the padding spaces counted as real characters. The order you nest functions in changes the answer, not just the syntax.

Aggregate Functions: Turning Rows Into a Summary

SELECT
    COUNT(*) AS harvest_count,
    SUM(honey_kg) AS total_kg,
    ROUND(AVG(honey_kg), 2) AS avg_kg,
    MIN(honey_kg) AS smallest_kg,
    MAX(honey_kg) AS largest_kg
FROM harvests;

Result:

harvest_count	total_kg	avg_kg	smallest_kg	largest_kg
4	41.5	10.38	5.0	15.0

Four rows go in, one row comes out. That's the aggregate signature: COUNT, SUM, AVG, MIN, and MAX all take a column and reduce it, whatever number of rows they're given, to a single number. Notice ROUND wrapping AVG here too, a scalar function applied to the result of an aggregate one. Functions compose like this constantly; there's no rule against feeding one function's output into another.

Aggregates get more useful paired with GROUP BY, which runs the same collapse once per group instead of once for the whole table:

SELECT hive_id,
    SUM(honey_kg) AS total_kg,
    FLOOR(SUM(honey_kg) * 1000 / 350) AS jars_of_350g
FROM harvests
GROUP BY hive_id
ORDER BY hive_id;

Result:

hive_id	total_kg	jars_of_350g
101	21.5	61
102	15.0	42
999	5.0	14

SUM collapses each hive's harvests into a total, and FLOOR figures out how many complete 350-gram jars that total fills, rounding down because a co-op can't sell three-quarters of a jar. Hive 999 shows up here because this query only looks at harvests, a reminder from the joins article that this hive_id has no matching row in hives at all.

Date Functions: Reading Time Out of a Timestamp

SELECT harvest_id, harvest_date, EXTRACT(MONTH FROM harvest_date) AS harvest_month
FROM harvests
ORDER BY harvest_date;

Result:

harvest_id	harvest_date	harvest_month
H1	2024-05-01	5
H3	2024-06-15	6
H4	2024-07-01	7
H2	2024-09-01	9

EXTRACT pulls a single component out of a date, useful the moment you want to group harvests by month rather than by exact day, spotting a June bloom pattern buried inside four rows is impossible by eye, but trivial once EXTRACT(MONTH FROM ...) feeds into a GROUP BY.

Dates also support direct arithmetic. Assume today is 2024-10-01, and the co-op wants to know how stale each harvest log is:

SELECT harvest_id, harvest_date, ('2024-10-01' - harvest_date) AS days_since_harvest
FROM harvests
ORDER BY harvest_date;

Result:

harvest_id	harvest_date	days_since_harvest
H1	2024-05-01	153
H3	2024-06-15	108
H4	2024-07-01	92
H2	2024-09-01	30

Subtracting two dates in PostgreSQL returns a plain integer number of days. MySQL wants DATEDIFF('2024-10-01', harvest_date), and SQL Server wants DATEDIFF(day, harvest_date, '2024-10-01'), same idea, different name and argument order per engine.

Conditional Functions: Branching Without Leaving SQL

SELECT harvest_id, honey_kg,
    CASE
        WHEN honey_kg < 8  THEN 'Small'
        WHEN honey_kg < 13 THEN 'Medium'
        ELSE 'Large'
    END AS harvest_size
FROM harvests
ORDER BY honey_kg;

Result:

harvest_id	honey_kg	harvest_size
H4	5.0	Small
H2	9.0	Medium
H1	12.5	Medium
H3	15.0	Large

CASE checks its conditions top to bottom and stops at the first match, which is why the boundaries (< 8, < 13) don't need to repeat the lower bound of each bucket.

COALESCE solves a narrower, more common problem: what to display in place of a NULL.

SELECT b.name, COALESCE(m.name, 'Founding member') AS mentor_name
FROM beekeepers b
LEFT JOIN beekeepers m ON b.mentor_id = m.keeper_id;

Result:

name	mentor_name
Amara Wanjiru	Founding member
Brian Otieno	Amara Wanjiru
Chiara Mwangi	Amara Wanjiru
David Kimani	Founding member
Grace Achieng	Brian Otieno

The self join finds each keeper's mentor by name, and returns NULL for the two keepers who don't have one. COALESCE takes that NULL and replaces it with the first non-null value in its argument list, here just one fallback string, though COALESCE accepts as many candidates as you give it and returns the first one that isn't NULL.

Window Functions: Per-Row Answers That Still See the Group

SELECT hive_id,
    SUM(honey_kg) AS total_kg,
    RANK() OVER (ORDER BY SUM(honey_kg) DESC) AS honey_rank
FROM harvests
GROUP BY hive_id;

Result:

hive_id	total_kg	honey_rank
101	21.5	1
102	15.0	2
999	5.0	3

This looks like the GROUP BY query from earlier, and it starts the same way, but RANK() OVER (...) doesn't collapse anything further. It ranks each already-grouped row against the others and keeps every row visible. Try to get a rank number next to a raw total using only GROUP BY and an aggregate, and you can't; the aggregate would need to already know every group's total before ranking any of them, which is exactly the problem window functions exist to solve.

The same idea works without collapsing rows at all, tracking a running total across a hive's season:

SELECT harvest_id, harvest_date, honey_kg,
    SUM(honey_kg) OVER (PARTITION BY hive_id ORDER BY harvest_date) AS running_total_kg
FROM harvests
WHERE hive_id = 101
ORDER BY harvest_date;

Result:

harvest_id	harvest_date	honey_kg	running_total_kg
H1	2024-05-01	12.5	12.5
H2	2024-09-01	9.0	21.5

Both rows from hive 101 survive, unlike a plain SUM(honey_kg) GROUP BY hive_id, which would flatten them into a single 21.5 and lose the harvest-by-harvest detail. PARTITION BY restarts the running total for each hive, and ORDER BY inside the OVER() clause decides the order the total accumulates in.

When to Reach for Each Kind

You need to...	Use
Clean, reformat, or combine text on every row	Scalar string functions
Do arithmetic or rounding on every row	Scalar numeric functions
Collapse many rows into one summary number	Aggregate functions
Pull a component out of a date, or measure a time gap	Date functions
Branch on a condition, or fill in a value for `NULL`	`CASE` / `COALESCE`
Rank, running-total, or compare rows without losing any rows	Window functions

Try It Yourself

Take the notes column and write a query that extracts just the first word of each note, using whatever substring function your database offers, SPLIT_PART, SUBSTRING_INDEX, or LEFT combined with a search for the first space. It's a small problem, but it forces you to nest two or three scalar functions correctly, which is the skill this whole article has really been about.

Subqueries and CTEs: Asking a Question Inside a Question

David Mwandairo — Mon, 21 Sep 2026 19:00:13 +0000

Some questions can't be answered in one pass. "Which hive produced the most honey?" needs the maximum honey figure before it can find the hive that matches it. "Which keepers are above average?" needs the average before it can compare anyone to it. SQL handles this the same way you'd handle it on paper: work out the smaller number first, then use it.

That's what a subquery is. A CTE does the same job with a different shape. We'll reuse the beekeeping co-op from the joins article, so the data below should look familiar, with one addition that makes the later examples worth running.

The Data, Recapped

See the tables used for this article here.
beekeepers

keeper_id	name	mentor_id
1	Amara Wanjiru	NULL
2	Brian Otieno	1
3	Chiara Mwangi	1
4	David Kimani	NULL
5	Grace Achieng	2

Grace Achieng is new. She's Brian's apprentice, which gives the co-op a three-generation mentorship line: Amara trained Brian, and Brian is now training Grace. We'll need that depth later.

hives

hive_id	keeper_id	location	established_year
101	1	Ruiru Rooftop	2021
102	2	Thika Road Garden	2022
103	3	Kiambu Backyard	2023
104	99	Abandoned Lot	2020

harvests

harvest_id	hive_id	harvest_date	honey_kg
H1	101	2024-05-01	12.5
H2	101	2024-09-01	9.0
H3	102	2024-06-15	15.0
H4	999	2024-07-01	5.0

What a Subquery Is

A subquery is a query nested inside another one, wrapped in parentheses. The database runs it, gets a result, and hands that result to the outer query as if it had been typed there directly. Depending on where it sits, a subquery can return a single value, a list of values, or an entire result set standing in for a table.

A scalar subquery: finding the single best harvest

SELECT hive_id, harvest_date, honey_kg
FROM harvests
WHERE honey_kg = (SELECT MAX(honey_kg) FROM harvests);

Result:

hive_id	harvest_date	honey_kg
102	2024-06-15	15.0

The inner query, SELECT MAX(honey_kg) FROM harvests, runs first and reduces to one number: 15.0. The outer query never sees the subquery at all once it's been resolved. A subquery that returns exactly one value is called a scalar subquery, and it can sit almost anywhere a literal number could.

A subquery with IN: hives that have never been harvested

SELECT hive_id, location
FROM hives
WHERE hive_id NOT IN (SELECT hive_id FROM harvests);

Result:

hive_id	location
103	Kiambu Backyard
104	Abandoned Lot

Here the inner query returns a list, (101, 101, 102, 999), and NOT IN checks each hive against that list. Hives 103 and 104 never appear in harvests, so they survive the filter. One thing worth flagging is that NOT IN turns dangerous the moment the subquery's list can contain a NULL, since NOT IN against a list containing NULL returns no rows at all, silently. harvests.hive_id is never NULL here, so it's safe, but NOT EXISTS is the safer default habit for this exact pattern once nullable columns get involved.

A correlated subquery: each hive's most recent harvest

SELECT h1.hive_id, h1.harvest_date, h1.honey_kg
FROM harvests h1
WHERE h1.harvest_date = (
    SELECT MAX(h2.harvest_date)
    FROM harvests h2
    WHERE h2.hive_id = h1.hive_id
);

Result:

hive_id	harvest_date	honey_kg
101	2024-09-01	9.0
102	2024-06-15	15.0
999	2024-07-01	5.0

This one is different in kind, not just in syntax. The inner query references h1.hive_id, a column from the outer query, so it can't run once and be done. The database runs it fresh for every row in harvests, filtered to that row's hive. Hive 101 has two harvest dates, and this picks the later one. This "latest record per group" shape shows up constantly: latest login per user, latest price per product, latest reading per sensor.

A correlated subquery in SELECT: hive count per keeper

SELECT b.name,
    (SELECT COUNT(*) FROM hives h WHERE h.keeper_id = b.keeper_id) AS hive_count
FROM beekeepers b;

Result:

name	hive_count
Amara Wanjiru	1
Brian Otieno	1
Chiara Mwangi	1
David Kimani	0
Grace Achieng	0

Same correlation, different location. The subquery sits in the column list this time, running once per row of beekeepers and returning a single number for each. David and Grace get 0, not NULL, because COUNT(*) on an empty match still counts to zero rather than finding nothing.

A subquery in FROM: a derived table

SELECT h.location, hive_totals.total_honey_kg
FROM hives h
JOIN (
    SELECT hive_id, SUM(honey_kg) AS total_honey_kg
    FROM harvests
    GROUP BY hive_id
) AS hive_totals ON h.hive_id = hive_totals.hive_id
WHERE hive_totals.total_honey_kg > 10;

Result:

location	total_honey_kg
Ruiru Rooftop	21.5
Thika Road Garden	15.0

The subquery in the FROM clause builds a small temporary table, hive_totals, that the outer query joins against like any other table. Note that hive 999's total of 5.0 kg quietly vanishes here: it has no matching row in hives, so the JOIN drops it before the WHERE clause even runs. A derived table behaves exactly like a real one for the rest of the query, it just doesn't exist anywhere except for the duration of this statement.

What a CTE Is

A CTE, common table expression, does the same job as a subquery i.e. it names a temporary result set for one query to use. The difference is where you write it. Instead of nesting it inside the query, you declare it up front with WITH, give it a name, and then write the rest of the query as if that name were a real table.

Rewriting the derived-table example above as a CTE:

WITH hive_totals AS (
    SELECT hive_id, SUM(honey_kg) AS total_honey_kg
    FROM harvests
    GROUP BY hive_id
)
SELECT h.location, hive_totals.total_honey_kg
FROM hives h
JOIN hive_totals ON h.hive_id = hive_totals.hive_id
WHERE hive_totals.total_honey_kg > 10;

Result:

location	total_honey_kg
Ruiru Rooftop	21.5
Thika Road Garden	15.0

Identical output, identical execution in most modern databases. The value here isn't a new capability, it's that you now read the query top to bottom: first, here's what hive_totals means; then, here's what to do with it. Once a query needs three or four steps, that ordering stops being a nicety and starts being the difference between a query you can debug and one you can't.

Where CTEs pull ahead: using the same result twice

Go back to an earlier goal: find keepers whose total honey is above the co-op average. Written with nested subqueries alone, the "total honey per keeper" calculation has to appear twice, once to list it, once to average it:

SELECT name, total_honey_kg
FROM (
    SELECT b.name, COALESCE(SUM(hv.honey_kg), 0) AS total_honey_kg
    FROM beekeepers b
    LEFT JOIN hives h ON b.keeper_id = h.keeper_id
    LEFT JOIN harvests hv ON h.hive_id = hv.hive_id
    GROUP BY b.name
) AS keeper_totals
WHERE total_honey_kg > (
    SELECT AVG(total_honey_kg)
    FROM (
        SELECT b.name, COALESCE(SUM(hv.honey_kg), 0) AS total_honey_kg
        FROM beekeepers b
        LEFT JOIN hives h ON b.keeper_id = h.keeper_id
        LEFT JOIN harvests hv ON h.hive_id = hv.hive_id
        GROUP BY b.name
    ) AS t
);

It works, but the five-line calculation is now duplicated word for word. Change one join later and you have to remember to change it twice. A CTE removes the duplication by naming the calculation once and referencing it as often as needed:

WITH keeper_totals AS (
    SELECT b.name, COALESCE(SUM(hv.honey_kg), 0) AS total_honey_kg
    FROM beekeepers b
    LEFT JOIN hives h ON b.keeper_id = h.keeper_id
    LEFT JOIN harvests hv ON h.hive_id = hv.hive_id
    GROUP BY b.name
)
SELECT name, total_honey_kg
FROM keeper_totals
WHERE total_honey_kg > (SELECT AVG(total_honey_kg) FROM keeper_totals);

Result:

name	total_honey_kg
Amara Wanjiru	21.5
Brian Otieno	15.0

The co-op's average sits at 7.3 kg across all five keepers, including the three who've harvested nothing yet. Amara and Brian clear it; everyone else doesn't. Same logic as the nested version, defined once, and one CTE now stands in for what would otherwise be two identical subqueries.

Where a subquery can't follow: recursive CTEs

WITH RECURSIVE lets a CTE reference itself, which makes it the only one of the two that can walk a chain of unknown length. The mentorship data is exactly that shape: Amara trained Brian, Brian is training Grace, and nothing in the schema tells you in advance how many links that chain will have.

WITH RECURSIVE mentorship_chain AS (
    -- Anchor: keepers with no mentor, the top of each line
    SELECT keeper_id, name, mentor_id, name AS lineage, 0 AS depth
    FROM beekeepers
    WHERE mentor_id IS NULL

    UNION ALL

    -- Recursive step: find apprentices of keepers already in the chain
    SELECT b.keeper_id, b.name, b.mentor_id,
           mc.lineage || ' -> ' || b.name, mc.depth + 1
    FROM beekeepers b
    JOIN mentorship_chain mc ON b.mentor_id = mc.keeper_id
)
SELECT name, depth, lineage
FROM mentorship_chain
ORDER BY depth, name;

Result:

name	depth	lineage
Amara Wanjiru	0	Amara Wanjiru
David Kimani	0	David Kimani
Brian Otieno	1	Amara Wanjiru -> Brian Otieno
Chiara Mwangi	1	Amara Wanjiru -> Chiara Mwangi
Grace Achieng	2	Amara Wanjiru -> Brian Otieno -> Grace Achieng

The anchor half of the query finds Amara and David, the two keepers with no mentor. The recursive half then runs repeatedly: first it finds anyone whose mentor is Amara or David (Brian and Chiara), then it runs again on those new rows and finds anyone whose mentor is Brian or Chiara (Grace), then it runs once more, finds nobody new, and stops. Three generations, discovered without knowing in advance how many there'd be. No plain subquery can do this; a subquery is a single, fixed level of nesting, and there's no way to tell it "keep going until you run out of matches." That open-ended repetition is what RECURSIVE adds.

Note: (The || operator concatenates text in PostgreSQL and SQLite. SQL Server uses +, and MySQL uses CONCAT(), so adjust that one line for your engine; everything else here is standard SQL.)

Subquery or CTE: How to Choose

Situation	Reach for
A single value used once, inline, in a `WHERE` or `SELECT`	Subquery
The same intermediate result needed more than once in one query	CTE
A query more than two or three logical steps deep	CTE, for the readability
Walking a hierarchy or chain of unknown depth	Recursive CTE, no alternative
A quick, throwaway filter you'll never look at again	Subquery

The two aren't really competitors. A subquery is a value or a table, dropped exactly where you need it. A CTE is a name, declared once, that the rest of the query can refer to as often as it likes, and in the recursive case, refer to before it's even finished being built. Most real queries end up mixing both: a CTE or two to name the steps that matter, and small scalar subqueries wherever a single value needs to be looked up in passing.

One caution before you assume a CTE is always the faster choice: whether a database materializes a CTE (runs it once, stores the result) or inlines it (folds it into the surrounding query, like a subquery) depends on the engine and its version. PostgreSQL 12 and later will inline a non-recursive CTE when it can; older versions treated every CTE as an optimization fence. When performance is on the line, check your engine's execution plan rather than assuming either form is faster by default.

Try It Yourself

Add a sixth beekeeper as Grace's apprentice, rerun the recursive query, and watch the chain grow by one row without changing a single line of SQL. That's the property no ordinary subquery can offer, and it's the clearest way to feel the difference between the two.

SQL Joins Explained: A Beekeeping Co-op in Six Queries

David Mwandairo — Mon, 21 Sep 2026 17:54:35 +0000

A join is not a special SQL feature reserved for experts. It is the answer to a problem every relational database creates on purpose: your data lives in more than one table, and a single query rarely cares about table boundaries.

To see how each join behaves, forget sales orders and customer records for a moment. We'll run a small urban beekeeping co-op instead: a handful of keepers, their hives, and the honey those hives produce. The data is small enough to hold in your head, which makes it easier to see exactly what each join adds or drops.

The Setup: Three Tables That Don't Line Up Perfectly

Here's the schema.

CREATE TABLE beekeepers (
    keeper_id INT PRIMARY KEY,
    name      VARCHAR(50),
    mentor_id INT REFERENCES beekeepers(keeper_id)
);

CREATE TABLE hives (
    hive_id           INT PRIMARY KEY,
    keeper_id         INT REFERENCES beekeepers(keeper_id),
    location          VARCHAR(50),
    established_year  INT
);

CREATE TABLE harvests (
    harvest_id    VARCHAR(5) PRIMARY KEY,
    hive_id       INT,
    harvest_date  DATE,
    honey_kg      DECIMAL(5,1)
);

And the data, deliberately messy in a realistic way:

beekeepers

keeper_id	name	mentor_id
1	Amara Wanjiru	NULL
2	Brian Otieno	1
3	Chiara Mwangi	1
4	David Kimani	NULL

hives

hive_id	keeper_id	location	established_year
101	1	Ruiru Rooftop	2021
102	2	Thika Road Garden	2022
103	3	Kiambu Backyard	2023
104	99	Abandoned Lot	2020

harvests

harvest_id	hive_id	harvest_date	honey_kg
H1	101	2024-05-01	12.5
H2	101	2024-09-01	9.0
H3	102	2024-06-15	15.0
H4	999	2024-07-01	5.0

Notice three deliberate cracks in this data. David Kimani (keeper 4) has never registered a hive. Hive 104 lists keeper_id = 99, a keeper who doesn't exist in the beekeepers table at all, maybe a data-entry error, maybe a keeper who left the co-op and got deleted. And harvest H4 points to hive_id = 999, a hive that was never created, likely a typo when someone logged the harvest by hand. Real databases have rows like this. A good demonstration of joins should too.

What a Join Actually Does

A join combines rows from two tables based on a matching condition, usually a shared key. The database doesn't merge the tables permanently; it builds a temporary result set for that one query, row by row, checking the condition each time.

The six join types differ only in one decision: what happens to a row that has no match on the other side. Some joins throw it away. Others keep it and fill the missing columns with NULL. Once you see that single rule at work, every join becomes predictable instead of memorized.

The Joins, One at a Time

1. INNER JOIN: only the rows that match on both sides

SELECT b.name, h.location, h.established_year
FROM beekeepers b
INNER JOIN hives h ON b.keeper_id = h.keeper_id;

Result:

name	location	established_year
Amara Wanjiru	Ruiru Rooftop	2021
Brian Otieno	Thika Road Garden	2022
Chiara Mwangi	Kiambu Backyard	2023

David Kimani disappears because he has no hive. Hive 104 disappears because its keeper doesn't exist. INNER JOIN is strict: a row survives only if both sides agree it exists.

Use it when an unmatched row would be meaningless in context, for example, a report on "active hive locations by keeper" has no use for a keeper with zero hives.

2. LEFT JOIN: keep everyone from the first table, matched or not

SELECT b.name, h.location, h.established_year
FROM beekeepers b
LEFT JOIN hives h ON b.keeper_id = h.keeper_id;

Result:

name	location	established_year
Amara Wanjiru	Ruiru Rooftop	2021
Brian Otieno	Thika Road Garden	2022
Chiara Mwangi	Kiambu Backyard	2023
David Kimani	NULL	NULL

David is back, with NULL standing in for the hive data he doesn't have. LEFT JOIN never drops a row from the table named on the left of the keyword, no matter what's missing on the right.

Use it whenever the absence itself is the answer you're looking for, a membership list that should show every member even if some haven't done anything yet.

3. RIGHT JOIN: keep everyone from the second table, matched or not

SELECT b.name, h.location, h.established_year
FROM beekeepers b
RIGHT JOIN hives h ON b.keeper_id = h.keeper_id;

Result:

name	location	established_year
Amara Wanjiru	Ruiru Rooftop	2021
Brian Otieno	Thika Road Garden	2022
Chiara Mwangi	Kiambu Backyard	2023
NULL	Abandoned Lot	2020

The mirror image of the previous query. Every hive shows up, including hive 104, whose orphaned keeper_id = 99 produces a NULL name. RIGHT JOIN is rare in practice mostly because you can rewrite it as a LEFT JOIN by swapping the table order, and most style guides prefer that for readability. It's worth knowing, though, especially when you're editing someone else's query rather than writing your own from scratch.

4. FULL OUTER JOIN: keep every row from both sides

SELECT b.name, h.location, h.established_year
FROM beekeepers b
FULL OUTER JOIN hives h ON b.keeper_id = h.keeper_id;

Result:

name	location	established_year
Amara Wanjiru	Ruiru Rooftop	2021
Brian Otieno	Thika Road Garden	2022
Chiara Mwangi	Kiambu Backyard	2023
David Kimani	NULL	NULL
NULL	Abandoned Lot	2020

This is the union of the LEFT and RIGHT results: nothing gets dropped from either table. It's the join to reach for during data audits, where the whole point is finding orphaned or incomplete records on both sides at once. One caution: MySQL doesn't support FULL OUTER JOIN directly. You emulate it with LEFT JOIN ... UNION ... RIGHT JOIN. PostgreSQL, SQL Server, and Oracle all support the keyword as written above.

5. CROSS JOIN: every row paired with every row, no condition at all

Say the co-op is scheduling two workshops and wants a sign-up grid with one row per keeper, per workshop.

CREATE TABLE workshops (
    workshop_id VARCHAR(3),
    topic       VARCHAR(50),
    workshop_date DATE
);
-- W1, Swarm Prevention, 2024-03-10
-- W2, Honey Extraction, 2024-03-17

SELECT b.name, w.topic, w.workshop_date
FROM beekeepers b
CROSS JOIN workshops w;

Result:

name	topic	workshop_date
Amara Wanjiru	Swarm Prevention	2024-03-10
Amara Wanjiru	Honey Extraction	2024-03-17
Brian Otieno	Swarm Prevention	2024-03-10
Brian Otieno	Honey Extraction	2024-03-17
Chiara Mwangi	Swarm Prevention	2024-03-10
Chiara Mwangi	Honey Extraction	2024-03-17
David Kimani	Swarm Prevention	2024-03-10
David Kimani	Honey Extraction	2024-03-17

Four keepers times two workshops gives eight rows. No ON clause, no matching logic, just every possible combination. CROSS JOIN is the one to be careful with: on two tables of ten thousand rows each, you'd get a hundred million rows. Reach for it deliberately, for generating combinations, filling calendar grids, building test data, not by forgetting a WHERE clause on a regular join.

6. SELF JOIN: a table matched against itself

The beekeepers table already tracks mentorship through mentor_id. To turn that into a readable list of who trained whom, join the table to a second copy of itself.

SELECT apprentice.name AS apprentice, mentor.name AS mentor
FROM beekeepers apprentice
INNER JOIN beekeepers mentor ON apprentice.mentor_id = mentor.keeper_id;

Result:

apprentice	mentor
Brian Otieno	Amara Wanjiru
Chiara Mwangi	Amara Wanjiru

There's only one physical table here. The two aliases, apprentice and mentor, let the database treat it as two separate tables for the length of the query. Amara and David don't appear as apprentices because their mentor_id is NULL, an INNER JOIN drops them the same way it would drop any other unmatched row. Swap in a LEFT JOIN if you want every keeper listed, with NULL in the mentor column for those who trained themselves.

A self join is the right tool whenever a table describes a relationship between rows in its own set: employees and managers, cities and neighboring cities, comments and their replies.

Bringing In the Harvests

So far every example has used beekeepers and hives. The harvests table has been sitting there unused, which is its own kind of realistic: plenty of production databases have a table nobody's queried in months. Let's put it to work and see how a hive with no harvest, and a harvest with no hive, behave.

SELECT h.hive_id, h.location, hv.harvest_date, hv.honey_kg
FROM hives h
LEFT JOIN harvests hv ON h.hive_id = hv.hive_id;

Result:

hive_id	location	harvest_date	honey_kg
101	Ruiru Rooftop	2024-05-01	12.5
101	Ruiru Rooftop	2024-09-01	9.0
102	Thika Road Garden	2024-06-15	15.0
103	Kiambu Backyard	NULL	NULL
104	Abandoned Lot	NULL	NULL

Hive 101 shows up twice because it has two harvest records, a reminder that a join's row count follows the data, not the table it started from. Hive 103, Chiara's hive, is only a year old and hasn't been harvested yet, so it appears once with NULL in place of harvest data. LEFT JOIN keeps it in the results instead of hiding a hive that simply hasn't produced anything yet.

Now flip it around and check for harvest H4's orphaned hive_id:

SELECT hv.harvest_id, hv.hive_id, hv.honey_kg, h.location
FROM harvests hv
LEFT JOIN hives h ON hv.hive_id = h.hive_id;

Result:

harvest_id	hive_id	honey_kg	location
H1	101	12.5	Ruiru Rooftop
H2	101	9.0	Ruiru Rooftop
H3	102	15.0	Thika Road Garden
H4	999	5.0	NULL

There it is: 5.0 kg of honey logged against a hive that doesn't exist in the hives table. In a real co-op this is exactly the kind of row a data-quality check should flag, and a LEFT JOIN followed by WHERE h.hive_id IS NULL is the standard way to find it.

Chain all three tables together and you get the report the co-op actually wants: total honey per keeper, including keepers who haven't harvested a drop.

SELECT b.name, COALESCE(SUM(hv.honey_kg), 0) AS total_honey_kg
FROM beekeepers b
LEFT JOIN hives h ON b.keeper_id = h.keeper_id
LEFT JOIN harvests hv ON h.hive_id = hv.hive_id
GROUP BY b.name;

Result:

name	total_honey_kg
Amara Wanjiru	21.5
Brian Otieno	15.0
Chiara Mwangi	0.0
David Kimani	0.0

Two LEFT JOINs in a row, one linking keepers to hives, the next linking hives to harvests, and every keeper still makes the list even if the chain runs into a NULL partway through. This is the pattern behind most real reporting queries: keep the entity you're reporting on, then reach outward through as many related tables as the question needs.

Choosing the Right One

You want...	Use
Only rows with a confirmed match on both sides	`INNER JOIN`
Every row from the main table, matches or not	`LEFT JOIN`
Every row from the secondary table, matches or not	`RIGHT JOIN`
Every row from both tables, to catch mismatches on either side	`FULL OUTER JOIN`
Every possible combination, with no matching condition	`CROSS JOIN`
Rows from a table related to other rows in the same table	`SELF JOIN`

Try It Yourself

The fastest way to make this stick is to break your own data the way hive 104 is broken here: create two small tables, delete a foreign key's parent row on purpose, then run all six joins against them and watch what changes. The gaps are where the learning happens.

DEV Community: David Mwandairo

Python Loops at the Nairobi Animal Orphanage

The for loop: you have the items, so visit each one

Counting with range()

Walking through a string

The while loop: you have a condition, so repeat until it changes

The trap: a loop that never ends

Which loop do I pick?

break and continue: change the loop from the inside

break ends the whole loop

continue ends only the current pass

Using both in a while True loop

enumerate(): let Python do the counting

Morning round at the orphanage: all four at once

Try it yourself

Building the Tembo Hotel Dashboard in Power BI

The brief in one breath

1. How I prepared the data for BI

Import the view, not the table

Three signs the dashboard reads clean data

Four rules the numbers rest on

Does the dashboard agree with the database?

2. The dashboard I built

3. The DAX behind each page

Page 1: Executive Overview

Page 2: Rooms & Guests

Page 3: Staff & Risk

4. Key insights

5. How I communicated the findings

6. What I learned from presenting the project

7. What I would do differently

Fix first: things that are wrong or misleading

Fix next: things that make the page hard to read

Use the measures already built

Do next time: the process

What comes next

Python Operators and Conditionals at the Airport Check-in Desk

Arithmetic operators: weighing the bag

Precedence: multiplication goes first

Comparison operators: asking the scale a question

Logical operators: combining questions

Short-circuit evaluation

and and or return a value, not only True or False

The bug nearly every beginner writes once

if, elif, else: choosing an outcome

elif is not the same as a second if

Putting it together: the check-in desk

What Python says when you slip

Try it yourself

Python Basics in the Matatu Context

Variables: names, not boxes

Naming rules

The four data types

Strings (str)

Integers (int)

Floats (float)

Booleans (bool)

Converting between types

Input and output

Output with print()

Input with input()

Syntax rules that hold it together

Practical example 1: the conductor's change counter

Practical example 2: the trip profit calculator

Practical example 3: the departure call

Try it yourself

Cleaning and Analyzing Tembo Hotel's Bookings

The brief in plain words

The plan: register first, get a room second

Step 1: Ingest the CSV without losing a row

Step 2: Audit the damage before touching anything

Step 3: Fix the data, one defect at a time

3.1 Duplicate bookings

3.2 Guest names

3.3 Phone numbers

3.4 Cities

3.5 Short vocabularies: room type, payment, status, nationality

3.6 Dates, the hard one

3.7 Staff salary

3.8 Money: totals and service prices

`and` and `or` return a value, not only True or False

Strings (`str`)

Integers (`int`)

Floats (`float`)

Booleans (`bool`)

Output with `print()`

Input with `input()`