DEV Community: Alex Murithi

Mastering Object-Oriented Programming (OOP) in Python

Alex Murithi — Fri, 02 Oct 2026 14:49:28 +0000

Object-Oriented Programming (OOP) organizes code into objects - bundles of data (attributes) and behavior (methods).
It makes programs modular, reusable, and easier to maintain.

1. Classes and Objects

i. Class
A class is a blueprint for creating objects. It defines attributes (data) and methods (functions).

class Car:
    make = "CrossOver SUV"

ii. Object
An object is an instance of a class - like an actual car built from the blueprint.

2. The `init` Method (Constructor)

When you create an object from a class, Python automatically calls a special method named __init__.
This method is known as the constructor, and its job is to initialize the object’s attributes - setting up its starting state.
It usually takes parameters that define the object’s initial data.
The first parameter is always self, which refers to the specific object being created.
Example1

class Car:
    make = "CrossOver SUV"

    def __init__(self, brand, color, max_speed, model):
        self.brand = brand
        self.color = color
        self.max_speed = max_speed
        self.model = model

    def start(self):
        print(f"The {self.brand} car has started")

    def stop(self, time):
        print(f"The {self.brand} car has stopped at {time}pm.")

    def acceleration(self, top_speed):
        print(f"The {self.brand} {self.model} can reach {top_speed} km/h")

Creating Object

car1 = Car("Toyota", "White", 180, "Hilux")
car3 = Car("BMW", "Blue", 260, "X5")

car3.start()
car3.stop(4)
car3.acceleration(260)
print(car1.color)
print(car1.make)

Output

The BMW car has started
The BMW car has stopped at 4pm.
The BMW X5 can reach 260 km/h
White
CrossOver SUV

Example2: M-Pesa Account Class

class MpesaAccount:
    def __init__(self, name, phone_number, balance):
        self.name = name
        self.phone_number = phone_number
        self.balance = balance

    def deposit(self, amount):
        self.balance += amount
        print(f"Deposited {amount}. New balance: {self.balance}")

    def withdrawal(self, amount):
        if amount > self.balance:
            print("Insufficient funds")
        else:
            self.balance -= amount
            print(f"Withdrew {amount}. New balance: {self.balance}")

    def check_balance(self):
        print(f"Balance: {self.balance}")

Creating object and usage

alex_account = MpesaAccount("Alex", "+254712345678", 0)
alex_account.deposit(1000)
alex_account.check_balance()
alex_account.withdrawal(500)

Output

Deposited 1000. New balance: 1000
Balance: 1000
Withdrew 500. New balance: 500

3. The Four Pillars of OOP

i. Encapsulation
Protecting data by keeping attributes private and exposing only necessary methods.
Encapsulation means wrapping data and methods into a single unit (the class) and restricting direct access to internal details.
It’s like locking your valuables inside a safe - only authorized methods (keys) can access or modify them.

Key idea in the example below:
Keep attributes private using double underscores (__balance).
Provide public methods (deposit(), withdraw()) to interact safely.

Example

class BankAccount:
    def __init__(self, owner, balance):
        self.owner = owner
        self.__balance = balance  # Private attribute

    def deposit(self, amount):
        if amount > 0:
            self.__balance += amount
            print(f"Deposited {amount}. New balance: {self.__balance}")
        else:
            print("Deposit must be positive")

    def check_balance(self):
        print(f"Balance: {self.__balance}")

Usage

acc = BankAccount("Alex", 3000)
acc.deposit(500)
acc.withdraw(1000)
print(acc.check_balance())

Output

Deposited 500. New balance: 3500
Withdrew 1000. New balance: 2500
2500

Why it matters:
Encapsulation prevents accidental or unauthorized changes to data, ensuring data integrity and security.

ii. Inheritance
Reuse attributes and methods from a parent class.
Inheritance allows one class (child) to reuse and extend the functionality of another class (parent).
It’s like a child inheriting traits from their parents but adding their own personality.

Key idea:
Use super() to call the parent’s constructor.
Add or override methods to customize behavior.
Example

class FarmAnimal:
    def __init__(self, name, age):
        self.name = name
        self.age = age

    def eat(self):
        print(f"{self.name} is eating")

Child Class Example

class Cow(FarmAnimal):
    def __init__(self, name, age, milk_per_day):
        super().__init__(name, age)
        self.milk_per_day = milk_per_day

    def produce_milk(self):
        print(f"{self.name} produces {self.milk_per_day} litres per day")

Usage

mukiri = Cow("Mukiri", 3, 250)
mukiri.eat()
mukiri.produce_milk()

Output

Mukiri is eating
Mukiri produces 250 litres per day

Why it matters:
Inheritance promotes code reuse, reduces duplication, and helps maintain a logical hierarchy - making large systems easier to manage.

iii. Polymorphism- One Interface, Many Forms
Same method name behaves differently depending on the object.
Polymorphism means “many forms” - different objects can respond to the same method call in their own way.
It’s like different musicians playing the same song on different instruments - same melody, unique sound.

Key idea:
Use the same method name across classes.
Each class defines its own version of the method.
Example

class Delivery:
    def delivery_status(self):
        print("Delivery is processing")

class Motorbike(Delivery):
    def delivery_status(self):
        print("Delivery is complete")

Usage

d1 = Delivery()
d2 = Motorbike()
d1.delivery_status()
d2.delivery_status()

Output

Delivery is processing
Delivery is complete

Why it matters:
Polymorphism makes code flexible and scalable - you can add new classes without changing existing logic.

iv. Abstraction
Abstraction means showing only what’s necessary and hiding the rest.
It’s like driving a car - you use the steering wheel and pedals without worrying about how the engine works.

Key idea:
Use the abc module and @abstractmethod decorator.
Define abstract methods that must be implemented by child classes.
Example

from abc import ABC, abstractmethod

class Shape(ABC):
    @abstractmethod
    def area(self):
        pass

class Rectangle(Shape):
    def __init__(self, length, width):
        self.length = length
        self.width = width

    def area(self):
        return self.length * self.width

Usage

rect = Rectangle(10, 5)
print(rect.area())

Output

Why it matters:
Abstraction simplifies complex systems by exposing only essential features - making code cleaner, easier to use, and less error-prone.

Comparison of the Four Pillars of OOP

Pillar	Description	Example in Python	Key Benefit
Encapsulation	Restrict direct access to attributes, expose via methods.	Private `__balance` in `BankAccount`.	Protects data integrity.
Inheritance	Child class reuses parent’s attributes/methods.	`Cow(FarmAnimal)` inherits `eat()`.	Promotes code reuse.
Polymorphism	Same method behaves differently across classes.	`delivery_status()` in `Delivery` vs `Motorbike`.	Increases flexibility.
Abstraction	Hide implementation, expose only essentials.	`Shape` with abstract `area()`.	Simplifies usage.

