DEV Community: Ian Githu

Python Loops: The Art of Repeating Tasks With Less Code

Ian Githu — Tue, 22 Sep 2026 18:55:59 +0000

Introduction

When I started learning Python, one thing that made programming feel more powerful was discovering I didn't have to write the same piece of code over and over

At first, repetition in programming seemed straightforward. If I wanted Python to display five numbers, I could write five print() statements. But what happens when I want to display 100 numbers? Or process 1,000 records from a dataset?

Writing the same code repeatedly would quickly become impractical.

This is where loops come in.

Loops let us tell Python to repeat a block of code instead of writing the same instructions multiple times. They're one of those concepts that seem simple at first but become incredibly useful as your programs grow more complex.

Whether you're processing a list of customers, checking a collection of scores, validating user input, searching through data, or automating repetitive tasks, loops are likely to become a regular part of your Python code.

Python gives us two main types of loops: the for loop and the while loop. We also have break and continue, which allow us to control how loops behave, and enumerate(), which makes it easier to track an item's position while looping through a collection.

Understanding the Idea Behind Loops

Before getting into the different types of loops, it's worth understanding what a loop is actually trying to solve.

Imagine that we want Python to print the numbers from 1 to 5.

We could write:

print(1)
print(2)
print(3)
print(4)
print(5)

There is nothing technically wrong with this code, but we're repeating ourselves.

Python gives us a better way:

for number in range(1, 6):
    print(number)

The output is:

Instead of writing five separate instructions, we've described a pattern and allowed Python to handle the repetition.

This is essentially what a loop does: it lets us write a block of code once and have Python execute it multiple times, following a set rule.
That rule could be based on a collection of values, a range of numbers, or a condition that remains true.

`for` Loops: Going Through a Collection

The first type of loop we'll look at is the for loop.

A for loop is commonly used when we want to go through items in a list, tuple, string, or range.

For example, suppose we have a list of fruits:

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

We can use a for loop to go through each fruit:

for fruit in fruits:
    print(fruit)

The output is:

Mango
Banana
Orange

What's happening here is actually quite simple.

Python takes the first item from the list and stores it temporarily in the variable fruit. It then executes the indented code:

print(fruit)

After that, Python moves to the next item and repeats the process.

The important thing to understand is that fruit represents the current item during each iteration of the loop.

We could call that variable almost anything:

for item in fruits:
    print(item)

The result would be the same. The name we choose needs to make sense for what the variable represents.

Using `for` Loops With `range()`

Another common way of using for loops is with Python's range() function.

For example:

for number in range(1, 6):
    print(number)

This produces:

One thing that initially confused me about range() was why 6 doesn't appear in the output.

The reason is that the ending value in range() is not included.

So:

range(1, 6)

means:

Start at 1 and stop before reaching 6.

We can also control the amount by which the number changes each time:

for number in range(2, 11, 2):
    print(number)

The output is:

Here, Python starts at 2, stops before 11, and increases the value by 2 each time.

This is useful for generating even numbers, creating countdowns, or repeating an operation a specific number of times.

Looping Through Strings

One interesting thing about Python is that we can loop through more than just numbers and lists.

We can also loop through a string:

word = "Python"

for letter in word:
    print(letter)

The output is:

P
y
t
h
o
n

Python processes the string one character at a time.

This can be useful when we need to inspect individual characters.

For example, we could count how many times the letter "a" appears in a word:

word = "banana"
count = 0

for letter in word:
    if letter == "a":
        count += 1

print(count)

The output is:

The loop checks each character in "banana". Every time it finds "a", the count increases by one.

This is a simple example, but it demonstrates an important idea: loops let us perform the same operation on every item in a sequence.

`while` Loops: Repeating While a Condition Is True

The second major type of loop in Python is the while loop.

A while loop works a little differently from a for loop. Instead of going through a collection, it keeps running as long as a certain condition is True.

For example:

number = 1

while number <= 5:
    print(number)
    number += 1

The output is:

The process starts with:

number = 1

Python then checks:

number <= 5

Because 1 is less than or equal to 5, the condition is true, so the loop runs.

It prints the number and then increases it:

number += 1

Python checks the condition again and continues the iteration.

Eventually, number becomes 6. At that point, 6 <= 5 is false, so Python stops the loop.

The important thing to remember about a while loop is that something inside the loop usually needs to change so the condition can eventually become false.

For example, this code would cause a problem:

number = 1

while number <= 5:
    print(number)

The loop never changes number, so it remains 1. Since 1 <= 5 is always true, Python keeps printing 1.

This is known as an infinite loop.

Adding:

number += 1

allows the loop to move toward its stopping condition.

Choosing Between `for` and `while`

A simple way to think about the difference is that a for loop is usually useful when you're going through a collection or a known sequence of values.

For example:

students = ["Ian", "Mary", "John"]

for student in students:
    print(student)

We know exactly what we're processing: the items in the students list.

A while loop is often more appropriate when we don't know exactly how many times something needs to happen.

For example, suppose we want to keep asking a user for a password until they enter the correct one:

password = ""

while password != "python123":
    password = input("Enter password: ")

print("Access granted.")

We don't know whether the user will enter the correct password on the first attempt or the tenth. The loop continues while the condition is true.

This distinction becomes easier to understand with practice, and eventually choosing between for and while starts to feel natural.

Controlling Loops With `break`

Sometimes we want to stop a loop before it has finished processing everything.

Python gives us the break statement for this purpose.

Consider:

for number in range(1, 11):
    if number == 6:
        break

    print(number)

The output is:

When Python reaches 6, it encounters break and immediately leaves the loop.

It's almost like telling Python:

"We've found what we need. Stop here."

This can be particularly useful when searching through data:

students = ["Brian", "Mary", "Ian", "John", "Sarah"]

for student in students:
    if student == "Ian":
        print("Student found!")
        break

    print(f"Checking {student}...")

Once Python finds "Ian", there's no reason to keep searching through the remaining names.

Skipping an Iteration With `continue`

While break stops a loop completely, the continue statement does something different.

It tells Python to skip the current iteration and move to the next one.

For example:

for number in range(1, 6):
    if number == 3:
        continue

    print(number)

The output is:

When Python reaches 3, it encounters continue. Instead of executing the rest of the code for that iteration, Python moves directly to the next number.

A simple way to remember the difference is:

break     → stop the loop completely
continue  → skip this iteration

This is useful when we want to ignore certain values but still process everything else.

For example:

scores = [78, -5, 85, -2, 91]

for score in scores:
    if score < 0:
        continue

    print(score)

The output is:

78
85
91

The negative scores are skipped, but the loop continues processing the remaining scores.

Using `enumerate()` With Loops

Some situations come up frequently when working with lists.

Suppose we have:

students = ["Ian", "Mary", "John"]

and we want to print each student's name while also tracking their position in the list.

We could do this:

for i in range(len(students)):
    print(i, students[i])

This works, but Python provides a cleaner and more readable solution through enumerate():

for index, student in enumerate(students):
    print(index, student)

