DEV Community: Navas Herbert

Teaching Python to Hold More Than One Thing

Navas Herbert — Sat, 12 Sep 2026 19:03:36 +0000

Four sessions in, every program we've written has dealt with one piece of data at a time - one name, one score, one total. Today that changes. Python is about to learn how to hold many things at once, in four different ways, each built for a specific job.

I open with a question: "if you had to store the names of every passenger on a matatu, would you create 14 separate variables?" The room always laughs, because obviously not - but that's genuinely how people try to solve this before they meet lists.

Same routine as always: open session5.py, keep it beside this article, type everything yourself.

Lists - One Variable, Many Values

A list is an ordered collection holding many values inside a single variable - think of a matatu passenger manifest, or the shopping list pinned to the kiosk wall. Every item sits at a position, called an index, and Python starts counting those positions at 0, not 1.

shopping_list = ['Bread', 'Milk', 'Sugar', 'Rice']
print(shopping_list)
print(len(shopping_list))

['Bread', 'Milk', 'Sugar', 'Rice']
4

Indexing starts at 0 because Python counts position as distance from the start, not a place number - the first item is 0 steps away, the second is 1 step away. It trips up everyone at first and becomes automatic within a week.

shopping_list = ['Bread', 'Milk', 'Sugar', 'Rice']
print(shopping_list[0])    # Bread - first item
print(shopping_list[2])    # Sugar - third item
print(shopping_list[-1])   # Rice - last item, negative counts from the end
print(shopping_list[-2])   # Sugar - second to last

Bread
Sugar
Rice
Sugar

Negative indexing is genuinely useful, not just a party trick - [-1] for "the last one" saves you from ever needing to know exactly how long a list is.

Slicing - Cutting Out a Section

Where indexing grabs one item, slicing grabs a whole section - like cutting a few slices out of a loaf, not the whole thing. The syntax is list[start:stop], and just like range(), it stops before the stop position.

numbers = [10, 20, 30, 40, 50, 60, 70]
print(numbers[1:4])   # [20, 30, 40] - position 1 up to (not including) 4
print(numbers[:3])    # [10, 20, 30] - from the start up to position 3
print(numbers[4:])    # [50, 60, 70] - from position 4 to the end

[20, 30, 40]
[10, 20, 30]
[50, 60, 70]

Add a third number and you get a step - how many positions to jump each time:

numbers = [10, 20, 30, 40, 50, 60, 70]
print(numbers[::2])    # every 2nd item
print(numbers[::-1])   # the whole list, reversed

[10, 30, 50, 70]
[70, 60, 50, 40, 30, 20, 10]

[::-1] is the fastest way to reverse anything in Python - no loop needed, just an empty start, empty stop, and a step of -1.

List Methods - Add, Remove, Sort

A method is an action performed on a list, written as list_name.method_name() - you're giving the list an instruction: add this, remove that, sort yourself.

queue = ['Amina', 'Brian']
queue.append('Njeri')        # adds to the END
print(queue)
queue.insert(1, 'Otieno')    # inserts AT position 1
print(queue)

['Amina', 'Brian', 'Njeri']
['Amina', 'Otieno', 'Brian', 'Njeri']

Taking things out works two ways - by value, or by position:

queue = ['Amina', 'Otieno', 'Brian', 'Njeri']
queue.remove('Otieno')       # removes BY VALUE
print(queue)

served = queue.pop(0)        # removes BY POSITION, and hands it back
print(f"Served: {served}")
print(queue)

['Amina', 'Brian', 'Njeri']
Served: Amina
['Brian', 'Njeri']

.pop() is the one worth remembering - it removes the item and returns it in one move, which is exactly what you want when you're processing a queue one person at a time. Sorting and checking membership round out the everyday toolkit:

scores = [67, 45, 90, 38, 72]
scores.sort()
print(scores)          # [38, 45, 67, 72, 90]

fruits = ['Mango', 'Banana', 'Mango', 'Orange']
print('Mango' in fruits)       # True
print(fruits.count('Mango'))   # 2

[38, 45, 67, 72, 90]
True
2

Tuples - Lists That Refuse to Change

A tuple looks like a list but uses round brackets, and once it's created, it's locked - no append, no remove, no editing, ever. Use a tuple for data that genuinely should never change mid-program: a national ID number, GPS coordinates, a date of birth.

student = ('Njeri', 19, 'Kisumu')
print(student[0])   # Njeri
print(student[1])   # 19

Njeri
19

Try to change one, and Python stops you - on purpose:

student = ('Njeri', 19, 'Kisumu')
student[1] = 20   # TypeError: 'tuple' object does not support item assignment

TypeError: 'tuple' object does not support item assignment

That error isn't a bug to work around - it's the entire point of choosing a tuple in the first place. What tuples are genuinely great for is unpacking - splitting one tuple straight into several named variables in a single line:

student = ('Njeri', 19, 'Kisumu')
name, age, city = student
print(f"{name} is {age} years old, from {city}")

Njeri is 19 years old, from Kisumu

Dictionaries - Look Things Up By Name, Not Position

A dictionary stores data as key: value pairs - like a phone contact list. You don't find a contact by "the 7th entry," you find them by name.

contacts = {'Amina': '0712345678', 'Brian': '0798765432'}
print(contacts['Amina'])
print(contacts.get('Brian'))

0712345678
0798765432

Two ways to look something up, and the difference matters: contacts['Otieno'] crashes your whole program with a KeyError if that key doesn't exist. contacts.get('Otieno') just quietly returns None instead - and you can even give it a fallback: contacts.get('Otieno', 'Not found'). Use .get() whenever you're not 100% sure the key is there.

contacts = {'Amina': '0712345678'}
contacts['Njeri'] = '0700111222'    # adds a new key
contacts['Amina'] = '0711000000'    # updates an existing key
print(contacts)

{'Amina': '0711000000', 'Njeri': '0700111222'}

Looping through a dictionary properly means getting both the key and value at once with .items():

prices = {'Bread': 65, 'Milk': 120, 'Sugar': 150}
for item, price in prices.items():
    print(f"{item}: KES {price}")

Bread: KES 65
Milk: KES 120
Sugar: KES 150

Sets - Only the Unique Ones

A set holds items with no duplicates and no guaranteed order - like a list of unique matatu routes operating in Nairobi. It doesn't matter how many matatus run Route 46, the route itself only appears once.

routes = {'Route 46', 'Route 34', 'Route 46', 'Route 11'}
print(routes)
print(len(routes))

{'Route 46', 'Route 34', 'Route 11'}
3

The duplicate 'Route 46' simply vanished - sets do that automatically, without you asking. This makes sets the fastest way to de-duplicate an existing list:

attendance = ['Amina', 'Brian', 'Amina', 'Njeri', 'Brian']
unique_students = set(attendance)
print(unique_students)
print(f"Unique students present: {len(unique_students)}")

{'Amina', 'Brian', 'Njeri'}
Unique students present: 3

Sets also come with their own comparison operators, which read almost like plain English once you know what they mean:

sacco_a = {'Route 46', 'Route 34', 'Route 11'}
sacco_b = {'Route 34', 'Route 58'}

print(sacco_a | sacco_b)   # union - all routes from both
print(sacco_a & sacco_b)   # intersection - routes BOTH saccos share
print(sacco_a - sacco_b)   # difference - routes ONLY sacco_a has

{'Route 46', 'Route 34', 'Route 11', 'Route 58'}
{'Route 34'}
{'Route 46', 'Route 11'}

& for "what do both have in common" comes up constantly once you start comparing two groups of anything - which students take two particular subjects, which customers bought from two different campaigns, and so on.

Putting It Together - Kiosk Stock Tracker

Every structure from today shows up in one program: a dictionary for stock and prices, a list to track today's sales, and a set to capture which items sold at all.

stock = {'Bread': 20, 'Milk': 15, 'Sugar': 10, 'Rice': 8}
prices = {'Bread': 65, 'Milk': 120, 'Sugar': 150, 'Rice': 180}
sold_today = [10, 7, 5, 4]   # units sold, matching stock order
items_sold = set()
total_revenue = 0

print("--- Sales Report ---")
for (item, price), units in zip(prices.items(), sold_today):
    revenue = price * units
    total_revenue += revenue
    items_sold.add(item)
    print(f"{item}: sold {units} units → KES {revenue}")

print(f"Unique items sold: {items_sold}")
print(f"Total revenue: KES {total_revenue}")

--- Sales Report ---
Bread: sold 10 units → KES 650
Milk: sold 7 units → KES 840
Sugar: sold 5 units → KES 750
Rice: sold 4 units → KES 720
Unique items sold: {'Bread', 'Milk', 'Sugar', 'Rice'}
Total revenue: KES 2960

Four structures, four different jobs, one working program. That's really what today was about - not memorising syntax, but recognising which structure fits which problem.

Teaching Python to Package Logic

Navas Herbert — Fri, 04 Sep 2026 13:05:00 +0000

I open Session 4 the same way every cohort: "You're an accountant. You calculate VAT on every invoice. What happens when the rate changes from 16% to 14% and you've written that calculation 50 times?"

Someone always says it out loud before I finish the question: "you'd have to fix it 50 times." Exactly. One missed spot, and 49 invoices are right while one is quietly wrong. That's the problem functions solve - write the logic once, give it a name, and every place that uses it updates the moment you change it in one location.

The Problem, Without Functions

Here's the VAT calculation done the way most people start out - copy, paste, adjust the numbers, repeat:

price1 = 1000
vat1 = price1 * 0.16
total1 = price1 + vat1
print(f"Item 1: Ksh {price1} + VAT {vat1:.0f} = Ksh {total1:.0f}")

price2 = 2500
vat2 = price2 * 0.16
total2 = price2 + vat2
print(f"Item 2: Ksh {price2} + VAT {vat2:.0f} = Ksh {total2:.0f}")

Item 1: Ksh 1000 + VAT 160 = Ksh 1160
Item 2: Ksh 2500 + VAT 400 = Ksh 2900

Works fine for two items. At 50 items, it's 50 near-identical blocks and 50 chances to make a typo. Here's the same thing solved properly:

def add_vat(price, rate=0.16):
    vat = price * rate
    total = price + vat
    return total

print(f"Item 1: Ksh {add_vat(1000):.0f}")
print(f"Item 2: Ksh {add_vat(2500):.0f}")
print(f"Export price: Ksh {add_vat(1000, rate=0.14):.0f}")

Item 1: Ksh 1160
Item 2: Ksh 2900
Export price: Ksh 1140

The logic exists in exactly one place. Fifty invoices, fifty calls, one function. Change the rate on that one line and every call picks it up instantly - that's what "write once, use anywhere" actually buys you.

def and Calling - Writing the Recipe vs Cooking the Meal

def tells Python you're defining a function - nothing runs yet. It's a recipe sitting in a book. The function only actually does something when you call it, by writing its name followed by parentheses.

def print_divider():
    print("=" * 40)

print_divider()
print(" STUDENT REPORT")
print_divider()
print(" Amina: 87")
print_divider()

========================================
 STUDENT REPORT
========================================
 Amina: 87
========================================

Defining print_divider() does nothing by itself - the four lines of output only appear because I called it three separate times. Writing the recipe and cooking the meal are two different steps, and beginners often expect the function to run the moment it's defined. It doesn't.

print vs return - The Distinction That Changes Everything

This is the single idea that separates people who can write functions from people who can write useful functions. print() shows something on screen and hands nothing back. return hands a value back to whoever called the function, so you can actually use it.

def add_print(a, b):
    print(a + b)   # shows on screen, gives nothing back

result = add_print(5, 3)
print("Result is:", result)   # Result is: None

8
Result is: None

add_print(5, 3) printed 8, sure - but result is None, because the function never handed anything back. Try to do maths with result and Python throws an error. Compare that to return:

def add_return(a, b):
    return a + b   # hands the value back to the caller

result = add_return(5, 3)
print("Result is:", result)          # Result is: 8

total = add_return(10, 20) + add_return(5, 5)
print("Total:", total)                # Total: 40

Result is: 8
Total: 40

Now result is an actual usable number - you can add it, multiply it, pass it into another function. The rule is simple: if you need to use the result somewhere else in your program, use return, not print.

This gets powerful fast once you combine it with conditionals:

def get_grade(score):
    if score >= 80: return "A"
    elif score >= 70: return "B"
    elif score >= 60: return "C"
    elif score >= 50: return "D"
    else: return "F"

scores = [87, 74, 55, 91, 43]
for score in scores:
    grade = get_grade(score)
    print(f"Score: {score}  Grade: {grade}")

Score: 87  Grade: A
Score: 74  Grade: B
Score: 55  Grade: D
Score: 91  Grade: A
Score: 43  Grade: F

A function stops the instant it hits a return - nothing after it in that function runs. And a function isn't limited to returning one value:

def analyse_scores(scores):
    total = sum(scores)
    average = round(total / len(scores), 1)
    return total, average, max(scores), min(scores)

marks = [78, 85, 91, 65, 72, 88]
tot, avg, hi, lo = analyse_scores(marks)
print(f"Total: {tot}  Average: {avg}  Highest: {hi}  Lowest: {lo}")

Total: 479  Average: 79.8  Highest: 91  Lowest: 65

Return four values with a comma, unpack all four into separate variables on the other side. Clean and readable.

Scope - Where Does a Variable Actually Live?

Scope is just: where does a variable exist, and who can see it? Think of a whiteboard inside a closed meeting room versus a notice board out in the corridor. The whiteboard gets wiped clean the moment the meeting ends, and no one outside the room ever saw what was on it - that's a local variable, created inside a function, gone the moment the function finishes. The corridor notice board is visible to everyone, all the time - that's a global variable.

def calculate_fee():
    fee = 5000   # LOCAL - only exists inside this function
    print("Fee inside function:", fee)

calculate_fee()
# print(fee)  ← NameError! fee doesn't exist out here

Fee inside function: 5000

You can read a global variable from inside a function with zero extra effort:

school = "Nairobi Tech Institute"   # GLOBAL

def show_school():
    print(f"School: {school}")   # reading a global - no special keyword needed

show_school()

School: Nairobi Tech Institute

But here's the trap: creating a variable inside a function with the same name as a global one does not touch the global - it silently creates a brand new, separate local variable that happens to share a name.

name = "Nairobi"   # GLOBAL

def show_local():
    name = "Mombasa"   # LOCAL - a completely different variable
    print("Inside function:", name)

show_local()
print("Outside function:", name)   # still "Nairobi"

Inside function: Mombasa
Outside function: Nairobi

If you genuinely need to modify a global from inside a function, you have to say so explicitly with the global keyword:

login_count = 0

def login(username):
    global login_count   # tells Python: use the GLOBAL one, not a new local
    login_count += 1
    print(f"{username} logged in. Total logins: {login_count}")

login("Amina")
login("Brian")
print("Final count:", login_count)

