DEV Community: Navas Herbert

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.

SQL Filtering: The Five Operators That Let You Ask Smarter Questions

Navas Herbert — Mon, 13 Jul 2026 20:17:55 +0000

Last week we covered the core SQL keywords - SELECT, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. Students could retrieve data, summarise it, sort it.

But there was a problem. Every WHERE clause we wrote used exactly one condition. price > 100. stock_level < 30. Clean, simple, single.

Real questions are never that simple.

"Show me all Dairy products that cost less than KES 70." That's two conditions. "Show me every product name that starts with the letter M." That's a pattern, not a value. "Show me products whose price is somewhere between 60 and 200 shillings." That's a range.

This session was about teaching how to ask those questions - and the five families of operators that make them possible.

The Dataset: Mama Nia's Duka

Same table we've been using all month - duka.duka_products. Mama Nia's shop. Products, categories, suppliers, prices, stock levels. A dataset that feels like a real place, which is exactly why the queries feel like real questions.

Five operator categories on the board before any code:

Category	Operators
Comparison	`=`, `!=`, `>`, `<`, `>=`, `<=`
Logical	`AND`, `OR`, `NOT`
Range	`BETWEEN`, `NOT BETWEEN`
Membership	`IN`, `NOT IN`
Search	`LIKE`, `ILIKE`

"By the end of today, you will use all five in the same query. Let's go one at a time."

Section 1: Comparison Operators - The Foundation

These are the ones students already half-know from mathematics. The only one that catches people is != - not equal to. The rest feel immediately natural.

Three examples, three different operators:

-- Example 1: Products above 150 shillings
SELECT product_name, price
FROM duka.duka_products
WHERE price > 150
ORDER BY price;

-- Example 2: Low on stock - less than or equal to 30 units
SELECT product_name, stock_level
FROM duka.duka_products
WHERE stock_level <= 30
ORDER BY stock_level;

-- Exact match
SELECT product_name
FROM duka.duka_products
WHERE price = 65.00;

I told the class: "Comparison operators are the vocabulary of filtering. Everything else we learn today uses these as building blocks."

The one worth pausing on: >= and <=. In English, "products with stock level 30 or below" translates directly to stock_level <= 30. The boundary value - 30 - is included. Students who meet BETWEEN later in the session sometimes forget that < alone excludes the boundary. A small distinction with meaningful consequences in inventory and financial queries.

Section 2: Logical Operators - AND, OR, NOT

AND - Both Must Be True

-- Dairy products that cost more than 70 shillings
SELECT product_name, product_category, price
FROM duka.duka_products
WHERE product_category = 'Dairy' AND price > 70;

AND is the strict filter. Both conditions must be true for a row to pass. Think of it as two bouncers at the door - the row has to get past both.

OR - At Least One Must Be True

-- Products that are either Beverages OR Household
SELECT product_name, product_category
FROM duka.duka_products
WHERE product_category = 'Beverages' OR product_category = 'Household';

OR is more relaxed. One condition being true is enough. The row passes if it satisfies either condition - or both.

NOT - Negate Everything

-- Everything that is NOT in Grains & Cereals
SELECT product_name, product_category
FROM duka.duka_products
WHERE NOT product_category = 'Grains & Cereals'
ORDER BY product_category;

NOT flips the result. Sometimes cleaner than writing the negative condition directly, especially when the positive condition is complex.

The Trap Nobody Sees Coming: AND Before OR

This is the moment of Section 2, and I engineer it deliberately every cohort.

PostgreSQL evaluates AND before OR - the same way multiplication happens before addition in mathematics. You learned BODMAS in school. SQL has its own version, and most beginners don't know it.

Here's the trap. Mama Nia wants Dairy products where price is either below KES 70 OR above KES 85:

-- WRONG — no parentheses
SELECT product_name, product_category, price
FROM duka.duka_products
WHERE product_category = 'Dairy' AND price < 70 OR price > 85;

This looks right. It isn't. SQL reads it as:

-- What PostgreSQL actually runs:
WHERE (product_category = 'Dairy' AND price < 70) OR price > 85

The AND binds first. The result: every product above KES 85 comes through — regardless of category - because the OR on the right has no category condition attached to it.

The fix is one pair of brackets:

-- CORRECT - parentheses group the OR together
SELECT product_name, product_category, price
FROM duka.duka_products
WHERE product_category = 'Dairy' AND (price < 70 OR price > 85);

We ran both versions live and compared the row counts. Different numbers. Visibly, provably different. That proof lands better than any explanation.

I told the class: "Any query that mixes AND and OR - use brackets. Not because you are unsure of the precedence, but because the next person reading your query definitely isn't. Brackets are documentation."

Section 3: BETWEEN - Ranges Made Readable

Without BETWEEN, a range filter needs two conditions joined by AND:

-- The long way
SELECT product_name, price
FROM duka.duka_products
WHERE price >= 60 AND price <= 200
ORDER BY price;

With BETWEEN:

-- The short way
SELECT product_name, price
FROM duka.duka_products
WHERE price BETWEEN 60 AND 200
ORDER BY price;

Same result. The BETWEEN version reads more like English - "price between 60 and 200" — which is exactly what we mean.

BETWEEN also works in reverse:

-- Products with stock level in range 20–40
SELECT product_name, stock_level
FROM duka.duka_products
WHERE stock_level BETWEEN 20 AND 40
ORDER BY stock_level;

-- Flip it: products OUTSIDE the 20–40 range
SELECT product_name, stock_level
FROM duka.duka_products
WHERE stock_level NOT BETWEEN 20 AND 40
ORDER BY stock_level;

Critical detail I repeat twice: BETWEEN is inclusive. BETWEEN 60 AND 200 includes products priced exactly 60 and exactly 200. If you want to exclude the boundaries, use > 60 AND < 200 instead.

This matters most in financial and date-range queries. A billing report BETWEEN two dates either includes or excludes those dates - and the answer matters. Know your boundaries.

Section 4: IN and NOT IN - Membership Checks

