DEV Community: Alex Murithi

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)

SQL Joins Made Simple

Alex Murithi — Tue, 15 Sep 2026 04:43:51 +0000

Relational databases often store related information across multiple tables. To answer real-world questions, we need to connect these tables using SQL JOINs.
The main types of joins - INNER, LEFT, RIGHT, and SELF JOIN

A JOIN allows us to combine rows from two or more tables based on a related column (often a primary key and foreign key).

JOIN Type	What It Returns	When to Use
INNER JOIN	Only rows that match in both tables	When you want rows with data on both sides
LEFT JOIN	All rows from the left table + matches from the right (NULLs if no match)	When you want to keep all left rows
RIGHT JOIN	All rows from the right table + matches from the left (NULLs if no match)	When you want to keep all right rows
SELF JOIN	A table joined to itself	When comparing rows within the same table

SQL Joins Explained Through a Duka Shop Database

We’ll use three tables:
Products (duka_products) → what you sell, their price, category, and supplier
Customers (duka_customers) → who shops at your store
Orders (duka_orders) → what each customer buys, when, and in what quantity

To answer business questions, you need to connect these tables. That’s where SQL JOINs come in

duka_products

product_id	product_name	product_category	price	stock_level	supplier
1	Mandazi Mix	Snacks & Bakery	120.00	40	Nairobi Foods Ltd
2	Githeri Pack	Grains & Cereals	210.00	55	Rift Valley Millers
3	Fresh Yogurt	Dairy	95.00	25	Brookside Dairy
4	Herbal Tea	Beverages	280.00	30	Kenya Beverages Ltd
5	Bar Soap	Household	60.00	70	Metro Wholesalers

duka_customers

customer_id	name	phone	location
1	Alice Kariuki	0712345678	Westlands
2	Brian Oduor	0723456789	Kasarani
3	Carol Wanjiru	0734567890	Rongai
4	Daniel Mwangi	0745678901	Westlands
5	Esther Njeri	0756789012	Thika

duka_orders

order_id	customer_id	product_id	quantity	order_date
1	1	2	3	2026-06-01
2	2	4	1	2026-06-02
3	3	1	2	2026-06-03
4	1	3	1	2026-06-04
5	4	5	4	2026-06-05

1. INNER JOIN - Matching Rows Only

Concept: Returns only rows where there is a match in both tables.
Use Case: When you only want rows with data on both sides.

Query: Which customer placed which order?

SELECT c.name, o.product_id, o.quantity
FROM duka.duka_customers c
INNER JOIN duka.duka_orders o
ON c.customer_id = o.customer_id;

Output:

name	product_id	quantity
Alice Kariuki	2	3
Alice Kariuki	3	1
Brian Oduor	4	1
Carol Wanjiru	1	2
Daniel Mwangi	5	4

Customers without orders are excluded.

2. Multi-Table INNER JOIN - Richer Insights

Concept: You can join more than two tables to get detailed reports.
Use Case: When you want to combine customers, orders, and products.

Query: Which customers ordered which products, and in what quantity?

SELECT c.name, p.product_name, o.quantity
FROM duka.duka_orders o
INNER JOIN duka.duka_customers c ON c.customer_id = o.customer_id
INNER JOIN duka.duka_products p ON p.product_id = o.product_id;

Output:

name	product_name	quantity
Alice Kariuki	Githeri Pack	3
Alice Kariuki	Fresh Yogurt	1
Brian Oduor	Herbal Tea	1
Carol Wanjiru	Mandazi Mix	2
Daniel Mwangi	Bar Soap	4

This helps you understand customer preferences.

3. LEFT JOIN - Keep All Left Rows

Concept: Returns all rows from the left table, plus matches from the right.
Use Case: When you want to keep all left rows, even if no match exists.

Query: Which products have never been ordered?

SELECT p.product_name
FROM duka.duka_products p
LEFT JOIN duka.duka_orders o
ON p.product_id = o.product_id
WHERE o.order_id IS NULL;

Output:

product_name
Herbal Tea
Mandazi Mix

Useful for inventory management - these products are unsold.