Conclusion
Object-Oriented Programming transforms how we structure Python code - from simple scripts to scalable systems.

Mastering Python String Methods

Alex Murithi — Wed, 23 Sep 2026 10:45:21 +0000

Python String Methods: Indexing, Slicing, Case, Splitting, Joining, and More
Strings are one of the most common data types in Python - and they come with powerful built-in methods for manipulation, formatting, and validation.

1. Indexing and Slicing

Strings are sequences, so you can access individual characters or slices using indexes.

city = 'Nairobi'
print(city[0])      # First character
print(city[-1])     # Last character

Output

N
i

Slicing

city = 'Nairobi'
print(city[0:3])    # Nai
print(city[3:])     # robi
print(city[::-1])   # Reverse

Output

Nai
robi
iboriaN

2. Concatenation and Repetition

You can combine strings with + and repeat them with *.

first = 'Nai'
second = 'robi'
print(first + second)
print("=" * 30)
print("Ha" * 3)

Output

Nairobi
==============================
HaHaHa

3. Strings Are Immutable

You can’t change characters directly - you must create a new string.

city = 'Nairobi'
# city[0] = 'K'  # TypeError
city = 'K' + city[1:]
print(city)

Output

Kairobi

4. Changing Case

String methods help standardize text before comparison.

name = 'nancy wanjiku'
print(name.upper())
print(name.lower())
print(name.capitalize())
print(name.title())

Output

NANCY WANJIKU
nancy wanjiku
Nancy wanjiku
Nancy Wanjiku

swapcase()

greeting = 'Hello WORLD'
print(greeting.swapcase())

Output

hELLO world

5. Removing Extra Spaces

name = '   Faith  '
print(len(name))
print(f'[{name.strip()}]')
print(f'[{name.lstrip()}]')
print(f'[{name.rstrip()}]')

Output

9
[Faith]
[Faith  ]
[   Faith]

Removing Specific Characters

code = '###PROMO2025###'
print(code.strip('#'))

Output

PROMO2025

6. Splitting Text

Break a string into a list using split().

sentence = "Python is greater for data"
words = sentence.split()
print(f"Word count: {len(words)}")

Output

Word count: 5

Split by Separator

csv_line = "Moses,23,Nairobi,Data Science"
csv_split = csv_line.split(',')
print(csv_split)

Output

['Moses', '23', 'Nairobi', 'Data Science']

Split Lines

notes = "Buy bread\nGo for walk\nRide Bike"
lines = notes.splitlines()
print(lines)

Output

['Buy bread', 'Go for walk', 'Ride Bike']

Practical Example

route_info = 'Route46,Nairobi,50'
r_info = route_info.split(',')
print(f"{r_info[0]} costs KES {r_info[2]}")

Output

Route46 costs KES 50

7. join() - The Opposite of split()

items = ['Bread', 'Milk', 'Sugar']
shopping_text = ','.join(items)
print(shopping_text)

Output

Bread,Milk,Sugar

Custom Separator

parts = ['Nairobi', 'Mombasa', 'Kisumu']
result = " | ".join(parts)
print(result)

Output

Nairobi | Mombasa | Kisumu

Real Use Case - Cleaning Text

dirty = "  Moses,  23  ,  Nairobi  ,  Data Science    "
dirty_list = dirty.split(',')
clean_list = [i.strip() for i in dirty_list]
clean = ','.join(clean_list)
print(clean)

Output

Moses,23,Nairobi,Data Science

8. find(), index(), count(), and replace()

Checking Existence

message = "Your M-pesa transaction of KES 500 was successful."
print('M-pesa' in message)
print('failed' in message)

Output

True
False

Finding Position

print(message.find('KES'))
print(message.find('failed'))

Output

26
-1

Counting and Replacing

text = "banana"
print(text.count('a'))

new_message = message.replace('successful', 'reversed')
print(new_message)

Output

3
Your M-pesa transaction of KES 500 was reversed.

9. Validation Methods

Check if a string contains digits, letters, or both.

print("12345".isdigit())
print("Joan".isalpha())
print("Joan123".isalnum())
print("Joan 123".isalnum())

Output

True
True
True
False

Input Validation Example

age = input("Enter your age: ")

while not age.isdigit():
    print("Enter a valid whole number")
    age = input("Enter your age: ")

age = int(age)
print(f"You are {age} years old"
)

Sample Output

Enter your age: abc
Enter a valid whole number
Enter your age: 25
You are 25 years old

10. Prefixes, Suffixes, and Alignment

startswith() and endswith()

phone = '0712345678'
print(phone.startswith('07'))
print(phone.endswith('678'))

Output

True
True

Padding and Alignment

name = 'Otieno'
print(f"[{name:<10}]")  # Left align
print(f"[{name:>10}]")  # Right align
print(f"[{name:^10}]")  # Center align

Output

[Otieno    ]
[    Otieno]
[  Otieno  ]

Table Formatting Example

employee = [
    ("Brian", "Developer", 102000),
    ("Otieno", "Finance", 78000),
    ("Njeri", "Data Engineer", 95000)
]

print(f"{'Name':<12} {'Role':<18} {'Salary':>12}")
print("-" * 40)
for name, role, salary in employee:
    print(f"{name:<12} {role:<18} Ksh {salary:>8}")

Output

Name         Role               Salary
----------------------------------------
Brian        Developer       Ksh  102000
Otieno       Finance         Ksh   78000
Njeri        Data Engineer   Ksh   95000

Conclusion

String methods make text manipulation in Python simple and powerful.
You can:

Slice and combine text easily

Clean and format data

Validate user input

Search and replace efficiently

Mastering these methods will make your Python programs cleaner, smarter, and more professional.

Python Data Structures

Alex Murithi — Tue, 22 Sep 2026 12:54:08 +0000

Python Data Structures: Lists, Tuples, Dictionaries, and Sets

Python’s built‑in data structures are the foundation for organizing and manipulating data.

1. Lists - Ordered and Mutable

A list is like a container that can hold multiple items in order. You can add, remove, or change elements at any time. Lists are flexible and widely used for tasks where data changes frequently.

students = ['Bob', 'Faith', 'Brian', 'Ken']   # Strings
scores = [78, 85, 67, 34]                     # Integers
prices = [125.5, 250.0, 85.75]                # Floats
mixed = ['Amina', 24, True, 3.14]             # Mixed types
nested = [[1,2,3], [4,5,6], [7,8,9]]          # List of lists

print(students)
print(scores)
print(prices)
print(mixed)
print(nested)

Output

['Bob', 'Faith', 'Brian', 'Ken']
[78, 85, 67, 34]
[125.5, 250.0, 85.75]
['Amina', 24, True, 3.14]
[[1, 2, 3], [4, 5, 6], [7, 8, 9]]

