Forem: Brian Muchoki

Joins and Windows Functions in SQL

Brian Muchoki — Sat, 28 Feb 2026 17:16:39 +0000

Joins

Let's JOIN It!
Joins combine two or more tables based on a related column.
They aid in:

Retrieval of connected data that is stored across multiple tables.
Matching records using columns.
Improvement of Data Analysis through the combination of related information.
Creation of a meaningful result set from separate tables.

Types of SQL joins

SQL joins are categorized by how rows from two tables are matched and combined.

SQL Inner Join: They retrieve rows where matching values exist in both tables. They help in combining records based on a related column, retrieving only matching rows from both tables and ensuring accurate data relationships between tables.

Syntax

Inner Join SELECT table1.column1,table1.column2,table2.column1,.... FROM table1 INNER JOIN table2 ON table1.matching_column = table2.matching_column;

The join is also known as the Inner Join.
For Instance, if I have two tables, the customers table and orders table, and I want to know the customer who made an order, I can use the common column, which is the customer_id, to merge the tables.
The Snapshot below shows a sample query:

Left Join: They retrieve all rows on the left table and matching rows from the right table, and show null values where no match exists. It prioritizes the first table you mention.

Syntax

Left Join SELECT table1.column1,table1.column2,table2.column1,.... FROM table1 LEFT JOIN table2 ON table1.matching_column = table2.matching_column;

The Left Join is also known as the Left Outer Join.
For Instance, if I have two tables, the customers table and orders table, and I want to know the customer who made an order, I can use the common column, which is the customer_id, to merge the tables. The first table I chose is the customers table.
The Snapshot below shows a sample query:

Right Join: It retrieves all rows from the right table and matching rows from the left table, and displays null values where no matching records exist in the left table. It prioritizes the second table you choose.
The Right Join is also known as the Right Outer Join.
Syntax

Right Join SELECT table1.column1,table1.column2,table2.column1,.... FROM table1 RIGHT JOIN table2 ON table1.matching_column = table2.matching_column;

For Instance, if I have two tables, the customers table and orders table, and I want to know the customer who made an order, I can use the common column, which is the customer_id, to merge the tables. The first table I chose is the orders table.
The Snapshot below shows a sample query:

SQL Full Join: It combines the results of both the left and right joins, providing complete data from both sides of the join.
Syntax

SELECT table1.column1,table1.column2,table2.column1,.... FROM table1 FULL JOIN table2 ON table1.matching_column = table2.matching_column;

If a customer has never placed an order, they still show up. If an order has no matching customer, it still shows up. Nobody gets left out.
The Snapshot below shows a sample query:

Window Functions in SQL

They perform calculations across a specific set of rows(the window)(defined by an over clause) that are related to the current row without collapsing them into a single value.
They use values from one or multiple rows to return a value for each row.
They are commonly used for tasks such as aggregation, averaging, ranking, and running totals.
Key Components of SQL Window Functions

Select: This defines the columns you want to select from the table_name.
Function: This is the window function you want to use.
Over Clause: This defines the partitioning and ordering of rows and can be applied with functions to compute aggregated values.
Partition by: This divides rows into partitions based on specified expressions. It is suitable for large datasets because it makes them simpler to manage.
Order by: This is a specified order expression to define the order in which rows will be processed within each partition.
Output column: This is the name you give to your output column. *** Window functions are used after the processing of WHERE, GROUP BY and HAVING.

Syntax

SELECT column_name1, window_function(column_name2) OVER ([PARTITION BY column_name3] [ORDER BY column_name4]) AS new_column FROM table_name;

Types of SQL Windows Functions
Aggregate Functions

AVG: Average value in a group. It ignores null values.
MAX: Finds the maximum value in an expression.
MIN: Finds the minimum value in an expression.
SUM: Sum of all values, or only the distinct values.
COUNT: This is the number of times a value is found in a group.
STDEV: Returns the statistical standard deviation of all values in the specified expression.
STDEVP: Returns the statistical standard deviation for the population for all the values in the specified expression.
VAR: Returns the statistical variance of all the values in the specified expression. An over clause may follow it.
VARP: Returns the statistical variance for the population for all the values in the specified expression.

Ranking Window Functions.

Row-Number: It assigns a unique number to each row in a result set. Increment is by 1 for every row, and avoids duplication.
Rank: It assigns a unique rank to each row with gaps/or while skipping duplicates/ties in a result set. Mainly used to organize and analyze data.
Dense Rank: It assigns a unique rank to each row without skipping rank numbers for duplicates. The same rank can have the same values or ties.
Percent Rank: It shows the relative rank of a row as a percentage within a group of rows. N-tile: It distributes rows in an ordered partition with a specified number of approximately equal groups.

Value Window Functions.