The output is:

0 Ian
1 Mary
2 John

The enumerate() function gives us both the index and the item during each iteration.

We can also tell enumerate() to start counting from a different number:

students = ["Ian", "Mary", "John"]

for number, student in enumerate(students, start=1):
    print(number, student)

The output becomes:

1 Ian
2 Mary
3 John

This is especially useful when creating numbered lists:

items = ["Rice", "Sugar", "Milk"]

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

The output is:

1. Rice
2. Sugar
3. Milk

Instead of manually creating and updating a counter, enumerate() handles the counting for us.

Putting Everything Together

The real value of these concepts becomes more obvious when we start combining them.

Suppose we have a list of student scores and want to display scores that are at least 40. We also want to show the student's position in the list.

We could write:

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

for number, score in enumerate(scores, start=1):

    if score < 40:
        continue

    print(f"Student {number}: {score}")

The output is:

Student 1: 78
Student 2: 45
Student 3: 91
Student 5: 85

The for loop goes through the scores; enumerate() gives us the student's number and score. The if statement checks the score, and continue allows us to skip scores below 40.

This is where Python starts becoming more interesting. The concepts we learn individually begin to work together to solve actual problems.

Why Loops Matter in Python

At first, loops might seem like nothing more than a way to print numbers repeatedly.

But their usefulness goes further than that.

Imagine working with a dataset containing thousands of customers. You might need to examine each record, calculate something, search for specific values, or identify records that meet certain conditions.

You wouldn't want to write separate instructions for every customer.

Instead, you describe the operation once and let Python repeat it.

The same principle applies to processing transactions, checking scores, reading files, validating information, generating reports, and automating repetitive tasks.

Final Thoughts

When I think about learning loops, I don't think the most important lesson is simply memorizing the syntax of for and while.

The bigger lesson is learning to identify patterns of repetition.

A for loop allows us to move through a collection or sequence one item at a time. A while loop allows us to keep acting while a condition remains true. break gives us a way to stop a loop completely when we've reached the point we need, while continue lets us skip a particular iteration without stopping the loop.

Then there's enumerate(), which makes working with both the position and value of items much easier.

At first, these concepts can feel like separate pieces of Python syntax that you need to memorize. But with enough practice, something changes.

Loops are just one piece of the Python puzzle, but they're an important one. Once you are comfortable with them, you're no longer simply telling Python what to do once.

You're learning how to tell Python how to do something repeatedly, intelligently, and efficiently.

Python Basics: Learning to speak the language of code

Ian Githu — Tue, 22 Sep 2026 15:04:38 +0000

Introduction

When I started learning Python, one thing that stood out to me was how readable the language felt. Compared with what I imagined programming would look like, Python seemed almost conversational. You could write a few lines of code, run them, and immediately see something happen.

Of course, that simplicity can be a little misleading. Python may have a beginner-friendly syntax, but there's still quite a bit you need to understand before your code starts making sense.
One of the best places to start is with the fundamentals: variables, data types, input and output, and basic Python syntax.

These concepts might not sound particularly exciting, but they are the foundation for almost everything you will eventually do in Python. Whether you're building an application, automating a task, analysing data, or working with databases, you'll constantly be using the ideas introduced here.

So let's start from the beginning.

Getting to Know Python

Python is a high-level, general-purpose programming language designed for readability and simplicity. It is widely used in web development, automation, data analysis, data science, artificial intelligence, and machine learning.

One reason Python is so popular is its relatively straightforward syntax.
For example, if we want Python to display a message, we can write:

print("Hello, Python!")

There is no complicated setup required. We tell Python what we want it to do, and Python does it.
That simplicity is one reason Python is such a good language for beginners. But before we can start building anything meaningful, we need to understand how Python stores and works with information.

Variables: Giving Information a Name

Almost every program needs to work with information.

A program might need to remember someone's name, store their age, track a product's price, or calculate the total amount spent by a customer.

This is where variables come in.

A variable is a name or label that references an object stored in your computer's memory.

For example:

name = "Ian"
age = 25

Here, name refers to "Ian" and age refers to 25.

We can then use those variables later in our program:

name = "Ian"
age = 25

print(name)
print(age)

The output is:

Ian
25

The useful thing about variables is that we don't have to keep writing out the actual values every time. Instead, we give the information a meaningful name and work with that name.

Variables can also change.

age = 25
age = 26

print(age)

The output will be:

This ability to store and update information is fundamental to programming.

Choosing Good Variable Names

Python provides rules for naming variables, but good practices matter too.

For example:

student_name = "Brian"
student_age = 22

These names tell us exactly what the variables represent.

Compare that with:

x = "Brian"
y = 22

The second example is valid Python, but x and y don't tell us much.

As programs become larger, meaningful variable names become increasingly important. Good names make your code easier to read, understand, and maintain.

Python variable names can contain letters, numbers, and underscores, but they cannot begin with a number or contain spaces.

For example:

student_name = "Brian"
student2_name = "Mary"

are valid, while:

2student = "Brian"
student name = "Mary"

are not.

Python is also case-sensitive, meaning name, Name, and NAME would be treated as different names.

Data Types: Not All Data Is the Same

Now that we know how to store values, there is another important question:

What kind of values are we storing?

Python uses data types to describe the kind of information a value represents.

Some of the most common types you'll encounter are strings, integers, floats, and Booleans.

A string, represented by str, is text:

name = "Ian"
city = "Nairobi"

An integer, represented by int, is a whole number:

age = 25
students = 40

A float is a number containing a decimal value:

price = 1500.50
average_score = 78.2

And a Boolean, represented by bool, can have one of two values:

is_logged_in = True
has_paid = False

Booleans become particularly useful when we start working with conditions because they allow programs to make decisions based on whether something is True or False.

For example:

age = 20

is_adult = age >= 18

print(is_adult)

Python evaluates age >= 18 and produces:

True

Checking a Data Type

Python provides a built-in function called type() that allows us to check what type of value we're working with.

name = "Ian"
age = 25
height = 1.75
is_student = True

print(type(name))
print(type(age))
print(type(height))
print(type(is_student))

The output will be:

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

Understanding data types is important because different types behave differently.

For example, these two variables may look similar:

age = 25

and:

age = "25"

But they aren't the same.

The first contains an integer, while the second contains a string containing the characters 2 and 5.

That distinction becomes important when performing calculations.

Working with Numbers

Python can perform the basic mathematical operations you would expect.

For example:

a = 10
b = 3

print(a + b)
print(a - b)
print(a * b)
print(a / b)

The output is:

13
7
30
3.3333333333333335

Python also provides operators for other types of calculations.

The % operator, for example, gives us the remainder after division:

print(10 % 3)

This produces:

The ** operator is used for exponentiation:

print(5 ** 2)

which produces:

These operators may seem simple, but they become extremely useful when we start writing programs that calculate totals, averages, percentages, financial values, and other metrics.

Giving Information to the User

So far, we've given Python all the information it needs directly in the code.

But programs become much more useful when users can interact with them.