Why Lists Matter
Lists are perfect for storing collections of items where order matters -like student names, daily scores, or shopping carts.

2. Tuples - Ordered and Immutable

A tuple looks like a list but cannot be changed after creation. This immutability makes tuples reliable for fixed data such as coordinates, RGB values, or days of the week.

nairobi_coord = (1.2864, 36.8172)
days = ('Mon', 'Tue', 'Wed', 'Thur', 'Fri', 'Sat', 'Sun')

print(nairobi_coord[0])   # Latitude
print(days[-1])           # Sunday
print(days[1:4])          # Tue, Wed, Thur

Output

1.2864
Sun
('Tue', 'Wed', 'Thur')

Why Tuples Matter
Tuples are useful when you want to ensure data stays constant. For example, in a mapping project, you store GPS coordinates in tuples to avoid accidental changes during calculations.

3. Dictionaries - Key‑Value Pairs

A dictionary stores data in key‑value pairs. Instead of accessing items by position, you use a key. This makes dictionaries ideal for structured data like user profiles, product catalogs, or JSON responses.

student = {
    "name": "Amina Wanjiku",
    "age": 24,
    "track": "Data Science",
    "scores": [78, 85, 91],
    "active": True
}

print(student["name"])          # Direct access
print(student.get("age"))       # Safe access
print(student.get("city", "Not found"))
print(student["scores"][0])     # Nested access

Output

Amina Wanjiku
24
Not found
78

Why Dictionaries Matter
Dictionaries are powerful for fast lookups and structured data. In a payroll automation script, you can use dictionaries to store employee details, with IDs as keys and salary/department as values - making retrieval and updates efficient.

4. Sets - Unique and Unordered

A set stores unique items. Duplicates are automatically removed, and order is not guaranteed. Sets are great for deduplication and membership tests.

cities = {"Nairobi", "Mombasa", "Kisumu", "Nairobi"}
print(cities)          # {'Nairobi', 'Mombasa', 'Kisumu'}
print(len(cities))     # 3

tags = ["Python", "data", "python", "sql", "data"]
unique_tags = set(tags)
print(unique_tags)     # {'Python', 'data', 'sql'}

Set Operations
Sets support mathematical operations like union, intersection, and difference.

ds_students = {"Hope", "Brian", "Otieno", "Njeri"}
de_students = {"Njeri", "Otieno", "Kamau", "Aisha"}

print(ds_students & de_students)  # Intersection
print(ds_students | de_students)  # Union
print(ds_students - de_students)  # Difference
print(ds_students ^ de_students)  # Symmetric difference

Output

{'Otieno', 'Njeri'}
{'Hope', 'Brian', 'Otieno', 'Njeri', 'Kamau', 'Aisha'}
{'Hope', 'Brian'}
{'Hope', 'Brian', 'Kamau', 'Aisha'}

Why Sets Matter
In a data‑cleaning pipeline, they are used to remove duplicate email addresses before sending notifications - ensuring each user received only one message.

Real‑World Example: Mini Inventory System

products = {
    'Sugar': {'price': 120, 'stock': 50},
    'Rice': {'price': 200, 'stock': 30},
    'Beans': {'price': 150, 'stock': 0}
}

# List of items to restock
restock_list = [item for item, info in products.items() if info['stock'] == 0]

# Tuple for supplier info
supplier = ('AgroSupplies Ltd', 'Nairobi')

# Set of unique categories
categories = {'Food', 'Grains', 'Food'}  # duplicates removed

print(f"Restock items: {restock_list}")
print(f"Supplier: {supplier[0]} located in {supplier[1]}")
print(f"Categories: {categories}")

Output

Restock items: ['Beans']
Supplier: AgroSupplies Ltd located in Nairobi
Categories: {'Food', 'Grains'}

Conclusion

Use lists for flexible, ordered data.

Use tuples for fixed, unchangeable data.

Use dictionaries for structured, key‑value mappings.

Use sets for unique, unordered collections.

Python Functions

Alex Murithi — Tue, 22 Sep 2026 12:53:22 +0000

Python Functions: Defining, Parameters, Return Values, and Scope

Functions are reusable blocks of code that perform a specific task. They help organize programs, reduce repetition, and make code easier to maintain.

1. Defining and Calling Functions

A function is defined using the def keyword, followed by its name and parentheses. Inside the function, you write the code that should run when the function is called.

def greet():
    print("Hello, welcome to Python!")

# Calling the function
greet()

Output

Hello, welcome to Python!

Why This Matters
Instead of writing the same greeting multiple times, you define it once and call it whenever needed. This makes your code shorter and easier to maintain.

2. Parameters and Arguments

Parameters are variables defined inside the function parentheses. Arguments are the actual values you pass when calling the function.

def greet_user(name):
    print(f"Hello, {name}!")

greet_user("Alice")
greet_user("Brian")

Output

Hello, Alice!
Hello, Brian!

Default parameters can also be used to provide fallback values:

def greet_user(name="Guest"):
    print(f"Hello, {name}!")

greet_user()
greet_user("Cynthia")

Output

Hello, Guest!
Hello, Cynthia!

3. Return Values

Functions can return results using the return keyword. This allows you to store or reuse the output.

def calculate_discount(price, rate=0.10):
    discount = price * rate
    return price - discount

final_price = calculate_discount(2000)
print(f"Final Price: Ksh {final_price}")

Output

Final Price: Ksh 1800.0

Why Return Values Matter
Returning values makes functions flexible. Instead of just printing results, you can use them in further calculations or store them in variables.

3. Scope - Local vs Global Variables

Scope determines where a variable can be accessed.

Local variables exist only inside a function.

Global variables exist outside and can be accessed anywhere.

message = "Global message"

def show_message():
    message = "Local message"
    print(message)

show_message()
print(message)

Output

Local message
Global message

Why Scope Matters
Understanding scope prevents bugs. For example, if you accidentally overwrite a global variable inside a function, it can affect the rest of your program.

Practical Examples
1. VAT Calculator
Instead of repeating VAT calculations for every item, define a function:

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

print(f"Item 1: Ksh {add_vat(1000)}")
print(f"Item 2: Ksh {add_vat(800)}")
print(f"Item 3: Ksh {add_vat(600)}")

Output

Item 1: Ksh 1140.0
Item 2: Ksh 912.0
Item 3: Ksh 684.0

2. Student Report Formatter

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

def student_info(name, score):
    print_divider()
    print(f"Student: {name}")
    print(f"Score: {score}")
    print_divider()

student_info("Amina", 87)
student_info("Brian", 98)

Output

========================================
Student: Amina
Score: 87
========================================
========================================
Student: Brian
Score: 98
========================================

3. Banking Example

def deposit(balance, amount):
    return balance + amount