Amina logged in. Total logins: 1
Brian logged in. Total logins: 2
Final count: 2

global is only ever needed to change a global variable - never to read one. And honestly, the cleaner habit long-term is to avoid global altogether: pass the value in as a parameter and return the updated result instead.

Default Arguments - Optional Inputs

A default argument is a parameter that already has a value baked in, so the caller can skip it entirely. It's the restaurant default drink - order nothing specific and water shows up automatically; ask for a soda and that arrives instead.

def send_message(recipient, message, channel="SMS"):
    print(f"Sending to {recipient} via {channel}: {message}")

send_message("Amina", "Your package has arrived")                  # uses default
send_message("Brian", "Meeting at 3pm", "WhatsApp")                # overrides it
send_message("Njeri", "Invoice attached", channel="Email")         # overrides it, named

Sending to Amina via SMS: Your package has arrived
Sending to Brian via WhatsApp: Meeting at 3pm
Sending to Njeri via Email: Invoice attached

One rule to keep in mind: default parameters must come after the non-default ones - def send(recipient, message, channel="SMS") is fine, def send(channel="SMS", recipient, message) is a SyntaxError. Python needs to know which required values are coming before it lets you make anything optional.

*args - Any Number of Arguments

Sometimes you genuinely don't know in advance how many values a function will get handed. A waiter doesn't know if two people or ten people are about to sit at the table - he just takes orders from whoever shows up. *args gives a function that same flexibility.

def total_cost(*prices):
    print(f"Prices received: {prices}")   # it's a tuple
    return sum(prices)

print(total_cost(150))
print(total_cost(150, 300, 800))
print(total_cost(50, 75, 120, 200, 350))

Prices received: (150,)
150
Prices received: (150, 300, 800)
1250
Prices received: (50, 75, 120, 200, 350)
795

Inside the function, prices is just a tuple - you can loop through it, sum it, measure its length, whatever you'd do with any other sequence. Regular parameters always come before *args:

def class_summary(teacher, *scores):
    print(f"Teacher: {teacher}")
    print(f"Students: {len(scores)}")
    if scores:
        print(f"Average: {sum(scores)/len(scores):.1f}")

class_summary("Mr. Navas", 78, 85, 91, 65, 72)

Teacher: Mr. Navas
Students: 5
Average: 78.2

teacher is required and always comes first; everything after it - however many values - gets swept into the scores tuple.

Putting It Together - Payslip Generator

By the end of the session, everything from today lands in one program: a function with a default argument, one that returns multiple values, and clean separation between calculation and display.

def calculate_paye(gross, relief=2400):
    tax = gross * 0.25
    net_tax = max(tax - relief, 0)
    return round(net_tax, 2)

def generate_payslip(name, gross):
    paye = calculate_paye(gross)
    net_pay = gross - paye
    print("=" * 40)
    print(f" PAYSLIP: {name}")
    print("=" * 40)
    print(f"Gross Pay: Ksh {gross:,.2f}")
    print(f"PAYE: Ksh {paye:,.2f}")
    print("-" * 40)
    print(f"NET PAY: Ksh {net_pay:,.2f}")
    print("=" * 40)

generate_payslip("Wanjiku Kamau", 85000)

========================================
 PAYSLIP: Wanjiku Kamau
========================================
Gross Pay: Ksh 85,000.00
PAYE: Ksh 9,760.00
----------------------------------------
NET PAY: Ksh 75,240.00
========================================

calculate_paye() does one job - number crunching - and returns a clean result. generate_payslip() does a different job - formatting and display. That separation, calculation in one function, presentation in another, is the exact shape almost every real Python program takes once it grows past a few lines.

I'm a data trainer in Nairobi running a full data programme -
Python foundations → Data Science or Data Engineering specialisations.

Teaching Python to Repeat Itself

Navas Herbert — Fri, 04 Sep 2026 12:56:39 +0000

Two sessions in, Python can store information and make decisions. Today it learns to do something a human would never have the patience for: repeat the exact same task, correctly, a thousand times in a row without complaining once.

I open with a simple pitch - what takes a person 100 steps takes Python three lines. Nobody quite believes that until they see it. So let's get there.

Same as always: open session3.py, keep it next to this article, type everything yourself. Don't copy-paste.

for Loops - Do This for Every Item

Picture a teacher taking attendance. For every student in the register - call their name, mark present, move to the next. They don't decide in advance how many times to repeat it; they just keep going until the register runs out. That's exactly what a for loop does.

students = ["Amina", "Brian", "Njeri", "Otieno", "Wanjiku"]

for student in students:
    print(f"Good morning, {student}!")

Good morning, Amina!
Good morning, Brian!
Good morning, Njeri!
Good morning, Otieno!
Good morning, Wanjiku!

student is a variable that Python refills on every round - first it's "Amina", then "Brian", and so on, until the list runs out and the loop just stops. No counting, no bookkeeping. That's the whole job of a for loop.

range() - Looping Over Numbers Instead of a List

Most of the time you don't have a ready-made list - you just want to count. range() generates numbers on the fly.

for i in range(5):
    print(i)

The one rule that catches everyone at least once: the stop value is never included. range(5) gives you 0 through 4, not 0 through 5. You can also give it a start and a step:

for i in range(1, 11, 2):
    print(i, end=" ")

1 3 5 7 9

range(1, 11, 2) starts at 1, stops before 11, and jumps by 2 each time. That end=" " keeps everything on one line instead of stacking vertically - handy for output like this.

The Accumulator Pattern - Building a Total Inside a Loop

This is the pattern you'll use more than any other single idea in this course. Start a variable at zero before the loop, then add to it on every round.

transactions = [1500, 3000, 800, 12000, 450, 2700]
total = 0

for amount in transactions:
    total = total + amount
    print(f"Added Ksh {amount:,} - Running total: Ksh {total:,}")

print(f"Final total: Ksh {total:,}")

Added Ksh 1,500 - Running total: Ksh 1,500
Added Ksh 3,000 - Running total: Ksh 4,500
Added Ksh 800 - Running total: Ksh 5,300
Added Ksh 12,000 - Running total: Ksh 17,300
Added Ksh 450 - Running total: Ksh 17,750
Added Ksh 2,700 - Running total: Ksh 20,450
Final total: Ksh 20,450

total = 0 sitting above the loop is the empty bucket. Every round drops one more amount into it. This exact shape - set it to zero, add inside the loop, use it after - shows up constantly once you start working with real data.

while Loops - Keep Going Until...

A for loop is for when you know how many times to repeat, or you've got a list to walk through. A while loop is for when you genuinely don't know - you just keep going until a condition flips to False. Think of a teacher marking exactly 30 scripts (for) versus a security guard at a gate who checks IDs until their shift ends, however long that takes (while).

count = 1
while count <= 5:
    print(f"Count: {count}")
    count += 1   # must change count, or this never ends

print("Done!")

Count: 1
Count: 2
Count: 3
Count: 4
Count: 5
Done!

That count += 1 isn't optional decoration - it's the whole reason the loop ever stops. Delete it and Python prints "Count: 1" forever. That's called an infinite loop, and it's the first real bug most people write in this course.

A hugely common shape is while True paired with break - run forever, and let the user decide when to stop:

total = 0
while True:
    entry = input("Enter amount (or done to finish): ")
    if entry.lower() == "done":
        break
    total += int(entry)
    print(f"Running total: Ksh {total:,}")

print(f"Final total: Ksh {total:,}")

Enter amount (or done to finish): 1500
Running total: Ksh 1,500
Enter amount (or done to finish): 3000
Running total: Ksh 4,500
Enter amount (or done to finish): done
Final total: Ksh 4,500

while True never becomes False on its own - break is the only exit, and it fires the instant the user types "done."

break vs continue - Two Very Different Interrupts

Both interrupt a loop, but not in the same way. Think of a matatu: it breaks down, everyone gets off, the journey's over - that's break. One passenger has no fare, so they get skipped and the matatu carries on to the next stop - that's continue.

scores = [78, 85, 91, 35, 66, 55, 42]

for score in scores:
    if score < 40:
        print(f"First failing score found: {score}")
        break
    print(f"  {score} - OK")

print("Search complete.")

  78 - OK
  85 - OK
  91 - OK
First failing score found: 35
Search complete.

66, 55, and 42 never even get checked - break shut the whole loop down the moment it found what it was looking for. Compare that to continue, which skips just the current round and moves on:

scores = [78, -1, 85, -1, 91]  # -1 means absent

for score in scores:
    if score == -1:
        continue
    print(f"  Score: {score}")

  Score: 78
  Score: 85
  Score: 91

The absent entries are silently skipped, but the loop keeps running through the rest of the list. break ends things. continue just skips ahead.

enumerate() - Position and Item, Together

Sometimes you don't just need the item, you need to know where it sits - item number 1, item number 2, and so on. You could do this with range(len(...)), but it's clunky. enumerate() does it cleanly.

students = ["Amina", "Brian", "Njeri", "Otieno", "Wanjiku"]

for number, name in enumerate(students, start=1):
    print(f"{number}. {name}")

1. Amina
2. Brian
3. Njeri
4. Otieno
5. Wanjiku

Every round now hands you two variables instead of one - the position and the item. start=1 just tells it to count from 1 instead of Python's default of 0. This combines naturally with everything you've learned so far:

scores = [78, 85, 45, 91, 38]

for position, score in enumerate(scores, start=1):
    status = "FAIL" if score < 50 else "PASS"
    print(f"Student {position}: {score:>3} {status}")

Student 1:  78 PASS
Student 2:  85 PASS
Student 3:  45 FAIL
Student 4:  91 PASS
Student 5:  38 FAIL

Nested Loops - A Loop Inside a Loop

A nested loop is a for loop living inside another for loop. The golden rule: for every single round of the outer loop, the inner loop runs all the way through, start to finish, before the outer loop moves on. Think of a school timetable - for each day, go through every period, then move to the next day.

days = ["Monday", "Tuesday", "Wednesday"]
periods = ["Period 1", "Period 2", "Period 3"]

for day in days:
    print(f"--- {day} ---")
    for period in periods:
        print(f"  {period}")

--- Monday ---
  Period 1
  Period 2
  Period 3
--- Tuesday ---
  Period 1
  Period 2
  Period 3
--- Wednesday ---
  Period 1
  Period 2
  Period 3

Three days times three periods gives nine inner prints - the inner loop resets back to Period 1 every time the outer loop steps to a new day. That multiplication - outer rounds × inner rounds - is worth doing in your head before you run any nested loop, so you know roughly what to expect.

Bringing It All Together - Class Results Processor

This is where every idea from today lands in one program: a while True loop to collect an unknown number of scores, continue to skip invalid entries, break to finish when the user types "done," and enumerate to build the final report.

scores = []

print("Enter student scores. Type done when finished.")
while True:
    entry = input("Score (or done): ")
    if entry.lower() == "done":
        break
    try:
        score = int(entry)
        if score < 0 or score > 100:
            print("  Invalid - must be 0 to 100. Skipping.")
            continue
        scores.append(score)
    except:
        print("  Not a number. Skipping.")
        continue

if not scores:
    print("No scores entered.")
else:
    print(f"\n=== CLASS REPORT ({len(scores)} students) ===")
    passed, failed = 0, 0
    for num, score in enumerate(scores, start=1):
        grade = "A" if score >= 80 else "B" if score >= 70 else "C" if score >= 60 else "D" if score >= 50 else "F"
        status = "PASS" if score >= 50 else "FAIL"
        if score >= 50:
            passed += 1
        else:
            failed += 1
        print(f"  Student {num:>2}: {score:>3}  Grade {grade}  {status}")

    print(f"\nTotal: {len(scores)}")
    print(f"Passed: {passed}  Failed: {failed}")
    print(f"Average: {sum(scores)/len(scores):.1f}")
    print(f"Highest: {max(scores)}  Lowest: {min(scores)}")

Enter student scores. Type done when finished.
Score (or done): 87
Score (or done): 45
Score (or done): 91
Score (or done): 62
Score (or done): done

=== CLASS REPORT (4 students) ===
  Student  1:  87  Grade A  PASS
  Student  2:  45  Grade F  FAIL
  Student  3:  91  Grade A  PASS
  Student  4:  62  Grade C  PASS

Total: 4
Passed: 3  Failed: 1
Average: 71.2
Highest: 91  Lowest: 45

Type this one out slowly. Every line traces back to something covered today - collecting unknown input with while True, filtering bad data with continue, exiting cleanly with break, and numbering the report with enumerate.

Try It Yourself

Easy - classifier. Loop through [250, 980, 120, 1500, 75] and print each price along with "Affordable" (under 500) or "Expensive" (500+). You'll need an if/else living inside your for loop - that's completely normal.

Medium - Savings goal calculator. Ask for a savings goal and a monthly savings amount. Use a while loop to add the monthly amount each round, printing the running total, and stop as soon as the goal is reached. Print how many months it took. Test with a goal of Ksh 50,000 and Ksh 8,000 a month - you should land on 7 months and a final total of Ksh 56,000.

Challenge - Matatu route finder. Store three routes as a list of dictionaries, each with a route name and a list of stops. Use nested loops to print every route and its stops, numbered. Then ask the user for a stop name and search through every route to find which one serves it - using break the moment you find a match so you're not searching for no reason.

Teaching Python to Make Decisions

Navas Herbert — Fri, 04 Sep 2026 07:10:18 +0000

Same deal as last time: open session2.py, keep it next to this article, type everything yourself.

Comparison Operators - Asking Yes or No Questions

Every decision starts with a question that has exactly two possible answers: True or False. That's what comparison operators give you.

age = 20
score = 75

print(age == 20)     # True - age IS 20
print(age == 25)     # False - age is NOT 25
print(score > 70)    # True
print(score <= 74)   # False - 75 is not <= 74

True
False
True
False

I flag one thing before anyone even runs this: == asks a question, = stores a value. Mixing them up is the single most common typo in this entire course - everyone does it at least once, usually while I'm mid-sentence explaining something else.

String comparisons work the same way, but with a twist:

track = "Data Science"
print(track == "Data Science")   # True
print(track == "data science")   # False - case sensitive!
print("Amina" < "Brian")          # True - alphabetical order

True
False
True

'Data Science' and 'data science' are completely different strings to Python. If you want to compare without caring about case, lowercase both sides first: track.lower() == "data science".

if - Making a Decision

An if statement checks a condition. If it's True, the indented block runs. If it's False, Python skips it - nothing happens, no error, it just moves on. Think of it like a gate: no ID, no drama, you just don't get let in.

score = int(input("Enter your score: "))
if score >= 50:
    print("You passed!")
print("Thank you for taking the test.")

Enter your score: 75
You passed!
Thank you for taking the test.

The line "Thank you for taking the test." isn't indented, so it always runs - pass or fail. Only the indented line is conditional. Run it again with a score of 30 and watch "You passed!" disappear while the thank-you stays.