Without IN, filtering for multiple specific values means chaining OR:

-- The long way
SELECT product_name, product_category
FROM duka.duka_products
WHERE product_category = 'Dairy' OR product_category = 'Household';

With IN:

-- The short way
SELECT product_name, product_category
FROM duka.duka_products
WHERE product_category IN('Dairy', 'Household');

Same result. One tenth of the typing. Add a third category - just add it to the list. No restructuring the query.

NOT IN gives you the inverse. Mama Nia wants to see all products except those from two specific suppliers:

-- Products NOT from these two suppliers
SELECT product_name, supplier
FROM duka.duka_products
WHERE supplier NOT IN('Kenya Grain Millers', 'Brookside Dairy');

IN also works on specific product names:

-- Check prices for specific products by name
SELECT product_name, price
FROM duka.duka_products
WHERE product_name IN('Sukari', 'Mkate', 'Maharagwe');

One honest warning about NOT IN: it behaves unexpectedly when any value in the list is NULL. If the column being checked has NULL values, NOT IN may return zero rows - silently, with no error. For this dataset all our values are populated, so we noted the warning and moved on. Worth knowing before you hit it in a real project.

Section 5: LIKE and ILIKE - Pattern Matching

Sometimes you don't know the exact value. You know it starts with something, or contains something, or follows a certain shape. That's what LIKE and ILIKE are for.

Two wildcard characters:

Symbol	Meaning
`%`	Matches any number of characters (including zero)
`_`	Matches exactly one character

-- Products starting with 'M'
SELECT product_name
FROM duka.duka_products
WHERE product_name LIKE 'M%';

-- Products containing 'ai' anywhere in the name
SELECT product_name
FROM duka.duka_products
WHERE product_name LIKE '%ai%';

-- The _ wildcard - exactly one character before 'kate'
SELECT product_name
FROM duka.duka_products
WHERE product_name LIKE '_kate';

-- ILIKE - case-insensitive search
-- 'SUKARI' matches 'Sukari', 'SUKARI', 'sukari'
SELECT product_name
FROM duka.duka_products
WHERE product_name ILIKE 'SUKARI';

LIKE is case-sensitive - LIKE 'sukari' won't match 'Sukari'. ILIKE is PostgreSQL's case-insensitive version.

The three % placement patterns, left on the board for the rest of the session:

LIKE 'M%'    →  starts with M
LIKE '%a'    →  ends with a
LIKE '%ai%'  →  contains 'ai' anywhere

The _ underscore matches exactly one character. LIKE '_kate' matches 'Mkate' - one character then 'kate'. Useful for product codes with fixed-length formats.

Rule of thumb I gave the room: "If a human is typing the search term into any kind of input box, use ILIKE. They'll type 'SUKARI', 'sukari', 'Sukari' - and you want all of them to match."

All Five Together - The Session Closer

SELECT product_name, product_category, price, supplier, stock_level
FROM duka.duka_products
WHERE product_category IN('Dairy', 'Beverages')          -- membership
AND price BETWEEN 50 AND 200                              -- range
AND supplier ILIKE '%brook%'                              -- pattern
AND product_name NOT IN('Maziwa')                         -- exclusion
AND stock_level > 20                                      -- comparison
ORDER BY price;

The Cheat Sheet: When to Use What

You want to…	Use
Match an exact value	`=`
Exclude a specific value	`!=`
Both conditions required	`AND`
Either condition is enough	`OR`
Negate a condition	`NOT`
Mix AND and OR	Brackets — always
Number or date range	`BETWEEN`
Exclude a range	`NOT BETWEEN`
One of several specific values	`IN`
Exclude several specific values	`NOT IN`
Pattern match, case-sensitive	`LIKE`
Pattern match, case-insensitive	`ILIKE`

Practice Problems

Easy:

-- 1. Show all products where stock_level is between 25 and 60
-- 2. Show products from 'Dairy' OR 'Beverages' category
-- 3. Show product names that start with the letter 'S'

Medium:

-- Mama Nia wants to review specific products:
-- Show 'Sukari', 'Mkate', and 'Maharagwe' only if price is above 50
-- Order by price descending

SELECT product_name, price
FROM duka.duka_products
WHERE product_name IN('Sukari', 'Mkate', 'Maharagwe')
AND price > 50
ORDER BY price DESC;

Challenge:

-- Restock priority report:
-- Products NOT supplied by 'Kenya Grain Millers' or 'Brookside Dairy'
-- AND whose name contains the letter 'a' anywhere (case-insensitive)
-- AND whose stock level is NOT BETWEEN 30 AND 80
-- Order by stock_level ascending (most urgent first)
-- Your query here...

What's Next: JOINs

Every query we have written so far has touched exactly one table. Next session, Mama Nia's duka gets a second table - and we start connecting them.

-- A preview
SELECT p.product_name, p.price, s.contact_person, s.phone
FROM duka_products p
JOIN suppliers s ON p.supplier_id = s.id
WHERE p.stock_level < 30;

One query. Two tables. The database starts feeling like a real system. See you then.

Try It Yourself

Dataset: Mama Nia's Duka — link coming soon
This week's SQL script on GitHub — link coming soon

Focus your practice on the AND + OR parentheses trap - write a query that gives the wrong answer without brackets, confirm the wrong row count, then fix it. That one exercise teaches more about SQL logic than a dozen clean examples.

SQL Week: We Deleted Products, Dropped Tables, and Found Out Which Supplier Was Sitting on the Most Stock

Navas Herbert — Sun, 12 Jul 2026 06:14:56 +0000

Mama Nia's duka has been running for a few months now.

The shelves are stocked. The products are categorised. The suppliers are logged. Everything is in the database - tidy, structured, exactly where it should be.

This week I walked into the classroom and told the cohort: "We're going to start by deleting things."

A few nervous looks. One student - Kamau - actually moved his laptop slightly further away from the edge of the desk, as if SQL could fall off.

That reaction is why I always start the table manipulation session with DELETE before anything else. Once students are comfortable removing data on purpose, with control, they stop being afraid of the database and start being curious about it. That shift is everything.