LAG: Retrieves values from rows that precede the current row in the result set.
LEAD: Retrieves values from rows that follow the current row in the result set.
FIRST_VALUE: Returns the first value in an ordered set of values within a partition.
LAST_VALUE: Returns the last value in an ordered set of values within a partition.
NTH_VALUE: Returns the value of the nth row in the ordered set of values.
CUME_DIST: Returns the cumulative distribution of a value in a group of values.

Benefits of SQL Window Functions

Efficiency: There are fewer queries to be processed by the query. Queries are concise and precise.
Versatility: They help in aggregation, ranking, distribution, etc., without narrowing the result set down to a single row.
Row-Level Context: They preserve the original rows and columns in the result set.

To Note:
Partition carefully because, without a partition, the whole table is treated as one group.
Check order by because it controls the calculation order in the window.

Conclusion

Data rarely comes neat and ready to use. Depending on your needs, joins and window functions are how you make sense of it; Connecting relationships and unearthing insights that would otherwise stay hidden. Knowing which tool to reach for makes all the difference. And like most things, the more you practice, the more natural it becomes.

SCHEMAS & DATA MODELLING IN POWER BI

Brian Muchoki — Wed, 18 Feb 2026 07:05:23 +0000

Introduction

Effective data management is a critical component of any organisation that aids in smarter, data-driven business decisions. At the heart of this lies a foundational understanding of Schemas and Data Modelling — two concepts that work together to define how data is structured, stored, and ultimately used to generate meaningful insights.

Data Modelling

Data Modelling is the process of designing and organising data structures to support databases. It provides a structural representation that demonstrates how data is stored, organised, and manipulated — serving as the blueprint for how information flows through a system.

Key Components of Data Modelling

Entities
Entities are real-world concepts that can be distinctly identified and about which data can be stored. For example, in a retail context, a Customer, a Product, or a Sale would each represent a separate entity.

Attributes
Attributes are the characteristics that describe or define an entity. They serve as the data points that can be used to sort, filter, or order a dataset. For instance, a Customer entity might have attributes such as Name, Age, and Location.

Relationships

Relationships refer to the connections between entities and their attributes. These connections help ensure that the model accurately reflects real-world actions or dependencies between data points. There are three types of relationships in a data model:

• One-to-One: One entity is related to exactly one instance of another entity. For example, one employee may be assigned exactly one staff ID.
• One-to-Many: The most common type in data modelling. One entity can have multiple instances of another entity. For example, one customer can place many orders.
• Many-to-Many: Multiple instances of one entity are related to multiple instances of another entity. This is the most complex type of relationship and is often resolved using a bridge or junction table to make it manageable.

Fact and Dimension Tables

Fact Table
The Fact Table is the core of a data model. It stores raw quantitative data for analysis — holding numerical values and metrics that can be aggregated to answer key business questions. Examples include sales revenue, units sold, or transaction counts.
Dimension Table
Dimension Tables provide the necessary context to Fact Tables. They contain descriptive attributes that can be used to filter, group, and label the data stored in a Fact Table. For example, a Date dimension might include attributes like Day, Month, Quarter, and Year, enabling time-based analysis of sales figures.

Schemas

Schemas define the structure and organisation of data within a data model. They determine how data is connected and related, which in turn influences the efficiency and performance of data queries and reports. A schema represents a logical grouping of tables that are associated with each other. Understanding schemas is essential for designing data models that support comprehensive, reliable analysis.
Types of Schemas

Star Schema
The Star Schema is the most commonly used schema in data warehousing — and in Power BI. It features a single central Fact Table surrounded by multiple Dimension Tables, with each Dimension Table connecting directly to the Fact Table. This layout resembles a star shape, hence the name. Its simplicity makes it easy to query and highly performant, making it the preferred choice for most reporting scenarios.

Snowflake Schema
The Snowflake Schema is a normalised version of the Star Schema. In this structure, Dimension Tables are further divided into sub-dimension tables, creating a more layered and complex arrangement. Normalisation reduces data redundancy by breaking dimension tables into multiple related tables. While this results in a web-like structure resembling a snowflake, it can add complexity to queries and may impact report performance if not managed carefully.

Why Good Modelling is Critical for Performance and Reporting

A well-designed data model simplifies complexity by visually mapping how different entities relate to each other, making it easier to understand, manage, and analyse datasets. Beyond clarity, good modelling delivers several tangible benefits:

• Ensures data consistency and quality through simplified database design and management.
• Improves query performance by reducing unnecessary calculations and data redundancy.
• Enhances data storage efficiency, keeping models lean and responsive.
• Provides the flexibility to scale models as data volumes grow and business needs evolve.
• Simplifies troubleshooting by making data relationships transparent and logical.
Together, these benefits create data models that are not only reliable today but adaptable for the future — supporting effective, data-driven decisions at every level of an organisation.

Conclusion