else and elif - More Than One Path

else gives you the opposite path - exactly one of the two blocks runs, never both, never neither:

score = int(input("Enter your score: "))
if score >= 50:
    print("PASS - Well done!")
else:
    print("FAIL - Please try again.")
print(f"Your score was: {score}")

Enter your score: 45
FAIL - Please try again.
Your score was: 45

elif - short for "else if" - lets you chain more than two paths:

score = int(input("Enter score: "))
if score >= 80:
    print("Grade A - Excellent!")
elif score >= 70:
    print("Grade B - Good")
elif score >= 60:
    print("Grade C - Average")
elif score >= 50:
    print("Grade D - Below Average")
else:
    print("Grade F - Failed")

Enter score: 73
Grade B - Good

Here's the part that trips people up every single cohort: Python checks top to bottom and stops at the first True condition. Order the boundaries wrong - say, checking score >= 50 before score >= 80 - and every passing score gets swallowed by the first, loosest condition before Python ever reaches the accurate one. Always put the most specific condition first.

Logical Operators - Checking More Than One Thing

Sometimes one question isn't enough. and needs both conditions True. or needs at least one. not flips True to False and back.

age = int(input("Age: "))
has_id = input("Do you have an ID? (yes/no): ")

if age >= 18 and has_id == "yes":
    print("Access granted - welcome!")
else:
    print("Access denied.")

Age: 20
Do you have an ID? (yes/no): yes
Access granted - welcome!

Age 20 with no ID, denied. Age 15 with ID, denied. Only both together get you through - that's and. Swap it for or and only one condition needs to hold, like a discount that applies if you're a student or a senior, not both.

Nested if - A Decision Inside a Decision

A nested if is exactly what it sounds like - an if inside another if, used when the second question only makes sense once the first has already been answered YES.

username = input("Username: ")
if username == "admin":
    password = input("Password: ")
    if password == "kenya2025":
        print("Welcome, admin!")
    else:
        print("Wrong password.")
else:
    print("User not found.")

Username: admin
Password: kenya2025
Welcome, admin!

Notice the password question never even appears if the username is wrong - there's no point asking. That's the whole reason to reach for nested if instead of elif: elif is for the same question with different possible answers (track: DS or DE); nested if is for a question that only exists because of how the previous one was answered.

Putting It Together - NHIF Deduction Calculator

By this point everyone's used every idea from today at least once. So we close with something real - an NHIF-style deduction calculator using actual government bracket logic, simplified for learning:

print("=== NHIF DEDUCTION CALCULATOR ===")
print()

name = input("Employee name: ")
salary = int(input("Gross monthly salary (Ksh): "))

if salary < 6000:
    nhif = 150
elif salary < 8000:
    nhif = 300
elif salary < 12000:
    nhif = 400
elif salary < 15000:
    nhif = 500
elif salary < 25000:
    nhif = 750
elif salary < 50000:
    nhif = 850
else:
    nhif = 950

net = salary - nhif

print()
print(f"Employee: {name}")
print(f"Gross: Ksh {salary:,}")
print(f"NHIF: Ksh {nhif:,}")
print(f"Net: Ksh {net:,}")

=== NHIF DEDUCTION CALCULATOR ===
Employee name: Brian Otieno
Gross monthly salary (Ksh): 35000

Employee: Brian Otieno
Gross: Ksh 35,000
NHIF: Ksh 850
Net: Ksh 34,150

Someone always asks, at this exact point, "wait - this is basically how payroll software works?" Yes. That's exactly what it is. It's just brackets and comparisons, stacked.

Try It Yourself

Easy - Matatu ticket pricing. Ask for a distance in km, then use if/elif/else to charge Ksh 30 for 0–5km, Ksh 50 for 6–15km, Ksh 80 for 16–30km, and Ksh 120 above that. Test with 3, 10, 25, and 50 km - you should get 30, 50, 80, 120.

Medium - M-Pesa daily limit checker. Ask for an account type (basic/premium) and an amount to send. Basic accounts cap at Ksh 70,000, premium at Ksh 300,000 - approve or reject based on which type and whether the amount fits the limit.

Challenge - Bank loan eligibility, using nested if. Ask if the user has a bank account. No → stop there. Yes → ask how many months they've been a customer. Under 6 months → not eligible yet. 6+ months → ask their monthly salary. Under Ksh 20,000 → doesn't qualify. Ksh 20,000+ → qualifies. Each question only appears because the one before it passed - that's the nested-if pattern in action.

What I Noticed Teaching This Round

The = vs == mix-up happened to almost everyone within the first ten minutes - and once it happens once, it basically never happens again
Nobody guessed correctly that elif order could silently break a grading program until I showed them Example 6 breaking live - that one lands better as a demonstration than an explanation

Next Week

Somebody's going to ask "what if I need to do this 100 times?" before I even bring it up - it happens every cohort. Loops answer that:

for i in range(5):
    print(f"Student {i + 1}")

Printing five things without five print() lines is a small thing to see for the first time, but it changes how people think about repetition entirely. That's next week.

Follow Along With My Beginner Python Class

Navas Herbert — Sat, 29 Aug 2026 12:11:36 +0000

The last time I wrote about this session, I had a group of ex-SQL students staring at a blank Python file like it owed them money. This time it's a fresh group - some have never opened a terminal before, one guy, Kevin, tells me he installed VS Code that morning and hasn't touched it since. Same nerves. Same blinking cursor. Same "so... do I just start typing?"

I've decided to do something different with this one. Instead of just telling you what happened in the room, I'm going to walk you through the actual session - the way I taught it. Open a file called session1.py, keep it next to this article, and type every example yourself. Don't copy-paste. Type it, break it, fix it. That's the whole point.

print() - Talking to the World

Everything starts here. print() is Python's way of showing you something on the screen - you tell it what to say, it says it.

print("Hello, world!")

Hello, world!

Text goes in quotes. Numbers don't need them. And if you separate two things with a comma, Python prints them both with a space in between:

print("My name is", "Amina")
print(42)
print("Python is", "great")

My name is Amina
42
Python is great

Kevin's first line wasn't "Hello, world" though. It was print("I installed VS Code and I'm still alive"). Fair enough.

Variables - Labelled Boxes

A variable is a labelled box where you store a piece of information. Instead of retyping "Amina Wanjiku" every time, you store it once and reuse the label.

name = "Amina Wanjiku"
age = 24
city = "Nairobi"

print(name, "is", age, "years old and lives in", city)

Amina Wanjiku is 24 years old and lives in Nairobi

Here's the bit that always makes people pause - updating a variable using itself:

score = 50
score = score + 10
print("Score:", score)

Score: 60

Read it right to left. Python calculates the right side first (50 + 10), then stores the result back in score. It's not algebra, it's an instruction: "take what's in the box, change it, put it back."

Data Types - What Kind of Thing Is It?

Python needs to know what kind of data it's holding before it decides what to do with it. You can add two numbers. You can join two pieces of text. You cannot mix them.

number_a = 5        # int
number_b = "5"       # str - this is the text "5", not the number

print(number_a + number_a)   # 10 - adds numbers
print(number_b + number_b)   # 55 - joins strings

10
55

This is the single most common beginner mistake, and it catches literally everyone at some point - usually the first time they use input(), because input() always hands you back a string, even if the user typed a number. If you need a number, you convert it:

text_number = "42"
real_number = int(text_number)
print(real_number + 8)   # 50

int(), float(), and str() convert between types. One trap worth knowing early: converting a float to an int drops the decimal, it doesn't round.

print(int(9.9))   # 9, not 10

input() - Letting the User Talk Back

So far Python has only been talking to you. input() lets the user talk to Python. When Python hits it, the program pauses, waits for someone to type something and press Enter, then hands that back to you - as a string, always.

name = input("What is your name? ")
print("Hello,", name)

What is your name? Brian
Hello, Brian

Notice the space before the closing quote in "What is your name? " - without it, the user's answer runs straight into the question with no gap. Small detail, makes a real difference.

For numbers, wrap the whole thing:

age = int(input("How old are you? "))
print("Next year you'll be", age + 1)

How old are you? 24
Next year you'll be 25

Arithmetic - Python as a Calculator

Python does everything a calculator does, plus a couple of operators most people haven't met before.