Part 1: DELETE - Removing Rows You No Longer Need

Mama Nia has decided to stop selling two products. Mafuta ya Taa isn't moving. Biskuti keeps going stale before it sells. Time to remove them from the system.

-- Remove a discontinued product
DELETE FROM duka.duka_products
WHERE product_name = 'Mafuta ya Taa';

-- Remove another
DELETE FROM duka.duka_products
WHERE product_name = 'Biskuti';

Two things I told the class before running either of these.

First - always run a SELECT with the same WHERE clause first. Before you delete, see exactly what you're about to remove:

SELECT * FROM duka.duka_products
WHERE product_name = 'Mafuta ya Taa';

If that SELECT returns what you expected - one row, correct product - then you delete. If it returns five rows or nothing at all, you've caught a problem before it became permanent.

Second - DELETE with no WHERE clause deletes every row in the table. The table structure stays. The data is gone. This is not a theoretical warning. Every experienced SQL developer has either done this once or knows someone who has. Always write the WHERE clause first, before the DELETE.

The DELETE ran. Two rows gone. The table still existed, just two products lighter. Kamau moved his laptop back to its normal position.

Part 2: DROP TABLE - When the Whole Table Needs to Go

Different situation. We created a draft table for a New Year promotion - new_year_sale_draft- and now that planning phase is over. The whole table needs to go.

-- First, create it
CREATE TABLE new_year_sale_draft(
    product_name    VARCHAR(50),
    discount_percent INT
);

-- Then remove it entirely
DROP TABLE duka.new_year_sale_draft;

The question I wait for every cohort came right on time - Aisha asked: "What's the difference between DROP and TRUNCATE?"

TRUNCATE removes all rows instantly but keeps the table structure, like wiping a whiteboard clean without removing the whiteboard. DROP removes everything - the whiteboard, the frame, the wall mount.

I put the three-way comparison on the board:

The table makes the distinction concrete: DELETE (WHERE) removes some rows, TRUNCATE wipes all rows, DROP removes the data and the structure. One column - Removes structure? - is the entire difference between TRUNCATE and DROP. Students who see that column stop confusing the two.

Part 3: Core SQL Keywords - Asking Better Questions

Now the main event. The same duka_products table. The same data. Seven keywords that let you ask increasingly sophisticated questions.

SELECT - Choose Your Columns

The most basic question: what do you want to see?

-- Mama Nia just wants to see product names and prices
SELECT product_name, price
FROM duka.duka_products;

product_name          | price
----------------------|-------
Unga wa Ngano         | 145
Sukari                | 110
Mafuta ya Kupikia     | 220
...

Never use SELECT * in production queries, I told them. It pulls every column whether you need it or not. Name what you want. Your future self, and anyone reading your queries later, will thank you.

WHERE - Filter Before You Analyse

Which products cost more than KES 100?

SELECT product_name, price
FROM duka.duka_products
WHERE price > 100
ORDER BY price;

Which products need restocking - stock below 30 units?

SELECT product_name, stock_level
FROM duka_products
WHERE stock_level < 30;

The analogy I use: WHERE is the bouncer. It checks every row at the door before anything else happens. Only rows that pass the condition get into the query results.

ORDER BY - Sort the Results

Mama Nia wants to see her most expensive products first:

SELECT product_id, product_name, price
FROM duka.duka_products
ORDER BY price DESC;

And her lowest-stock items first - so she knows what to reorder soonest:

SELECT product_id, product_name, stock_level
FROM duka.duka_products
ORDER BY stock_level ASC;

ASC is the default. DESC flips it. Students who forget which is which remember "DESC sounds like descending, like going downstairs" - which is either helpful or annoying depending on who I ask.

LIMIT - Cap the Results

Top 3 most expensive products:

SELECT product_name, price
FROM duka_products
ORDER BY price DESC
LIMIT 3;

And a more specific version - the 2 priciest products specifically within Grains & Cereals:

SELECT product_id, product_name, price, product_category
FROM duka.duka_products
WHERE product_category = 'Grains & Cereals'
ORDER BY price DESC
LIMIT 2;

This is the WHERE + ORDER BY + LIMIT combination that shows up constantly in real reporting. Filter first, sort second, cap third.

GROUP BY- Summarise by Category

Now the queries that start to feel like real analysis.

How many products does Mama Nia carry in each category, and what's the average price per category?

SELECT product_category, COUNT(*) AS num_products, AVG(price) AS avg_price
FROM duka.duka_products
GROUP BY product_category
ORDER BY product_category;

product_category    | num_products | avg_price
--------------------|--------------|----------
Cooking Essentials  | 4            | 187.50
Grains & Cereals    | 5            | 132.00
Household Goods     | 3            | 95.00
...

Then the one that generated the most energy in the room - which supplier is sitting on the most stock value?

SELECT supplier, COUNT(*) AS num_products,
SUM(price * stock_level) AS total_stock_value
FROM duka.duka_products
GROUP BY supplier
ORDER BY total_stock_value DESC;

When that ran and a specific supplier came out top, Njeri said: "So Mama Nia should probably have a closer relationship with that supplier — they're holding the most of her money."

That's the moment. When a SQL query produces a genuine business insight, not just a correct output.

HAVING - Filter the Groups Themselves

This is WHERE's more specific sibling - and the one that confuses beginners most.

WHERE filters individual rows before grouping. HAVING filters groups after grouping. They look similar. They do very different things. The timing is everything.

Which categories have at least 2 different products?

SELECT product_category, COUNT(*) AS num_products
FROM duka.duka_products
GROUP BY product_category
HAVING COUNT(*) >= 2
ORDER BY product_category;

Notice: HAVING COUNT(*) >= 2, not HAVING num_products >= 2. The alias num_products doesn't exist yet when HAVING runs - it only exists in the final output. I put this on the board explicitly because it's the exact mistake everyone makes the first time:

--  This will error
HAVING num_products >= 2

--  This works
HAVING COUNT(*) >= 2