Python gives us two particularly important tools for this: input() and print().

We've already seen print(), which is used to display information:

print("Welcome to Python!")

The input() function works in the opposite direction. It allows the program to ask the user for information.

For example:

name = input("Enter your name: ")

print(name)

When the program runs, the user might enter:

Enter your name: Davis

Python then stores that input in the name variable.

We can combine this with an f-string to create a more natural message:

name = input("Enter your name: ")

print(f"Hello, {name}!")

If the user enters Davis, Python produces:

Hello, Davis!

This is a small example, but it introduces an important idea: programs don't always have to work with information known in advance, before the code was written.

They can receive information while they are running.

The Important Relationship Between `input()` and Data Types

There is one detail about input() that every Python beginner should understand:

input() always returns a string.

Consider this:

age = input("Enter your age: ")

print(type(age))

If the user enters:

Python still treats the value as:

"25"

which is a string.

This can cause problems if we perform mathematical operations on it.

For example:

age = input("Enter your age: ")

print(age + 5)

Python will produce an error because it cannot directly add an integer to a string.

To solve this, we can convert the input to an integer using int():

age = int(input("Enter your age: "))

print(age + 5)

Now, if the user enters 25, Python stores it as the integer 25, and the calculation works.

We can also convert input to a floating-point number:

price = float(input("Enter the price: "))

If the user enters 1500.50, Python stores the value as a float.

This process of changing a value from one data type to another is called type conversion.

Putting Variables, Data Types, Input, and Output Together

Once we understand these individual concepts, we can start combining them to create small but useful programs.

Imagine we want to create a simple program that collects a student's name and three scores and then calculates their average.

We could write:

name = input("Enter student name: ")

score1 = int(input("Enter first score: "))
score2 = int(input("Enter second score: "))
score3 = int(input("Enter third score: "))

average = (score1 + score2 + score3) / 3

print(f"Student: {name}")
print(f"Average score: {average}")

If the user enters:

Enter student name: Ian
Enter first score: 80
Enter second score: 75
Enter third score: 90

The program calculates the average and produces:

Student: Ian
Average score: 81.66666666666667

We're using strings for the student's name, integers for the scores, input() to collect information, int() to convert the scores, arithmetic operators to calculate the average, and print() and f-strings to display the result.

This is where Python starts becoming interesting. Individual concepts that seemed small on their own begin working together to solve an actual problem.

A Quick Look at Python Syntax

Learning Python isn't only about knowing what different functions do. You also need to understand how Python expects code to be structured.

One of the most important characteristics of Python is its use of indentation.

For example:

age = 20

if age >= 18:
    print("You are an adult.")

Notice the spaces before print().

The indentation tells Python that the print() statement belongs to the if statement.

This is different from some programming languages that use curly braces to define blocks of code. In Python, indentation is part of the syntax.

Python is also strict about capitalization.

For example:

print("Hello")

works, but:

Print("Hello")

does not.

Similarly:

name = "Ian"
Name = "Brian"

creates two different variables.

These details may seem small, but they become second nature with practice.

Comments: Leaving Notes in Your Code

Python also allows us to add comments to our code.

A comment begins with #:

# Store the student's name
name = "Ian"

# Display the name
print(name)

Python ignores comments when running the program.

Comments can be useful when explaining why a particular piece of code exists or leaving notes for other developers. However, they shouldn't explain every obvious line of code. Ideally, your code should be readable enough to explain itself wherever possible.

From Small Building Blocks to Bigger Programs

At this point, variables, data types, input, output, operators, and syntax might seem like separate topics.

In reality, they are closely connected.

A typical Python program might receive information from a user using input(), store the information in variables, convert it into the appropriate data types, perform calculations using operators, and finally display the result using print().

For example:

name = input("Enter your name: ")
salary = float(input("Enter your monthly salary: "))
expenses = float(input("Enter your monthly expenses: "))

savings = salary - expenses

print(f"{name}, your monthly savings are Ksh {savings}.")

A user might enter:

Enter your name: Ian
Enter your monthly salary: 60000
Enter your monthly expenses: 35000

and receive:

Ian, your monthly savings are Ksh 25000.0.

It's a simple program, but it demonstrates the basic flow of many real programs:

Input → Process → Output

The user provides information, the program processes it, and the result is displayed.

Final Thoughts

Learning Python starts with understanding a handful of fundamental ideas.

Variables give us a way to store and work with information. Data types tell Python what kind of information we're dealing with. input() allows our programs to interact with users, while print() allows us to display information and results.

Then there is syntax: indentation, capitalization, operators, comments, and the structure of Python statements. These may seem like small details at first, but becoming comfortable with them is what allows you to write Python code without constantly fighting the language.

The important thing is not to rush through these concepts simply because they are considered "basics." These are the building blocks you'll keep coming back to as you learn more advanced topics.

Eventually, you'll move from variables and input() to conditionals and loops. Then you'll start writing functions, working with lists and dictionaries, handling files, building classes, querying databases, and analysing data.

But every one of those steps builds on the same foundation.

When you're learning Python, you don't need to understand everything at once. Write small programs. Make mistakes. Read the error messages. Change the code and run it again.

Eventually, those strange-looking lines of Python stop feeling like instructions you're memorizing and start feeling like ideas you're expressing.

And that's when learning to code starts becoming genuinely fun.

When One Query Isn't Enough: A Love Letter to CTEs and Subqueries in PostgreSQL

Ian Githu — Wed, 09 Sep 2026 19:27:24 +0000

Introduction

If you've spent enough time writing SQL, you've probably reached that point where a query starts simple and then somehow turns into a monster.

You begin with:

SELECT *
FROM trips;

Then someone asks, "Can we find the drivers whose average fare is above the overall average?"

No problem. You add an AVG().

Then they ask, "Only include drivers who have completed at least five trips."

You add a COUNT().

Then comes, "And their average rating should be below 3."

Before you know it, you're staring at a query with nested queries inside nested queries, wondering whether you wrote SQL or accidentally created a puzzle.

This is where subqueries and Common Table Expressions (CTEs) become extremely useful.

Both techniques help us deal with complex SQL, but they do it in slightly different ways. Once you understand the difference, writing analytical queries becomes much more comfortable.

In this article, we'll explore what CTEs and subqueries are, how they differ, when to use each one, and walk through practical PostgreSQL examples.

So, What Exactly Is a Subquery?

A subquery is simply a query inside another query.

The idea is straightforward: PostgreSQL first uses the inner query to produce a result, and the outer query uses that result to complete the bigger task.

For example, suppose we want to find drivers whose average fare is higher than the average fare for all trips.

We could write:

SELECT driver_id,
       AVG(fare) AS avg_fare
FROM trips
GROUP BY driver_id
HAVING AVG(fare) > (
    SELECT AVG(fare)
    FROM trips
);

The part inside the parentheses is the subquery:

SELECT AVG(fare)
FROM trips;

It calculates the overall average fare.

The main query then calculates the average fare for each driver and asks:

"Is this driver's average higher than the overall average?"