a = 15
b = 4
print("Addition:", a + b)          # 19
print("Division:", a / b)          # 3.75
print("Floor division:", a // b)   # 3 - drops the decimal
print("Modulus:", a % b)           # 3 - the remainder
print("Power:", a ** b)            # 50625

% - modulus - is the one that gets the most questions. It gives you the remainder after dividing. It sounds abstract until you use it to check whether a number is even:

number = 42
print(number % 2 == 0)   # True - no remainder, so it's even

True

f-strings - The Clean Way to Print

Once you're mixing several variables into one message, commas get messy fast. f-strings fix that - put an f before the quote and drop variables straight into {}.

name = "Amina"
age = 24
balance = 15750.5

print(f"My name is {name} and I am {age} years old.")
print(f"Next year {name} will be {age + 1}")
print(f"Balance: Ksh {balance:,.2f}")

My name is Amina and I am 24 years old.
Next year Amina will be 25
Balance: Ksh 15,750.50

That last one - {balance:,.2f} - adds comma separators and rounds to two decimal places, right inside the string. The moment I showed that formatting trick, three people in the room said "wait, do it again."

The Moment Everything Clicked

I always end Session 1 by putting every single concept from the day into one program, live, in front of the class - a school grade calculator that asks for a name, a subject, and three test scores, then prints a full report.

print("===============================")
print(" KENYA SCHOOL GRADE CALCULATOR")
print("===============================")
print()

name = input("Student name: ")
subject = input("Subject: ")

print(f"Enter 3 test scores for {name}:")
score1 = int(input("  Test 1: "))
score2 = int(input("  Test 2: "))
score3 = int(input("  Test 3: "))

total = score1 + score2 + score3
average = total / 3
highest = max(score1, score2, score3)
lowest = min(score1, score2, score3)

print()
print("===============================")
print(f" REPORT: {name}")
print("===============================")
print(f"Subject: {subject}")
print(f"Scores: {score1}, {score2}, {score3}")
print(f"Total: {total} / 300")
print(f"Average: {round(average, 1)}")
print(f"Highest: {highest}")
print(f"Lowest: {lowest}")

===============================
 KENYA SCHOOL GRADE CALCULATOR
===============================
Student name: Amina Wanjiku
Subject: Mathematics
Enter 3 test scores for Amina Wanjiku:
  Test 1: 78
  Test 2: 85
  Test 3: 91

===============================
 REPORT: Amina Wanjiku
===============================
Subject: Mathematics
Scores: 78, 85, 91
Total: 254 / 300
Average: 84.7
Highest: 91
Lowest: 78

Nobody in the room had written more than five lines of code an hour earlier. Watching that report print out, built entirely from things they'd just learned, is the reason I still enjoy teaching Session 1 even after doing it more than once.

Try It Yourself

Easy - Matatu fare calculator. Ask for the number of passengers and the fare per person, then print the total collected, the driver's 30% share, and the owner's 70% share. Test with 14 passengers at Ksh 80 each - you should get a total of Ksh 1,120.

Medium - M-Pesa send calculator. Ask for a sender name, a recipient name, and an amount. Hardcode a charge of Ksh 11, then print a summary showing sender, recipient, amount, charge, and total.

Challenge - Personal budget calculator. Ask for a name, a monthly salary, and four expenses (rent, food, transport, other). Calculate total expenses, savings, and savings as a percentage of salary - rounded to one decimal place. Bonus: print an encouraging message if savings are positive, and a warning if they're negative. Test with a salary of Ksh 50,000 and expenses of 18,000 / 8,000 / 4,000 / 5,000 - you should land on savings of Ksh 15,000, or 30% of salary.

What I Noticed Teaching This Round

Starting with print() instead of a definition of "programming" still works every time - people want to see something happen before they care why
The "5" + "5" trap is unavoidable and that's fine - I'd rather they hit it in a safe example than three weeks from now debugging something real
f-strings landing before conditionals or loops means students write cleaner output from day one instead of learning commas first and unlearning them later
This cohort asked more "why" questions than the last one - more of them are switching careers, not just adding a skill, and it shows in how carefully they read error messages

I'm a data trainer in Nairobi running a full data programme

Nested if statements

Navas Herbert — Thu, 20 Aug 2026 19:01:28 +0000

An 𝗶𝗳 𝘀𝘁𝗮𝘁𝗲𝗺𝗲𝗻𝘁 teaches Python to ask one question. A 𝗻𝗲𝘀𝘁𝗲𝗱 𝗶𝗳 teaches it to ask the 𝗿𝗶𝗴𝗵𝘁 𝗳𝗼𝗹𝗹𝗼𝘄-𝘂𝗽.

That distinction is small on paper and huge in practice.

A single 𝗶𝗳 gives you a fork in the road - one path or the other, never both. Python checks the condition, takes the matching path, and whatever comes after the block always runs regardless.

𝗲𝗹𝗶𝗳 extends this - Python checks conditions top to bottom and stops at the first true one. Order matters more than most beginners expect. The moment you put the least specific condition first, every input hits it and the rest of the chain never runs.

𝙉𝙚𝙨𝙩𝙚𝙙 𝙞𝙛 𝙞𝙨 𝙬𝙝𝙚𝙧𝙚 𝙩𝙝𝙞𝙣𝙜𝙨 𝙜𝙚𝙩 𝙞𝙣𝙩𝙚𝙧𝙚𝙨𝙩𝙞𝙣𝙜.

The second question only gets asked if the first answer was 𝘆𝗲𝘀. That's not a limitation - that's the logic of real-world decisions.

The bank loan example captures it cleanly: first, do you have an account? If no, stop - there's nothing more to check. If yes, how long have you been a customer? If less than 6 months, stop - tenure matters. If yes, what's your salary? Only then does the loan decision happen.

Three levels of nesting. Each inner question only makes sense after the outer one passed. That's not just Python - that's how any system that gatekeeps access should think.

The pattern shows up everywhere once you recognize it: login flows, checkout validation, eligibility checks, access control. The second question depends on the first answer. That dependency is exactly what nested if expresses.

One rule worth keeping early: if your nested if goes beyond three levels deep, it's usually a signal to restructure - either with and/or to combine conditions, or by breaking the logic into functions.

Python #Programming #LearningInPublic #SoftwareDevelopment #DataEngineering

SQL CTEs: Writing Queries Like You're Telling a Story

Navas Herbert — Thu, 06 Aug 2026 11:11:22 +0000

Last session we did subqueries. Nested SELECT inside SELECT, inner query running first, outer query using the result. Powerful. But by the end, some of the harder questions had queries that looked like this:

SELECT r.rider_name,
    (SELECT SUM(fare) FROM safari.trips t WHERE t.rider_id = r.rider_id) AS total_spent
FROM safari.riders r
WHERE (SELECT SUM(fare) FROM safari.trips t WHERE t.rider_id = r.rider_id) > (
    SELECT AVG(spend) FROM (
        SELECT SUM(fare) AS spend FROM safari.trips GROUP BY rider_id
    ) sub
)

I put that on the projector without saying anything. Gave the class 30 seconds to read it.

Then I asked: "If this query broke at 3pm on a Friday, how long would it take you to find the bug?"

Silence. Then someone said: "Too long."

That's the setup for CTEs.

What a CTE Actually Is

A CTE - Common Table Expression - is a named, temporary result set that you define before the main query and reference by name. It's not stored anywhere. It doesn't persist after the query runs. But for the duration of that query, it behaves exactly like a table.

WITH cte_name AS (
    -- your inner query here
)
SELECT columns
FROM cte_name
WHERE conditions;

The WITH keyword opens it. The name goes before AS. The inner query goes in brackets. Then the main SELECT reads from the CTE by name like it's reading from any other table.

I put the comparison on the board:

	Subquery	CTE
Where it lives	Embedded inside the main query	Defined before the main query
Has a name	No (alias only in FROM)	Yes - used anywhere in the main query
Readability	Gets messy as complexity grows	Reads like steps in a story
Can be reused	Has to be repeated	Referenced by name, once
Performance	Similar in most cases	Similar in most cases

"A CTE doesn't make your query faster. It makes it readable. And readable queries are the ones that get maintained, debugged, and trusted."

The Dataset: SafariRide

Three tables. safari.drivers - driver name, car model, join date, status (Active/Inactive). safari.riders - rider name, city, membership tier. safari.trips - trip date, distance, fare, rider rating, payment method, with foreign keys linking to both drivers and riders.

Forty trips. Ten drivers. Real enough to ask real questions about.

We also introduced something this session that I want to highlight before the questions: query documentation. Every CTE in today's script had a comment above it:

WITH driver_trip_counts AS (
    -- Step 1: Aggregate trips per driver inside the CTE
    SELECT driver_id, COUNT(*) AS total_trips
    FROM safari.trips
    GROUP BY driver_id
)
-- Step 2: Join back to get readable names and filter
SELECT d.driver_name, dtc.total_trips
FROM safari.drivers d
JOIN driver_trip_counts dtc ON d.driver_id = dtc.driver_id
WHERE dtc.total_trips >= 5
ORDER BY dtc.total_trips DESC;

I told the class: "Good documentation isn't about writing more comments - it's about writing the RIGHT comments. Explain WHY you made a choice, not just WHAT the code does. Your future self, and your teammates, will thank you."

Section A: Basic CTEs

A1. Busy Drivers

Which drivers have completed 5 or more trips?

The challenge: the trips table has driver IDs. The drivers table has names. You need to aggregate first, then join, then filter.

WITH driver_trip_counts AS (
    SELECT driver_id, COUNT(*) AS total_trips
    FROM safari.trips
    GROUP BY driver_id
)
SELECT d.driver_name, dtc.total_trips
FROM safari.drivers d
JOIN driver_trip_counts dtc ON d.driver_id = dtc.driver_id
WHERE dtc.total_trips >= 5
ORDER BY dtc.total_trips DESC;

The CTE does the counting. The outer query joins to get names and filters for the threshold. Two clean steps, each with one job.

A2. Big Spenders

Which riders have spent more than KES 3,000 in total fares?

WITH rider_spend AS (
    SELECT rider_id, SUM(fare) AS total_spent
    FROM safari.trips
    GROUP BY rider_id
)
SELECT r.rider_name, rs.total_spent
FROM safari.riders r
JOIN rider_spend rs ON r.rider_id = rs.rider_id
WHERE rs.total_spent > 3000
ORDER BY rs.total_spent DESC;

The pattern is becoming visible: CTE aggregates, outer query joins and filters. That's the Section A rhythm.

A3. Above-Average Earners

Which drivers earn above the overall average fare?

WITH driver_avg_fare AS (
    SELECT driver_id, ROUND(AVG(fare), 2) AS avg_fare
    FROM safari.trips
    GROUP BY driver_id
)
SELECT d.driver_name, daf.avg_fare
FROM safari.drivers d
JOIN driver_avg_fare daf ON d.driver_id = daf.driver_id
WHERE daf.avg_fare > 955.23
ORDER BY daf.avg_fare DESC;

The overall average (955.23) was calculated separately and noted in a comment. In Section C we'll make that dynamic too - but for now, calculated-once-and-noted is honest and readable.

A4. Popular Payment Methods

Which payment methods were used in more than 12 trips?

WITH payment_counts AS (
    SELECT payment_method, COUNT(*) AS num_trips
    FROM safari.trips
    GROUP BY payment_method
)
SELECT payment_method, num_trips
FROM payment_counts
WHERE num_trips > 12
ORDER BY num_trips DESC;

Notice: no JOIN here. The CTE has everything we need. The outer query just filters it. Not every CTE needs a join - sometimes the aggregation step is all the complexity there is.

A5. Top 3 Busiest Drivers

Just the top 3 - with a tiebreaker.

WITH driver_trip_counts AS (
    SELECT driver_id, COUNT(*) AS total_trips
    FROM safari.trips
    GROUP BY driver_id
)
SELECT d.driver_name, dtc.total_trips
FROM safari.drivers d
JOIN driver_trip_counts dtc ON d.driver_id = dtc.driver_id
ORDER BY dtc.total_trips DESC, d.driver_name ASC
LIMIT 3;

The second ORDER BY column - d.driver_name ASC - is the tiebreaker. If two drivers share the same trip count, alphabetical order decides who appears first. Without it, ties produce inconsistent results across runs.

A6. Riders Who Need a Check-In Call

Which riders have taken fewer than 2 trips - including riders with zero trips?

This is the question that caught people. An INNER JOIN would silently drop riders with no trips at all, because there's nothing to match on. You need a LEFT JOIN inside the CTE.

WITH rider_trip_counts AS (
    SELECT r.rider_id, COUNT(t.trip_id) AS trip_count
    FROM safari.riders r
    LEFT JOIN safari.trips t ON r.rider_id = t.rider_id
    GROUP BY r.rider_id
)
SELECT rr.rider_name, rtc.trip_count
FROM safari.riders rr
JOIN rider_trip_counts rtc ON rr.rider_id = rtc.rider_id
WHERE rtc.trip_count < 2
ORDER BY rtc.trip_count;

Two things worth calling out: the LEFT JOIN inside the CTE keeps riders who haven't taken any trips. And COUNT(t.trip_id) instead of COUNT(*) - counting a specific column returns 0 when there's no match, because t.trip_id is NULL for unmatched rows. COUNT(*) would incorrectly return 1.

Section B: Medium - CTE + JOIN + CASE WHEN

B1. Active Driver Leaderboard

Revenue per driver, but only for Active drivers.

WITH driver_revenue AS (
    SELECT driver_id, SUM(fare) AS total_revenue
    FROM safari.trips
    GROUP BY driver_id
)
SELECT d.driver_name, d.car_model, dr.total_revenue
FROM safari.drivers d
JOIN driver_revenue dr ON d.driver_id = dr.driver_id
WHERE d.status = 'Active'
ORDER BY dr.total_revenue DESC;

The status filter lives in the outer query - not the CTE. The CTE calculates revenue for every driver. The outer query decides which drivers you care about. Keeping the filter outside the CTE makes the CTE reusable. If you later want inactive driver revenue, you change one word in the outer query.

B2. Rating Breakdown

How many trips rated Poor, Good, or Excellent?

CASE WHEN inside a CTE - two sessions combined into one query.

WITH rated AS (
    SELECT trip_id,
        CASE
            WHEN rider_rating <= 2 THEN 'Poor'
            WHEN rider_rating = 3  THEN 'Good'
            ELSE 'Excellent'
        END AS rating_label
    FROM safari.trips
)
SELECT rating_label, COUNT(*) AS num_trips
FROM rated
GROUP BY rating_label
ORDER BY num_trips DESC;

The CTE labels every trip. The outer query counts the labels. Without the CTE, you'd have to repeat the entire CASE WHEN inside a GROUP BY or wrap it in a subquery. With the CTE, the labelling logic is written once, named rated, and the outer query just asks "how many of each label?"

B3 & B6: Premium Riders and Nairobi Riders

Both follow the same pattern - aggregate in the CTE, filter on a rider attribute in the outer JOIN:

-- Premium members only
WHERE r.membership_tier = 'Premium'

-- Nairobi riders only  
WHERE r.city = 'Nairobi'

I pointed out the repetition deliberately. "Notice how the CTE - rider_avg_fare or rider_spend - didn't change at all between these two questions. The aggregation logic is identical. What changed was which riders we cared about, and that change happened in one place: the WHERE clause on the outer query. That's the value of separating the calculation from the filter."

B5. Early vs. Late Month Trips

Did SafariRide do more business in the first or second half of each month?

CASE WHEN combined with EXTRACT - two sessions' worth of tools in one CTE:

WITH labelled_trips AS (
    SELECT trip_id, fare,
        CASE
            WHEN EXTRACT(DAY FROM trip_date) <= 15 THEN 'Early (1-15)'
            ELSE 'Late (16-31)'
        END AS month_period
    FROM safari.trips
)
SELECT month_period, COUNT(*) AS num_trips, SUM(fare) AS total_fare
FROM labelled_trips
GROUP BY month_period
ORDER BY num_trips DESC;

The CTE labels each trip as early or late using EXTRACT(DAY). The outer query summarises both groups. The compounding effect is real - every concept from previous sessions is usable inside a CTE.

Section C: Hard - Chained CTEs

C1. Underperforming Drivers

Drivers with 4+ trips AND an average rating below 3.0 - the ones who need coaching.

Two CTEs, chained: the second one reads from the first.

WITH driver_performance AS (
    SELECT driver_id,
        COUNT(*) AS total_trips,
        ROUND(AVG(rider_rating), 2) AS avg_rating
    FROM safari.trips
    GROUP BY driver_id
),
needs_coaching AS (
    SELECT driver_id, total_trips, avg_rating
    FROM driver_performance
    WHERE total_trips >= 4 AND avg_rating < 3.0
)
SELECT d.driver_name, nc.total_trips, nc.avg_rating
FROM safari.drivers d
JOIN needs_coaching nc ON d.driver_id = nc.driver_id
ORDER BY nc.avg_rating ASC;

driver_performance calculates everything. needs_coaching filters it. The outer query joins to get names. Three clear steps, each with a name and a single job.

The rule for chained CTEs: each one can reference any CTE defined before it. They run in order, top to bottom. Think of them as numbered steps - Step 1 can't reference Step 2, but Step 2 can reference Step 1.

C2. The Rewrite That Made the Case for CTEs

This was the session's most important moment. We wrote C2 two ways: subquery first, then CTE.

The subquery version - which we'd written earlier to show the problem:

SELECT r.rider_name,
    (SELECT SUM(fare) FROM safari.trips t WHERE t.rider_id = r.rider_id) AS total_spent
FROM safari.riders r
WHERE (SELECT SUM(fare) FROM safari.trips t WHERE t.rider_id = r.rider_id) > (
    SELECT AVG(spend) FROM (
        SELECT SUM(fare) AS spend FROM safari.trips GROUP BY rider_id
    ) sub
)

The same logic as a CTE:

WITH rider_spend AS (
    SELECT rider_id, SUM(fare) AS total_spent
    FROM safari.trips
    GROUP BY rider_id
),
spend_benchmark AS (
    SELECT AVG(total_spent) AS avg_spend
    FROM rider_spend
)
SELECT r.rider_name, rs.total_spent
FROM safari.riders r
JOIN rider_spend rs ON r.rider_id = rs.rider_id,
    spend_benchmark sb
WHERE rs.total_spent > sb.avg_spend
ORDER BY rs.total_spent DESC;

I asked the class which one they'd rather come back to in six months. Every hand went to the CTE version. Then I pointed at the subquery version and said: "The subquery calculates SUM(fare) for each rider twice - once in SELECT and once in WHERE. That's the same calculation written in two places. The CTE writes it once, names it rider_spend, and references it wherever needed. DRY - Don't Repeat Yourself - applies to SQL as much as it does to Python."

C4. Window Function Preview - RANK() Inside a CTE

The session closer was a preview of the next topic. You can't filter directly on a window function result in a WHERE clause - WHERE RANK() OVER (...) <= 3 is illegal. But you can calculate the rank inside a CTE and filter on it in the outer query.

WITH ranked_riders AS (
    SELECT r.rider_id, r.rider_name,
        SUM(t.fare) AS total_spent,
        RANK() OVER (ORDER BY SUM(t.fare) DESC) AS spend_rank
    FROM safari.riders r
    JOIN safari.trips t ON r.rider_id = t.rider_id
    GROUP BY r.rider_id, r.rider_name
)
SELECT rider_name, total_spent, spend_rank
FROM ranked_riders
WHERE spend_rank <= 3;

This is one of the most practical uses of CTEs in real analytics work. Window functions - RANK, ROW_NUMBER, DENSE_RANK - almost always require a CTE wrapper to filter on their results. We'll go deep on this next session. Today was just a look at the door.

C5 & C6: Busiest and Most Efficient

C5 chained two CTEs to find the best-rated drivers among the busiest. C6 was the "design your own" question - students chose a business question that needed two steps. The one I modelled: revenue per kilometre driven.

WITH driver_totals AS (
    SELECT driver_id, SUM(fare) AS total_fare, SUM(distance_km) AS total_km
    FROM safari.trips
    GROUP BY driver_id
),
efficiency AS (
    SELECT driver_id, total_fare, total_km,
        ROUND(total_fare / total_km, 2) AS revenue_per_km
    FROM driver_totals
)
SELECT d.driver_name, e.revenue_per_km
FROM safari.drivers d
JOIN efficiency e ON d.driver_id = e.driver_id
ORDER BY e.revenue_per_km DESC
LIMIT 5;

The design-your-own question produced the best student queries of the session. Once the CTE pattern is understood, the creativity goes into the business question rather than the syntax.

The Three Rules for Good CTEs

1. One job per CTE. If you find yourself explaining what a CTE does and the explanation has an "and" in it - split it.

2. Name it what it contains. driver_trip_counts is clear. temp1 is not. The name should make the outer query readable without needing to go back and read the CTE definition.

3. Comments explain WHY, not WHAT. -- Count trips per driver is obvious from the code. -- Using LEFT JOIN to preserve drivers with zero trips explains a decision the next reader wouldn't know to question.

Practice Problems

Section A style:

-- Write a CTE that finds the average distance_km per driver
-- Show only drivers whose average trip distance is above 15km
-- Order longest average first

Section B style:

-- Write a CTE that labels each trip as:
-- 'Short' (distance < 10km), 'Medium' (10–20km), 'Long' (> 20km)
-- Count trips and total fare per label

Section C style:

-- Chain two CTEs:
-- CTE 1: Each rider's trip count and total spend
-- CTE 2: Keep only Gold or Premium tier riders from safari.riders
-- Final: Show riders in CTE 2 who also appear in CTE 1 with total_spent > 2000
-- Ordered by total_spent descending

What I Noticed Teaching This Session

1. The subquery-vs-CTE comparison is the most important moment of the session. Showing the nested subquery first - ugly, repeated logic, hard to debug - and then rewriting it as two named CTEs makes the motivation for CTEs visceral rather than theoretical. Don't skip the ugly version.

2. Documentation changed how students thought about their queries. When I asked them to add a comment to each CTE step before running it, they had to articulate what the CTE was doing - and several caught logical errors before running anything. Writing the comment forced the thinking.

What's Next: Window Functions

CTEs gave us a way to filter on window function results. Next session we understand what those functions actually do.

WITH ranked AS (
    SELECT driver_name, total_trips,
        RANK() OVER (ORDER BY total_trips DESC) AS rank,
        ROUND(AVG(total_trips) OVER (), 1) AS avg_trips
    FROM driver_trip_counts
)
SELECT * FROM ranked WHERE rank <= 5;

RANK, ROW_NUMBER, DENSE_RANK, running totals, moving averages - all next week. And every one of them will use the CTE pattern we built today.

Try It Yourself

- Download SafariDDL.sql

Start with Section A, work through in order. When you get to C2, write the subquery version first - even though it's ugly - before writing the CTE version. That contrast is the whole lesson.

I'm a data trainer in Nairobi running a full data programme -
Python foundations → Data Science or Data Engineering specialisations.
I write weekly about what we covered.
Follow along or drop your questions in the comments.

I Built a SaaS to Prove a FastAPI Router Change Most People Missed

Navas Herbert — Mon, 27 Jul 2026 15:21:43 +0000

Every so often a framework change ships that looks small in the changelog and turns out to be a real architectural unlock. This is one of them.

FastAPI 0.140.0 quietly changed how include_router() works. The changelog says "preserve APIRouter/APIRoute instances instead of cloning them." That's one sentence. Here's what it actually means - and what you can build because of it.

The Old Behavior

When you called app.include_router(sub_router), FastAPI copied every route out of sub_router and rebuilt them inside the parent app. The sub_router object you kept a reference to was now stale.

Anything you added to it afterward was invisible to the running app - because the app was serving a snapshot from the moment of inclusion.

pro_router = APIRouter(prefix="/pro")
app.include_router(pro_router)

# This route is INVISIBLE - app already snapshotted the router at inclusion time
@pro_router.get("/analytics/{slug}")
async def analytics(slug: str):
    ...

The only way around this was to re-include the router - which meant either restarting the process or building a custom mount system most people wouldn't bother with.

The New Behavior

The original APIRouter and APIRoute instances are preserved instead of cloned.

The router object you included IS the router object serving traffic.

pro_router = APIRouter(prefix="/pro")
app.include_router(pro_router)

# This route IS visible - FastAPI holds a live reference to the same object
@pro_router.get("/analytics/{slug}")
async def analytics(slug: str):
    ...

Small sentence. Real consequence: a router can grow new endpoints after your app has already started - same process, no restart, no redeploy.

I Built Something to Prove It

Reading a changelog note is one thing. Watching it work in a live terminal is different. So I built SnipLink - a tiny link-shortener SaaS - specifically to demo this behavior.

The mechanic:

pro_router is defined once, with zero custom routes, and included into the app at startup
Free-tier users get a 404 on /pro/analytics/{slug} - the route literally does not exist yet
The moment a user calls /billing/upgrade, that handler registers a brand-new @router.get("/analytics/{slug}") directly on pro_router - the same live object FastAPI already holds
Same process. No restart. No redeploy.

Here's the full project structure:

sniplink/
├── app/
│   ├── main.py          # app setup, router inclusion
│   ├── routers/
│   │   ├── auth.py      # signup, api key generation
│   │   ├── links.py     # shorten, redirect
│   │   ├── billing.py   # upgrade - the moment the route appears
│   │   └── pro.py       # pro_router lives here, starts empty
│   └── core/
│       └── store.py     # in-memory storage
├── pyproject.toml
└── requirements.txt

The Key Code

pro.py - the router that starts empty:

from fastapi import APIRouter, Depends
from app.core.store import store
from app.core.auth import get_current_user

pro_router = APIRouter(prefix="/pro", tags=["pro"])

def register_analytics_route():
    """
    Called at runtime - after the router is already included in the app.
    Under the old FastAPI behavior, this would have been invisible.
    Under 0.140.0+, the app holds a live reference to pro_router,
    so this route appears immediately, no restart needed.
    """
    @pro_router.get("/analytics/{slug}")
    async def analytics(slug: str, user=Depends(get_current_user)):
        link = store["links"].get(slug)
        if not link:
            return {"detail": "Unknown slug"}
        return {
            "slug": slug,
            "clicks": link["clicks"],
            "target_url": link["target_url"]
        }

billing.py - the upgrade endpoint that fires the registration:

from fastapi import APIRouter, Depends
from app.routers.pro import register_analytics_route
from app.core.auth import get_current_user
from app.core.store import store

router = APIRouter(prefix="/billing", tags=["billing"])

@router.post("/upgrade")
async def upgrade(user=Depends(get_current_user)):
    # Upgrade the user's plan in storage
    store["users"][user["api_key"]]["plan"] = "pro"

    # Register the route on the live router object
    register_analytics_route()

    return {
        "plan": "pro",
        "message": "Pro unlocked. Try GET /pro/analytics/{slug} now."
    }

main.py - inclusion happens once, at startup:

from fastapi import FastAPI
from app.routers.auth import router as auth_router
from app.routers.links import router as links_router
from app.routers.billing import router as billing_router
from app.routers.pro import pro_router

app = FastAPI(title="SnipLink")

app.include_router(auth_router)
app.include_router(links_router)
app.include_router(billing_router)
app.include_router(pro_router)   # starts empty - grows at runtime

The Live Demo - Terminal Proof

Here's the full sequence, no edits:

Step 1 - Sign up (free tier):

curl -X POST localhost:8000/auth/signup \
  -H "Content-Type: application/json" \
  -d '{"email": "navas123@gmail.com"}'

{"api_key": "sk_la3yzxfuetrazshtu92yhnib", "plan": "free"}

Step 2 - Shorten a link:

curl -X POST localhost:8000/links \
  -H "X-API-Key: sk_la3yzxfuetrazshtu92yhnib" \
  -H "Content-Type: application/json" \
  -d '{"target_url": "https://fastapi.tiangolo.com"}'

{"slug": "pneu49", "target_url": "https://fastapi.tiangolo.com/", "clicks": 0}

Step 3 - Hit the pro endpoint (free tier):

curl localhost:8000/pro/analytics/pneu49 \
  -H "X-API-Key: sk_la3yzxfuetrazshtu92yhnib"

{"detail": "Not Found"}

The route doesn't exist. This isn't a permissions error - 404 Not Found means FastAPI has no matching route registered. The endpoint is not there.

Step 4 - Upgrade:

curl -X POST localhost:8000/billing/upgrade \
  -H "X-API-Key: sk_la3yzxfuetrazshtu92yhnib"

{"plan": "pro", "message": "Pro unlocked. Try GET /pro/analytics/{slug} now."}

register_analytics_route() just fired. Same process. No restart.

Step 5 - Hit the same path again:

curl localhost:8000/pro/analytics/pneu49 \
  -H "X-API-Key: sk_la3yzxfuetrazshtu92yhnib"

{"slug": "pneu49", "clicks": 0, "target_url": "https://fastapi.tiangolo.com/"}

The response changed. The route now exists.

The Detail Worth Slowing Down On

Look at Steps 3 and 5 again. Both requests hit the same URL. Same server. Same process. The difference is what happened between them.

Step 3 returns "Not Found" - FastAPI routing matched nothing.
Step 5 returns real data - the route is now registered and serving.

And open /docs between those two requests. The OpenAPI schema gains a new endpoint in real time - you can watch it appear in Swagger UI the moment the upgrade fires. No page reload trick. FastAPI regenerates the schema from the live router state.

What This Unlocks for SaaS

Before this refactor, dynamic route registration was fragile enough that most people avoided it. You'd reach for a different pattern - a middleware check, a feature flag in the handler, or just a full restart.

Now the pattern is clean: feature gating at the router level.

User on free tier → route doesn't exist → clean 404
User upgrades → route appears → feature is live
No middleware, no flag checks, no infrastructure change

This is different from a permission check inside a handler. The route genuinely doesn't exist for free users. They can't hit a 403, they can't probe the endpoint shape, they see exactly what unauthenticated users see for any nonexistent path.

One Caveat Worth Knowing

register_analytics_route() is called every time someone upgrades. If multiple users upgrade, the function runs multiple times. FastAPI handles duplicate route registration by keeping the first and ignoring subsequent identical paths - but it's worth being deliberate about this.

In production you'd want a guard:

_analytics_registered = False

def register_analytics_route():
    global _analytics_registered
    if _analytics_registered:
        return

    @pro_router.get("/analytics/{slug}")
    async def analytics(slug: str, user=Depends(get_current_user)):
        ...

    _analytics_registered = True

Or use a module-level set to track which routes have been registered, keyed by path.

Deployment

One command. One minute.

fastapi deploy

FastAPI Cloud
🚀 Deploying app...
✅ Build complete!
...
✅ Ready the chicken! 🐔
Your app is ready at https://sniplink.fastapicloud.dev

One note worth saving if you use a nested app/ layout: run fastapi dev with no file path argument. Passing app/main.py explicitly hits a known import-path bug in nested layouts - it raises ModuleNotFoundError: No module named 'app'. Declare the entrypoint in pyproject.toml instead:

[tool.fastapi]
entrypoint = "app.main:app"

That sidesteps the bug for both fastapi dev and fastapi deploy.

Try It Yourself

git clone https://github.com/Navashub/sniplink
cd sniplink
python3 -m venv .venv && source .venv/bin/activate
pip install -r requirements.txt
fastapi dev

Open http://127.0.0.1:8000/docs and follow the upgrade sequence from /docs - watch the schema change in real time.

Live demo: https://sniplink.fastapicloud.dev

Requires fastapi[standard]>=0.140.0 - the first version with the router-preservation refactor this whole project exists to demo.

I'm a Data Engineer and AI Builder in Nairobi - building in public across FastAPI, RAG systems, data pipelines, and SaaS products. I write about what I'm building as I build it, mistakes included.

SQL Subqueries: A Query Inside a Query (And Why That's Powerful)

Navas Herbert — Wed, 22 Jul 2026 19:07:18 +0000

Every query we've written so far has been self-contained. One SELECT, one table, one question.

But some questions can't be answered in one step.

"Which quiz scores are higher than the group average?" - you need the average first. "Which members scored above 80?" - you need the member IDs first, then the names. "Who improved from Quiz 1 to Quiz 2?" - you need two separate lookups before you can compare.

A subquery is the solution. It's a SELECT written inside another SELECT. The inner one runs first - its result gets handed to the outer query to use. Two questions, one statement.

Today we worked through 8 questions on a new dataset: a study group with 16 quiz scores across 7 members. We solved every question manually first - two separate queries - then collapsed them into one using a subquery. That pattern is the most important thing in this session. Not the syntax. The pattern.

Where Subqueries Can Live

Before any questions, I drew this on the board:

-- 1) In WHERE/HAVING - filter using a calculated value
WHERE score > (SELECT AVG(score) FROM quiz_scores)

-- 2) In FROM - use a calculated table as if it were a real table
FROM (SELECT member_id, AVG(score) FROM quiz_scores GROUP BY member_id) AS averages