def withdraw(balance, amount):
    if amount > balance:
        print("Insufficient funds")
        return balance
    return balance - amount

balance = 1000
balance = deposit(balance, 500)
balance = withdraw(balance, 300)
print(f"Final Balance: Ksh {balance}")

Output

Final Balance: Ksh 1200

Conclusion

Functions are used to organize and reuse code.

Pass parameters makes functions flexible.

Return values are used to capture results.

Understanding scope helps avoid variable conflicts.

Mastering functions makes Python programs cleaner, more modular, and easier to maintain.

Mastering Python Loops

Alex Murithi — Mon, 21 Sep 2026 05:16:46 +0000

Python Loops: for, while, break, continue, and enumerate()

Loops are one of the most powerful features in Python. They allow us to repeat tasks, process collections, and control program flow without writing repetitive code.

1. for Loops - Iterating Over Collections

A for loop is used to iterate over a sequence (like a list, tuple, or string). It’s the most common loop in Python because it directly works with collections.

names = ["Alice", "Brian", "Jane", "David"]

for name in names:
    print(f"Good morning, {name}")
print("For loop ended")

Output

Good morning, Alice
Good morning, Brian
Good morning, Jane
Good morning, David
For loop ended

Looping with range()
range() generates a sequence of numbers, often used in loops.

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

for n in range(1, 11, 2):
    print(n)

Output

Practical Examples
Multiplication table for 5

for i in range(1, 6):
    print(f"5 X {i} = {5 * i}")

Output

Squares of numbers

for i in range(1, 6):
    print(f"{i} squared is {i ** 2}")

Output

1 squared is 1
2 squared is 4
3 squared is 9
4 squared is 16
5 squared is 25

Summing numbers

numbers = [10, 20, 30, 40]
total = 0
for number in numbers:
    total += number
print(f"Total: {total}")

Output

Total: 100

2. while Loops - Repeat Until a Condition is False

A while loop runs as long as a condition is True. It’s useful when you don’t know in advance how many times you’ll need to repeat.

count = 1
while count < 5:
    print(f"Count: {count}")
    count += 1

Output

Count: 1
Count: 2
Count: 3
Count: 4

Practical Examples
Password check

password = ""
while password != "lux2025":
    print("You entered the wrong password")
    password = input("Input password: ")
print("Access Granted")

Output

You entered the wrong password
Input password: 1234
You entered the wrong password
Input password: lux2025
Access Granted

Savings goal calculator

saving_goal = int(input("What is your saving goal (Ksh)? "))
monthly_savings = int(input("How much can you save per month (Ksh)? "))

total = 0
months = 0

while total < saving_goal:
    total += monthly_savings
    months += 1
    print(f"Month {months}: Total so far = Ksh {total}")

print(f"It took {months} months to reach Ksh {total}")

Output

Month 1: Total so far = Ksh 1200
Month 2: Total so far = Ksh 2400
Month 3: Total so far = Ksh 3600
Month 4: Total so far = Ksh 4800
Month 5: Total so far = Ksh 6000
It took 5 months to reach Ksh 6000

3. break and continue - Controlling Loop Flow

break: exits the loop entirely.

continue: skips the current iteration and moves to the next.

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

total = 0
for amount in transactions:
    if amount > 10000:
        print("Large transaction detected. Stopping...")
        break
    total += amount
    print(f"Running total: {total}")

Output

Running total: 1500
Running total: 4500
Running total: 5300
Large transaction detected. Stopping...

4. enumerate() - Loop With Index

Sometimes you need both the item and its index. enumerate() provides this elegantly.

fruits = ['Mango', 'Banana', 'Orange']

for index, fruit in enumerate(fruits):
    print(f"{index}: {fruit}")

Output

0: Mango
1: Banana
2: Orange

Real‑World Examples

1. Class Scores Analysis

scores = [78, 85, 91, 65, 72]

total = 0
above_70 = 0

for score in scores:
    total += score
    if score > 70:
        above_70 += 1

average = total / len(scores)
print(f"Total: {total}")
print(f"Above 70 Scores: {above_70}")
print(f"Average: {average}")

Output

Total: 391
Above 70 Scores: 4
Average: 78.2

2. ATM PIN Validator

correct_pin = "4532"
attempts = 0
max_attempts = 3

while attempts < max_attempts:
    pin = input("Enter your PIN: ")
    attempts += 1

    if pin == correct_pin:
        print("Access granted! Welcome.")
        break
    else:
        remaining = max_attempts - attempts
        if remaining > 0:
            print(f"Wrong PIN. {remaining} attempts left")

if attempts == max_attempts and pin != correct_pin:
    print("Card Blocked. Please visit the branch")

Output

Enter your PIN: 1234
Wrong PIN. 2 attempts left
Enter your PIN: 9999
Wrong PIN. 1 attempts left
Enter your PIN: 4532
Access granted! Welcome.

Conclusion
Use for loops to iterate over collections or ranges.

Use while loops when repetition depends on a condition.

Use break to exit early and continue to skip iterations.

Use enumerate() when you need both index and value.

Loops are the backbone of automation in Python - they make repetitive tasks efficient and clean.

Python Operators & Conditionals

Alex Murithi — Mon, 21 Sep 2026 05:14:18 +0000

comparison operators and conditional statements (if, elif, else)

i. Comparison Operators - Checking Values in Python
Comparison operators are used to compare two values. They always return a Boolean result (True or False). This is useful when making decisions in code.

age = 20
score = 75

print(age == 20)     # True
print(age == 25)     # False
print(age != 18)     # True
print(score > 20)    # True
print(score < 70)    # False
print(score >= 75)   # True
print(score <= 74)   # False

Output

True
False
True
True
False
True
False

ii. Comparing Strings - Case Sensitivity Matters
String comparisons check if two text values are equal. Python is case-sensitive, so "Data Engineering" is not equal to "data Engineering".

track = "Data Engineering"

print(track == "Data Engineering")
print(track == "data Engineering")
print(track == "Data Science")
print(track != "Data Science")

Output

True
False
False
True

iii. Alphabetical Comparison - Order of Characters
Strings can also be compared alphabetically. Python uses Unicode values, so "A" is less than "C".

print("Amina" < "Collins")   # True, A comes before C
print("Nairobi" == "nairobi") # False, case-sensitive

Output

True
False

iv. Basic if Statement - Single Condition Check
An if statement runs code only when the condition is true. If the condition is false, the block is skipped.

score = 55

if score >= 50:
    print("You passed")      
print("Thank you for taking the test")

Output

You passed
Thank you for taking the test

v. if with else - Two Possible Paths
The else block runs when the if condition is false, giving two possible outcomes.

score = 45

if score >= 50:
    print("Pass well done")
else:
    print("FAIL - Please try again")

print(f"Your Score was {score}")

Output