If yes, PostgreSQL returns the driver.

That's the basic idea behind a subquery: solve a smaller problem first, then use its answer to solve the bigger problem.

Where Do Subqueries Come In Handy?

Subqueries can appear in several places.

One common example is with WHERE.

Imagine you want to find customers who have spent more than a particular benchmark. The subquery can calculate that benchmark, while the main query identifies the customers who exceed it.

For example, we can find riders who have spent more than the average rider spending:

SELECT rider_id,
       SUM(total_fare) AS total_spend
FROM trips
GROUP BY rider_id 
HAVING SUM(total_fare) > ( SELECT AVG(rider_total) 
                          FROM (
                                SELECT rider_id, 
                                       SUM(total_fare) AS rider_total
                                FROM trips
                                GROUP BY rider_id 
                              ) AS rider_spending
);

"In this example, the inner query first calculates each rider's total spending by grouping trips by rider_id and summing total_fare, giving us a total spending amount per rider. The next level of the subquery uses AVG(rider_total) to calculate the average spending across all riders. The outer query repeats the per-rider spending calculation and uses the HAVING clause to compare each rider's total against that average. As a result, the query returns only riders whose spending exceeds the average. In simple terms, the inner query establishes the benchmark, while the outer query uses it to identify riders who spent above average."

Another useful application is EXISTS.

EXISTS checks whether a related record exists.

For example:

SELECT r.rider_id,
       r.rider_name
FROM riders r
WHERE EXISTS (
    SELECT 1
    FROM trips t
    WHERE t.rider_id = r.rider_id
);

This asks PostgreSQL whether a trip exists for each rider.

If a matching trip exists, the query returns that rider.

EXISTS is particularly useful when you don't actually need data from the inner query. You only want to know whether a matching record exists.

Subquery in FROM

A subquery can also act like a temporary table:

SELECT *
FROM (
    SELECT driver_id,
           AVG(fare) AS avg_fare
    FROM trips
    GROUP BY driver_id
) AS driver_stats
WHERE avg_fare > 1000;

In this example, the subquery is placed inside the FROM clause, allowing the outer query to treat its result like a temporary table. The inner query groups trips by driver_id and calculates each driver's average fare using AVG(fare), then assigns this result the alias driver_stats. The outer query selects all columns from this temporary result and filters using WHERE avg_fare > 1000, returning only drivers whose average fare exceeds 1,000. This approach is useful when you want to perform a calculation first and then filter or further analyze the results."

Then What Is a CTE?

A CTE, or Common Table Expression, allows us to define a temporary named result set at the beginning of a query.

Instead of embedding the smaller query directly inside the main query, you assign it a name using the WITH keyword.

For example:

WITH driver_stats AS (
    SELECT driver_id,
           AVG(fare) AS avg_fare
    FROM trips
    GROUP BY driver_id
)

SELECT *
FROM driver_stats
WHERE avg_fare > 1000;

Here, driver_stats is the CTE.

Inside the CTE, we calculate the average fare for every driver.

Then, in the main query, we treat that result almost like a temporary table and filter it.

The biggest advantage is that the query becomes easier to read.

Instead of trying to understand everything at once, you can look at the query step by step.

CTEs Are Great for Multi-Step Problems

This is where CTEs really shine.

Let's say the operations manager asks:

"Show me drivers who have completed at least four trips but have an average rider rating below 3."

There are two things we need to calculate:

The number of trips completed by each driver.
The average rating for each driver.

We can separate those tasks:

WITH driver_trips AS (
    SELECT driver_id,
           COUNT(*) AS total_trips
    FROM trips
    GROUP BY driver_id
),

driver_ratings AS (
    SELECT driver_id,
           AVG(rider_rating) AS avg_rating
    FROM trips
    GROUP BY driver_id
)

SELECT dt.driver_id,
       dt.total_trips,
       dr.avg_rating
FROM driver_trips dt
JOIN driver_ratings dr
    ON dt.driver_id = dr.driver_id
WHERE dt.total_trips >= 4
  AND dr.avg_rating < 3.0;

Look at how the query flows.

First, we calculate the number of trips.

Then, we calculate the average rating.

Finally, we join the two results and apply our conditions.

That's much easier to follow than cramming everything into one enormous query.

CTE vs. Subquery: What's the Real Difference?

At a basic level, both can help you achieve similar results.

For example, this uses a subquery:

SELECT *
FROM (
    SELECT driver_id,
           AVG(fare) AS avg_fare
    FROM trips
    GROUP BY driver_id
) AS driver_stats
WHERE avg_fare > 1000;

We could rewrite it using a CTE:

WITH driver_stats AS (
    SELECT driver_id,
           AVG(fare) AS avg_fare
    FROM trips
    GROUP BY driver_id
)

SELECT *
FROM driver_stats
WHERE avg_fare > 1000;

Both approaches can produce the same result.

The main difference is how the query is organized.

The subquery is embedded directly inside the main query.

The CTE is defined separately at the beginning and given a meaningful name.

That difference matters more as your queries get bigger.

Readability. CTEs are named and front-loaded, so you follow the logic in order. Subqueries, especially nested ones, read inside-out, and that gets old fast once things get complicated.

Reusability. Reference a CTE twice in the same query and you're not repeating yourself. Try that with a subquery, and you'll usually end up copy-pasting the same block, or wrapping it in yet another layer.

Recursion. This is where CTEs pull ahead entirely. PostgreSQL supports WITH RECURSIVE, which is what you reach for with hierarchical data: org charts, category trees, bill-of-materials, anything with a parent-child structure that repeats itself. Subqueries just can't do this on their own.

WITH RECURSIVE org_chart AS (
    SELECT id, name, manager_id, 1 AS level
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    SELECT e.id, e.name, e.manager_id, oc.level + 1
    FROM employees e
    JOIN org_chart oc ON e.manager_id = oc.id
)
SELECT * FROM org_chart ORDER BY level;

The first part of the CTE, known as the anchor query, finds employees with no manager (manager_id IS NULL). These employees represent the top level of the organization and start at level 1. The second part, connected with UNION ALL, is the recursive query; it joins the employees table back to the CTE to find employees whose manager_id matches an employee already found. Each new level increases the count by 1, and PostgreSQL repeats this process until no more employees remain to find. The final SELECT then displays the complete organizational hierarchy, ordered by level. In simple terms, the recursive CTE starts at the top of the hierarchy and works its way downward, discovering each level of employees along the way.

A Simple Rule to Remember

Here's a practical rule I use when thinking about the two:

If the logic is small and only needed once, a subquery is often perfectly fine.

If the query has several steps, a CTE will usually make your life easier.

For example, this is a good candidate for a subquery:

SELECT driver_id,
       AVG(fare)
FROM trips
GROUP BY driver_id
HAVING AVG(fare) > (
    SELECT AVG(fare)
    FROM trips
);

There's only one small calculation inside the subquery, so there's no real need to introduce a CTE.

But if the problem involves several calculations, filtering stages, and joins, a CTE will generally be easier to understand.