-- 3) In SELECT - add a single computed value to every row
SELECT score, (SELECT AVG(score) FROM quiz_scores) AS group_avg

Three positions. Different jobs. We'll use all three today.

The Study Group Dataset

Two tables: study_group.members (member ID, name, city) and study_group.quiz_scores (score ID, member ID, quiz number, score). Sixteen rows of quiz data across seven members. Simple enough to see what's happening, realistic enough that the questions actually matter.

Q1: Scores Above the Group Average

"Which quiz scores are higher than the average score of the whole group?"

Step 1 - find the average:

SELECT AVG(score) FROM study_group.quiz_scores;
-- 73.5625

Step 2 - use that number:

SELECT score_id, member_id, score
FROM study_group.quiz_scores
WHERE score > 73.5625;

This works. But it's fragile. Next week, when new scores are added, the average changes and this query is wrong. We've hardcoded a number that should be dynamic.

Subquery - dynamic forever:

SELECT score_id, member_id, score
FROM study_group.quiz_scores
WHERE score > (SELECT AVG(score) FROM study_group.quiz_scores);

The inner query runs first, calculates today's average, and passes it to the outer WHERE. Tomorrow, when new scores land, this query automatically uses the new average. No manual updating.

I told the class: "The moment you find yourself copying a number from one query result into another query - stop. That's a subquery waiting to be written."

Q2: The Single Highest Score

"Find the member who recorded the highest score."

Step 1:

SELECT MAX(score) FROM study_group.quiz_scores;
-- 95

Step 2:

SELECT score_id, member_id, score
FROM study_group.quiz_scores
WHERE score = 95;

Subquery:

SELECT score_id, member_id, score
FROM study_group.quiz_scores
WHERE score = (SELECT MAX(score) FROM study_group.quiz_scores);

Clean, one statement, always returns the current highest score regardless of what the data looks like. If two members tie for first, both rows come back - MAX() returns the value, and = matches every row with that value.

Q3: Beat Felix's Score

"Find all scores that beat Felix's Quiz 1 result."

This one is interesting because the inner query isn't an aggregate - it's a lookup for a specific row.

Step 1:

SELECT score
FROM study_group.quiz_scores
WHERE member_id = 5 AND quiz_number = 1;
-- 91

Step 2:

SELECT score_id, member_id, score
FROM study_group.quiz_scores
WHERE score > 91;

Subquery:

SELECT score_id, member_id, score
FROM study_group.quiz_scores
WHERE score > (
    SELECT score
    FROM study_group.quiz_scores
    WHERE member_id = 5 AND quiz_number = 1
);

The inner query returns one value - Felix's Quiz 1 score. The outer query compares every row against it. I made a point here: "A subquery in WHERE must return exactly one value when used with = or >. If Felix had two Quiz 1 entries and the inner query returned two rows, PostgreSQL would throw an error. Always be sure your inner query returns what the outer query expects."

Q4: Names of Members Who Scored Above 80

Now subqueries get more interesting. The quiz scores table has member IDs. The members table has names. The question asks for names. Two tables, two steps, one answer.

