DEV Community: Raphael Njeri

Understanding SQL Window Functions

Raphael Njeri — Mon, 21 Sep 2026 16:39:31 +0000

When I first came across SQL window functions, I found them a bit confusing. I was already comfortable with basic queries, filtering data, using GROUP BY, and calculating things like totals and averages. Window functions looked different because they allowed me to perform calculations without losing the individual rows.

As I practiced them, I started to understand that window functions are mainly useful when I want to analyse a row while also looking at other related rows.

What is a window function?

A window function performs a calculation across a set of rows while keeping the original rows in the result.

For example, I can calculate the average salary in each department:

SELECT
employee_name,
department,
salary,
AVG(salary) OVER (PARTITION BY department) AS department_average
FROM employees;

The interesting part for me is that I still get every employee in the result. SQL adds the department average alongside each employee's information.

This is different from using GROUP BY.

Window Functions vs GROUP BY

Before learning window functions, I would use GROUP BY when I wanted a calculation for each group.

For example:
SELECT
department,
AVG(salary) AS average_salary
FROM employees
GROUP BY department;

This gives me the average salary for each department, but I no longer have the individual employee records in the result.

With a window function:

SELECT
employee_name,
department,
salary,
AVG(salary) OVER (PARTITION BY department) AS average_salary
FROM employees;

I get both the employee information and the department average.

This was one of the things that helped me understand why window functions are useful.

What does PARTITION BY do?

PARTITION BY was another part that I needed to understand properly.

I understood it as dividing the data into groups before the calculation is performed.
For example:

SELECT
employee_name,
department,
salary,
AVG(salary) OVER (PARTITION BY department) AS department_average
FROM employees;

Here, the employees are separated according to their department, and the average is calculated within each department.

So if I have Finance, Sales and IT departments, each department gets its own average.

Ranking rows with ROW_NUMBER()

One of the practical examples I worked with was ranking records.

ROW_NUMBER() gives each row a number based on the order I specify.

SELECT
employee_name,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_number
FROM employees;

Since I used ORDER BY salary DESC, the employee with the highest salary gets number 1.

I can also rank employees separately within each department:

SELECT
employee_name,
department,
salary,
ROW_NUMBER() OVER (
PARTITION BY department
ORDER BY salary DESC
) AS department_rank
FROM employees;

Now the ranking starts again from 1 for every department.

This showed me how PARTITION BY and ORDER BY can work together inside a window function.

What is the difference between RANK() and ROW_NUMBER()?

I also learned that ROW_NUMBER() is not the same as RANK().

For example, if two employees have the same salary, ROW_NUMBER() will still give them different numbers.

SELECT
employee_name,
salary,
ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_number
FROM employees;

The result could look like:

employee salary row_number
John 80000 1
Mary 80000 2
Peter 70000 3

With RANK():

SELECT
employee_name,
salary,
RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

The two employees with the same salary can receive the same rank:

employee salary salary_rank
John 80000 1
Mary 80000 1
Peter 70000 3

The difference became clearer to me when I looked at actual examples instead of just reading the definitions.

Using LAG() to compare rows

Another window function I learned was LAG().

LAG() allows me to look at a value from a previous row.

For example, if I have daily sales:

SELECT
sale_date,
amount,
LAG(amount) OVER (ORDER BY sale_date) AS previous_amount
FROM sales;
The result could be:

sale_date amount previous_amount
2026-01-01 500 NULL
2026-01-02 700 500
2026-01-03 450 700

This means I can compare the current value with the previous value.

I can even calculate the difference:

SELECT
sale_date,
amount,
LAG(amount) OVER (ORDER BY sale_date) AS previous_amount,
amount - LAG(amount) OVER (ORDER BY sale_date) AS difference
FROM sales;

I found this useful because it showed me how SQL can be used to analyse changes between records instead of just producing totals.

What about LEAD()?

LEAD() works in a similar way to LAG(), but instead of looking at the previous row, it looks at a following row.

For example:

SELECT
sale_date,
amount,
LEAD(amount) OVER (ORDER BY sale_date) AS next_amount
FROM sales;

So if LAG() looks backwards, I can think of LEAD() as looking forward.

This makes them useful when comparing values across different rows.

Calculating a running total

I also learned that window functions can be used to calculate running totals.

For example:

SELECT
sale_date,
amount,
SUM(amount) OVER (ORDER BY sale_date) AS running_total
FROM sales;

If my sales were:

sale_date amount
Jan 1 500
Jan 2 300
Jan 3 700

The running total would become:

sale_date amount running_total
Jan 1 500 500
Jan 2 300 800
Jan 3 700 1500

This is different from simply calculating the total sales because I can see how the total changes as I move through the records.

Understanding the OVER() clause

One thing I had to get used to was the OVER() clause.

For example:

RANK() OVER (
PARTITION BY department
ORDER BY salary DESC
)

At first, this looked like a lot of SQL in one place. Breaking it down made it easier:

RANK() tells SQL what calculation I want.
OVER() tells SQL that I am using a window.
PARTITION BY department separates the records by department.
ORDER BY salary DESC determines the order within each department.