4. RIGHT JOIN - Keep All Right Rows

Concept: Returns all rows from the right table, plus matches from the left.
Use Case: When you want to keep all right rows, even if no match exists.

Query: Show all customers, even those who never ordered.

SELECT c.name, o.quantity
FROM duka.duka_orders o
RIGHT JOIN duka.duka_customers c
ON o.customer_id = c.customer_id;

Output:

name	quantity
Alice Kariuki	3
Brian Oduor	1
Carol Wanjiru	2
Daniel Mwangi	4
Esther Njeri	NULL

Customers like Esther Njeri appear even if they haven’t ordered.

5. SELF JOIN - Compare Within the Same Table

Concept: A table joined to itself.
Use Case: Comparing rows within the same table.

Query: Which customers live in the same location?

SELECT a.name AS customer1, b.name AS customer2, a.location
FROM duka.duka_customers a
INNER JOIN duka.duka_customers b
ON a.location = b.location
AND a.customer_id <> b.customer_id;

Output

customer1	customer2	location
Alice Kariuki	Daniel Mwangi	Westlands

Helps you plan local promotions.

General Notes on Joins
INNER JOIN → Only matched rows

LEFT JOIN → All left rows + matches (NULLs for missing right rows)

RIGHT JOIN → All right rows + matches (NULLs for missing left rows)

SELF JOIN → Compare within the same table

Database from Scratch Using PostgreSQL

Alex Murithi — Sun, 19 Jul 2026 17:23:28 +0000

Building a School Database from Scratch Using PostgreSQL

Introduction

Databases play an important role in storing, organizing, and managing information efficiently. Educational institutions such as schools handle large amounts of data, including student details, subjects, and academic performance records. A properly designed database helps ensure that this information can be stored securely and retrieved easily.

This article demonstrates the process of designing and managing a relational database for Greenwood Academy, a secondary school in Nairobi, using PostgreSQL. as an EXAMPLE

The project covers the complete database workflow, including:

Creating a database schema
Designing relational tables
Defining constraints and relationships
Inserting and modifying records
Querying data using SQL
Applying filtering, aggregation, and conditional logic

The key SQL concepts covered include:

Data Definition Language (DDL)
Data Manipulation Language (DML)
SQL filtering techniques
SQL operators
Aggregate functions
CASE WHEN statements

Part1: Creating schemas and tables (DDL)

A database schema provides a logical structure for organizing database objects such as tables, views, and functions.

For the Greenwood Academy database, a dedicated schema is created to separate school-related data from other database objects.

CREATE SCHEMA greenwood_academy;

SET search_path TO greenwood_academy;

The SET search_path command ensures that all database objects created afterward are stored within the greenwood_academy schema.

Designing Database Tables

A relational database stores information in structured tables where data is organized into rows and columns.

The Greenwood Academy database consists of three main tables:

students
subjects
exam_results

Creating the Students Table

The students table stores basic information about learners, including names, gender, class, and location.

CREATE TABLE students (
    student_id INT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    gender VARCHAR(1),
    date_of_birth DATE,
    class VARCHAR(10),
    city VARCHAR(50)
);

Part2: Modifying table structures using ALTER TABLE

Database structures may change as new requirements arise. PostgreSQL provides the ALTER TABLE command to modify existing tables without recreating them.

Adding a Column

A phone number column can be added to the students table using:

ALTER TABLE students
ADD COLUMN phone_number VARCHAR(20);

Removing a Column

Unnecessary columns can be permanently removed.

ALTER TABLE students
DROP COLUMN phone_number;

Part3: Inserting, updating and deleting records (DML)

After creating the database structure, the next step is populating the tables with actual records.

After creating the database structure, records can be added using Data Manipulation Language (DML).

The main DML commands include:

INSERT: Adds new records
UPDATE: Modifies existing records
DELETE: Removes records

Example:
The INSERT statement is used to add new records into database tables.

Adding Student Records

The students table stores learner information such as names, gender, date of birth, class, and city.