Step 1 - get the IDs:

SELECT member_id
FROM study_group.quiz_scores
WHERE score > 80;
-- 1, 2, 4, 5, 7

Step 2 - get the names using IN:

SELECT name, member_id, city
FROM study_group.members
WHERE member_id IN (1, 2, 4, 5, 7);

Subquery:

SELECT name, member_id, city
FROM study_group.members
WHERE member_id IN (
    SELECT member_id
    FROM study_group.quiz_scores
    WHERE score > 80
);

The IN operator is perfect here because the inner query returns a list - multiple member IDs, not just one. IN checks whether each row's member_id appears in that list.

The connection I made to last week: "IN with a subquery is the same IN operator you used with a hardcoded list like IN ('Dairy', 'Beverages'). The only difference is the list is now calculated dynamically by another query instead of typed in by you."

Q5: Members Who Never Scored Above 80

One word changes. IN becomes NOT IN. The result flips completely.

SELECT name, member_id, city
FROM study_group.members
WHERE member_id NOT IN (
    SELECT member_id
    FROM study_group.quiz_scores
    WHERE score > 80
);

This is the cleanest demonstration of NOT IN with a subquery - you don't rewrite the inner query at all, you just negate the outer condition. The members table hasn't changed. The quiz scores haven't changed. One word, completely different answer.

The honest warning I give every time: NOT IN behaves incorrectly when the subquery returns any NULL values. If member_id is NULL for any quiz score row, NOT IN returns zero results - silently, with no error. For a clean dataset like this one it's fine. In production, be aware. NOT EXISTS is the NULL-safe alternative.

Q6: Members With Average Above 70 - Subquery in FROM

This is where subqueries start feeling like a different tool entirely.

"Which members have a personal quiz average above 70?"

You can't write WHERE avg_score > 70 directly - avg_score is an alias that doesn't exist until after SELECT runs. The solution: calculate the averages first, treat that result as a table, then filter it.

Step 1 - build the averages:

SELECT member_id, ROUND(AVG(score), 1) AS avg_score
FROM study_group.quiz_scores
GROUP BY member_id;

Subquery in FROM:

SELECT member_averages.member_id, avg_score
FROM (
    SELECT member_id, ROUND(AVG(score), 1) AS avg_score
    FROM study_group.quiz_scores
    GROUP BY member_id
) AS member_averages
WHERE avg_score > 70;

The inner query builds the averages table. The outer query filters it. Notice: the inner result gets an alias - AS member_averages - because PostgreSQL requires every derived table in FROM to have a name.

I described it this way: "The subquery in FROM is a temporary table that only exists for the duration of this query. It has no name in the database, it doesn't persist, it can't be queried separately. It's built, used, and thrown away - all in one statement."

We also wrote the version that includes the member's name - which requires a JOIN inside the subquery:

SELECT member_id, name, avg_score
FROM (
    SELECT m.member_id, name, ROUND(AVG(score), 1) AS avg_score
    FROM study_group.quiz_scores q
    INNER JOIN study_group.members m ON q.member_id = m.member_id
    GROUP BY m.member_id, name
) AS member_averages
WHERE avg_score > 70;

A JOIN inside a subquery, filtered by the outer query. This is the point where the syntax gets dense but the logic stays clean - because we built it step by step.

Q7: Show the Group Average Alongside Every Row - Subquery in SELECT

"Show each quiz score alongside the overall group average, so you can see at a glance whether each score is above or below the mean."

SELECT
    score_id,
    member_id,
    score,
    (SELECT ROUND(AVG(score), 1) FROM study_group.quiz_scores) AS group_avg
FROM study_group.quiz_scores
ORDER BY score DESC;

score_id | member_id | score | group_avg
---------|-----------|-------|----------
12       | 5         | 95    | 73.6
8        | 4         | 92    | 73.6
3        | 5         | 91    | 73.6
...
16       | 7         | 45    | 73.6

The inner query returns one number - the group average - and that number gets stamped onto every row. The group average is the same on every row because it's calculated once and repeated.

"This is useful when you want to show a benchmark alongside individual values. A student's mark and the class average in the same row. A product's price and the category average. A daily sales figure and the monthly average. Same pattern every time - a single-value subquery in the SELECT list."

The rule: a subquery in SELECT must return exactly one row and one column. More than one row and PostgreSQL throws an error. This is a scalar subquery - it returns a scalar (single) value.

Q8: Members Whose Personal Average Beats the Group Average - Subquery in HAVING

The final position: HAVING. This is the cleanest example of why subqueries in HAVING exist.

"Which members have a personal average higher than the overall group average?"

You need two averages: each member's average (requires GROUP BY) and the group's overall average (a single number). You can't put the group average in WHERE because WHERE runs before grouping. HAVING runs after. And a subquery gives you the comparison value dynamically.

SELECT
    member_id,
    ROUND(AVG(score), 1) AS avg_score
FROM study_group.quiz_scores
GROUP BY member_id
HAVING AVG(score) > (
    SELECT ROUND(AVG(score), 1) FROM study_group.quiz_scores
);

I wrote the order of operations on the board: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY. "The subquery in HAVING runs at step 4, after grouping has already happened. By then each member has their own average. HAVING compares that average against the subquery result and keeps only the members who beat it."

The Three Positions - Summary

Position	What the subquery does	Must return
WHERE / HAVING	A value to filter against	One value (scalar) or a list (for IN)
FROM	A temporary table the outer query treats as real	Any number of rows and columns (needs an alias)
SELECT	A single value added to every output row	Exactly one row, one column

The Two-Step Pattern - The Most Important Thing

Every question today followed the same structure:

Write two separate queries. Get both answers. Verify them.
Take the result of Query 1 and replace it with a subquery inside Query 2.

This isn't just a teaching technique - it's how experienced SQL developers actually write subqueries. You don't start with the nested version. You build each piece, confirm it works, then combine. When a subquery breaks, you pull the inner query out, run it separately, check the result, and put it back. Debugging a subquery is just debugging two queries.

What I Noticed Teaching This Session

1. The two-step pattern removes the intimidation. Nobody finds a nested query readable the first time they see it. But everyone can follow two separate queries. Building the two steps first - then showing how they collapse into one - makes the final syntax feel like a natural conclusion rather than a magic trick.

What's Next: CTEs

Subqueries are powerful. But deeply nested subqueries can become hard to read. CTEs - Common Table Expressions - are the cleaner alternative for complex multi-step logic.

-- The same Q6 query, written as a CTE instead of a subquery
WITH member_averages AS (
    SELECT m.member_id, name, ROUND(AVG(score), 1) AS avg_score
    FROM study_group.quiz_scores q
    JOIN study_group.members m ON q.member_id = m.member_id
    GROUP BY m.member_id, name
)
SELECT member_id, name, avg_score
FROM member_averages
WHERE avg_score > 70;

Same result. Different structure. The intermediate table has a name, lives at the top, and the main query reads cleanly against it. Next session we'll rewrite today's subqueries as CTEs and see which approach fits which problem.

I'm a data trainer in Nairobi running a full data programme -
Python foundations → Data Science or Data Engineering specialisations.

SQL Date & Time: From Today's Date to Birthday Reminders in 12 Questions

Navas Herbert — Tue, 21 Jul 2026 19:58:49 +0000

Every dataset has dates in it somewhere. Date of birth. Exam date. Order date. Registration date. And almost every useful question you can ask about that data involves doing something with those dates - comparing them, formatting them, extracting parts of them, calculating distances between them.

Today we worked through 12 questions on Greenwood Academy's student and exam records. Each one built on the last. By Question 12 we were building a birthday reminder query that could run in a real school system - using the same functions we introduced in Question 1.

Here's every question, every function, and every moment that landed.

Q1: What Time Is It Right Now?

Before touching any table data, we started with the simplest date question possible.

SELECT
    CURRENT_DATE       AS today,
    NOW()              AS current_datetime,
    CURRENT_TIMESTAMP  AS timestamp;

today       | current_datetime              | timestamp
------------|-------------------------------|-------------------------------
2026-07-05  | 2026-07-05 10:23:41.847+03   | 2026-07-05 10:23:41.847+03

Three ways to ask PostgreSQL what time it is. The distinction matters:

CURRENT_DATE - date only, no time component
NOW() and CURRENT_TIMESTAMP - both return date + time + timezone; they're effectively the same

"Use CURRENT_DATE when you only care about the date - filtering by today, calculating days between two dates. Use NOW() when you need the exact moment something happened - audit logs, timestamps on records."

Q2: Pull the Year, Month, and Day Out Separately

A date of birth stored as 2008-03-15 is a single value. What if you want just the year? Just the month? EXTRACT does it.

SELECT
    first_name,
    date_of_birth,
    EXTRACT(YEAR  FROM date_of_birth) AS birth_year,
    EXTRACT(MONTH FROM date_of_birth) AS birth_month,
    EXTRACT(DAY   FROM date_of_birth) AS birth_day
FROM greenwood_academy.students;

first_name | date_of_birth | birth_year | birth_month | birth_day
-----------|---------------|------------|-------------|----------
Amina      | 2008-03-15    | 2008       | 3           | 15
Brian      | 2010-11-22    | 2010       | 11           | 22
Njeri      | 2007-07-04    | 2007       | 7            | 4

Syntax: EXTRACT(field FROM column). The field can be YEAR, MONTH, DAY, HOUR, MINUTE, SECOND, DOW (day of week), and more. We'll use several of these before the end of the session.

"EXTRACT doesn't change the column - it reads it and returns a number. You're not modifying anything, you're asking a specific question about part of the date."

Q3: How Old Is Each Student?

AGE() calculates the interval between two dates. Pass it CURRENT_DATE and a date of birth and it returns the full age - years, months, and days.

SELECT
    first_name,
    last_name,
    date_of_birth,
    AGE(CURRENT_DATE, date_of_birth) AS full_age
FROM greenwood_academy.students;

first_name | date_of_birth | full_age
-----------|---------------|---------------------------
Amina      | 2008-03-15    | 18 years 3 mons 21 days
Brian      | 2010-11-22    | 15 years 7 mons 14 days
Njeri      | 2007-07-04    | 19 years 0 mons 1 day

The output is a PostgreSQL interval - a readable duration. Perfect for displaying a person's age in a report. Not useful yet if you need to sort or compare ages numerically, which is where Q4 comes in.

Q4: Age in Whole Years - The Nesting Trick

For sorting, filtering, or displaying a clean number, you want age in years only. The solution: nest AGE() inside EXTRACT().

SELECT
    CONCAT(first_name, ' ', last_name) AS full_name,
    EXTRACT(YEAR FROM AGE(date_of_birth)) AS age_years
FROM greenwood_academy.students
ORDER BY age_years DESC;

full_name       | age_years
----------------|----------
Njeri Kamau     | 19
Amina Hassan    | 18
Brian Otieno    | 15

I wrote the order of operations on the board: "The inner AGE() runs first - it calculates the interval between today and the date of birth. Then the outer EXTRACT(YEAR FROM ...) pulls just the year number out of that interval. Two functions, one clean integer."

This is the first nesting pattern of the session, and it's important - it shows that SQL functions can be composed, each one feeding its output into the next.

Q5: How Many Days Ago Was Each Exam?

Date arithmetic in PostgreSQL is simple: subtract two dates and you get the number of days between them as an integer.

SELECT
    result_id,
    exam_date,
    CURRENT_DATE - exam_date AS days_ago
FROM greenwood_academy.exam_results;

result_id | exam_date   | days_ago
----------|-------------|----------
1         | 2024-03-15  | 843
2         | 2024-04-02  | 825
3         | 2024-05-10  | 787

date1 - date2 returns an integer. No function required. The simplicity is the point - I told the class: "When you need days between two dates, just subtract them. PostgreSQL handles the calendar for you - leap years, month lengths, all of it. You get the right number."

Q6: When Are Results Released? Adding Time with INTERVAL

You can add time to a date using INTERVAL. The value and the unit go together inside quotes.

SELECT
    result_id,
    exam_date,
    exam_date + INTERVAL '2 weeks' AS result_release_date
FROM greenwood_academy.exam_results;

result_id | exam_date   | result_release_date
----------|-------------|--------------------
1         | 2024-03-15  | 2024-03-29
2         | 2024-04-02  | 2024-04-16

INTERVAL '2 weeks' and INTERVAL '14 days' give the same result - I ran both to prove it. You can use days, weeks, months, years, hours, minutes - whatever unit fits the problem.

Real-world use: payment due dates, subscription renewal dates, probation end dates, warranty expiry. Any situation where you need to add a fixed duration to a stored date.

Q7: Displaying Dates in a Human-Friendly Format

Dates stored as 2024-03-15 are machine-readable. Reports need Friday, 15th March 2024. TO_CHAR() handles the formatting.

SELECT
    result_id,
    exam_date,
    TO_CHAR(exam_date, 'Day, DDth Month YYYY') AS friendly_date
FROM greenwood_academy.exam_results;

result_id | exam_date   | friendly_date
----------|-------------|---------------------------
1         | 2024-03-15  | Friday   , 15th March  2024
2         | 2024-04-02  | Tuesday  , 02nd April  2024

One thing worth noting: PostgreSQL pads Day and Month to a fixed width with trailing spaces - you can see "Friday " with extra spaces. Add FM (Fill Mode) at the start of the format to suppress padding: TO_CHAR(exam_date, 'FMDay, DDth FMMonth YYYY').

Q8: Two Formats, One Query

TO_CHAR() is flexible enough to produce any format you need. Here we run two different formats side by side:

SELECT
    result_id,
    exam_date,
    TO_CHAR(exam_date, 'DD/MM/YYYY')  AS first_format,
    TO_CHAR(exam_date, 'Month YYYY')  AS month_year
FROM greenwood_academy.exam_results;

result_id | exam_date   | first_format | month_year
----------|-------------|--------------|------------
1         | 2024-03-15  | 15/03/2024   | March 2024
2         | 2024-04-02  | 02/04/2024   | April 2024

Common format codes worth memorising:

Code	Meaning	Example
`DD`	Day number (with leading zero)	`05`
`MM`	Month number	`03`
`YYYY`	4-digit year	`2024`
`Month`	Full month name	`March`
`Mon`	Abbreviated month	`Mar`
`Day`	Full day name	`Friday`
`FMDay`	Day name, no padding	`Friday`
`DDth`	Day with suffix	`15th`

Q9: Filtering by Part of a Date

EXTRACT isn't only useful in SELECT - it works inside WHERE too. This is one of the most commonly used date patterns in real queries.

SELECT
    first_name, last_name, date_of_birth
FROM greenwood_academy.students
WHERE EXTRACT(YEAR FROM date_of_birth) = 2008;

first_name | last_name | date_of_birth
-----------|-----------|---------------
Amina      | Hassan    | 2008-03-15
Samuel     | Kiprop    | 2008-09-11