FAIL - Please try again
Your Score was 45

vi. if-elif-else - Multiple Conditions
elif allows checking multiple conditions in sequence. Order matters — the first true condition is executed.

score = 72

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

Output

Grade B - Good

Matatu Ticket Pricing- Example
Conditionals can model real-world systems like fare calculation.

distance = 12

if distance <= 5:
    fare = 30
elif distance <= 15:
    fare = 50
elif distance <= 30:
    fare = 80
else:
    fare = 120

print("=========MATATU FARE========")
print(f"Distance: {distance}km | Fare Ksh{fare}")
print("----------------------------")

Output

text
=========MATATU FARE========
Distance: 12km | Fare Ksh50
----------------------------

vii. Logical Operators - Combining Conditions
Logical operators (and, or, not) let us combine multiple conditions.

Using and - Both Must Be True

age = 18
has_id = "yes"

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

Output

text
Access granted - Welcome

Using or - At Least One Must Be True

is_student = "yes"
is_senior = "no"

if is_student == "yes" or is_senior == "yes":
    print("Discount applied - 25% off")
else:
    print("No discount")

Output

text
Discount applied - 25% off

Example: Banking - Transaction Limits
Different account types have different limits. Conditionals enforce these rules.

account = "basic"
amount = 80000

if account == "basic" and amount <= 70000:
    print("Transaction Approved")
elif account == "basic" and amount > 70000:
    print("Limit Exceeded for basic account")
elif account == "premium" and amount <= 300000:
    print("Transaction Approved")
else:
    print("Limit exceeded for premium account (max Ksh 300,000)")

Output

Limit Exceeded for basic account

viii. Nested if - Step-by-Step Checks
Nested if allows deeper checks when one condition depends on another.

username = "admin"

if username == "admin":
    password = "kenya2025"
    if password == "kenya2025":
        print("Welcome admin")
    else:
        print("Incorrect password")
else:
    print("User not found")

Output

Welcome admin

Example: Track Enrolled - elif for Alternatives
elif is useful when there are multiple possible paths.

track = "DE"

if track == "DS":
    print("Your next course: Pandas and Numpy")
elif track == "DE":
    print("Your next course: Kafka and Airflow")
else:
    print("Unknown track")

Output

Your next course: Kafka and Airflow

Example: Laptop process - Nested Decision
Here, the second question depends on the first answer.

has_laptop = "yes"
os_type = "Windows"

if has_laptop == "yes":
    if os_type == "Windows":
        print("Install python from python.org")
    else:
        print("Python may already be installed - check with python3 --version")   
else:
    print("Please borrow a laptop for this session")

Output

Install python from python.org

Example: Loan Qualification - Multiple Nested Checks
This example combines several conditions to decide loan eligibility.

has_account = "yes"
months_in = 8
salary = 25000

if has_account == "yes":
    if months_in < 6:
        print("You need at least 6 months of account history")
    else:
        if salary < 20000:
            print("Minimum salary for a loan is Ksh 20000")
        else:
            print("Congratulations! You qualify for a loan")
else:
    print("Sorry, you do not have an account")

Output

Congratulations! You qualify for a loan

Conclusion

Comparison operators check values and relationships.

Conditionals (if, elif, else) control program flow.

Logical operators (and, or) combine conditions.

Nested if allows deeper decision-making.

Order of conditions matters - always put the most specific first.

Python Basics

Alex Murithi — Sun, 20 Sep 2026 10:01:38 +0000

Fundamentals of Python programming: variables, data types, input/output, and basic syntax - with practical examples.

1. First Line of Python - Printing Output

The print() function displays text or values on the screen.

print("Hello Python!")
print("Learning step by step")
print("Line A\n")
print("Line B")

Output

Hello Python!
Learning step by step
Line A

Line B

\n creates a new line.

2. Variables - Storing Information

Variables are containers for values. You assign them using =.

student = "Maria"
age = 19
city = "Mombasa"

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

Output

Maria is 19 years old and lives in Mombasa

Variables can be updated or replaced.

points = 40
print("Points before:", points)

points = 55   # replace old value
print("Points after:", points)

points = points + 15
print("Points plus 15:", points)

Output

Points before: 40
Points after: 55
Points plus 15: 70

3. Style Rules Readable Code

Use descriptive variable names and keep track of updates.

name = "James Mwangi"
age = 30
track = "Web Development"
sessions_left = 5

print(name, "is", age, "years old.", "Track:", track, "Sessions left:", sessions_left)

sessions_left = sessions_left - 1
print("Sessions left:", sessions_left)

Output

James Mwangi is 30 years old. Track: Web Development Sessions left: 5
Sessions left: 4

4. Data Types - Kinds of Values

Python has built-in data types:

str → text (strings, always in quotes)

int → whole numbers

float → decimal numbers

bool → logical values (True/False)

title = "Python Basics"   # str
year = 2026               # int
price = 99.99             # float
is_published = False      # bool

print(type(title))
print(type(year))
print(type(price))
print(type(is_published))

Output

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

Strings that look like numbers ("10") are still text, not integers.

5. Input - Getting User Data

The input() function lets users type values. By default, it returns a string.

name = input("Enter your name: ")
print("Hello,", name)
print("Welcome to Python Basics")

Output

Enter your name: Kelvin
Hello, Kelvin
Welcome to Python Basics

6. Convert input to numbers using int() or float().

age = int(input("Enter your age: "))
print("You are", age, "years old")
print("Next year you will be", age + 1)

Output

Enter your age: 21
You are 21 years old
Next year you will be 22

7. f-Strings - Formatted Output

f-strings make it easy to embed variables inside text.

name = "Sophia"
age = 22
city = "Kisumu"
balance = 3200.50

print(f"Name: {name} Age: {age} City: {city} Balance: {balance}")
print(f"Next year {name} will be {age + 1} years old")

Output

Name: Sophia Age: 22 City: Kisumu Balance: 3200.5
Next year Sophia will be 23 years old

Practical Examples - Applying the Basics
Example 1: Simple Calculator with VAT

price = int(input("Enter the price (Ksh): "))
quantity = int(input("Enter quantity: "))

subtotal = price * quantity
vat = subtotal * 0.16
total = subtotal + vat

print(f"Item price: Ksh {price}")
print(f"Quantity:   {quantity}")
print(f"Subtotal:   Ksh {subtotal}")
print(f"VAT (16%):  Ksh {vat}")
print(f"Total:      Ksh {total}")

Output

Enter the price (Ksh): 200
Enter quantity: 3
Item price: Ksh 200
Quantity:   3
Subtotal:   Ksh 600
VAT (16%):  Ksh 96.0
Total:      Ksh 696.0

Example 2: Bus Ticket Booking

print("=" * 35)
print("           BUS TICKET BOOKING")
print("=" * 35)