One of the Best Things About CTEs: You Can Chain Them

CTEs don't have to stop after one.

You can create one CTE and then use it to build another.

For example:

WITH driver_trips AS (
    SELECT driver_id,
           COUNT(*) AS total_trips
    FROM trips
    GROUP BY driver_id
),

qualified_drivers AS (
    SELECT driver_id,
           total_trips
    FROM driver_trips
    WHERE total_trips >= 5
)

SELECT *
FROM qualified_drivers;

The first CTE calculates the number of trips.

The second CTE takes that result and keeps only drivers with at least five trips.

The final query simply displays the result.

This approach can be incredibly useful when you're working through a complicated business question one step at a time.

When Would You Use These in Real Projects?

CTEs and subqueries aren't just SQL exercises. They show up frequently in real data work.

You might use them to:

Calculate total revenue by customer.
Find drivers performing above or below average.
Identify customers who have never made a purchase.
Compare monthly revenue with overall revenue.
Find the top-performing products.
Calculate cancellation rates.
Identify duplicate or suspicious records.
Prepare data for a Power BI dashboard.
Break complicated data-cleaning operations into manageable steps.

For example, imagine you're analysing a transport company's bookings.

The business asks:

"Which routes generate the most revenue, and which routes have the highest cancellation rates?"

You might need to calculate revenue first, cancellation counts next, and then combine those results.

A CTE can make that workflow much easier to build and understand.

Don't Think of CTEs as Automatically "Better"

It's tempting to think that because CTEs look cleaner, they should always replace subqueries.

That's not necessarily true.

A simple subquery can be perfectly readable and may be the most natural solution.

Likewise, using five CTEs for a problem that can be solved with one straightforward query can make the SQL unnecessarily long.

The important thing is to choose the approach that makes the logic clear without adding unnecessary complexity.

Also, don't confuse readability with performance. Whether a CTE or subquery performs better can depend on the PostgreSQL version, query structure, indexes, materialization behaviour, and the execution plan. When performance matters, use EXPLAIN or EXPLAIN ANALYZE rather than assuming one technique will always be faster.

Final Thoughts

CTEs and subqueries are two tools that can make a big difference once you move beyond basic SQL.

A subquery is essentially a query inside another query. It's especially useful when you need a smaller calculation to provide a value or condition for the main query.

A CTE uses the WITH clause to give an intermediate query a name. This makes it particularly useful when you're dealing with several logical steps.

If you're learning SQL, don't worry about trying to master everything at once. Start with simple subqueries. Once you're comfortable with them, move into CTEs and practise breaking larger problems into smaller stages.

Eventually, you'll start looking at a complicated business question and naturally think:

"Okay, what's the first thing I need to calculate?"

That's really the skill you're developing.

SQL isn't just about knowing commands. It's about learning how to break a problem down logically and then telling the database exactly how to solve it.

And once you get comfortable with CTEs and subqueries, those intimidating SQL queries start looking a lot less intimidating.

From Empty Schema to Real Insights: A Two-Week Walkthrough of PostgreSQL

Ian Githu — Sun, 19 Jul 2026 09:06:37 +0000

Two weeks ago, if you had asked me to explain the difference between a WHERE clause and a HAVING clause, I probably wouldn't have had an answer. Fast forward to today, and I can build a relational database from scratch, fill it with real data, break it on purpose, fix it, and write queries that turn raw data into meaningful insights. I'll walk you through that journey using a school database for Greenwood Academy, complete with students, subjects, and exam results.

We'll build the database step by step, explore the key PostgreSQL concepts, write practical queries that solve real problems, and provide practical examples you can follow along.

What is a database?
A database is an organized collection of related data that is stored electronically and designed to make it easy to store, retrieve, update, and manage information.

What is a Database Management System (DBMS)?
It is a software that enables us to create, manage, and query databases.
Some common examples include: MySQL, PostgreSQL, SQLite

Types of Databases

1. Relational Databases
A relational database stores data in tables (rows and columns).
The tables are related to each other using primary and foreign keys.
2. Non-Relational Databases
They store data in flexible formats rather than fixed tables, which are best suited for handling large volumes of unstructured data.

Why do we use databases?

Efficient storage of large amounts of data.
Easy updates without affecting unrelated data.
Reduced duplication through a proper design.
Data security with controlled access.
Support for multiple users working simultaneously.

Creating a Relational Database

SQL(Structured Query Language) is the standard language used to interact with relational databases.

SQL Data Types and Constraints

Data types define the kind of data that can be stored in each column of a table. They help maintain accuracy and consistency.
Constraints are rules applied to columns or tables to control what data can be stored.

Categories of Data Types include:
1. Numeric data types

**INT** - stores standard whole numbers
**SERIAL** - stores auto-incrementing integers like IDs
**DECIMAL** - used for exact decimal values

2. Character/strings
- Char(n) - stores fixed-length text
- Varchar(n) - variable length text with a maximum limit, e.g., varchar(50)
- Text - variable-length text with no strict limit

3. Date and Time
- Date - stores date only
- Time - stores time only
- Timestamp - stores both date and time

Constrains
NOTNULL - ensures a column must have a value
NULL - allows a column to have no value
DEFAULT - provides a value automatically if none is given
PRIMARY KEY - uniquely identifies each row
UNIQUE - ensures all values in a column are different
FOREIGN KEY - it references a primary key from another table
CHECK - ensures a value meets a specific condition

The setup: Structure of the dataset we're working with

- students-student_id, first_name, last_name, gender, date_of_birth, class, city
- subjects-subject_id, subject_name, department, teacher_name, credit_hours
- exam_results-result_id, student_id, subject_id, marks, exam_date, grade

1. DDL - Designing the structure

DDL(Data Definition Language) refers to the set of commands that create and modify database objects such as schemas and tables. Common commands include CREATE SCHEMA, CREATE TABLE, and ALTER TABLE.
Tables are the primary structures used to store data in a relational database.
A schema is a logical container used to group related tables.

Schema Creation

CREATE SCHEMA greenwood_academy;
SET search_path TO greenwood_academy;

This creates a schema called greenwood_academy and sets it as the working environment.

Creating the students table

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)
);

This creates a table called students, with the student_id as the primary key. The NOT NULL constraint is used to ensure the database rejects incomplete data.

Creating the subjects table

CREATE TABLE subjects (
    subject_id      INT PRIMARY KEY,
    subject_name    VARCHAR(100) NOT NULL UNIQUE,
    department      VARCHAR(50),
    teacher_name    VARCHAR(100),
    credit_hours    INT
);

This creates a table called subjects, with the subject_id as the primary key.

Creating the exam_results table

CREATE TABLE exam_results (
    result_id       INT PRIMARY KEY,
    student_id      INT NOT NULL references students(student_id)
    subject_id      INT NOT NULL references subjects(subject_id)
    marks           INT NOT NULL,
    exam_date       DATE,
    grade           VARCHAR(2)
);

