DEV Community: Ezekiel Kinyua

Joins in SQL; A Deep Dive

Ezekiel Kinyua — Sat, 17 Oct 2026 13:03:13 +0000

In SQL, joins are operations used to combine rows from two or more tables based on a related column between them. Joins are essential in relational databases because they allow you to retrieve data that is spread across multiple tables and present it in a unified way. This is particularly useful in normalized databases where related data is stored in separate tables to reduce redundancy and improve data integrity.

Why do we use Joins?
1.Data Normalization: In relational databases, data is often divided into multiple tables to eliminate redundancy. Joins allow you to reassemble this data as needed.
2.Complex Queries: Joins enable complex queries that can extract meaningful insights from multiple related tables.
3.Data Integrity: By linking tables through joins, you maintain data integrity and consistency, ensuring that related data is accurate and synchronized.

Types of SQL Joins
There are various types of SQL joins. Let’s explore some of the most widely used ones:

1.INNER JOIN
An INNER JOIN is one of the most used joins in SQL. It returns only the rows that have matching values in both tables. If there is no match, the row is not included in the result set.
The INNER JOIN essentially combines the data from two or more tables based on a related column, and it filters out rows that do not satisfy the join condition.

Query

SELECT columns
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

•table1 and table2 are the names of the tables you want to join.
•common_column is the column that exists in both tables and is used as the join condition.
•columns are the columns you want to select in the result set.

2.LEFT JOIN
A LEFT JOIN, also referred to as a LEFT OUTER JOIN, is a type of SQL join where all rows from the left table (table1) are returned regardless of whether there is a match in the right table (table2). It includes the matching rows from the right table based on a related column specified in the ON clause of the LEFT JOIN statement.

This join condition ensures that for each row from the left table, the LEFT JOIN includes the corresponding matching rows from the right table in the result set. If there is no match found in the right table, NULL values are returned for the columns from the right table.

Query

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;

•SELECT columns specifies the columns you want to select from the combined result set.
•table1 and table2 are the names of the tables you want to join.
•LEFT JOIN indicates that you're performing a left join.
•ON table1.common_column = table2.common_column specifies the join condition, where common_column is the column that exists in both tables and is used to match rows between them.

3.RIGHT JOIN
A RIGHT JOIN, also known as RIGHT OUTER JOIN, is a type of SQL join that returns all rows from the right table (table2) and includes matching rows from the left table (table1). The join condition is based on a related column between the two tables, specified in the ON clause of the RIGHT JOIN statement.

The RIGHT JOIN ensures that all rows from the right table are retained in the result set, and for each row from the right table, it includes the corresponding matching rows from the left table based on the join condition. If there is no match found in the left table, NULL values are returned for the columns from the left table.

Query

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;

•SELECT columns specifies the columns you want to select from the combined result set.
•table1 and table2 are the names of the tables you want to join.
•RIGHT JOIN indicates that you're performing a right join.
•ON table1.common_column = table2.common_column specifies the join condition, where common_column is the column that exists in both tables and is used to match rows between them.

Conclusion
SQL joins are integral for combining data from multiple related tables to generate meaningful relationships, that facilitate better insights. Understanding INNER JOIN, LEFT JOIN, and RIGHT JOIN helps us retrieve the exact data needed for the analysis.

Window Functions: A guide to mastery.

Ezekiel Kinyua — Sat, 17 Oct 2026 12:35:52 +0000

Introduction

A SQL Window function that performs a calculation across a set of rows related to the current row, while preserving the individual rows in the result.
Unlike aggregate functions, which group rows into a single output, window functions return a value for each row based on defined window, using the 'OVER()' clause to specify the partitioning and ordering of the rows within that window.

The name window in SQL is essentially a group of rows or observations within a table or result set. Depending on how the query is structured, multiple windows within the same table might be defined.

Types of Window Functions

Window functions are classified into several types based on their purpose and functionality.

Aggregate Window Function

Aggregate window functions allow you to perform calculations such as finding totals, averages, or counts - across a specific set of rows also know as a Window. This returns result for each individual row in your dataset. These functions combine the power of regular aggregate functions such as SUM, AVG, COUNT etc., with the flexibility of window functions.

AVERAGE() Window Function

Suppose we want to find the average years of experience and specialty, without losing any individual rows.

Query;

Result;

AVG(grade) OVER(PARTITION BY subject); calculates the average years of experience. The PARTITION BY specialty groups the rows by doctor specialty and calculates the average within each group.
Every row keeps its individual data, but now each row shows the average years of experience for that specialty, making comparison easy for individual specialty against the average years of experience.

MIN() and MAX() Window Function

MIN() - This functions returns the smallest value in a set of rows. In the context of window functions, it finds the minimum value within a specified "window" without reducing the total number of rows in the result set.
MAX() - The function returns the largest value in a set of rows. In this context, it finds the maximum value within a specified window.

Suppose we want to find the highest and lowest number of years of experience within the doctors table.