passenger_name = input("Enter passenger name: ")
route = input("Enter route: ")
fare = int(input("Enter fare (Ksh): "))
booking_fee = 15

print("=" * 35)
print("           TICKETING")
print("=" * 35)
print(f"Passenger      : {passenger_name}")
print(f"Route          : {route}")
print(f"Fare           : Ksh{fare}")
print(f"Booking fee    : Ksh{booking_fee}")
print("-" * 35)
print(f"Total          : Ksh{fare + booking_fee}")
print("=" * 35)
print("         THANK YOU FOR BOOKING")
print("=" * 35)

Output

Enter passenger name: Brian
Enter route: Nakuru
Enter fare (Ksh): 120
Passenger      : Brian
Route          : Nakuru
Fare           : Ksh120
Booking fee    : Ksh15
Total          : Ksh135
THANK YOU FOR BOOKING

Example 3: School Grade Calculator

print("=" * 38)
print("     SCHOOL GRADE CALCULATOR")
print("=" * 38)

student_name = input("Enter Student name: ")
subject = input("Enter subject: ")
print(f"Enter 3 test scores for {student_name}:")
test_1 = int(input("  Test 1: "))
test_2 = int(input("  Test 2: "))
test_3 = int(input("  Test 3: "))

average = (test_1 + test_2 + test_3) / 3

print("=" * 38)
print(f"   REPORT: {student_name}")
print("=" * 38)
print(f"Subject: {subject}")
print(f"Average Score: {average}")

Output

Enter Student name: Lydia
Enter subject: English
Enter 3 test scores for Lydia:
  Test 1: 65
  Test 2: 70
  Test 3: 80

REPORT: Lydia
Subject: English
Average Score: 71.666...

Conclusion

print() displays output.

Variables store values that can be updated.

Python has built-in data types (str, int, float, bool).

input() gets user input, and you can convert it to numbers.

f-strings make output formatting easier.

Data Cleaning and Analysis with SQL

Alex Murithi — Wed, 16 Sep 2026 17:01:23 +0000

1. Problem Statement

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

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

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

Mixed casing in names and cities

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

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

Invalid ratings (0, 6)

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

2. Data & Database Structure

Staging Table
All columns as TEXT to accept dirty data.

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

3. SQL Cleaning Transformations

Names standardized with INITCAP(TRIM()).

Phones normalized to local format (07…).

Gender reduced to Male / Female.

Seat classes mapped to Economy / Business.

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

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

Invalid ratings set to NULL.

Duplicates removed using ctid.

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

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

4. Analytical View

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

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

Sample Output of v_clean_trips

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

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

5. Business Analysis & Outputs

i. Route Performance

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

Output:

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

Insight: Nairobi → Mombasa is the backbone route.

ii. Driver Performance

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

Output:

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

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

iii. Monthly Revenue Trends

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

Output:

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

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

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

Gender Split: Female passengers slightly outnumber males.

Seat Class Preference:

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

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

v. Cancellations & Lost Revenue

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

Output

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

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

6. Key Findings

Top Route: Nairobi → Mombasa dominates revenue.

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

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

Passenger Profile: Nairobi passengers dominate, mostly Economy class.

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

Operations: Fridays and mornings are busiest.

7. Challenges & Solutions

Dirty dates → solved with TO_DATE() conversions.

Phone formats → normalized with regex.

Duplicate IDs → removed using ctid.

Invalid ratings → set to NULL.

Key Takeaways:
Always stage dirty data first.

Cleaning is 70% of the work.

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

Indexes matter for performance.

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

Conclusion

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

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

SQL Window Functions Made Simple

Alex Murithi — Wed, 16 Sep 2026 03:46:17 +0000

When you need to calculate averages, ranks, or totals without collapsing rows, SQL’s window functions are your best friend. They let you perform calculations across related rows while keeping every original record visible - perfect for analytics, leaderboards, and comparisons.

Exploring window functions using a sample Safari Trips database.

Safari Trips Database Schema
Working with two tables: drivers and trips.

drivers

driver_id	driver_name	city
1	James Kariuki	Nairobi
2	Mary Achieng	Kisumu
3	Peter Otieno	Mombasa
4	Sarah Njeri	Eldoret

trips

trip_id	driver_id	rider_id	trip_date	fare	rider_rating
101	1	11	2026-08-01	2000	4.8
102	2	12	2026-08-02	1500	4.5
103	3	13	2026-08-03	2500	4.9
104	4	14	2026-08-04	1800	4.6

Definition:
A window function performs a calculation across a set of related rows - called a window - and adds the result as a new column.

Unlike GROUP BY, which collapses rows into groups, window functions preserve all rows and simply add computed values alongside them.

Example 1: Average Fare per Driver
GROUP BY way:

SELECT driver_id, AVG(fare) AS avg_fare
FROM safari.trips
GROUP BY driver_id;

Output:

driver_id	avg_fare
1	2000
2	1500
3	2500
4	1800

Window function way:

SELECT driver_id, fare, AVG(fare) OVER() AS avg_fare
FROM safari.trips;

Output:

driver_id	fare	avg_fare
1	2000	1950
2	1500	1950
3	2500	1950
4	1800	1950

Notice: every row survives, with the overall average added alongside.

Section A - Ranking Functions
A1. Driver Revenue Ranking

WITH driver_revenue AS (
    SELECT driver_id, SUM(fare) AS total_revenue
    FROM safari.trips
    GROUP BY driver_id
)
SELECT 
    d.driver_name,
    dr.total_revenue,
    RANK() OVER(ORDER BY dr.total_revenue DESC) AS revenue_rank
FROM driver_revenue dr
JOIN safari.drivers d ON d.driver_id = dr.driver_id;

Output:

driver_name	total_revenue	revenue_rank
Peter Otieno	2500	1
James Kariuki	2000	2
Sarah Njeri	1800	3
Mary Achieng	1500	4

A2. Each Driver’s Best Trip

WITH ranked_trips AS (
    SELECT 
        driver_id,
        trip_id,
        fare,
        trip_date,
        ROW_NUMBER() OVER(PARTITION BY driver_id ORDER BY fare DESC) AS fare_rank
    FROM safari.trips
)
SELECT d.driver_name, rt.trip_id, rt.fare, rt.trip_date
FROM ranked_trips rt
JOIN safari.drivers d ON d.driver_id = rt.driver_id
WHERE rt.fare_rank = 1
ORDER BY rt.fare DESC;

Output:

driver_name	trip_id	fare	trip_date
Peter Otieno	103	2500	2026-08-03
James Kariuki	101	2000	2026-08-01
Sarah Njeri	104	1800	2026-08-04
Mary Achieng	102	1500	2026-08-02

Section B - Totals and Aggregates
B1. Every Trip with Rider’s Total Spend