Which categories average over KES 100 per product?

SELECT product_category, AVG(price) AS avg_price
FROM duka.duka_products
GROUP BY product_category
HAVING AVG(price) > 100
ORDER BY avg_price DESC;

Same pattern - repeat the aggregate function in HAVING, not the alias.

The Capstone - Everything Together

The session closer. One query that uses everything:

Which categories are holding more than KES 10,000 worth of stock?

SELECT product_category, SUM(price * stock_level) AS total_stock_value
FROM duka.duka_products
GROUP BY product_category
HAVING SUM(price * stock_level) > 10000
ORDER BY total_stock_value DESC;

And the inverse - which supplier-category combinations are holding less than KES 10,000?

SELECT product_category, supplier, SUM(price * stock_level) AS total_stock_level
FROM duka.duka_products
GROUP BY product_category, supplier
HAVING SUM(price * stock_level) < 10000
ORDER BY product_category, supplier;

I left this on the board for the entire capstone section:

This is the sequence SQL actually runs your query - not the order you write it. Understanding this sequence explains why aliases don't work in HAVING, why WHERE runs before grouping, and why LIMIT always caps the final sorted output. Every confusion about SQL clauses traces back to not knowing this order.

Practice Problems

Easy:

-- 1. Show all products from the 'Household Goods' category, ordered by price
-- 2. Which products have a stock level above 50? Show name and stock level only.
-- 3. Show the top 5 cheapest products

Medium:

-- Find the total stock value per supplier (price * stock_level)
-- Only show suppliers whose total stock value is above 5,000
-- Order by total stock value, highest first

SELECT supplier, SUM(price * stock_level) AS total_stock_value
FROM duka.duka_products
GROUP BY supplier
HAVING SUM(price * stock_level) > 5000
ORDER BY total_stock_value DESC;

Challenge:

-- Mama Nia wants a restocking priority report:
-- Show categories where the average stock level is below 40
-- Include: category name, number of products, average stock level, total stock value
-- Order by average stock level ascending (most urgent first)
-- Your query here...

What I Noticed Teaching This Session

1. Starting with DELETE was the right call every time. Fear of the database is the biggest obstacle beginners carry. The moment students deliberately remove data and see the table still running correctly - just smaller - the fear converts to confidence.

2. WHERE vs HAVING needs more than one explanation. I explained it twice, showed the alias error deliberately, and still had students reach for the alias in their own queries. The only fix that works is letting them hit the error themselves, reading it, and tracing back to why. Budget time for this.

3. The supplier stock value query produced the best discussion. "Which supplier is Mama Nia most dependent on?" is a real business question. When SQL answers it, students stop thinking of queries as exercises and start thinking of them as tools. That shift is the whole point of using a realistic dataset.

4. The order of operations diagram is worth drawing every session. FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT - written on the board and left visible. Every question about why something doesn't work traces back to a misunderstanding of this sequence.

What's Next

Next session: JOINs. Mama Nia's duka is about to get a second table - a suppliers table with contact details and payment terms. And we'll start connecting the two.

-- A preview of what's coming
SELECT p.product_name, p.price, s.contact_person, s.payment_terms
FROM duka_products p
JOIN suppliers s ON p.supplier = s.supplier_name
WHERE p.stock_level < 30;

Two tables. One query. The data starts to feel like a real system. See you then.

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

Follow along or drop your questions in the comments.*

FastAPI Just Got Its Own Deployment Platform - And It's One Command

Navas Herbert — Sun, 05 Jul 2026 16:35:17 +0000

I came across: FastAPI Cloud.

Built by the same team behind FastAPI itself. Still in beta. Free hobby tier, no credit card, up to 3 apps.

The pitch is simple: deploying a FastAPI app has always required work that has nothing to do with FastAPI - writing a Dockerfile, configuring ports, setting up a Render or Railway dashboard, writing a Procfile, remembering to add uvicorn to requirements.txt. None of that is your actual application. It's all overhead that stands between finishing your code and having it live on a real URL.

FastAPI Cloud removes all of it. The deploy command is:

fastapi deploy

That's it. I sent the link to the cohort group chat and then immediately opened my own terminal to try it.

What Actually Happened

I pointed it at a simple bookshop API I had sitting in a local folder - GET /books, POST /books, nothing fancy. Ran fastapi deploy. The CLI asked if I wanted to log in, opened a browser auth page, and I was authenticated in under a minute.

Then it asked which team to deploy to. I selected RAG AI (my workspace), confirmed the directory, and it started building.

The build log scrolled through installing dependencies, copying source code, configuring the working directory - and then:

Ready the chicken! 🐔
Your app is ready at https://bookshop-api.fastapicloud.dev

I genuinely laughed at "Ready the chicken." Then I clicked the link.

Green checkmark. us-east-1. Last updated 36 seconds ago. A real deployed app on a real public URL, from a project folder sitting on my Windows laptop, in under three minutes total.

/docs - live, interactive, the full Swagger UI. GET /books, POST /books, Book schema in the Schemas section. A complete deployed API with automatic documentation, no configuration file written, no dashboard manually created.

Why This Matters for Teaching

The hardest part of teaching FastAPI to beginners isn't FastAPI. It's everything that comes after - "now how do I show someone else what I built?"

Getting a student from working local API to public URL currently takes anywhere from 20 minutes to an entire class session, depending on which platform you use and what breaks. Docker confusion, Render free-tier cold starts, missing environment variables, wrong start commands - all of that is friction that has nothing to do with the actual skill we're trying to teach.

fastapi deploy collapses that to three minutes and one command. For a teaching context, that's not a small improvement. That changes what's possible in a single session.

Caveats Worth Knowing

It's in beta. The free hobby tier covers up to 3 apps, which is enough for ssomeone projects and quick demos. I don't know yet how it handles environment variables for projects with API keys (the RAG build would need that), persistent storage, or what happens at higher traffic. Those are questions for when they're out of beta.

But for what it does right now - take a FastAPI project and put it on a live public URL with one command - it works exactly as advertised.