INSERT INTO students VALUES
(1,'Amina','Wanjiku','F','2008-03-12','Form 3','Nairobi'),
(2,'Brian','Ochieng','M','2007-07-25','Form 4','Mombasa'),
(3,'Cynthia','Mutua','F','2008-11-05','Form 3','Kisumu'
);

Results

Updating existing information:

UPDATE students
SET city='Nairobi'
WHERE student_id=5;

Part4: Querying data using WHERE conditions

The SELECT statement is used to retrieve information stored in database tables.

Example: Finding all Form 4 students:

SELECT *
FROM students
WHERE class='Form 4';

The WHERE clause filters records based on specific conditions.
Results

Part5: Using BETWEEN, IN, NOT IN and LIKE operators

SQL provides several operators for searching and filtering information.

BETWEEN Operator

The BETWEEN operator is used when searching for values within a specific range.

SELECT *
FROM exam_results
WHERE marks BETWEEN 50 AND 80;

Results

LIKE Operator

The LIKE operator is used for pattern-based searches.

SELECT *
FROM students
WHERE first_name LIKE 'A%';

The % wildcard represents any number of characters.
Results

Part6: Aggregating data using COUNT

Aggregate functions allow calculations to be performed on groups of records.

The COUNT() function determines the number of records matching a condition.

Example:

SELECT COUNT(*) AS form3_students
FROM students
WHERE class='Form 3';

Results

Part7: Classifying data using CASE WHEN

The CASE WHEN statement applies conditional logic within SQL queries.

It is useful when creating categories from existing data.

Example:

SELECT
marks,
CASE
    WHEN marks >=80 THEN 'Distinction'
    WHEN marks >=60 THEN 'Merit'
    WHEN marks >=40 THEN 'Pass'
    ELSE 'Fail'
END AS performance
FROM exam_results;

This query creates a new performance category based on examination marks.
Results

Conclusion

These foundational SQL skills are essential for database administration, backend development, and data analytics. Mastering concepts such as DDL, DML, filtering, aggregation, and CASE WHEN provides a strong foundation for working with larger and more complex database systems in real-world applications.

Connecting Power BI to SQL Databases: Local and Cloud-Based

Alex Murithi — Wed, 08 Jul 2026 13:22:40 +0000

Introduction

Modern data analytics workflows require efficient connections between data storage platforms and visualization tools. Business Intelligence tools such as Microsoft Power BI allow analysts to connect directly to SQL databases, transform data, and create interactive dashboards for decision-making.

SQL databases can be hosted in different environments, including:
Local databases hosted on a personal computer or organizational server
Cloud databases hosted by managed database provider.

Part 1: Connecting Power BI to a Local PostgreSQL Database

A local PostgreSQL database provides a database environment that runs directly on a computer. It is commonly used during development, testing, and small-scale analytics projects.

Step 1: Installing and Preparing PostgreSQL Locally

PostgreSQL is installed locally to provide a database environment for storing the dataset.
After installation, the PostgreSQL server runs on the local machine and is ready to accept database connections.

The local database connection details includes:
Host:, localhost, Port: 5432
Username: postgres
Password: ********

Step 2: Connecting Local PostgreSQL to DBeaver

DBeaver is used as the database management tool to connect to the PostgreSQL server.

Creating the connection:
Open DBeaver
Select:
Database

New Database Connection
PostgreSQL Enter the PostgreSQL connection details.

The connection are tested to confirm successful communication between DBeaver and the local PostgreSQL server.

Step 3: Creating a Database for the Project
After establishing the PostgreSQL connection, a database is created to store the dataset.
The created database is then used as the storage location for importing the dataset.

Step 4: Loading the Dataset into Local PostgreSQL

The dataset was loaded into PostgreSQL using the DBeaver import wizard.
The import process involves:

Selecting the project database.
Choosing the dataset file.
Configuring import settings.
Mapping columns.
Executing the import process.

DBeaver automatically creates the required table structure and loads the dataset records into PostgreSQL.

Step 5: Validating the Imported Data

After the import process is completed, the dataset is opened in DBeaver to verify that the records have been successfully loaded.
The validation included checking:

Table structure
Column names
Number of records
Sample data entries