The exam_results table is created with the result_id as the primary key. The references clause is introduced to showcase the relationship between the exam_results table, the students, and the subjects table.
The student_id and subject_id columns are foreign keys in the exam_results table; they reference the primary keys in their respective tables.

ALTER TABLE - used to modify an existing table

ALTER TABLE students ADD COLUMN phone_number VARCHAR(20);
ALTER TABLE subjects RENAME COLUMN credits TO credit_hours;
ALTER TABLE students DROP COLUMN phone_number;

Alter Table allowed us to add the phone_number column, rename the credits column to credit_hours, and drop the phone_number column we had created.

2. DML - Putting Data In, Taking it out

DML(Data Manipulation Language) consists of commands used to add, modify, and remove data from tables. The most common commands are INSERT, UPDATE, and DELETE.
INSERT - adds new records to a table, whether it is a single row or multiple rows in a single statement, by listing several sets of values.

Inserting data into students table

INSERT INTO students (student_id, first_name, last_name, gender, date_of_birth, class, city) 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'),
(4,  'David',   'Kamau',   'M', '2007-02-18', 'Form 4', 'Nairobi'),
(5,  'Esther',  'Akinyi',  'F', '2009-06-30', 'Form 2', 'Nakuru'),
(6,  'Felix',   'Otieno',  'M', '2009-09-14', 'Form 2', 'Eldoret'),
(7,  'Grace',   'Mwangi',  'F', '2008-01-22', 'Form 3', 'Nairobi'),
(8,  'Hassan',  'Abdi',    'M', '2007-04-09', 'Form 4', 'Mombasa'),
(9,  'Ivy',     'Chebet',  'F', '2009-12-01', 'Form 2', 'Nakuru'),
(10, 'James',   'Kariuki', 'M', '2008-08-17', 'Form 3', 'Nairobi');

Inserting data into subjects table

insert into subjects (subject_id, subject_name, department, teacher_name, credit_hours) values 
(1,  'Mathematics',       'Sciences',  'Mr. Njoroge',  4),
(2,  'English',           'Languages', 'Ms. Adhiambo', 3),
(3,  'Biology',           'Sciences',  'Ms. Otieno',   4),
(4,  'History',           'Humanities','Mr. Waweru',   3),
(5,  'Kiswahili',         'Languages', 'Ms. Nduta',    3),
(6,  'Physics',           'Sciences',  'Mr. Kamande',  4),
(7,  'Geography',         'Humanities','Ms. Chebet',   3),
(8,  'Chemistry',         'Sciences',  'Ms. Muthoni',  4),
(9,  'Computer Studies',  'Sciences',  'Mr. Oduya',    3),
(10, 'Business Studies',  'Humanities','Ms. Wangari',  3);

Inserting into exam_results

insert into exam_results (result_id, student_id, subject_id, marks, exam_date, grade) values  
(1,  1, 1, 78, '2024-03-15', 'B'),
(2,  1, 2, 85, '2024-03-16', 'A'),
(3,  2, 1, 92, '2024-03-15', 'A'),
(4,  2, 3, 55, '2024-03-17', 'C'),
(5,  3, 2, 49, '2024-03-16', 'D'),
(6,  3, 4, 71, '2024-03-18', 'B'),
(7,  4, 1, 88, '2024-03-15', 'A'),
(8,  4, 6, 63, '2024-03-19', 'C'),
(9,  5, 5, 39, '2024-03-20', 'F'),
(10, 6, 9, 95, '2024-03-21', 'A');

UPDATE - It changes existing records.
Always filter by something specific, ideally the primary key, to avoid updating every row in the table.

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

DELETE - removes rows from a table, but maintains the structure of the table. It is accompanied by the where clause to filter the table; otherwise, you risk emptying the whole table.

DELETE FROM exam_results
WHERE result_id = 9;

This removes the row with the student_id of 9.

3. Filtering with WHERE - involves asking the database a question
The WHERE clause is used to filter rows based on a condition, and AND/OR lets you combine conditions.
AND means every condition must be met:

SELECT first_name, last_name, class, city
FROM students
WHERE class = 'Form 3'
  AND city = 'Nairobi';

This returns all the students who are in form 3, and come from Nairobi city.

OR means any one of them must hold

SELECT first_name, last_name, class
FROM students
WHERE class = 'Form 2'
   OR class = 'Form 4';

This returns either a student who is in form 2 or form 3.

4. Range, Membership & Search operators
This involves filtering the rows with the WHERE clause and the range operators BETWEEN, IN, NOT IN, LIKE

BETWEEN
It checks a range inclusive of both ends.

SELECT *
FROM exam_results
WHERE marks BETWEEN 50 AND 80;

The 50 and 80 marks are included in the result.

IN
It replaces a chain of OR when matching against a list of values.

SELECT first_name, last_name, city
FROM students
WHERE city IN ('Nairobi', 'Mombasa', 'Kisumu');

NOT IN
It excludes a list instead of matching it

SELECT first_name, last_name, class
FROM students
WHERE class NOT IN ('Form 2', 'Form 3');

This returns students who are either in form 1 or form 4.

5. AGGREGATION - COUNT, SUM, GROUP BY, HAVING, LIMIT
Aggregation functions are used to get a meaningful summary of how many and how much in total.

COUNT
COUNT(*) - It is paired with the WHERE clause, and it counts the matching rows

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

This returns the total number of students in Form 3.

SUM
SUM() Adds up a numeric column across matching rows

SELECT SUM(credit_hours) AS total_credit_hours
FROM subjects
WHERE department = 'Sciences';

It sums up the total credit_hours in the science department only.

GROUP BY
It aggregates per category, not just overall; it categorises rows by a shared column first, then runs the aggregate per category.

SELECT
    subject_id,
    COUNT(*)       AS results_recorded,
    AVG(marks)      AS average_marks
FROM exam_results
GROUP BY subject_id;

It will group subject_id, then return the average marks and count of results per subject_id grouping.

HAVING
It filters the rows after grouping and aggregation is done.

SELECT
    subject_id,
    COUNT(*)       AS results_recorded,
    AVG(marks)      AS average_marks
FROM exam_results
GROUP BY subject_id
HAVING COUNT(*) > 1;

This returns only subjects that have more than one recorded exam result.

LIMIT
It mostly follows the ORDER BY clause that arranges rows either in ascending or descending order, and it specifies how many rows are returned.

SELECT
    subject_id,
    COUNT(*)       AS results_recorded,
    AVG(marks)      AS average_marks
FROM exam_results
GROUP BY subject_id
HAVING COUNT(*) > 1
ORDER BY average_marks DESC
LIMIT 3;

This returns only the top 3 average_marks.

6. CASE WHEN
It evaluates conditions top to bottom and returns the label for the first one that matches.
The order of the conditions matters because the first WHEN that evaluates true wins, broader conditions have to come last, not first.

SELECT
    result_id,
    student_id,
    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;

When you place the condition marks >= 40 above marks >= 80, then every top score gets mislabeled as a "Pass".