Once I understood what each part was doing, the queries became much easier to read.

Where I can use window functions

After practicing window functions, I can see how they can be useful in real data analysis.

For example, I could use them to:

-Rank customers according to their purchases.
-Find the highest-paid employee in each department.
-Compare current sales with previous sales.
-Calculate running totals.
-Calculate averages while keeping individual records.
-Analyse changes in data over time.

This makes window functions useful when I need more information than a simple GROUP BY query can provide.

What I found challenging

The main challenge for me was understanding how the different parts of a window function work together.

I initially focused on the function itself, such as RANK() or LAG(), without paying much attention to what was inside OVER().

After practicing, I realised that the function and the window definition work together. The PARTITION BY determines the group I am working within, while ORDER BY determines how the rows are arranged for the calculation.

That made a big difference in how I understood the topic.

What I understood from this session

The biggest thing I learned is that window functions allow me to perform calculations across related rows without removing the individual records.

I also learned that different functions are useful for different situations. ROW_NUMBER() and RANK() can be used for ranking, LAG() and LEAD() can help compare rows, while functions such as SUM() and AVG() can be used for calculations across a window.

More importantly, I now understand why I would choose a window function instead of GROUP BY in some situations.

Conclusion

Window functions were one of the SQL topics that took me some time to understand, but working through practical examples made them clearer.

I can now use functions such as ROW_NUMBER(), RANK(), LAG(), LEAD(), SUM() and AVG() with OVER() to analyse data while keeping the original records.

I still have more to learn about window functions, but I now have a good understanding of the basic concept and how they can be applied when working with data.

Python Basics: My First Steps into Python

Raphael Njeri — Mon, 07 Sep 2026 15:48:36 +0000

When I started learning Python, I initially thought programming was mostly about memorizing different commands and writing code that works. As I started practicing, I realized that it is more about understanding how information is stored, changed and used by a program.

In this session, I focused on the basics of Python. The main areas I covered were variables, data types, input and output, comments, and basic Python syntax. These may look like simple concepts, but they form the foundation for everything I will be doing with Python later.

Why Python?

One of the things that interested me about Python is how readable the code is. Compared with some programming languages, Python does not require a lot of complicated syntax to perform simple tasks.
Python is also widely used in data analysis and data science, which is one of the reasons I am interested in learning it. Since my goal is to work with data, understanding Python gives me another tool for analyzing and solving problems.

Where do I start with Python?

The first thing I needed to understand was how to write a simple Python statement.

One of the easiest examples is:

print("Hello, World!")

The print() function displays information in the output.

I can also use it to display a calculation:

print(10 + 5)

Output:

This helped me understand that Python can do more than simply display text. It can also perform operations and return a result.

What is a variable?

A variable is a name that I give to a piece of information so that I can use it later in my program.

For example:

name = "Raphael"
age = 28

Here, name contains "Raphael" while age contains 28.

I can then use the variables instead of repeatedly writing the actual values:

print(name)
print(age)

Output:

Raphael
28

The = sign in this case is used to assign a value to a variable.

For example:

course = "Data Science"

I understood this as Python taking the value "Data Science" and assigning it to the variable course.

What are data types?

Not all information stored in Python is the same. Python has different data types depending on the kind of value I am working with.

Some of the basic ones I learned are:

1. Integer (int)

An integer is a whole number.

age = 28
units = 19
2. Float (float)

A float is a number that contains a decimal.

price = 500.50
height = 1.74
3. String (str)

A string is text.

name = "Raphael"
course = "Python"
4. Boolean (bool)

A Boolean has only two possible values:

is_active = True
is_complete = False

I can check the type of a value using type().

age = 28

print(type(age))

The output is:

(class 'int')

Another example:

name = "Raphael"

print(type(name))

Output:
(class 'str')

This was important for me because I learned that Python needs to know what kind of data it is working with.

What is the difference between a string and a number?

This was one of the areas I had to pay attention to.

For example:

age = 28

Here, age is an integer.

But:

age = "28"

Here, age is a string because 28 is inside quotation marks.

Although both appear to contain 28, Python treats them differently.

I can confirm this using:

print(type(age))

This made me understand why data types matter when writing programs, especially when calculations are involved.

How can a Python program receive information?

Python provides the input() function for receiving information from a user.
For example:

_name = input("What is your name? ")

print(name)

If I enter:

Raphael

Python stores the response in the name variable.

I can then use it somewhere else in the program.

_name = input("What is your name? ")

print("Welcome", name)_

The important thing I learned here is that input() normally gives me a string.
Therefore, if I ask someone to enter their age:

age = input("Enter your age: ")

the value is treated as text.

If I need it as a number, I can convert it:

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

The int() converts the input into an integer.

Putting the concepts together

After learning variables, data types, input and output, I tried combining them into a small program.

_name = input("Enter your name: ")
age = int(input("Enter your age: "))
course = input("Enter the course you are studying: ")

print("Name:", name)
print("Age:", age)
print("Course:", course)_

If I enter:

Enter your name: Raphael
Enter your age: 28
Enter the course you are studying: Data Science

The program displays:

Name: Raphael
Age: 28
Course: Data Science