Step 6: Connecting Power BI to Local PostgreSQL

After loading the dataset into PostgreSQL, Power BI is then connected to the local database.

In Power BI Desktop:
Home

Get Data
PostgreSQL Database

The PostgreSQL connection details were entered:

Server:localhost:5432, Database: database_name

Authentication is completed using the PostgreSQL username and password.

Step 7: Loading Data into Power BI

After successful authentication, Power BI displays the available PostgreSQL tables.
The data was then prepared using Power Query before creating dashboard visuals.

Part 2: Connecting Power BI to Cloud-Based PostgreSQL Using Aiven

Cloud databases provide scalable and secure database solutions without requiring users to manage physical database infrastructure.
Aiven PostgreSQL is used as the cloud database platform.

Step 1: Creating an Aiven PostgreSQL Database
An Aiven PostgreSQL service was created to host the dataset.
Aiven provided the required connection information:
Host address
Port number
Database name
Username
Password
SSL certificates

Step 2: Connecting Aiven PostgreSQL to DBeaver

DBeaver is configured to connect to the Aiven PostgreSQL database.
The connection details provided by Aiven are entered:
Host:Aiven server address
Port:5432
Database:Database name
Username:Username

Step 3: Adding SSL Certificate Configuration

Aiven requires SSL encryption to establish a secure connection between the database client and the database server.

The SSL certificates provided by Aiven included:

CA Certificate
Client Certificate
Client Key The certificates are added in DBeaver under: Connection Settings
SSL The SSL mode is configured according to Aiven requirements.

Step 4: Importing the Dataset into Aiven PostgreSQL

After establishing the secure connection, the dataset is imported into Aiven PostgreSQL using DBeaver.

The same import process is followed:

Select database
Import dataset
Configure column mapping
Execute import

The dataset is successfully stored in the cloud PostgreSQL database.

Step 5: Connecting Power BI to Aiven PostgreSQL

After loading the dataset into Aiven, Power BI is connected to the cloud database.
In Power BI:
Home

Get Data
PostgreSQL Database The Aiven server details are entered: Server: Aiven Host Address Database: Database Name

Step 6: Loading Aiven Data into Power BI

After authentication, Power BI displays the available tables from Aiven PostgreSQL.
The dataset is then selected and loaded into Power BI for analysis.

How Excel is Used in Real-World Data Analysis

Alex Murithi — Sun, 07 Jun 2026 13:14:45 +0000

Introduction

Microsoft Excel is one of the most widely used tools in data analysis across industries. Despite being simple and easy to use, it plays a powerful role in transforming raw data into meaningful insights that support decision-making in business, finance, marketing, and even geospatial fields.

Application in real world

Excel is also heavily used in financial reporting. Companies rely on it to manage budgets, track expenses, calculate profits, and prepare financial forecasts. Financial analysts use Excel to generate reports that help management understand the financial health of an organization and plan for future growth.
In addition, Excel is widely used in marketing and customer analytics. Marketing teams analyze campaign performance, customer engagement, and return on investment using Excel dashboards and reports. This helps businesses understand which marketing strategies are effective and how customer behavior changes over time.
Beyond traditional business use cases, Excel also plays an important role in the geospatial industry. GIS and geoinformatics professionals use Excel to organize and clean spatial and survey data before importing it into GIS software such as QGIS or ArcGIS.

Some of the functions and Features that Excel offers.

Excel provides several powerful features that support data analysis. Functions such as SUM(), AVERAGE(), and COUNT() allow analysts to perform quick calculations on large datasets.
Data Validation ensures that only correct and consistent data is entered, improving data quality.
Conditional Formatting helps highlight trends and patterns visually, making it easier to interpret data. Filters allow users to focus on specific subsets of data, especially when working with large datasets.

Personal reflection on how learning Excel has changed the way I see data.

Excel remains a foundational tool in data analysis due to its simplicity, flexibility, and wide range of applications. It bridges the gap between raw data and meaningful insights across multiple industries. Whether in business, finance, marketing, or geospatial analysis, Excel continues to be a powerful tool for turning data into decisions.