"You don't need to know the full date. You just need the year. EXTRACT pulls it out, WHERE compares it. This pattern works for any part - filter by month to find March birthdays, filter by day to find everyone born on the 1st of any month."

Q10: Grouping Exams by Month with DATE_TRUNC

How many exams were held in each month? The challenge: exam dates are specific days (2024-03-15, 2024-03-21). You can't GROUP BY the exact date - you'd get one row per exam. You need to snap all March dates to a single value.

DATE_TRUNC() does this - it rounds a date down to the start of a specified unit.

SELECT
    DATE_TRUNC('month', exam_date) AS exam_month,
    COUNT(*)                        AS total_exams
FROM greenwood_academy.exam_results
GROUP BY DATE_TRUNC('month', exam_date)
ORDER BY exam_month;

exam_month           | total_exams
---------------------|------------
2024-03-01 00:00:00  | 4
2024-04-01 00:00:00  | 3
2024-05-01 00:00:00  | 5

DATE_TRUNC('month', '2024-03-15') returns 2024-03-01. So does DATE_TRUNC('month', '2024-03-21'). Both March dates become the same value - GROUP BY can now treat them as a single group.

"DATE_TRUNC is how you go from daily data to weekly or monthly summaries without changing your raw data. You're not deleting the day - you're temporarily ignoring it for the purpose of grouping."

Q11: Was the Exam on a Weekday or Weekend?

EXTRACT(DOW FROM date) returns the day of the week as a number: 0 = Sunday, 1 = Monday, through 6 = Saturday.

SELECT
    result_id,
    exam_date,
    TO_CHAR(exam_date, 'FMDay')            AS day_name,
    EXTRACT(DOW FROM exam_date)            AS day_number,
    CASE
        WHEN EXTRACT(DOW FROM exam_date) IN (0, 6) THEN 'Weekend'
        ELSE 'Weekday'
    END AS day_type
FROM greenwood_academy.exam_results;

result_id | exam_date   | day_name  | day_number | day_type
----------|-------------|-----------|------------|----------
1         | 2024-03-15  | Friday    | 5          | Weekday
2         | 2024-04-02  | Tuesday   | 2          | Weekday
3         | 2024-03-17  | Sunday    | 0          | Weekend

Two things working together here: TO_CHAR(exam_date, 'FMDay') gives the readable name. EXTRACT(DOW FROM exam_date) gives the number we can test in CASE WHEN. Sunday is 0 and Saturday is 6 - those two values are the Weekend, everything else is a Weekday.

This is also the first query where CASE WHEN connects directly to a date function - a pattern that shows up constantly in real reporting: label the data based on when something happened.

Q12: The Session Closer - Birthday Reminders

The most complex query of the session. "Find every student whose birthday falls in the next 30 days."

The challenge: dates of birth are from years ago. 2008-03-15 can't be compared directly to today's date range because the year is wrong. You need to rebuild "this year's version" of each birthday.

SELECT
    first_name,
    last_name,
    date_of_birth,
    TO_CHAR(date_of_birth, 'DDth FMMonth') AS birthday
FROM greenwood_academy.students
WHERE
    TO_DATE(
        TO_CHAR(CURRENT_DATE, 'YYYY') || '-' ||
        TO_CHAR(date_of_birth, 'MM-DD'),
        'YYYY-MM-DD'
    ) BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '30 days';

I broke this down step by step before running it:

Step 1 - Extract the current year as text: TO_CHAR(CURRENT_DATE, 'YYYY') → '2026'

Step 2 - Extract the birth month and day: TO_CHAR(date_of_birth, 'MM-DD') → '03-15'