Try It

pip install "fastapi[standard]"
fastapi deploy

You need a FastAPI project already set up locally. Head to fastapicloud.com, sign up for the hobby tier, and try it against something you've already built. The deploy takes about two minutes. The /docs URL works immediately.

If you've been putting off deploying a project because the setup felt like too much overhead - this removes the excuse.

Not all chunking is equal.

Navas Herbert — Thu, 02 Jul 2026 15:19:17 +0000

The strategy you pick at ingestion quietly determines how good your RAG retrieval will ever be - no matter how good your embedding model is.

Six strategies, ranked from baseline to production-grade:

𝗙𝗶𝘅𝗲𝗱-𝗦𝗶𝘇𝗲 - split every N characters, regardless of meaning. Fastest to implement. Cuts mid-word, mid-sentence. Baseline only.

𝗙𝗶𝘅𝗲𝗱 + 𝗢𝘃𝗲𝗿𝗹𝗮𝗽 - same as above, but consecutive chunks share a repeated tail. Reduces hard cuts. Still not semantically aware. A quick upgrade, not a real fix.

𝗥𝗲𝗰𝘂𝗿𝘀𝗶𝘃𝗲 𝗦𝗽𝗹𝗶𝘁 - tries natural separators in order: paragraph → sentence → word → character. Respects language structure. The default choice for most general-purpose RAG.

𝗠𝗮𝗿𝗸𝗱𝗼𝘄𝗻-𝗔𝘄𝗮𝗿𝗲 - splits on document headers and sections. Each chunk is one logical topic. Free section metadata for filtering. Requires structured documents.

𝗦𝗲𝗺𝗮𝗻𝘁𝗶𝗰 - embeds every sentence, measures similarity between consecutive ones, cuts where similarity drops sharply. Chunks align with actual meaning. Costs more at ingest time.

𝗔𝗴𝗲𝗻𝘁𝗶𝗰 / 𝗣𝗿𝗼𝗽𝗼𝘀𝗶𝘁𝗶𝗼𝗻 - an LLM rewrites each piece into a self-contained atomic fact. No dangling pronouns, no lost context. Best possible retrieval quality. Most expensive.

The one thing worth remembering across all of them: overlap is a band-aid, not a fix. It reduces boundary cuts but doesn't make chunks semantically coherent.

Choose your strategy based on what you're optimizing for - ingestion speed, retrieval quality, or cost.

Sharing what I'm learning in public.

RAG #AI #Python #LLM #LearningInPublic

Day 2 of 3: We Added an LLM and the Project Became a Real Product

Navas Herbert — Wed, 01 Jul 2026 20:42:58 +0000

I opened Day 2 with the same diagram I drew on Day 1.

Question → [embed] → [search vectors] → top-k chunks → [stuff into prompt] → LLM → Answer + sources

I pointed at the left half - everything up to and including top-k chunks. "Yesterday we built all of this. We proved retrieval works, with zero AI model involved. Distance numbers, real chunks, real search."

Then I pointed at the right half - [stuff into prompt] → LLM → Answer + sources.

"Today is this. Two new ideas, one new file. By the end of this session, your project stops being a search engine and becomes a product."

One New File: touch rag.py

Yesterday's project had three files. Today it gets one more.

That's it. One file. I wrote its job on the board before we touched it: "rag.py has exactly two jobs - build a prompt that forces the model to answer only from our chunks, and call an LLM with that prompt. It never touches ChromaDB. It never touches FastAPI. It just receives chunks and returns an answer."

The updated folder structure:

interdoc/
    |--app/
           |---- ingestion.py
           |---- vectorstore.py
           |---- rag.py          ← new today
           |---- main.py

File 4: rag.py - The Heart of Day 2

import os
from dotenv import load_dotenv
from openai import OpenAI

load_dotenv()
client = OpenAI(api_key=os.getenv("OPENAI_API_KEY"))

I stopped immediately at line one. "Notice the API key never appears as text anywhere in our code. It lives in .env, which must be in .gitignore. If this project ever goes on GitHub, that key must never go with it."

build_prompt() - The Most Important Function of the Week