7. JOINS
A join connects related tables using the relationships you've created using primary and foreign keys, allowing PostgreSQL to combine the data into a single result set. In our case, we can answer a single question that requires all three tables, as joins allow separate pieces of information to work together as if they are stored in a single table.

INNER JOIN
It returns only the rows that have a match in both tables.

SELECT s.first_name, s.last_name, sub.subject_name, e.marks
FROM students s
INNER JOIN exam_results e ON s.student_id = e.student_id
INNER JOIN subjects sub ON e.subject_id = sub.subject_id;

If a student has no exam results, they won't appear in this output.

LEFT JOIN
A LEFT JOIN returns every row from the left table and matching rows from the right table.
If there is no match, the columns on the right will return a NULL

SELECT s.first_name, s.last_name, e.marks
FROM students s
LEFT JOIN exam_results e ON s.student_id = e.student_id;

Any student who hasn't sat a single exam still shows up, with NULL in the marks column, highlighting the missing results.

RIGHT JOIN
It returns all the rows from the right table and matching rows from the left table.
If there is no match, the columns from the left return NULL

SELECT sub.subject_name, e.marks
FROM exam_results e
RIGHT JOIN subjects sub ON e.subject_id = sub.subject_id;

It returns the subjects with no recorded marks.

SELF JOIN
It joins a table to itself, thus helping to compare rows within the same table.

SELECT
    a.first_name AS student_a,
    b.first_name AS student_b,
    a.city
FROM students a
INNER JOIN students b
    ON a.city = b.city
   AND a.student_id < b.student_id;

This pairs students who live in the same city, without matching a student against themselves or listing every pair twice. The clause a.student_id < b.student_id ensures no duplicate pairing.

Conclusion

What This Two-Week Sprint Actually Taught Me

Looking back, the biggest lesson wasn't learning a particular SQL command; it was understanding how everything fits together.

Before you can query data, you need somewhere to store it, which is why DDL comes first. Once the structure is in place, you use DML to fill your tables with meaningful data. Only then does it make sense to start asking questions with SELECT, filtering with WHERE, summarizing with GROUP BY and HAVING, and finally connecting related tables with joins.

At first, I wondered why we didn't jump straight into joins. But after spending time querying individual tables, it became obvious why they're so important. A single table can only tell part of the story. The real power of a relational database comes from storing data in separate, well-organized tables and then bringing it together whenever you need to answer a question.

That realization completely changed how I think about SQL. It stopped feeling like a long list of commands to memorize and started feeling like a language for exploring data. Whether you're asking how many, how much, which students performed best, or what trends exist over time, SQL gives you the tools to find those answers.

I'm looking forward to learning more SQL concepts in the weeks ahead.

Data Modelling, Joins, Relationships and Schemas in Power Bi.

Ian Githu — Tue, 23 Jun 2026 20:58:40 +0000

Often not, most beginners assume that having clean data, visually appealing charts, and well-thought-out dashboards is the most important part of data analysis, only to realize that the quality of analysis largely depends on the organization of the underlying data. You could still get the wrong numbers if the data model is broken.

Power BI is a very powerful tool. What you'll see on a report page is the output of the charts and KPI cards. The action is in the model: what you are doing with your tables, how they interact, and how filters flow through them.

That is where concepts such as modelling, joins, schemas, and relationships come in. This article will help us understand how they relate to one another.

What is Data Modelling

Data modelling is the process of structuring data into tables, defining how they connect, and ensuring that Power BI can accurately filter data from all the tables. It is basically creating a blueprint for your data before building dashboards and doing reports. A good data model ensures:

Reports are easy to build
Calculations return accurate results
Improved performance

Data models are usually viewed under the Model View of Power BI.

Power Query handles data preparation: cleaning, shaping, and joining raw data.
The Data Model is where structure and relationships thrive.
Reports are just the window you look through to see the result.

Schemas

A Schema is defined as the structure of the tables: the number of tables present, the type of tables, and their arrangement. There are two commonly used schemas: a star schema and a snowflake schema.

Star Schema

It consists of one fact table at the centre, containing measurable quantitative data(sales figures, order IDs) and several dimension tables containing descriptive data (customer names, locations, and products).

The fact table is usually wide with many rows, foreign keys and numeric values. The dimension tables are narrow with few rows and descriptive attributes.

Snowflake Schema

A Snowflake schema is an extension of the star schema where the dimension tables are further broken down into additional tables. They are normalised, hence eliminating redundancy.

Joins

A join combines two tables based on a common column.
In Power BI, joins are used when combining tables in Power Query through the merge query feature.

Types of joins

Assume you have the following two tables;

Join Type	Description	Example Result
Inner Join	Returns only rows that have matching values in both tables.	Customers with orders only.
Left Outer Join	Returns all rows from the first (left) table and matching rows from the second table. Non-matching rows from the second table appear as null.	All customers, including those with no orders.
Right Outer Join	Returns all rows from the second (right) table and matching rows from the first table. Non-matching rows from the first table appear as null.	All orders, even if customer information is missing.
Full Outer Join	Returns all rows from both tables. Matching rows are combined, and non-matching rows contain null values where data is missing.	Complete list of customers and orders.
Left Anti Join	Returns rows from the first table that do not have matching values in the second table.	Customers who have never placed an order.
Right Anti Join	Returns rows from the second table that do not have matching values in the first table.	Orders with no corresponding customer records.

Understanding relationships in Power BI

Relationships inform Power BI how tables are connected. They allow information stored in different tables to work together. They are the connections you define between tables in the model view.

For example, a customer may have multiple orders, and each order may contain different products.
Relationships help connect with multiple data sources using cardinality.
Cardinality specifies how the rows in one table are related to the rows in another table.

One-to-many relationship

It is a connection between two tables in a data model where one unique value in one table can be associated with multiple values in the other table. For example, customers and products: each customer can make multiple purchases of a product, so there is a one-to-many relationship between them, but each purchase(many) is associated with only one customer(one).

Many-to-many relationship

Many-to-many relationships exist when both sides of a join can have multiple matching rows.

One-to-One relationship

Usually appears when you've deliberately split a large table for performance reasons.
One record corresponds to exactly one record in another table.

Conclusion

It's tempting to treat data modelling as a box to tick before you get to the "real" work of building visuals. But the truth is, your model is the real work. Everything else is built on top of it.

Schemas give your model structure. A well-designed star schema keeps your DAX simple and your queries fast. As your dataset grows, a clean schema won't collapse under the pressure.

Joins in Power Query let you shape and integrate data at the source, before it ever enters the model. Understanding the difference between join types means you pull exactly the data you need.

Relationships are the connections that make your report interactive. When a user clicks a year on a timeline and every visual updates accordingly, that's relationships doing their job. Get them wrong, wrong cardinality, wrong direction, and your filters either don't work or produce numbers that look right but aren't.

Cross-filter direction might seem like a minor configuration choice, but it has real consequences for how filter context propagates through your model and, by extension, how every single measure in your report behaves.

Together, these four concepts form the foundation of effective data analysis in Power BI. As I continue learning Power BI, I have come to appreciate that good analysis starts long before creating charts; it begins with building the right foundation.