Step 3 - Join them with || (PostgreSQL's string concatenation): '2026' || '-' || '03-15' → '2026-03-15'

Step 4 - Convert that text back to a real date: TO_DATE('2026-03-15', 'YYYY-MM-DD') → 2026-03-15

Step 5 - Check if that date falls in the next 30 days: BETWEEN CURRENT_DATE AND CURRENT_DATE + INTERVAL '30 days'

The result: every student with a birthday coming up in the next month, with their birthday formatted as 15th March.

"This is a real-world pattern. Schools use it for birthday lists. HR systems use it for contract renewals. Subscription services use it for renewal reminders. The logic is always the same - rebuild this year's version of a stored date, then test whether it falls in your target window."

The Full Function Reference

Function	What it does	Example
`CURRENT_DATE`	Today's date	`2026-07-05`
`NOW()`	Current date + time + timezone	`2026-07-05 10:23:41+03`
`EXTRACT(field FROM date)`	Pull one part out of a date	`EXTRACT(YEAR FROM dob)` → `2008`
`AGE(date1, date2)`	Interval between two dates	`18 years 3 mons 21 days`
`date1 - date2`	Days between two dates	`843`
`date + INTERVAL '14 days'`	Add time to a date	`2024-03-29`
`TO_CHAR(date, format)`	Format a date as text	`'15th March 2024'`
`DATE_TRUNC('month', date)`	Round date down to month start	`2024-03-01`
`EXTRACT(DOW FROM date)`	Day of week (0=Sun, 6=Sat)	`5` (Friday)
`TO_DATE(text, format)`	Parse text into a date	`TO_DATE('2026-03-15', 'YYYY-MM-DD')`

Practice Problems

Easy:

-- 1. Show each student's first name and the year they were born
-- 2. How many days old is each student? (CURRENT_DATE - date_of_birth)
-- 3. Format each exam date as 'April 2024' style

Medium:

-- Show each student's full name and age in whole years
-- Order from oldest to youngest

SELECT
    CONCAT(first_name, ' ', last_name) AS full_name,
    EXTRACT(YEAR FROM AGE(date_of_birth)) AS age_years
FROM greenwood_academy.students
ORDER BY age_years DESC;

Challenge:

-- Monthly exam summary:
-- Show which month each exam fell in, how many exams that month,
-- and whether the month was in Term 1 (Jan-Apr), Term 2 (May-Aug),
-- or Term 3 (Sep-Dec)
-- Use DATE_TRUNC for grouping and CASE WHEN + EXTRACT for the term label
-- Your query here...

I'm a data trainer in Nairobi running a full data programme

SQL Functions: Cleaning Text and Rounding Numbers the Right Way

Navas Herbert — Mon, 20 Jul 2026 21:23:20 +0000

Data arrives messy.

Names in ALL CAPS. Cities spelled differently in different rows. Exam marks with five decimal places nobody asked for. Teachers listed as "Mr. Njoroge" when the report needs "Prof. Njoroge". First and last names in separate columns when the output needs a single full name.

Every analyst has sat with a spreadsheet full of this kind of thing and spent hours fixing it manually. Today we taught the class how to fix it in SQL - once, in a query, automatically, for every row in the table.

Two categories. String functions for text. Number functions for numeric values. One dataset: Greenwood Academy - students, subjects, teachers, exam results.

Part 1: String Functions

UPPER() and LOWER() - Forcing a Case

The most basic text transformation. UPPER() converts everything to capitals. LOWER() converts everything to lowercase.

-- All first names in capitals
SELECT
    first_name,
    UPPER(first_name) AS upper_first_name
FROM greenwood_academy.students;

first_name  | upper_first_name
------------|------------------
Amina       | AMINA
Brian       | BRIAN
Njeri       | NJERI

-- All last names in lowercase
SELECT
    last_name,
    LOWER(last_name)
FROM greenwood_academy.students;

The practical use case I gave the class: "Imagine you're comparing two datasets - one where names were entered in ALL CAPS and one where they're in Title Case. Before you JOIN them, you run LOWER() on both sides so the comparison is apples to apples. One function call, no manual editing."

INITCAP() - Title Case, Automatically

INITCAP() capitalises the first letter of each word and lowercases everything else. It's the clean-up function for data that arrived in ALL CAPS or all lowercase.

I ran this live, on my own name, before touching the table:

SELECT 'NAVAS HERBERT';
SELECT INITCAP('NAVAS HERBERT');

NAVAS HERBERT
Navas Herbert

One function. Every word properly capitalised. Then the same thing on the students table - names that arrived in the wrong case, cleaned in a single column transformation.

The distinction between the three:

Function	Input	Output
`UPPER()`	`navas herbert`	`NAVAS HERBERT`
`LOWER()`	`NAVAS HERBERT`	`navas herbert`
`INITCAP()`	`NAVAS HERBERT`	`Navas Herbert`

For most name-cleaning tasks, INITCAP() is the right call. For case-insensitive comparisons, LOWER() both sides. UPPER() is useful for codes, abbreviations, and labels where everything should shout.

LENGTH() - How Long Is This Text?

LENGTH() counts every character in a string, including spaces.

SELECT
    subject_name,
    LENGTH(subject_name) AS name_length
FROM greenwood_academy.subjects;

subject_name      | name_length
------------------|------------
Mathematics       | 11
English           | 7
Kiswahili         | 9
Physical Education| 18

Not the most glamorous function, but one of the most useful for data validation. "If a phone number column has LENGTH() of anything other than 10, something was entered wrong. If a national ID has LENGTH() other than 8, flag it. LENGTH() is how you catch formatting errors without reading every row."

SUBSTRING() - Cutting Out Part of a Text

Sometimes you only want a piece of the string. SUBSTRING(text, start, length) cuts from a starting position for a specified number of characters.

SELECT
    first_name,
    SUBSTRING(first_name, 1, 3) AS name_prefix
FROM greenwood_academy.students;

first_name  | name_prefix
------------|------------
Amina       | Ami
Brian       | Bri
Wanjiku     | Wan

Position 1 is the first character. SUBSTRING(first_name, 1, 3) means: start at position 1, take 3 characters.

Real-world use cases: extracting the year from a date string, pulling a department code from a longer employee ID, trimming a product SKU to its category prefix. Any time you need part of a string consistently, SUBSTRING is the tool.

CONCAT() - Joining Text Together

CONCAT() takes multiple pieces of text and joins them into one. You can pass columns, string literals, spaces - anything.

-- Combining first and last name into a full name
SELECT
    first_name,
    last_name,
    CONCAT(first_name, ' ', last_name) AS full_name
FROM greenwood_academy.students;

first_name | last_name  | full_name
-----------|------------|------------------
Amina      | Hassan     | Amina Hassan
Brian      | Otieno     | Brian Otieno
Wanjiku    | Kamau      | Wanjiku Kamau

The space in the middle - ' ' - is a string literal passed as an argument. Without it, CONCAT(first_name, last_name) gives you AminaHassan. Always include the separator you actually want.

Then the more expressive version - building readable sentences from columns:

-- Combine subject and teacher into a readable label
SELECT
    CONCAT(subject_name, ' - ', teacher_name) AS subject_teacher
FROM greenwood_academy.subjects;

subject_teacher
-----------------------------
Mathematics - Mr. Njoroge
English - Ms. Achieng
Kiswahili - Mr. Kamau

And going further:

SELECT
    CONCAT(teacher_name, ' is teaching ', subject_name) AS description
FROM greenwood_academy.subjects;

description
-----------------------------------------
Mr. Njoroge is teaching Mathematics
Ms. Achieng is teaching English
Mr. Kamau is teaching Kiswahili

One student - Otieno - said: "This is how report generators work, isn't it? They're just CONCATting column values into sentences." Exactly. Every automated email, every generated PDF header, every dashboard label that says "Welcome, Amina" - somewhere upstream, a CONCAT is running.

TRIM() - Removing Rogue Spaces

Data entry is inconsistent. People press the spacebar before or after a value and nobody notices until a comparison fails or a JOIN returns zero rows because 'English' doesn't match ' English '.

TRIM() removes leading and trailing spaces.

SELECT
    '  English   ',
    LENGTH('  English   '),
    TRIM('  English   '),
    LENGTH(TRIM('  English   '))

?column?      | length | btrim   | length
--------------|--------|---------|-------
  English     |     12 | English |     7

Twelve characters with the spaces. Seven characters without them. Same word - completely different in any comparison or JOIN without TRIM.

I told the class this is the function that fixes the most confusing bugs in beginner SQL. "You write a WHERE clause that should obviously match. It returns nothing. You stare at it for ten minutes. Then you realise the value in the database has a trailing space. TRIM() is the fix. Run it on both sides of any comparison where values came from user input."

REPLACE() - Swapping One Value for Another

REPLACE(string, find, replacement) scans a string and swaps every occurrence of one piece of text for another.

We used it two ways.

Abbreviating city names - combined with CASE WHEN from last week:

SELECT DISTINCT city,
CASE
    WHEN city = 'Eldoret'  THEN 'ELD'
    WHEN city = 'Nakuru'   THEN 'NKR'
    WHEN city = 'Nairobi'  THEN 'NRB'
    WHEN city = 'Kisumu'   THEN 'KSM'
    WHEN city = 'Mombasa'  THEN 'MSA'
    ELSE city
END AS city_short
FROM greenwood_academy.students;

city     | city_short
---------|----------
Nairobi  | NRB
Mombasa  | MSA
Kisumu   | KSM
Nakuru   | NKR
Eldoret  | ELD

Updating titles across a column:

-- Swap 'Mr' for 'Prof' across all teacher names
SELECT
    teacher_name,
    REPLACE(teacher_name, 'Mr', 'Prof') AS teacher_renamed
FROM greenwood_academy.subjects;

teacher_name   | teacher_renamed
---------------|----------------
Mr. Njoroge    | Prof. Njoroge
Mr. Kamau      | Prof. Kamau

One thing I flagged immediately: REPLACE() is literal and global - it swaps every occurrence of the search string in the column, not just the first one. If teacher_name contained "Mr. Mwangi Mrima", both Mr occurrences would be replaced. Real data sometimes has these edge cases. Always test on a SELECT before running on an UPDATE.

Part 2: Number Functions

Three functions. One concept: controlling how a number is rounded.

ROUND(), CEIL(), FLOOR() - Three Ways to Handle Decimals

ROUND(value, decimal_places) - rounds to the nearest value at the specified decimal place. Standard rounding: 0.5 and above rounds up, below 0.5 rounds down.

CEIL(value) - always rounds up to the next whole integer, no matter how small the decimal.

FLOOR(value) - always rounds down to the previous whole integer, no matter how large the decimal.

We ran all three on the same data - exam marks divided by 10 to produce a score out of 10:

SELECT
    result_id,
    marks,
    ROUND(marks / 10.0, 1)  AS round_out_of_10,
    CEIL(marks / 10.0)      AS ceil_out_of_10,
    FLOOR(marks / 10.0)     AS floor_out_of_10
FROM greenwood_academy.exam_results;

result_id | marks | round_out_of_10 | ceil_out_of_10 | floor_out_of_10
----------|-------|-----------------|----------------|----------------
1         | 73    | 7.3             | 8              | 7
2         | 85    | 8.5             | 9              | 8
3         | 67    | 6.7             | 7              | 6
4         | 92    | 9.2             | 10             | 9
5         | 58    | 5.8             | 6              | 5

Reading across a single row: marks = 73. Divided by 10 gives 7.3. ROUND() keeps it at 7.3 (one decimal place). CEIL() takes it up to 8 - always up. FLOOR() takes it down to 7 - always down.

I drew the analogy on the board: "ROUND is how a fair teacher grades. CEIL is the optimistic teacher - when in doubt, give the benefit. FLOOR is the strict teacher - partial credit only counts when you've fully earned the next mark."

The / 10.0 instead of / 10 is worth a callout. In PostgreSQL, dividing an integer by an integer gives an integer - 73 / 10 returns 7, not 7.3. Writing 10.0 forces decimal division. It's a small detail that produces completely wrong results if you miss it.

Real-world use cases:

ROUND() - financial totals, averages, percentages in reports
CEIL() - billing systems that charge for partial units (you used 2.1 hours, you're billed for 3)
FLOOR() - discount tiers (you spent KES 2,950, that's tier 2, not tier 3)

The Full Function Cheat Sheet

String Functions:

Function	What it does	Example
`UPPER(text)`	All capitals	`UPPER('amina')` → `AMINA`
`LOWER(text)`	All lowercase	`LOWER('AMINA')` → `amina`
`INITCAP(text)`	Title case	`INITCAP('amina hassan')` → `Amina Hassan`
`LENGTH(text)`	Character count	`LENGTH('English')` → `7`
`SUBSTRING(text, start, len)`	Extract part of string	`SUBSTRING('Amina', 1, 3)` → `Ami`
`CONCAT(t1, t2, ...)`	Join strings together	`CONCAT('Amina', ' ', 'Hassan')` → `Amina Hassan`
`TRIM(text)`	Remove edge spaces	`TRIM(' English ')` → `English`
`REPLACE(text, find, new)`	Swap text	`REPLACE('Mr. Njoroge', 'Mr', 'Prof')` → `Prof. Njoroge`

Number Functions:

Function	What it does	Example
`ROUND(n, dp)`	Round to decimal places	`ROUND(7.3, 0)` → `7`
`CEIL(n)`	Always round up	`CEIL(7.3)` → `8`
`FLOOR(n)`	Always round down	`FLOOR(7.9)` → `7`

Practice Problems

Easy:

-- 1. Show all student names in lowercase
-- 2. How many characters are in each subject name?
-- 3. Extract the first letter of every teacher's name using SUBSTRING

Medium:

-- Build a student directory label:
-- Format: "Hassan, Amina - Nairobi"
-- (last_name, first_name - city)

SELECT
    CONCAT(last_name, ', ', first_name, ' - ', city) AS directory_label
FROM greenwood_academy.students;

Challenge:

-- Greenwood Academy wants a clean results summary:
-- Show: full name (CONCAT), their marks, score out of 10 (ROUND to 1dp),
-- and a 'grade_label' using CASE WHEN:
--   9–10 → 'Distinction'
--   7–8.9 → 'Merit'
--   5–6.9 → 'Pass'
--   below 5 → 'Fail'
-- You'll need to JOIN students and exam_results
-- Hint: the ROUND score is a decimal - think about how CASE WHEN handles it

What I Noticed Teaching This Session

1. INITCAP on a personal name unlocks it immediately. Running INITCAP('NAVAS HERBERT') live - your own name - before touching the table makes the function feel immediately useful rather than abstract. Every student thought of a dataset they'd used where names were in the wrong case.

2. TRIM explains the bug everyone has had. That moment when a WHERE clause should obviously match but returns nothing - trailing space on one side, clean value on the other. When students recognise that bug from their own experience, TRIM becomes unforgettable.

3. CONCAT into a sentence made CONCAT make sense. CONCAT(teacher_name, ' is teaching ', subject_name) - the output reads like a real sentence, and students immediately connected it to automated reports, email templates, and dashboard labels they'd seen. The function stopped feeling like a SQL trick and started feeling like a tool they'd actually use.

4. Showing ROUND, CEIL, and FLOOR on the same data in the same query is the only way to teach them. Side by side on the same marks, the difference is immediate and visual. Explaining them sequentially with separate examples loses the contrast that makes each one memorable.

What's Next: JOINs

Every function we learned today works on a single table. Next session we connect multiple tables - students, subjects, results - and run these same functions across the combined data.

-- A preview: full name with their subject and rounded score
SELECT
    CONCAT(s.first_name, ' ', s.last_name) AS full_name,
    sub.subject_name,
    ROUND(r.marks / 10.0, 1) AS score_out_of_10
FROM greenwood_academy.students s
JOIN greenwood_academy.exam_results r ON s.student_id = r.student_id
JOIN greenwood_academy.subjects sub ON r.subject_id = sub.subject_id
ORDER BY score_out_of_10 DESC;

Same functions, two JOINs, one clean report. That's next week.

Try It Yourself

Dataset: Greenwood Academy - link coming soon
This week's SQL script on GitHub - link coming soon

Try the challenge problem - the full results summary combining CONCAT, ROUND, and CASE WHEN. That single query uses three sessions of material: functions today, CASE WHEN last week, and GROUP BY from two weeks ago. If it runs cleanly, you're building real SQL intuition.

I'm a data trainer in Nairobi running a full data programme -
Python foundations → Data Science or Data Engineering specialisations.
I write weekly about what we covered, what worked, and what surprised me.

SQL CASE WHEN: Teaching Your Queries to Think

Navas Herbert — Sat, 18 Jul 2026 10:30:42 +0000

Every query we've written so far has been honest about what it can do. It retrieves columns, filters rows, sorts results. It reports what's already there.

Today we taught SQL to make decisions.

Not retrieve a price - classify it. Not return a stock level - evaluate it. Not show an age - categorise it. CASE WHEN is the if/elif/else of SQL, and once it clicks, students stop thinking of queries as read-only reporters and start thinking of them as tools that can reason about data.

We worked across two datasets today: Mama Nia's duka for the fundamentals, and Afya Clinic's patient records for the session closer - where CASE WHEN does something genuinely surprising.

The Syntax: If/Elif/Else, SQL Style

Before any code, I wrote the structure on the board:

SELECT column,
CASE
    WHEN condition1 THEN 'label1'
    WHEN condition2 THEN 'label2'
    ELSE 'default_label'
END AS new_column_name
FROM table_name;

"Read it like English. When this is true, give me this label. When that is true, give me that label. Otherwise, give me this default. End the block, name the result."

The ELSE is optional - if you leave it out and no condition matches, PostgreSQL returns NULL. I told the class to always include it unless they explicitly want NULLs. An unexpected NULL in production is harder to debug than a clear default label.

Example 1: Price Tiers - Mama Nia's Duka

The first question I wrote on the board: "Can we look at a price and automatically label it Budget, Mid-range, or Premium - without hardcoding it in Excel?"

Sabuni ya kufulia at KES 55 - Budget. Sukari at KES 165 - Mid-range. Mchele Pishori at KES 235 - Premium. The price_tier column didn't exist in the table. We created it on the fly, from a rule.

I made a point of the ORDER BY price at the end. The results sort by the original numeric price, not by the label alphabetically. You can see the clean transition: Budget rows at the top, then Mid-range, then Premium - which is only possible because we're sorting on the real number, not the string we created.

The question that always comes next: "Can we filter by the new column? Like WHERE price_tier = 'Budget'?"

Honest answer: no - not directly. The alias price_tier doesn't exist when WHERE runs (same reason HAVING aliases don't work). You'd wrap it in a subquery or CTE. We noted that and moved on - it's a Day 2 concept.

Example 2: Stock Status - Real Decisions for Real Operations

Same idea, different business question. Mama Nia needs to know at a glance which products need attention:

Mkate and Mtindi at stock level 20 - Low Stock. Maziwa Fresh at 30 - Adequate. Mchele Pishori at 60 and Sukari at 90 - Well Stocked.

I pointed at the boundary between Low Stock and Adequate - Mkate and Mtindi both at 20, both flagged Low Stock. Then Maziwa Fresh at exactly 30 - Adequate. "What did we say about BETWEEN last week? Inclusive on both ends. stock_level < 30 doesn't include 30. BETWEEN 30 AND 50 picks it up. No product falls through the gap - but only because we thought the boundaries through carefully."

This is the practical skill: before writing a CASE WHEN, map out the boundaries on paper first. Overlapping conditions, gaps in coverage, and fence-post errors are all invisible until a real product shows up in the wrong category.

Example 3: The Simple CASE WHEN - Exact Value Matching

The first two examples used the searched form - conditions are full expressions. There's also a simple form for when you're matching one column against a list of exact values:

Chai ya Majani and Soda - Beverages, Aisle 3. Mtindi and Maziwa Fresh - Dairy, Aisle 2. All five Grains & Cereals products - Aisle 1. Sabuni ya kufulia - Household, Aisle 4. Mkate - Snacks & Bakery, Aisle 5.

The difference from the searched form: CASE product_category WHEN 'Dairy' THEN ... - the column goes after CASE, not after WHEN. Cleaner when you're doing one-to-one label mapping. Less flexible - you can't write range conditions in this form.

I told the class: "Think of the simple form as a lookup table built into your query. You're saying: for this column, when the value is exactly this, map it to that. It's exact matches only - no greater-than, no BETWEEN."

One student - Njeri - immediately asked: "What if a product is in a category that isn't listed? Like a new category we haven't assigned an aisle to yet?"

She ran it. NULL in the aisle column for any unmatched category. Which is why you add an ELSE:

CASE product_category
    WHEN 'Grains & Cereals' THEN 'Aisle 1'
    ...
    ELSE 'Aisle TBC'
END AS aisle

Njeri caught the production bug before the query ever went anywhere near production. That question was worth the whole example.

Switching Datasets: Afya Clinic Patients

Same concept, completely different context - clinic.afya_patients. A table of patients with names and ages. The question: can we classify every patient by age bracket automatically?

Faith Nyambura, age 5 - Child. Amina Hassan, age 8 - Child. Ruth Chebet, age 16 - Teen. James Otieno, age 34 - Adult. Grace Wanjiru, age 67 - Senior. John Mutua, age 81 - Senior.

The same CASE WHEN structure, the same four-condition logic, a completely different domain. I made this point explicitly: "CASE WHEN is a pattern. Once you understand the pattern, it works on prices, stock levels, ages, credit scores, risk categories, exam grades - anything where you need to classify a number or a value into a label."

The Session Closer: SUM(CASE WHEN) - A Pivot in Eight Lines

This is the part of the session I look forward to most. It's the moment CASE WHEN stops being a labelling tool and becomes an analytical one.

The question: "How many patients fall into each age bracket - all in a single row?"

Without this technique, you'd write four separate COUNT queries. Or use GROUP BY, which gives you four rows. But what if you need all four numbers side by side - in one row - for a report or a dashboard?

I drew this on the board first, by hand:

Name           age    (age < 12)
Faith           5         1
Amina           8         1
Ruth           16         0
Mary           29         0
James          34         0
Linda          38         0
Peter          45         0
Samuel         55         0
Grace          67         0
David          72         0
John           81         0
                        = 2

"If age < 12 is true, we get a 1. If it's false, we get a 0. SUM() adds up all the 1s. That's the child count. We do the same thing for every bracket - four CASE WHEN expressions inside four SUM() calls - all in the same SELECT."

child_count | teen_count | adult_count | senior_count
------------|------------|-------------|-------------
          2 |          1 |           5 |            3

One row. Four columns. Every patient counted, every bracket covered, zero GROUP BY.

The silence in the room after that ran was a good kind. A few people read the output twice.

Kamau said: "So you turned a CASE WHEN into a number you can add. A condition became arithmetic."

Exactly. That's the whole trick. THEN 1 ELSE 0 converts a boolean evaluation into a number. SUM() adds the numbers. The result is a count of rows where the condition was true - without ever writing COUNT(*) or GROUP BY.

This pattern has a name in data engineering: a pivot. You're taking categories that would normally appear as rows (one row per bracket) and spreading them across columns (one column per bracket) in a single row. It shows up constantly in reporting - monthly revenue per product line, weekly signups by region, patient counts by age group for a clinic dashboard.

Two Forms, One Concept

Before closing I put the comparison on the board:

Form	Syntax	Use when
Searched	`CASE WHEN condition THEN ...`	Ranges, multiple columns, complex logic
Simple	`CASE column WHEN value THEN ...`	Exact value mapping on one column

Both produce a new computed column. Both end with END AS column_name. The searched form is more powerful - you can mix conditions, columns, BETWEEN, anything. The simple form is cleaner when you're doing pure lookup mapping.

Practice Problems

Easy:

-- 1. Add a 'restock_urgency' column: stock < 20 = 'URGENT', 20–40 = 'Soon', else = 'OK'
-- 2. Label each product as 'Affordable' (price < 80) or 'Pricey' (price >= 80)
-- 3. Using afya_patients, label patients as 'Minor' (age < 18) or 'Adult' (age >= 18)

Medium:

-- Map each supplier to a region using simple CASE WHEN:
-- 'Kenya Grain Millers' → 'Central'
-- 'Brookside Dairy' → 'Rift Valley'
-- anything else → 'Unknown'

SELECT product_name, supplier,
CASE supplier
    WHEN 'Kenya Grain Millers' THEN 'Central'
    WHEN 'Brookside Dairy'     THEN 'Rift Valley'
    ELSE 'Unknown'
END AS supplier_region
FROM duka.duka_products;

Challenge:

-- Using duka_products, build a one-row stock health summary:
-- How many products are Low Stock? Adequate? Well Stocked?
-- Use SUM(CASE WHEN) - same pattern as the afya_patients pivot
-- Thresholds: Low Stock < 30, Adequate 30–50, Well Stocked > 50
-- Your query here...

What I Noticed Teaching This Session

1. The hand-drawn 1s and 0s table unlocked SUM(CASE WHEN). Writing out Faith (1), Amina (1), Ruth (0)... David (0), John (0), total = 2 - by hand, slowly - before showing any SQL, meant that when the query ran and returned 2, students understood exactly where that number came from. The concept was clear before the syntax appeared.

2. Switching datasets mid-session helped. Using Mama Nia's duka for the first three examples and Afya Clinic for the last two showed that CASE WHEN is a pattern, not a Duka-specific trick. The concept transferred immediately because the structure was identical - only the domain changed.

What's Next: JOINs

CASE WHEN works on one table. Next session we bring two tables together for the first time.

-- A preview
SELECT p.product_name, p.price, s.contact_person,
CASE
    WHEN p.stock_level < 30 THEN 'Call supplier now'
    ELSE 'Stock OK'
END AS restock_action
FROM duka_products p
JOIN suppliers s ON p.supplier_id = s.id;

CASE WHEN inside a JOIN. The two concepts stack - everything we learned today works inside the queries we'll write next week.