def build_prompt(question: str, chunks: list[str], metadatas: list[dict]) -> str:
    blocks = []
    for i, (chunk, meta) in enumerate(zip(chunks, metadatas), start=1):
        blocks.append(f"[Source {i}: {meta['source']}, chunk {meta['chunk_index']}]\n{chunk}")
    context = "\n\n".join(blocks)
    return (

"Use the context below to answer the question" - curbs the model from reaching into its own training data and answering from memory.

"If context does not contain the answer, say so politely or say you don't have enough information - do not guess" - this is the safety valve against hallucination. I told the class to remember this line specifically. Every RAG system that gives confidently wrong answers is missing this instruction somewhere.

"Reference relevant source using [Source N]" - we are telling the model the exact citation format to use. We built the [Source 1: filename, chunk N] labels in the blocks loop above, so the model can actually reference them. The citation capability comes from us, not from the model.

One sentence in the prompt is the entire difference between a trustworthy system and a hallucinating one.

generate_answer() - Calling the Model

def generate_answer(question: str, search_results: dict) -> dict:
    chunks = search_results["documents"][0]
    metadatas = search_results["metadatas"][0]
    if not chunks:
        return {"answer": "No documents have been uploaded yet.", "sources": []}

    prompt = build_prompt(question, chunks, metadatas)
    response = client.chat.completions.create(
        model="gpt-4o-mini",
        max_tokens=500,
        messages=[{"role": "user", "content": prompt}],
    )

    answer_text = response.choices[0].message.content
    sources = [{"source": m["source"], "chunk_index": m["chunk_index"]} for m in metadatas]
    return {"answer": answer_text, "sources": sources}

search_results is exactly what search_chunks() from yesterday returns - we didn't change that function at all. Day 2 plugs into Day 1's output without touching it.

The if not chunks guard at the top: "Always check before calling an expensive API. If nobody has uploaded anything yet, we return a clear message immediately instead of making an API call that would produce a useless response."

The sources list at the bottom: "Why do we return sources as a separate structured list instead of just leaving the citations inside the answer text? Because whoever builds a frontend on Day 3 can now display sources separately - highlight them, make them clickable, show the actual chunk text on hover. Buried inside an answer string, that's impossible."

Adding /query to main.py

We didn't rewrite main.py. We added to it - keeping everything from Day 1 exactly as it was.

from pydantic import BaseModel
from app.vectorstore import search_chunks
from app.rag import generate_answer

class QueryRequest(BaseModel):
    question: str
    top_k: int = 4

@app.post("/query")
async def query_documents(request: QueryRequest):
    results = search_chunks(request.question, top_k=request.top_k)
    return generate_answer(request.question, results)

I pointed at the endpoint body. "Two lines of real logic. Retrieve, then generate. Every endpoint we've written follows the same shape: receive the request, delegate the work to the right specialist file, return the result. main.py still doesn't do any real work itself."

Someone asked why there's no response_model here the way the Grade Tracker endpoints had last week. Good question — the answer structure coming back from generate_answer() is a plain dict that varies depending on how many sources come back. We'd need a Pydantic model to enforce a strict shape. For today's session that's a Day 3 polish item, not today's concern.

First Test: Confirm /upload Still Works

Before touching /query, we re-uploaded the document.

chunks_created: 34 - small_language_research.pdf, 34 chunks stored and embedded. Day 1's pipeline still running clean. This step is not optional: if ChromaDB has no vectors to search, /query returns empty results and the LLM produces a confused non-answer. Upload first, always.

The Moment of the Session: We Tried to Break It

Now the part I look forward to most in every AI session - deliberately trying to make the system fail.

We opened /docs, expanded POST /query, clicked Try it out, and typed a question I knew was nowhere in the uploaded document:

{
  "question": "Where is company Datapulse ai headquarted?",
  "top_k": 3
}

The document was a language research paper. There is no "Datapulse AI" in it. No headquarters. Nothing even close.

The response:

I read this out loud and said: "Look at what just happened. Retrieval found the three chunks it considered most relevant to that question - and they're all from the language research paper, which has nothing to do with Datapulse. The LLM received those chunks, saw they contained no useful information, and said exactly that. It did not make something up. It refused."

Then the line I want everyone to remember: "When the model correctly refuses to answer something it doesn't know - that is not the system being unhelpful. That is the system working exactly as designed. A confident wrong answer would have been the actual failure."

The sources array is visible in the response even on a refusal. That's correct - the system is being transparent about what it searched, even when what it found wasn't enough to answer. On Day 3 when students build frontends, they can display those sources as "searched but insufficient"- which is genuinely more trustworthy than silence.

We ran two more stress tests before closing:

Asking something vague - a fuzzy, poorly-phrased question about the same document. The answer came back, but less precisely. We talked about why: retrieval quality depends on the closeness between how the question is phrased and how the relevant chunk is worded. Better questions get better retrieval. Prompt quality matters on both ends.

Uploading two documents on different topics, then asking a question only answerable from one - the sources in the response correctly pointed to the right file, not the irrelevant one. Vectors from separate documents live in the same space, and the closest ones win regardless of which file they came from.

What Exists Now

I said this slowly at the end of the session:

"Upload any PDF or text file. Ask any question in plain English. The system embeds your question, searches the stored vectors for the most relevant chunks, builds a grounded prompt from those chunks, calls an LLM with strict instructions to answer only from what it was given, and returns an answer with citations pointing to the exact source chunks. If the answer isn't in the documents, it says so. That's a real product."

One new file. rag.py. That's all today took to cross the line from "interesting search engine" to "document intelligence API."

I'm an AI engineer in Nairobi
Follow along or drop your questions in the comments.

Day 1 of 3: We Built the Retrieval Half of RAG - Before Touching a Single AI Model

Navas Herbert — Tue, 30 Jun 2026 15:36:29 +0000

I opened today's session with a sentence I wanted everyone to hold onto for the next three days:

"By the end of today, you will be able to upload a real document through an API endpoint, and have it automatically split into chunks, turned into vectors, and stored so it can be searched by meaning, not just by keyword. We will not touch any AI model today - that is on purpose. We want you to see retrieval working on its own first."

This is Day 1 of a 3-day Document Intelligence API build with the cohort 6 interns. Same FastAPI skills they used last week building the Grade Tracker- Depends, response models, endpoints - but a new kind of database underneath. Today was retrieval only. No LLM, no generation, no API key required. Just proving that the search half of the system actually works.

Here's how it went.

Three Ideas, Whiteboard Only, Ten Minutes

Before any code, I drew three ideas on the board and told the room: "I want you to be able to redraw this from memory by the end of today."

Embedding: a piece of text turned into a list of numbers, a vector, such that text with similar meaning ends up close together in that number space. "Cat" and "dog" land near each other. "Cat" and "spreadsheet" land far apart. The model was never told what a cat is - it learned this closeness from patterns in huge amounts of text.

Vector search : just nearest-neighbour search in that number space. Take a question, embed it the same way, find which stored vectors sit closest. That closeness is what "relevant" means here.

RAG (Retrieval-Augmented Generation) - retrieve the relevant chunks first, then paste them into the LLM's prompt, so it answers using your data instead of guessing from what it memorised during training.

Question --> [embed] --> [search vectors] --> top-k chunks --> [stuff into prompt] --> LLM --> Answer + sources

I said the one sentence I repeat every time I teach this, slowly, twice: "The LLM never sees your documents unless retrieval puts the right chunk into the prompt." That single idea, if it clicks on Day 1, makes the rest of the week make sense. Everything else is implementation detail around that one fact.

Setting Up: Real Folders, Built Live in the Terminal

No starter repo. No zip file. We built the project structure from nothing, together, in the terminal.

mkdir interdoc && cd interdoc
mkdir app && cd app
touch ingestion.py vectorstore.py main.py

Three empty files. I wrote their names on the board before we touched any of them - ingestion.py, vectorstore.py, main.py - and pointed at each one as we built it. This cohort already knows this rhythm from last week's database.py, models.py, schemas.py, crud.py, main.py build. One file, one job. Files talk to each other through clean function calls, not by reaching into each other's internals.

I also had the Day 1 goals written out as a checklist before we started - explain what an embedding is and why similar meanings end up as nearby vectors, explain the difference between keyword and vector search, upload a real file through your own endpoint and have it chunked and stored, explain why chunk overlap matters, run a search query with zero AI involved, and know exactly what's still missing to call this a real product.

That last line on the checklist : "hio ni kesho" (that's tomorrow) - was a deliberate boundary. Today is retrieval. Generation is Day 2's problem, not today's.

File 1: ingestion.py - Extract Text, Then Chunk It

"This file has exactly one job: take raw bytes from an uploaded file and turn them into a list of clean text chunks, ready to be embedded. It never talks to ChromaDB and it never talks to FastAPI."

import io
from pypdf import PdfReader

def extract_text(filename: str, file_bytes: bytes) -> str:
    if filename.lower().endswith(".pdf"):
        reader = PdfReader(io.BytesIO(file_bytes))
        return "\n".join(page.extract_text() or "" for page in reader.pages)
    return file_bytes.decode("utf-8", errors="ignore")

io.BytesIO lets us treat raw bytes sitting in memory as if they were a file, without ever saving anything to disk. The uploaded file arrives as bytes - PdfReader expects something that behaves like a file - so this bridges the two. I made a point of telling the class: "Notice we are not saving the uploaded file anywhere. We pull the bytes straight out of memory, extract the text, and the original file is gone. For this project that's fine - we only care about the text inside, not the file itself."

Someone asked: "What about Word documents? Scanned PDFs?" Honest answer: scanned PDFs need OCR - a separate tool that reads text out of images - and Word docs need a different library entirely. Today we keep it to PDF and plain text on purpose, so the lesson stays about retrieval, not file-format archaeology.

Then the function the whole session would orbit around:

def chunk_text(text: str, chunk_size: int = 300, overlap: int = 50) -> list[str]:
    words = text.split()
    chunks = []
    start = 0
    while start < len(words):
        end = start + chunk_size
        chunk = " ".join(words[start:end])
        if chunk.strip():
            chunks.append(chunk)
        start += chunk_size - overlap
    return chunks

"This is the function I want you to be able to explain to a friend tonight. We cannot embed an entire document as one giant vector - it would be too vague, mixing twenty different ideas into one point in space. So we split the text into smaller pieces and embed each chunk separately."

We did it by hand on the board with tiny numbers - chunk_size=5, overlap=2, on a 12-word sentence. First chunk: words 0 to 5. Next start: 0 + 5 - 2 = 3. Second chunk: words 3 to 8. Words 3 and 4 appear in both chunks - that's the overlap. Why does that matter? If the answer to someone's question sits right at the boundary between two chunks, without overlap you'd cut that sentence in half and lose it in both pieces.

The REPL Moment - And a Real Typo

We tested chunk_text() live, in the interactive Python shell, before wiring up anything else.

from app.ingestion import chunk_text

text = "An embedding is a piece of text turned into a list of numbers (vector) - such that text with similar MEANING ends up close together in that number space"
chunks = chunk_text(text, chunk_size=15, overlap=5)
len(chunks)

I typed chunk[0] to look at the first piece - forgetting the s. Python answered immediately with NameError: name 'chunk' is not defined. Did you mean: 'chunks'?

I left it on screen and let the class read it out loud. Then corrected it: chunks[0].

'An embedding is a piece of text turned into a list of numbers (vector) -'

chunks[1]

'list of numbers (vector) - such that text with similar MEANING ends up close together'

I pointed directly at the repeated words - "vector" and "-" sitting at the start of chunks[1], the same words that closed out chunks[0]. That's the overlap, working exactly as designed, visible in real output rather than just described in theory.

I told the room: "That NameError is going to happen to every single one of you this week, probably more than once. Python tells you exactly what it thinks you meant. Read the error before you panic." A typo in a live REPL is one of the most useful teaching moments available - it shows that errors are information, not failure.

We also walked through what happens if overlap >= chunk_size deliberately - start never moves forward, or moves backward, and the while loop never terminates. I had a volunteer try it on a short string and watch it hang, then Ctrl+C out. A great bug to see once, on purpose, in a safe sandbox, rather than discover by accident at midnight.

File 2: vectorstore.py : Where Chunks Become Vectors

"File two is where chunks actually become vectors and get saved somewhere we can search later. Two new ideas live here: sentence-transformers, which turns text into numbers, and ChromaDB, which stores those numbers and knows how to search them."

import chromadb
from chromadb.utils import embedding_functions

client = chromadb.PersistentClient(path="./data/chroma_db")

embedding_fn = embedding_functions.SentenceTransformerEmbeddingFunction(
    model_name="all-MiniLM-L6-v2"
)

collection = client.get_or_create_collection(
    name="documents",
    embedding_function=embedding_fn,
)

def add_chunks(doc_id: str, chunks: list[str]) -> int:
    ids = [f"{doc_id}_{i}" for i in range(len(chunks))]
    metadatas = [{"source": doc_id, "chunk_index": i} for i in range(len(chunks))]
    collection.add(documents=chunks, metadatas=metadatas, ids=ids)
    return len(chunks)

def search_chunks(query: str, top_k: int = 4):
    return collection.query(query_texts=[query], n_results=top_k)

PersistentClient(path="./data/chroma_db") means Chroma writes everything to disk, in that folder. Stop the server, restart it tomorrow, your uploaded documents are still there. I made a point of this line specifically: "This single line is the entire 'database connection' for our vector store. There's no separate server running in the background like there would be with PostgreSQL. Chroma is a library that reads and writes a folder on disk. That's the whole infrastructure."

The embedding function deserved its own callout: "This model runs locally. It downloads once, the first time you use it, then every embedding after that happens on your own laptop, for free, forever. Compare that to the LLM we plug in tomorrow, which calls out to a paid API. Embeddings are cheap and local. Generation is the part that costs money."

I pointed directly at metadatas in add_chunks() - "this is the detail that turns a toy demo into a real product. Without it, we'd have vectors with no idea which document or which part of a document they came from, and no way to show a citation when we answer a question later."

And search_chunks() got the moment of the file: "Sit with this for a second. One line of code is doing embed-the-question, then nearest-neighbour search across however many chunks we have stored, and returning the best matches with their distances. Everything before this was preparation. This line is the actual retrieval in Retrieval-Augmented Generation."

When someone asked why ChromaDB and not Pinecone, Weaviate, or pgvector - honest answer: those are excellent production choices, but every one of them needs an account, an API key, or a running server process. Chroma needs none of that, which is exactly why it's the right teaching tool this week. The concepts transfer directly the day you need a production-grade option.

File 3: main.py - Small on Purpose

"Notice how small this file is. ingestion.py and vectorstore.py do all the real work; main.py just connects an HTTP request to the right function calls."

from fastapi import FastAPI, UploadFile, File
from app.ingestion import extract_text, chunk_text
from app.vectorstore import add_chunks

app = FastAPI(title="Document Intelligence API")

@app.get("/")
def root():
    return {"status": "alive"}

@app.post("/upload")
async def upload_document(file: UploadFile = File(...)):
    contents = await file.read()
    text = extract_text(file.filename, contents)
    chunks = chunk_text(text)
    n = add_chunks(doc_id=file.filename, chunks=chunks)
    return {"filename": file.filename, "chunks_created": n}

Same separation-of-concerns rule as crud.py last week - main.py doesn't extract text, doesn't embed anything, doesn't touch the database directly. It receives the HTTP request and hands the work to the right specialist file.

"Look at how this endpoint reads almost like a sentence: read the file, extract the text, chunk it, add it to the store, confirm it worked. Five lines, five clear steps. Every endpoint we write this week follows this same 'receive, delegate, confirm' shape."

Starting the Server - Another Honest Typo

uvicorn app.main:app --reload

First attempt, I fat-fingered it: --relaod. Uvicorn didn't recognise the flag and complained. I caught it, fixed the spelling, and reran it.

I didn't edit that moment out of how I told the story to the class either. "This is what live coding actually looks like. I've typed this command hundreds of times and I still mistype it occasionally. The terminal tells you exactly what went wrong. Read it, fix it, move on." Once corrected, the server started cleanly with auto-reload watching for file changes.

We opened http://127.0.0.1:8000/docs, expanded POST /upload, clicked Try it out, chose a short sample text file, and hit Execute.

{
  "filename": "sample_notes.txt",
  "chunks_created": 4
}

A real number greater than zero. The whole pipeline - upload, extract, chunk, embed, store - had just run, end to end, through an actual HTTP request.

I warned the room ahead of time: if chunks_created comes back as 0, the most common cause is a scanned PDF with no extractable text layer - extract_text() returns an empty string for every page in that case. Always keep a backup plain-text file ready for exactly this situation.

The Payoff: Proving Retrieval Works, Zero LLM Involved

"This next part is the payoff for today. We are going to prove that our system can already find the right information, before we ever connect an AI model."

A throwaway script, scratch.py, in the project root:

from app.vectorstore import search_chunks

results = search_chunks("what is this document about?", top_k=3)

for doc, meta, dist in zip(
    results["documents"][0],
    results["metadatas"][0],
    results["distances"][0],
):
    print(f"[{meta['source']} chunk {meta['chunk_index']}] (distance={dist:.3f})")
    print(doc[:150], "...")
    print()

python scratch.py

Real chunks came back, each with a distance score attached. I read the output out loud and pointed straight at the distance numbers: "Lower means more similar. That's it. That's the whole mystery of 'similarity search' : it's just a number, and we are sorting by it."

Then the checkpoint question that ties the whole day together: change the question in scratch.py to something completely unrelated to the uploaded document, rerun it, and watch what happens to the distance values. When the question has nothing to do with what's stored, the distances jump noticeably higher - visible, numeric proof that the system genuinely doesn't know things it was never given.

What Exists Right Now, With Zero AI Generation Involved

I said this out loud at the end of the session, slowly:

"A real API endpoint that accepts any PDF or text file, automatically splits it into overlapping chunks, turns those chunks into vectors using a local embedding model, and stores them so they can be found again by meaning, not by exact keyword match. That's the entire retrieval half of RAG - working, and testable, right now."

Tomorrow we build the other half: taking those retrieved chunks, handing them to an LLM, and getting back an answer with citations. That's when this stops being a search engine and becomes a product.

What I Noticed Teaching This Session

1. The "redraw it from memory" bar works. Telling students up front that they'll need to reproduce the embeddings diagram themselves changes how closely they listen to the first ten minutes. Theory with an explicit accountability check lands differently than theory alone.

2. Real typos teach better than clean demos. The chunk[0] NameError and the --relaod flag mistake weren't planned, but I didn't edit either one out of how I narrated the session. Seeing an instructor make and fix a real mistake, live, removes a huge amount of fear from a beginner's own inevitable errors.

3. Distance numbers demystify "AI search" instantly. The moment students see an actual float next to each retrieved chunk, and watch that number change when the question stops being relevant, "semantic search" stops being a buzzword and becomes something they can reason about directly.

4. Drawing the line at "no LLM today" was the right call. It would have been tempting to wire in generation on Day 1 for a flashier demo. Keeping retrieval isolated meant every student could see, concretely, exactly what part of the system was doing the finding - before any model got a chance to paper over a weak retrieval step with a confident-sounding answer.

Homework Before Day 2

Upload 2–3 real documents - your own notes, a resume, a public FAQ page saved as text, anything
Edit scratch.py and try at least 5 different questions against your own documents
Try a question that should not be answerable from your documents, and look closely at the distance values compared to one that should be

Next: generation, citations, and the moment this stops being a search engine.

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

Follow along or drop your questions in the comments.*