SELECT 
    rider_id,
    trip_id,
    fare,
    SUM(fare) OVER(PARTITION BY rider_id) AS rider_total_spend
FROM safari.trips
ORDER BY rider_id;

Output:

rider_id	trip_id	fare	rider_total_spend
11	101	2000	2000
12	102	1500	1500
13	103	2500	2500
14	104	1800	1800

Section C - Comparing Rows
C1. Fare Compared to Previous Trip

SELECT 
    d.driver_name,
    trip_id,
    trip_date,
    fare,
    LAG(fare) OVER(PARTITION BY t.driver_id ORDER BY trip_date) AS prev_fare,
    fare - LAG(fare) OVER(PARTITION BY t.driver_id ORDER BY trip_date) AS fare_change
FROM safari.trips t
JOIN safari.drivers d ON d.driver_id = t.driver_id
WHERE t.driver_id = 1;

Output (Driver 1):

driver_name	trip_id	trip_date	fare	prev_fare	fare_change
James Kariuki	101	2026-08-01	2000	NULL	NULL
James Kariuki	105	2026-08-10	2200	2000	200

LAG() looks back at the previous row, making it easy to track changes over time.

Key Takeaways
Window functions let you calculate across rows while keeping all data visible.

Use them for ranking, running totals, comparisons, and analytics.

Common functions:

ROW_NUMBER() → Sequential numbering

RANK() / DENSE_RANK() → Leaderboards

SUM() / AVG() → Totals and averages

LAG() / LEAD() → Previous or next row comparisons

Conclusion
Window functions are the backbone of analytical SQL. They bridge the gap between raw data and insights - whether you’re ranking drivers, comparing fares, or tracking trends.

Understanding Subqueries and CTEs in SQL

Alex Murithi — Tue, 15 Sep 2026 17:16:03 +0000

Sometimes a single SQL query isn’t enough. To answer one question, you may first need to answer another. That’s where subqueries and Common Table Expressions (CTEs) come in. They let you nest logic, reuse results, and keep queries readable.

Exploring both concepts using a retail sales database with customers, products, and sales.

Retail Dataset
customers

customer_id	name	city
1	Alice Kariuki	Nairobi
2	Brian Oduor	Kisumu
3	Carol Wanjiru	Mombasa
4	Daniel Mwangi	Eldoret

products

sale_id	customer_id	product_id	quantity	sale_date	discount
1	1	101	1	2026-08-01	NULL
2	2	102	2	2026-08-02	10
3	3	103	1	2026-08-03	NULL
4	4	104	5	2026-08-04	5

sales

sale_id	customer_id	product_id	quantity	sale_date	discount
1	1	101	1	2026-08-01	NULL
2	2	102	2	2026-08-02	10
3	3	103	1	2026-08-03	NULL
4	4	104	5	2026-08-04	5

1. Subqueries

A subquery is a query inside another query. The inner query runs first, and its result is used by the outer query.

Example 1: Sales above the average discount

SELECT sale_id, customer_id, discount
FROM sales
WHERE discount > (SELECT AVG(discount) FROM sales WHERE discount IS NOT NULL);

Output:

sale_id	customer_id	discount
2	2	10

The subquery calculates the average discount, and the outer query finds sales above it.

Example 2: Highest product price

SELECT product_id, product_name, price
FROM products
WHERE price = (SELECT MAX(price) FROM products);

Output:

product_id	product_name	price
101	Laptop	75000

Example 3: Customers who bought more than Alice’s order quantity

SELECT sale_id, customer_id, quantity
FROM sales
WHERE quantity > (
    SELECT quantity
    FROM sales
    WHERE customer_id = 1
);

Output:

sale_id	customer_id	quantity
4	4	5

2. Common Table Expressions (CTEs)

A CTE is a temporary result set defined with WITH. It makes queries easier to read and reuse.

Example 1: Sales above the average discount (CTE way)

WITH avg_discount AS (
    SELECT AVG(discount) AS avg_disc
    FROM sales
    WHERE discount IS NOT NULL
)
SELECT sale_id, customer_id, discount
FROM sales, avg_discount
WHERE discount > avg_disc;

Output:

sale_id	customer_id	discount
2	2	10

Example 2: Customers with multiple purchases

WITH customer_sales AS (
    SELECT customer_id, COUNT(*) AS total_sales
    FROM sales
    GROUP BY customer_id
)
SELECT c.name, cs.total_sales
FROM customers c
JOIN customer_sales cs ON c.customer_id = cs.customer_id
WHERE cs.total_sales > 1;

Output:

name	total_sales
Alice Kariuki	2

Example 3: Products priced above the average

WITH avg_price AS (
    SELECT AVG(price) AS avg_price
    FROM products
)
SELECT product_name, price
FROM products, avg_price
WHERE price > avg_price;

Output:

product_name	price
Laptop	75000

General Notes
Subqueries → Useful for filtering, comparisons, and nested logic.

CTEs → Useful for readability, reusability, and breaking down complex queries.

Both help you write cleaner, more powerful SQL.

Key Takeaway
Subqueries and CTEs are two sides of the same coin:

Use subqueries when you need quick, inline calculations.

Use CTEs when you want clarity and reusability in complex queries.

Together, they make SQL flexible enough to handle real-world reporting needs - whether you’re analyzing discounts, product prices, or customer purchases.

Conditional Logic in SQL

Alex Murithi — Tue, 15 Sep 2026 13:43:22 +0000

SQL isn’t just about retrieving data - sometimes you need to apply rules or handle missing values directly in your queries. That’s where conditional expressions like CASE WHEN and COALESCE come in. They let you make decisions and keep your reports clean without changing the underlying tables.

CASE WHEN and COALESCE Explained with a a sample hospital database.

patients

patient_id	name	age	diagnosis
1	James Kariuki	45	Diabetes
2	Mary Achieng	30	NULL
3	Peter Otieno	65	Hypertension
4	Sarah Njeri	50	NULL

appointments

appointment_id	patient_id	doctor	visit_date	fee
101	1	Dr. Kim	2026-09-01	2000
102	2	Dr. Ali	2026-09-02	1500
103	3	Dr. Kim	2026-09-03	2500
104	4	Dr. Patel	2026-09-04	NULL

1. CASE WHEN - Conditional Logic

CASE WHEN works like an if-else statement inside SQL. It lets you categorize or transform values based on conditions.

Example: Categorize patients by age group.

SELECT name,
       age,
       CASE 
         WHEN age < 40 THEN 'Young Adult'
         WHEN age BETWEEN 40 AND 60 THEN 'Middle Age'
         ELSE 'Senior'
       END AS age_group
FROM patients;

Output:

name	age	age_group
James Kariuki	45	Middle Age
Mary Achieng	30	Young Adult
Peter Otieno	65	Senior
Sarah Njeri	50	Middle Age