How Excel is used in Real-World Data Analysis

Ian Githu — Sat, 06 Jun 2026 14:45:56 +0000

Every once in a while, you will hear the catchy phrase "data is the new gold." This is because organizations and businesses are using data to make decisions, solve problems, and develop actionable insights. Often, the available datasets are messy. Programs such as Microsoft Excel exist to transform the raw, messy data into meaningful insights that can be used to make data-driven decisions.

As a beginner learning data analytics, I have encountered Excel as one of the tools required for my day-to-day activities. I have gained valuable knowledge on cleaning datasets, analyzing, and uncovering trends. In this article, I'll share what I've learnt in my first week, showcase the various uses of Excel in real-world data analysis, features and formulas that I have learnt so far, and give a personal reflection of how learning Excel has changed the way I see data.

What is Excel?
Microsoft Excel is a spreadsheet software tool that organizes data into rows and columns, making it easier to manage large amounts of information. It allows users to collect, organize, perform calculations, analyze the available trends, and create visualizations.

How Excel is used in real-world data analysis
1. Supporting business decisions
Businesses encounter large amounts of data from customer information, sales records, and inventory entries. Excel helps businesses to track sales performance and monitor the inventory stock levels. By tracking sales, they can analyze customer behavior and identify trends and patterns common to their customers. For example, an electronic retail business can analyze its monthly sales data to identify which products are fast-moving, which generate the most revenue, and which need improvement. These insights help the owners make informed business decisions.

2. Financial reporting
Companies use Excel for financial analysis; they track income and expenses, prepare monthly financial reports, monitor cash flow, compare the actual spending against budgets, and predict future revenues. This analysis helps organizations prepare sensible budgets, understand how changes in pricing may affect profitability, identify overspending, and make adjustments accordingly.

3. Measuring market performance
Digital marketers collect data from websites, social media platforms, and online ads. They use Excel to organize this information and analyze campaign performance. They track website visits, customer sign-ups, advertising costs, and conversion rates. The insights are then used to identify trends and compare various campaign results to determine which campaigns yield the highest return on investment.

4. Project management
Excel is used to plan timelines, allocate tasks, and monitor progress. With a feature like conditional formatting, one can easily highlight overdue tasks. Projects are handled more efficiently.

Features and Formulas I've learned so far

During my first week, I explored some important Excel features that help with data cleaning and organization:
. Excel interface-understanding Excel's structure: rows, columns, cells
. Entering and Editing Data-understanding the supported data types, which include: Text, Numbers, Dates, Formulas
. Data sorting-which means arranging data in a specific order, either ascending(A-Z or smallest to largest) or descending (Z-A or largest to smallest).
. Data filtering-It allows one to display only the rows that meet certain criteria and hide the rest temporarily.*_
*. Data validation-It allows one to restrict the type of data users can input into a cell.
. Freezing panes-Freeze panes are used to keep headers or important columns in view as you scroll, especially for large data sets.
. Data formatting-Number formatting changes how numbers display without altering their actual value. It improves readability and meaning.
Conditional formatting Highlights cells automatically based on rules or criteria, helping spot trends or outliers.
. Removing duplicates-Duplicate data leads to errors in analysis.

Formulas learnt so far
SUM()
The SUM function adds a range of numbers together
May be used to calculate the total sales or revenue in a business
AVERAGE()
It calculates the mean value of a dataset
Used to find employee performance score
MIN()
It finds the smallest number
Can be used to find the youngest employee in an organization
MAX()
It finds the largest number
Can be used to find the age of the oldest employee in a company
COUNT()
Counts how many numbers are in a range
COUNTBLANK()
It counts the empty cells
Used to identify missing data
COUNTIF() and COUNTIFS()
These functions count records that meet specific conditions
Example: counting employees in a department who are of a certain gender or age
MODE()
It identifies the most frequently occurring value
MEDIAN()
It returns the middle value in a dataset
It helps in understanding data distributions
SUMIF and SUMIFS
These functions add values based on the specified criteria.
Can be used to calculate the total sales of a particular product based on its location

My personal reflection: How learning Excel changed the way I see data
Excel has helped me understand that every data set contains valuable information. Simple actions such as identifying missing values and calculating the sum, averages, mean, and mode can reveal meaningful insights. It has made me understand the importance of cleaning, standardizing, and validating data before doing analysis. Errors in a dataset can lead to inaccurate conclusions. I understand that data can tell stories, reveal patterns, and support real-world decisions. Excel is indeed a powerful tool that transforms raw data into actionable insights.

Indeed, Excel has been an eye-opener on how I view data analysis; it's not just about numbers but asking the important questions and making better decisions. I'm looking forward to diving deeper into Excel and exploring this powerful tool.

DEV Community: Ian Githu

Python Loops: The Art of Repeating Tasks With Less Code

Introduction

Understanding the Idea Behind Loops

for Loops: Going Through a Collection

Using for Loops With range()

Looping Through Strings

while Loops: Repeating While a Condition Is True

Choosing Between for and while

Controlling Loops With break

Skipping an Iteration With continue

Using enumerate() With Loops

Putting Everything Together

Why Loops Matter in Python

Final Thoughts

Python Basics: Learning to speak the language of code

Introduction

Getting to Know Python

Variables: Giving Information a Name

Choosing Good Variable Names

Data Types: Not All Data Is the Same

Checking a Data Type

Working with Numbers

Giving Information to the User

The Important Relationship Between input() and Data Types

Putting Variables, Data Types, Input, and Output Together

A Quick Look at Python Syntax

Comments: Leaving Notes in Your Code

From Small Building Blocks to Bigger Programs

Final Thoughts

When One Query Isn't Enough: A Love Letter to CTEs and Subqueries in PostgreSQL

Introduction

So, What Exactly Is a Subquery?

Where Do Subqueries Come In Handy?

Then What Is a CTE?

CTEs Are Great for Multi-Step Problems

CTE vs. Subquery: What's the Real Difference?

A Simple Rule to Remember

One of the Best Things About CTEs: You Can Chain Them

When Would You Use These in Real Projects?

Don't Think of CTEs as Automatically "Better"

Final Thoughts

From Empty Schema to Real Insights: A Two-Week Walkthrough of PostgreSQL

Creating the subjects table

Creating the exam_results table

Inserting data into students table

Inserting data into subjects table

Inserting into exam_results

Conclusion

What This Two-Week Sprint Actually Taught Me

Data Modelling, Joins, Relationships and Schemas in Power Bi.

What is Data Modelling

Schemas

Star Schema

Snowflake Schema

Joins

Understanding relationships in Power BI

Conclusion

How Excel is used in Real-World Data Analysis

`for` Loops: Going Through a Collection

Using `for` Loops With `range()`

`while` Loops: Repeating While a Condition Is True

Choosing Between `for` and `while`

Controlling Loops With `break`

Skipping an Iteration With `continue`

Using `enumerate()` With Loops

The Important Relationship Between `input()` and Data Types