Query

Result

MIN(doctor_experience_years) OVER(PARTITION BY doctor_specialty) calculates minimum years of experience for each doctor in the department. Our data only contains one doctor per department.
MAX(doctor_experience_years) OVER(PARTITION BY doctor_specialty) calculates maximum years of experience for each doctor in the department. Our data only contains one doctor per department.

COUNT() Window Function

This function is used to count the number of rows within a specified window of rows, without collapsing the result set. It is similar to the aggregate function COUNT(), but in this case, it counts rows within each partition while retaining each row in the output.

Query

Result

Ranking Window Function

These functions assign a rank or position to each row within a specific set of rows. They are used to order rows and then assign a numerical rank based on that order. They don't group rows together, instead, they allow each row to retain its data while also showing its rank relative to others.

ROW_NUMBER() Window Function
This function is used to assign a unique, sequential number to each row within a partition of a result set. It resets the numbering for each partition and increments by 1 for every row within that partition or result set.

Suppose we have a table of doctors with their specialty and years of experience, and we want to assign a unique row number to each doctor within their specialty, ordered by years of experience.

Query

Result

**RANK() Window Function
This function assigns a rank to each row within a partition of a result set, based on the values in specified column. The rows with equal values are given the same rank, but this causes gaps in the ranking subsequent rows.

Query

Result

The PARTITION BY Specialty ranks the doctors within each specialty.
Ordering by years of experience ranks the doctors within the same specialty in descending order of years of experience.

DENSE_RANK() Window Function
This function is used to rank rows within a result set or partition without skipping any ranks, even when there are ties. Unlike the RANK() function, which skips ranks for tied values, DENSE_RANK() assigns the same rank to tied rows but does not leave gaps in the ranking sequence.

PERCENT_RANK() Window Function
This function calculates the relative rank of a row as a percentage between 0 and 1 within its result set or partition. It is used to find the percentile ranking of a row compared to other rows.
It is a useful function for determining the relative position of a row in a dataset, expressed as a percentage. It is particularly helpful when analyzing the distribution of values across a dataset.

NTILE() Window Function
It divides rows into a specified number of roughly equal-sized groups or buckets, assigning each row a "bucket number". This is useful for distributing data evenly into groups, like quartiles or percentiles.

VALUE() Window Function
This function refers to specific window functions that retrieve values from certain positions within a window frame of rows.

• LAG(): Accesses data from a previous row at a specified physical offset before the current row.
• LEAD(): Accesses data from a subsequent row at a specified physical offset after the current row.
• FIRST_VALUE(): Returns the value of the specified expression from the very first row in the window frame.
• LAST_VALUE(): Returns the value of the specified expression from the last row in the window frame.
• NTH_VALUE(): Retrieves the value from an exact user-defined row position within the window partition.