Schemas are vital for building an effective data model in Power BI. When deciding which schema to use, it is essential to balance the benefits of normalisation against the need for simplicity and performance. Rather than treating schemas as mutually exclusive choices, combining the strengths of each — the accessibility of the Star Schema with the reduced redundancy of the Snowflake Schema — can lead to stronger, more efficient data model designs.

The result is improved query performance, better data storage efficiency, and greater overall data operations — all of which contribute to scalable models that enable smarter, more confident business decisions.

How Analysts Translate Messy Data, DAX, and Dashboards into Action Using Power BI

Brian Muchoki — Mon, 09 Feb 2026 17:49:34 +0000

Power BI has become an indispensable tool for data analysis, and analysts are using it to transform raw, unstructured data into meaningful insights that drive actionable business decisions.

FROM MESSY TO CLEAN

1. Connecting to Your Data Sources.

The journey of analysts begins with connecting Power BI to the respective data sources. The data could be residing in Excel workbooks, SQL databases, Cloud Services, etc. Power BI offers versatile connectivity options that bring information from multiple sources. x

2. Transforming Messy Data with Power Query.

Data must be cleaned and prepared before any form of analysis.
Power Query is essential in this part-it serves as the data transformation engine, allowing you to remove duplicates, correct errors, handle null values, change data types, find and replace values with the corrected values or updated values, and standardize formats. This process creates a reliable table that feeds your data model, ensuring that every calculation and visualization built is credible.

3. Performing Calculations with DAX

DAX (Data Analysis Expressions) is the analytical layer. Once the data is cleaned and loaded into the model, DAX takes the center stage. This enables analysts to create calculated columns, measures, and calculated tables that perform computations beyond raw data. DAX transforms static information into dynamic insights through aggregations, time intelligence functions, filtering, and custom metrics that answer specific business needs. These calculations set the tone for visualization. For instance, if we are talking about Sales Data, we could perform calculations on specific products, location, demographic, and time period.

4. Building Dashboards

To bring this to life, analysts go to the report view to bring insights to life. In report view, graphs, charts, interactive slicers, and customization of visuals happen to match the specific needs and arrange everything into an interactive dashboard. These dashboards display patterns, trends, and present key performance indicators in ways that make the information understandable. The result is usually an interactive experience that helps the stakeholders to explore data and make informed decisions. For Instance, Graphs and Charts would show you the revenue trends across years, products, and regions, whereas slicers and filters could help to only focus on what we want-it could be sales for a specific location.

5. Conclusion on Power BI

Power BI's strength lies in its ability to handle messy data, disconnected data sources, and to create polished interactive dashboards that drive informed decision-making processes. Analysts transform chaos into clarity and raw data into actionable insights that move organizations forward.

Introduction to MS Excel for Data Analytics

Brian Muchoki — Tue, 03 Feb 2026 15:58:25 +0000

Getting Started with Excel. Excel It!

1. Excel Definition and Its Use.
Excel is the most popular spreadsheet developed by Microsoft, which organizes data in Columns and Rows. It runs on Windows, macOS, Android, and iOS.
GLOSSARY IN EXCEL
Cell:It is the rectangular intersection of a column and a row where data is stored and manipulated.
Column: It is a vertical set of cells in a spreadsheet, identified by letters(A, B, C, etc.). They run from top to bottom.
Data Validation: It is a feature that controls the kind of data that can be entered into a cell.
Formula Bar: It is the bar near the top of the spreadsheet where you can edit the contents of the currently selected cell.
Pivot tables: They summarize and reorganize data from a larger table. You can filter, group, and perform calculations to see patterns and totals.
Range: A group of selected cells in a spreadsheet, which can be a single cell, multiple adjacent cells, or non-adjacent cells. They are written like A1:B10.
Rows: A horizontal set of cells in a spreadsheet identified by numbers (1,2,3, etc). They run from left to right.
Workbook: This is the entire spreadsheet file that contains one or more worksheets.
Worksheet: This is a single sheet or tab within a workbook where you enter and work with data.
2. Overview of Excel Interface.
Ribbon This is the toolbar interface at the top of spreadsheet applications that organizes commands and features into tabs. Each tab contains a group of related tools and buttons, making it easier to find and use different functions.

Workbook

Worksheet
-At the bottom, you can see sheets labeled as Original, Sheet1, KPIs, etc.

3. Data Entry and Organization in Excel.
Data is organized in rows and columns. Columns have headings and cells where you enter data.

You can enter numerical data, strings, dates, times, percentages, currents, and formulas into cells. Numerical data is typically aligned to the right by default, whereas strings are aligned to the left.
Each cell has a unique address like A1, B8, H9 that can be used in formulas and reference data.
Cells can be formatted to display data in specific ways, like adding currency symbols and changing date formats, etc.
Sorting, filtering, and freezing planes are some of the data organizational tools.
Best Practices:

Keep one type of data per column.
Avoid blank rows or columns within your data range.

4. Data Cleaning and Manipulation/Formatting.
Removing Duplicates: Ensuring that each record appears only once in your dataset.
Finding and replacing values: Searching for specific data and replacing it with updated or corrected information.
Data alignment: Adjusting how data is positioned within cells.
Data consistency: Ensuring uniformity in text case throughout your data.
Trimming whitespaces: Removing extra spaces before, after, or between text that can cause errors in sorting, filtering, and formulas.
Removing special characters: Cleaning up unwanted symbols or line breaks.
Correcting data types: Converting each data type into the right data type.
Summary
Data cleaning and formatting involve preparing raw data for analysis by removing duplicates, correcting inconsistencies, standardizing formats, and removing errors.
5. Pivot Tables and Data Visualization.

Pivot tables analyze large data sets. They allow users to summarize, group, and perform calculations to reveal patterns and insights without altering original data.

Data visualization using charts makes the analysis clear. Excel offers various chart types- bar charts, line graphs, pie charts, and scatter plots- each suited to different analytical needs.
When combined with pivot tables, these visual tools create a powerful system for exploring data interactively and present findings as actionable insights.
6. Creating Dashboards.

Dashboards are visual displays that bring together charts in one place. They combine multiple charts, tables, and key metrics on a single screen, allowing you to monitor performance, track progress, and make informed decisions. Good dashboards use clear visual elements like slicers and filters that let you explore different aspects of your data with a simple click.
The main purpose of dashboards is to make complex data easy to understand and act upon. Dashboards save time by presenting everything you need in an organized view.
7. Conclusion.
Excel remains an essential tool for data analysis, offering accessible features from basic to advanced that enable users at any skill level to transform raw data into meaningful insights and informed decisions.

LET'S GIT IT—A Beginner's Guide to Version Control.

Brian Muchoki — Sun, 18 Jan 2026 12:23:22 +0000

KEY WORDS
-Git: It is a version control system that monitors changes in your files or code over time.
It is like a save feature that remembers every version of your work, and you can easily go back to previous versions.
Use: Developers use it to monitor changes, experiment with new features, whilst maintaining their main code, and for collaboration.

Git Bash: A command-line interface that provides GIT commands.
Use: A Tool for using GIT on Windows, since Windows does not naturally understand GIT commands.

GitHub: It is a cloud-based hosting service for Git repositories with collaboration features.
It is like a Google Drive for code.
Use:

Back up your online code.
Share code with teammates.
Collaborate on projects.
Show your work to potential employees.

SSH: Secure Shell.
It is a cryptographic network protocol for secure communication over an unsecured network.
Use: To safely connect your computer to remote servers without data or password interception.

SSH KEY: It is a secure access credential used in the SSH protocol for authentication between a client and a remote server.
Use:

Prove your identity to GitHub.
It is more secure than a password.

How do they all work?

Write code on your computer.
Git monitors all your changes locally.
Git Bash lets you tell Git what to do (Save changes, etc.)
SSH key authenticates you to GitHub.
GitHub stores your code online safely.

Hands-on Training with GIT

What you need!
1.Have Git Bash downloaded and a GitHub account on your computer.
Download Git Bash
Sign up to GitHub
2.Configure your identity: Open Git Bash and set your username and password(Should be similar to your GitHub username and email)

git config --global user.name "your name"
git config --global user.email "your.email"

3.Generate SSH Key
This is more convenient and recommended.

ssh-keygen -t ed25519 -C "your-github-email@example.com"

4.Copy Your Public Key

cat ~/.ssh/id_ed25519.pub

5.Add to GitHub:

Go to GitHub.com
Click your profile picture → Settings
Click SSH and GPG keys (left sidebar)
Click New SSH key
Paste your key, give it a title, and click Add SSH key 6.Test Connection:

ssh -T git@github.com

Basic Workflow

1.Create a repository on your computer.
2.Make changes to your code: create files, edit code, save your work in your text editor.
3.Check what has changed.
4.Stage your changes(Preparation to save).
5.Save to Git(save with a message about what you did).
6.Push to GitHub(Uploading the saved changes to GitHub).
7.Pull from GitHub(Downloading changes from GitHub to your computer)

Getting Started: Cloning Your First Repository

Basic Git Workflows

Understanding Version Control

Shows all your commits(saves) with messages, dates, and who made them.

git log

What's Next?

Now that you've set up Git and GitHub, here are your next steps:

Create your first repository and push some code
Practice daily: Commit your changes regularly with clear messages
Explore branching: Learn how to experiment without breaking your main code
Collaborate: Contribute to open-source projects or work with friends

Remember: Everyone starts somewhere. The more you use Git, the more natural it becomes. Happy coding!