This simple example helped me see how the different concepts connect.

The program receives information, stores it in variables, converts the age to an integer, and finally displays the information.

What are comments used for?

I also learned that I can add notes to my code without affecting the way the program runs.

A comment starts with #.

Store the student's name

name = "Raphael"

print(name)

Python ignores the comment when running the program.

I found comments useful because when code becomes longer, they can help me remember what a particular section is meant to do.

A small example from my learning

I can use what I have learned to create a simple personal information program:

name = "Raphael"
age = 28
course = "Data Science"
completed_excel = True

print("Name:", name)
print("Age:", age)
print("Course:", course)
print("Completed Excel:", completed_excel)

Here I am using different data types in the same program:

name → string
age → integer
course → string
completed_excel → Boolean

This was a simple exercise, but it helped me understand that a Python program can work with different types of information at the same time.

What I understood from this session

Before this session, I could look at Python code and understand some of the words, but I did not fully understand how the different pieces fitted together.

After practicing, I now understand that a basic Python program can start with information being stored in variables. Each value has a data type, and I can use functions such as input() and print() to interact with the user.

I also learned that small details matter. For example, "28" and 28 may look similar to me, but Python treats them as different types of data.

Challenges I encountered

My biggest challenge was getting comfortable with the syntax and understanding why Python sometimes treats values differently.
I also had to understand that simply writing code is not enough. I need to understand what each line is doing.
For example:

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

At first, this can look like one complicated line. After breaking it down, I understood that:

input() asks the user for information.
The information received is initially text.
int() converts that text into an integer.
The result is stored in the age variable.

Breaking code into smaller parts made it much easier for me to understand.

Why these basics matter to me

The concepts from this session are important because they will be used throughout my Python learning.
When I start working with datasets, I will need to understand what type of information I am dealing with. For example, a customer's name will be text, an age will normally be an integer, and a price may be a float.
I therefore see Python basics as the foundation for the more advanced topics I will learn later, including conditionals, loops, functions and data structures.

Conclusion

This session gave me my starting point in Python. I learned how to create variables, identify basic data types, receive information using input(), display results using print(), and add comments to my code.
The most important lesson for me was not just learning the commands, but understanding what is happening when the code runs.
I now have a better foundation to continue with Python and gradually move from writing simple programs to using Python to solve real data-related problems.

Relationship Schemas and Joins in Data Modeling

Raphael Njeri — Sun, 05 Jul 2026 17:32:37 +0000

This week I learned about relationship schemas and joins, and I now understand why they are important when working with databases. Before this topic, I thought all the data could just be stored in one table, but I have realized that this is not the best approach because it creates a lot of repeated information and makes the database harder to manage.

From what I have understood, a relationship schema is simply the way different tables in a database are connected. Instead of putting everything in one table, the data is separated into different tables depending on what it represents. For example, students, teachers, and payments can each have their own table, and then they are linked using IDs. This makes the data more organized and easier to update.

I also learned that there are different types of relationships. A one-to-one relationship is where one record is connected to only one other record. A one-to-many relationship is the one I found easiest to understand because one teacher can teach many students, but each student is assigned to only one teacher. There is also a many-to-many relationship where one student can take several courses and one course can have many students. In such a case, another table is needed to connect the two.

Another concept I learned is joins. Joins are used when information is stored in different tables but we want to see it together. The INNER JOIN only shows records that match in both tables. The LEFT JOIN returns everything from the first table even if there is no matching record in the second table. The RIGHT JOIN does the opposite, while the FULL OUTER JOIN returns all records from both tables whether they match or not.

As I continue practicing data modeling and using Power BI, I can now see why relationship schemas and joins are necessary. They help in creating a proper data model instead of using one flat table. This makes reports more accurate, reduces duplicated data, and makes it easier to analyze information. Although I still need more practice, I now have a much better understanding of how tables relate to each other and how joins help retrieve data from those related tables.

How excel is used in real world Data analysis.

Raphael Njeri — Sun, 07 Jun 2026 10:54:16 +0000

Microsoft Excel is a spreadsheet software used to organize, calculate, analyze and visualize data. As I begin my journey in Data Science and Analytics, I have discovered that Excel is much more than just a spreadsheet application. It is a powerful tool that helps transform raw data into meaningful information.

One of the first things I learned is how Excel helps with data entry and organization. Data can be arranged neatly in rows and columns making it easier to manage records, track information, and maintain accuracy. Proper organization is important because it makes data easier to understand and work with.

I also learned the importance of data cleaning. In many cases raw data contains errors, duplicates, or missing information. Excel provides useful tools such as Find and Replace, Remove Duplicates, Text to Columns and Filters that make it easier to clean and prepare data for analysis.

Another key lesson has been data analysis. Excel offers a variety of formulas and functions including SUM, AVERAGE, COUNT, MAX, MIN and IF, which help perform calculations and uncover insights from data. These tools make it possible to identify trends, measure performance, and support decision-making.

From my experience so far, Excel has shown me that no matter how data is collected, it becomes much easier to clean, organize and analyze when using the right Excel tools. It is an essential skill for anyone starting a career in Data Science and Analytics.