LEAD() Window Function
This function allows access of data from the following (next0 row relative to the current row in the result set. It helps in comparing a row with future rows, making it useful for operations like calculating differences over time.

LAG() Window Function
The function is the opposite of LEAD(). It allows you to access data from a preceding row relative to the current row. It's useful for comparing the current row with past values.

FIRST_VALUE() Window Function
Both FIRST_VALUE and LAST_VALUE functions are used to retrieve the first or last value in a window of ordered rows. These functions are useful for accessing the first or last data point within a partition or an entire dataset. Useful for retrieving the earliest event in time series, such as the first sale, the first payment, etc.

LAST_VALUE() Window Function
The LAST_VALUE() function returns the last value in the window frame based on the specified ordering. Useful for retrieving the most recent data, like the last transaction or the most recent sale.

Nth_VALUE() Window Function
This function allows you to retrieve the value from a specific row, based on its position, within a window or partition. It is useful when you need to access not just the first or last value, but any specific row in the dataset.
It returns the value of the N-th row within the ordered window.

Conclusion
Window functions are essential functions for data analysis while maintaining individual rows. By mastering aggregate, ranking, and value functions, you can perform calculations, compare records, and uncover meaningful patterns in your datasets.
The key is to understand how OVER(), PARTITION BY, and ORDER BY work together.

Learning Excel For Data Science & Analytics

Ezekiel Kinyua — Sun, 30 Aug 2026 14:35:39 +0000

Excel is one among the most popular tools generally used in data analysis.
It allows us to perform various functions when handling data. These include;

Data entry – this involves keying in data collected into cells of a worksheet, to maintain records, manage information and keep a track on information.
Data cleaning – this simply means making data usable by removing duplicates, errors and inconsistencies from a dataset.
Data analysis – using excel, we can transform raw data into actionable insights, which informs better decision making.
Reporting – after cleaning and analyzing, the result is information that is easily understandable by various users to draw insights and make informed decisions.

Basic Excel Terminologies;

- Ribbon
This is the section of a worksheet where we have tabs and icons, arranged horizontally at the top of the worksheet. It contains various functions used to perform various operations while working on data.

- Cell
These are the grid-like spaces on the worksheet where we key-in data. These are the intersections between a column and a row.

- Cell Address
This can be referred to as the name or the address of a selected cell. It shows the intersection between a column and a row. In this case, “column – R, row -19”.

- Column
This is the vertical grouping of the cells. Columns are named alphabetically, which can be seen at the top of the worksheet. In this case, the column is named as “A”.

- Row
This is the continuous horizontal running of cells across a worksheet.
They are named in numbers. In this case, the row is denoted as “4”.

- Tables
This is a combination of rows and columns.

Functions

- Operators
You can perform various functions on a data set to derive various insights using Excel. These include the following functions;

Addition (+)
Subtraction (+)
Multiplication (*)
Division (/)
Exponent (^)

- Aggregate functions
These functions require a user to type in a specific function on Excel. These allows us to perform various operations such as;

SUM – performs an addition of a range of figures.
POWER – this raises a figure to a given power or exponent.
PRODUCT – Multiplies all numbers in a given range.
AVERAGE – this is the mean of a set of figures.
MEDIAN – this gives the middle figure in a set of numbers.
MODE – this shows the figure the appears most frequently.
MIN - this function gives the result of the lowest value out of a group of figures or numbers.
MAX – this function gives a result of the highest value out of a group of figures or numbers.
SQUARE ROOT (SQRT) – this allows us to find the square root of a certain figure.

- Statistical Functions
These tools or functions allow us to summarize and analyze data to arrive at insightful decisions. These functions include;

COUNT – These counts only numeric cells or columns
COUNTA – this function counts all non-blank cells
COUNTBLANK – This function counts just the blanks.

- Conditional Functions

COUNTIF / COUNTIFS – this function counts cells meeting a specific condition.
SUMIF /SUMIFS – this gives a result of a total of certain entries that meet a certain condition.
AVERAGEIF - this gives a result of the average of certain entries that meet a certain condition.
TODAY - this function gives the current date.
NOW – this function gives the current time.
YEAR – this function extracts the year from a date entry.
MONTH - this function extracts the month from a date entry.
DAY - this function extracts the day from a date entry.
NETWORKDAYS – this function calculates the total number of working days between two dates.
DATEDIF – this function gives the total difference between two dates.

- Conditional Formatting
This function allows us to highlight various entries in cells to make them observable based on certain specified conditions.

Conclusion

These functions and features offers an array functions and capabilities in Data Analysis. We are able to draw valuable insights and arrive at informed decisions. I am looking forward to further understanding more functions and formulae in Excel as i continue to better my skills in data analytics.

My Data Science & Analytics Journey: Understanding Git, Git Bash & GitHub

Ezekiel Kinyua — Sun, 23 Aug 2026 16:39:48 +0000

Introduction

Every journey starts with a single first step. My journey in Data Science and Analytics started a fortnight ago. Coming from a career in finance and investments which revolves around numbers and making sense of them, a good interpretation and presentation is key for the numbers to make sense. And this is how i ended up enrolling at LuxdevHq.

At first, the Git ecosystem sounded complicated, but surprisingly, it is not and it is quite interesting to learn and use.

What is Git?

Git is a software tool that allows me to make changes, manage and track projects or files that are locally hosted on my computer's hard drive.
Git tracks by keeping a record of previous versions of my projects. It acts like a camera that captures a moment. A record of this would help to determine changes happening in that moment.

What is Git Bash?

Think of Git Bash as a terminal where you can write commands to manage files or folders and projects. You can also use Git Bash to write commands that communicates with GitHub.

What is GitHub?

GitHub is simply an online storage for your projects and files that allows you to access them remotely away from your PC. It also allows for sharing of the projects and files, and to also collaborate with other users of the same projects or files.

Further Definitions

Repository - Also know as a Repo This refers to a project being tracked and managed by Git Bash.
Push - This refers to sending or uploading a file or project from a local machine to an online hosting such as GitHub.
Cloning - This refers to downloading a git from the online hosting into my local machine.
Branch - A branch allows you to work on a new project without altering the original version.

Setting up the Git ecosystem on my PC

Installing Git

I downloaded from git-scm.com and installed. This was the first step to installing Git Bash. To check that it worked, i ran the command

git --version

Git Configuration

This involved setting my name by running the command

git config --global user.name "My name"

To configure my email,

git config --global user.email "My email"

Setting up SSH Keys

This facilitates securely connecting my PC to GitHub. GitHub needs to securely connect to my PC.

Creating a repository on my PC

This involves creating files and folders on my computer. A repo is simply a project or folder that is being tracked by Git.

Connecting to GitHub

After securely storing the projects on my PC, the next step is to Push them online so that they are shareable and accessible remotely.

Conclusion

This has been my last two weeks, in a nutshell.
What i have learnt so far is that it is a journey of that requires me to commit. As much as it is about learning and understanding new tools, it is more about how i will interpret and approach problems. The errors, the commands and every success are all part of this learning journey.