CASE WHEN helps you apply business rules directly in queries.

Example: Flag expensive appointments.

SELECT appointment_id, fee,
       CASE 
         WHEN fee >= 2000 THEN 'High Cost'
         ELSE 'Standard'
       END AS fee_category
FROM appointments;

Output:

appointment_id	fee	fee_category
101	2000	High Cost
102	1500	Standard
103	2500	High Cost
104	NULL	Standard

2. COALESCE - Handling NULLs

COALESCE replaces NULL values with a default. It’s like saying: “If this is missing, use that instead.”

Example: Show diagnosis, or mark as ‘Not Recorded’.

SELECT name,
       COALESCE(diagnosis, 'Not Recorded') AS diagnosis_status
FROM patients;

Output:

name	diagnosis_status
James Kariuki	Diabetes
Mary Achieng	Not Recorded
Peter Otieno	Hypertension
Sarah Njeri	Not Recorded

COALESCE ensures missing data doesn’t break your reports.

Example: Replace missing fees with 0.

SELECT appointment_id,
       COALESCE(fee, 0) AS fee_paid
FROM appointments;

Output:

appointment_id	fee_paid
101	2000
102	1500
103	2500
104	0

General Notes
CASE WHEN → Adds conditional logic (like if-else).

COALESCE → Handles missing values by substituting defaults.

Together, they make SQL queries smarter and more resilient.

Key Takeaway
Conditional logic in SQL lets you apply rules and handle missing data without changing your tables. Whether you’re categorizing patients, flagging expensive appointments, or filling in missing diagnoses, CASE WHEN and COALESCE give you the flexibility to make your queries reflect real-world business needs.

SQL Functions Explained

Alex Murithi — Tue, 15 Sep 2026 13:08:00 +0000

SQL isn’t just about retrieving data - it also lets you transform, calculate, and analyze data using functions. Functions are built-in operations that take input, perform a task, and return a result.

Exploring SQL functions using a sample university database with students, courses, and enrollments.

Sample University Dataset

students

student_id	name	age	major
1	John Mwangi	20	Computer Sci
2	Sarah Achieng	22	Business
3	David Otieno	19	Engineering
4	Mary Njeri	21	Computer Sci

courses

course_id	course_name	credits
101	Database Systems	3
102	Accounting Basics	4
103	Physics I	3
104	Web Development	2

enrollments

enrollment_id	student_id	course_id	grade	enroll_date
1	1	101	85	2026-01-10
2	2	102	90	2026-01-12
3	3	103	70	2026-01-15
4	4	104	88	2026-01-18

i. Aggregate Functions

Aggregate functions summarize data.

Example: Count how many students are enrolled.

SELECT COUNT(*) AS total_students
FROM students;

Output:

total_students
4

COUNT() returns the number of rows

Example: Find the average grade across all enrollments.

SELECT AVG(grade) AS avg_grade
FROM enrollments;

Output:

avg_grade
83.25

AVG() calculates the mean value.

ii. String Functions

String functions manipulate text.

Example: Convert student names to uppercase.

SELECT UPPER(name) AS upper_name
FROM students;

Output:

upper_name
JOHN MWANGI
SARAH ACHIENG
DAVID OTIENO

UPPER() transforms text to uppercase.

Example: Get the first 3 letters of each course.

SELECT SUBSTRING(course_name, 1, 3) AS short_code
FROM courses;

Output:

short_code
Dat
Acc
Phy

SUBSTRING() extracts part of a string.

iii. Numeric Functions

Numeric functions perform calculations.

Example: Round grades to the nearest 10.

SELECT student_id, ROUND(grade, -1) AS rounded_grade
FROM enrollments;

Output:

student_id	rounded_grade
1	90
2	90
3	70
4	90

ROUND() adjusts numeric precision.

iv. Date/Time Functions

Date functions help analyze time-based data.

Example: Extract the month from enrollment dates.

SELECT enrollment_id, EXTRACT(MONTH FROM enroll_date) AS enroll_month
FROM enrollments;

Output:

enrollment_id	enroll_month
1	1
2	1
3	1
4	1

EXTRACT() pulls out parts of a date.

General Notes on SQL Functions
Aggregate functions → summarize data (COUNT, SUM, AVG, MIN, MAX)

String functions → manipulate text (UPPER, LOWER, SUBSTRING, LENGTH)

Numeric functions → perform math (ROUND, CEIL, FLOOR)

Date functions → handle time (NOW, EXTRACT, DATE_PART)

DEV Community: Alex Murithi

Mastering Object-Oriented Programming (OOP) in Python

1. Classes and Objects

2. The __init__ Method (Constructor)

Output

Output

3. The Four Pillars of OOP

Usage

Output

Child Class Example

Usage

Output

Usage

Output

Usage

Output

Comparison of the Four Pillars of OOP

Mastering Python String Methods

1. Indexing and Slicing

2. Concatenation and Repetition

3. Strings Are Immutable

4. Changing Case

5. Removing Extra Spaces

6. Splitting Text

7. join() - The Opposite of split()

8. find(), index(), count(), and replace()

9. Validation Methods

10. Prefixes, Suffixes, and Alignment

Conclusion

Python Data Structures

1. Lists - Ordered and Mutable

2. Tuples - Ordered and Immutable

3. Dictionaries - Key‑Value Pairs

4. Sets - Unique and Unordered

Real‑World Example: Mini Inventory System

Conclusion

Python Functions

1. Defining and Calling Functions

2. Parameters and Arguments

3. Return Values

3. Scope - Local vs Global Variables

Conclusion

Mastering Python Loops

1. for Loops - Iterating Over Collections

2. while Loops - Repeat Until a Condition is False

3. break and continue - Controlling Loop Flow

4. enumerate() - Loop With Index

Real‑World Examples

Python Operators & Conditionals

Conclusion

Python Basics

1. First Line of Python - Printing Output

2. Variables - Storing Information

3. Style Rules Readable Code

4. Data Types - Kinds of Values

5. Input - Getting User Data

6. Convert input to numbers using int() or float().

7. f-Strings - Formatted Output

Conclusion

Data Cleaning and Analysis with SQL

1. Problem Statement

2. Data & Database Structure

3. SQL Cleaning Transformations

4. Analytical View

5. Business Analysis & Outputs

6. Key Findings

7. Challenges & Solutions

Conclusion

SQL Window Functions Made Simple

Understanding Subqueries and CTEs in SQL

1. Subqueries

2. Common Table Expressions (CTEs)

Conditional Logic in SQL

1. CASE WHEN - Conditional Logic

2. COALESCE - Handling NULLs

SQL Functions Explained

i. Aggregate Functions

ii. String Functions

iii. Numeric Functions

iv. Date/Time Functions

2. The `init` Method (Constructor)