Forem: Brian Nyamwange

Joins and Windows Function in Sql.

Brian Nyamwange — Mon, 02 Mar 2026 19:59:13 +0000

Introduction

As a data analyst or Data engineer mastering Sql narrows down to getting into advanced concepts that will enable one to manage, query and optimize databases. In this article we will explore Joins and windows functions in sql.

Joins

Joins bringing together information from different tables turning a database into a unified space where data relationship becoming the building for effective data analysis

Types of joins

Inner Join
This join combines two or more tables and returns only rows that have matching values in the tables.
For example look for an order in the orders table

Then customer table

The relationship between the two tables is the customer_id.To create an inner join use this;

SELECT Orders.order_id , Clients.first_name, Orders.order_date
FROM Orders
INNER JOIN Clients ON Orders.customer_id=Clients.Customer_id;

it will display something like

Left Join

The left join returns all rows from the left teble and only the matched rows from the right table, if the is no match in the right table the result from the right table will be null.
For example using the selection of customers and orders table used in the inner join, we can create a left join using the following syntax

SELECT Clients.last_Name, Orders.order_id 
FROM Clients
LEFT JOIN Orders ON Clients.customer_id  = Orders.customer_id 
ORDER BY Clients.last_Name;

The result should be;

Right Join

The right join is the opposite of the left join it shows all rows from the right table and matched rows from the left table.
For example using the selection of customers and orders table used in the inner join, we can create a right join using the following syntax

SELECT Orders.Order_id, clients.last_Name, clients.first_Name
FROM Orders
RIGHT JOIN clients ON Orders.customer_id = clients.customer_id 
ORDER BY Orders.order_id;

The result should be

Full Join

It returns all rows when there is a match in either the left or right table, if the rows on the left have no match on the right,the result inludes data of the left table and null on the right table and vice versa.

For example using the selection of customers and orders table used in the inner join, we can create a full join using the following syntax

SELECT Clients.first_Name, Orders.order_id 
FROM clients
FULL JOIN Orders
ON Clients.customer_id  = Orders.customer_id

The result should be

Self Join

This is a regular join where a table is joined by itself. it is used to compare rows within the same table or find related records in a single table

Windows function in Sql

Windows function in Sql are a feature that allows one to perform calculations across a set of of table rows related to the current table without collapsing the result into a single value.
The basic sythax for a window function is

function_name (expression) OVER (
    [PARTITION BY partition_expression]
    [ORDER BY sort_expression]
    [frame_clause]

Types of windows functions.

we have two types of windows function aggregate and ranking window functions.

Aggregate function.

It calculates aggregates over a window of rows while retaining individual rows
some of the aggregate functions include:

Sum() - sums values within a window.
Count() - counts rows within a window.
Max() - Returns the maximum value in a window.
Min() - returns the minimum value in a window.
Average() - calculates the average value in a window.

Examples using average; we will calculate average salary within departments. Using the following sythax

SELECT first_name, hire_date , department, salary, 
       AVG(salary) OVER( PARTITION BY department) AS Avg_Salary
 FROM employees

The result should be

Ranking window function.

They are used to assign rank to each rows. common ranking functions include;

Rank() - assigns ranks to rows skipping duplicates in the rows.
Dense rank() -assigns ranks to rows without skipping duplicates. -Row_Number - assigns a unique number to each row in the result set.
Percent_Rank - shows the relative rank of a row as a percentage between 0 and 1. for example using the same employees table we are going to rank employees within their departments using their salary We will use the following synthax;

select 
    employee_id, 
    first_name, 
    last_name, 
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) AS rank
FROM employees;

The result should be;

How Analysts Translate Messy data, Dax, and Dashboards into Action using Power Bi.

Brian Nyamwange — Mon, 09 Feb 2026 05:23:12 +0000

Introduction.

In the real world data is not always presented to an analyst being clean or clear for decision making. Power Bi helps analysts turn this raw, unclear data into easily understandable information by using dashboards making it easy for business executive to make quick decisions. In this article we are going to learn how this happens.

Messy Data

As we have seen analysts do not always get data that is clean data, so present it as it is will give inaccurate results that will negatively impact decision making, among the issues they encounter are;

Missing Values
Duplicate Values
inconsistent Formats

Data Preparation

For data to be cleaned and make sense analysts use Power query in Power Bi to transorm data.Once you have get your data from the data source,you do not load the data instead you click transorm data here you will remove duplicates, fix inconsistent formatting for example currencies, converting data types ensuring fields calculate correctly, splitting or merging columns to organize information consistently, removing null values that disrupt aggregation and reporting.
Each of this actions is recorded in the applied steps pane, making the process reproducable and tranparent.

The Data model.

Once cleaning is done, to make it easier working with calculations analyst design a data model, the most recommended data model to use is the star schema, in this model data is organised into two main parts; fact tables and dimension tables, facttables store quantitative,measurable business events such as sales or transactions while dimensions tables stores information that gives context to the data in fact tables.

Using Dax for Data Transformation.

Dax(Data analysis Expressions),is the formula language that, creates calculations,measures and relationships after the data has been loaded, Dax defines how data behaves in analysis with calculated measures and tables that helps transform raw data into meaningful insights.
In Power Bi, Aggregation is used to convert raw data into meaningful summaries such as Average, Sum, Mean, Median e.t.c this forms the basis for all reporting and dashboards.
In hindsight Dax shapes data into answers that reflects on the business priorities.

Making Insights visible: Dashboards and Reports

After cleaning, modeling and calculating your data as ana analyst you create Dashboards.Dashboards visualizes the data communicating key insights and supporting decision making, the most success dashboards are able to start conversations, prompt actions and influence decision making.
Analysts are able to create dashboards using charts, line graphs, slicers
A well built dashboard highlights key performance indicators, it focuses on decison not data exploration and long term trends,each dashboard created by analyst should have a clear purpose. Below is an example of a dashboard.

Conclusion.

Inconclusion Power Bi turns raw data into a shared understanding of a business,begining from unclean data, through modelling followed by dax where business logic is defined, and it becomes visible in dashboards where business decision are made.

Understanding Schemas and Data Modelling in Power Bi

Brian Nyamwange — Mon, 02 Feb 2026 14:04:45 +0000

Introduction.

Power Bi is a data visualization tool used by organizations to turn data into actionable data focused decisions,in this article we will focus on the various schemas and data modelling in power bi.

What is a schema in Power Bi?

A schema in power Bi basically refers to the structure and organization of tables,data type and relationships within a data model.

Types of schemas in power bi.

Star Schema.

A star Schema is a type of schema where a fact table is surrounded by dimensional tables forming a star like pattern.

The central fact table contains quantitative data e.g sales,quantity sold and profit while the dimensional tables will contain information about the products,regions and customers.

Snowflake

A snowflake schema is an extension of the star schema, it contains the fact table that extends to dimensional tables which are further broken down to sub dimensions.

In a snowflake schema, the fact table contains quantitative data,the dimensional tables will contain information about products,regions and customers,the sub-dimensional tables will contain information such as product category.

Data Modelling in power Bi.

Data modelling structures raw data collected and identifies the relationship between the data.
In power BI data modelling helps with detremining how data is structured,related and interpreted for analysis,reporting and visualization.

Fact Tables and Dimension Tables

Fact Table

A fact table in contains the quantitative information that needs to be analyzed,it includes keys that link to dimensional tables.

Dimension Table.

Dimension Tables give us the information required to categorize,filter or group information in the fact table.
Dimension tables contain information such as product,channel,orderdates etc. basically the dimension table describes who,what,where and when.

Relationships in Power Bi.

Relationships in power bi defines how tables related to each other.

Core Relationship Types in Power Bi.

Many to one- a table with multiple instances for example sales and relates to a table unique values for example products.
one to many- It is the reverse of the many to one.
One to One -Used when two tables have unique values for the key column.
Many to Many -Handles situations where both tables contain duplicates,often requiring bridging tables.

key relationships concepts.
Cardinality- refers to the type of relationships.
Cross filter Direction-Determines how filters flow.
Active vs Inactive- A model having multiple relationships between tables but only one is active.

Why Good is critically for performance and Accurate Reporting.

Good Modelling Ensures that data is accurate,easily accesible,consistent avoiding situations where poor data leads to poor decision making.
Good modelling can:

Enhances Scalability- as data grows a ggod model scales efficiently without degrading performance and makes reports easier for others to understand.
Optimized query speed- well designed models like star schema allows databases to retrieve information faster.
Reduces Errors-by creating clear relationships upfront,you prevent data inconsistencies.

In conclusion Data modelling in power bi is the root of effective analyticsunderstanding fact and dimension tables,using schemas and defining relationships ensures better performance and accurate Reporting.

Learn Excel; A Guide For Beginners.

Brian Nyamwange — Sun, 25 Jan 2026 16:06:18 +0000

Introduction to Excel

What is excel?

You have probably heard about excel. A popular spreadsheet that allows one to insert, read and manipulate data in rows and columns.
Starting with learning Excel is one of the major steps to a career in Data analysis. It helps you build essential skills such as, organizing, cleaning, summarizing, visualizing and interpreting data.

Excel interface:Ribbons,sheets,cells,columns,rows,

When you open your Excel workbook, you see a grid based space designed to organize, analyze and store data.The picture below is how the excel looks on default when opened.

Some of the notable features from the image are;

The Excel ribbon- it is the rows of tabs and icons at the top of excel that allows one to quickly find,understand and use commands to complete certain tasks. it contains the; file,home,insert,page layout,formulas,data,review,view tabs.
Columns- they run vertically top to bottom, identifiable by letters A,B,C...AA,BB,CC...

Rows- They run horizontally, left to right, identifiable by numbers 1,2,3...
Cells - A cell is where a row and a column intersect. identified by a cell reference e.g (A2,C5)
Worksheets- is a one page of data with an excel file.

Data types:Texts,dates and numbersoc

In excel data types, defines how excel displays,processes, and stores data in cells. The three most comomon data types used in data analytics are dates,numbers and texts.

Text Data type- this is used for information that is descriptive rather than numerical e.g names,countries,cities.
Number type data- This shows data that can be quantifiable i.e excel can calculate with. e.g Quantity sold, unit price and can be formatted as currency,decimal or percentage.

Entering and Editing data.

Accurate data entry ensures accurate and reliable,calculations analysis and reporting.

Entering data in excel.

Click on an empty cell,Type the value(date,text or number) then press enter button.

Editing data in excel.

You can edit data in a cell by double clicking on the cell then filing in with the right information.

Formulas and Functions in Excel.

Formulas are equations you create to calculate values in a cell.All formulas start with =.
functions are pre built formulas in excel that are used to perform common calculations.Commonly used excel functions are:
Sum used to add totals.

Average finds the mean of the values requested.

Min and Max Finds the lowest and highest values in a data set.

=MIN(A1:A10)
=MAX(A1:A10)

IF the if function in excel allows you to make a logical comparison between a value and what you expect.

AND/OR used to test multiple conditions.
=AND(A1>50, B1>50) =OR(A1>50, B1>50)
CONCATENATE used to join one or two texts together. e.g.

=CONCATENATE(B2,"", C2)

It joins the contents in cell B2 to those in cell C2 while leaving a space in between.

Data cleaning and Sorting.

Data cleaning and sorting helps one to fix errors such as misspelled words, trailing spaces,improper cases, remove duplicates etc. we will dive in and see who to solve some of these issues

Remove duplicates

When you use the remove duplicate feature, the duplicate data is permanetly deleted. it is a common practice to copy the original data to another worksheet to avoid losing important information. below you are guided on how to remove duplicates

select the range of cells that has duplicate data you want to remove.
Go to Data at the top of your excel sheet
Click on remove duplicates then click Enter button.

Other times duplicate data can be useful, use conditional formatting to find and highlight duplicate data,review the duplicates and decide if you want the data removed.use the following steps:

Select cells you want to check for duplicates.
Select Home,Conditional formating,Highlight cell rules,Duplicate values.

Convert text to numbers

Numbers that are stored as text can lead to unexpected results.To convert text to numbers you can use the value function by;

Creating a new column, in one of the cells of the new column type=VALUE()inside the parenthesis type the cell reference,next fill the cells formula down the other cells. #Sorting data. Sorting data in excel organises it alphabetically,numerically or by date. Select a cell in the column you want to sort. on the data tab, in the sort and filter group select how you want you data arranged.

Filtering data

it helps one to temporary hide some data you are working on so that you can focus on certain data.
To filter a range of data follow this procedure

Select any cell within the range you want to filter
On the tab select Data then filter
Select the column header arrow then select text filters or number filters then enter the filter criteria then okay.

Data Analysis with pivot tables,charts and dashboards

Pivot Tables

A pivot table is used to summarize,calculate and analyze data that lets you see comparisons,trends and patterns in you data.

To create a pivot table, select the cells you want to create the pivot table from,Select insert at the top tab then pivot table

It creates a pivot table based on the existing table

Select new worksheet then okay.

Pivot charts

Pivot charts compliment pivot tables by adding data visualization to the summary data in pivot tables.

They are created by

Click any cell in your data range.
Go to insert tab and click pivot chart.

pivot charts are used to;
Analyze trends over time using line charts.
Compare categorical data with bar charts.
Display data proportions with pie charts.

Dashboards

They are interactive, visual summary of key data using charts,tables and KPIs in a single view for quick analysis.
Dashboards are create from a combination of pivot tables,pivot charts and slicers that are copied and pasted in a new worksheet.The below image showsa dashboard created from the combination of pivot charts,pivot tables and slicers.

A Beginners'Guide on Version control with Git: How to Push,Pull, and Track changes.

Brian Nyamwange — Sat, 17 Jan 2026 19:12:17 +0000

Git version Control

To any aspiring developer, Git is one of the most important tools ypu will use on a day to day basis. Whether working on personal projects or collaborating with a team. It helps you track changes, manage code versions, and safely manage projects over time.Understanding Git early in your journey will make you a more efficient, confident and professional developer.
In this Article, you are going to dive into something really interesting.
To start, we will talk about Version Control.

Version Control

Lets look at an example, Suppose you have been working on a project as a team, you have spent days and weeks working on it.Your Supervisor loves it a few days later she asks you to make a few changes on the project,Months later she comes back and says

"actually the other version was better". How do you handle this?

Challenges like those are the ones the Git solves. That is why it is reffered to as Version control it allows one to revert selected files or an entire project back to a previous state, see who made changes last, and compare changes overtime with different developers working on the same project.

Approaches to use Git for version control

Git via command line.

it is the most common method used, developers use terminal commands to interact with Git. The following steps guide you go to navigate through:

Step one; Download and install Git.

Download and install Git, after intallation verify the Git version by running the following command

git --version

Step two; Iniatialize a Git repository

Open a terminal on Gitbash and navigate to your project folder.
Iniatialize Git by runnning

git init

Step three; Staging changes

Staging means you are saying "my changes are ready and they can move to the next step" you can run the following commands to stage your files git add filename stages a specific file. git add stages all changes/add all files.

Step Four; Committing Changes

Committing means permanently saving those changes to your local repository.
To commit changes use the following commands

git commit

Step five; Viewing Commit History

This lets you see what changed,who changed it and when using Git. you can see the full history using the following commands

git log

After Seeing how Git acts as a version control, we will see how you can track changes on Git.

Tracking Changes with Git

When working on a project it is easy to lose track on which files have been modified, Now here where git status comes in it acts as a checkpoint, it helps you see what has changed in your project or what needs attention before you commit.

Git Push

The Git push command is basically used to upload commits from your local repository to a remote repository e.g. Github.This allows other developers to see and access your work.

When to use:

You have committed meaningful changes.
You want to share your work with teammates.
You want to back up your code to a remote repository.
Use the following command when you want to push a commit

git push

Git pull

The git pull command is used to update your local repository with changes made to a remote repository.

When to use git pull

Before starting to work on a shared project to ensure you are working on the latest version of the code.
Before Pushing changes to avoid conflicts with new updates from others.
use the following command when you want to pull

git pull

Knowing how to use Git might be tricky in the early stages but once you know how to track changes, push your work and pull updates collaborating with others and managing your projects becomes easier.

Forem: Brian Nyamwange

Joins and Windows Function in Sql.

Introduction

Joins

Types of joins

Left Join

Right Join

Full Join

Self Join

Windows function in Sql

Types of windows functions.

Aggregate function.

Ranking window function.

How Analysts Translate Messy data, Dax, and Dashboards into Action using Power Bi.

Introduction.

Messy Data

Data Preparation

The Data model.

Using Dax for Data Transformation.

Making Insights visible: Dashboards and Reports

Conclusion.

Understanding Schemas and Data Modelling in Power Bi

Introduction.

What is a schema in Power Bi?

Types of schemas in power bi.

Star Schema.

Snowflake

Data Modelling in power Bi.

Fact Tables and Dimension Tables

Fact Table

Dimension Table.

Relationships in Power Bi.

Core Relationship Types in Power Bi.

key relationships concepts.

Why Good is critically for performance and Accurate Reporting.

Learn Excel; A Guide For Beginners.

Introduction to Excel

What is excel?

Excel interface:Ribbons,sheets,cells,columns,rows,

Data types:Texts,dates and numbersoc

Entering and Editing data.

Entering data in excel.

Editing data in excel.

Formulas and Functions in Excel.

Data cleaning and Sorting.

Remove duplicates

Convert text to numbers

Filtering data

Data Analysis with pivot tables,charts and dashboards

Pivot Tables

Pivot charts

Dashboards

A Beginners'Guide on Version control with Git: How to Push,Pull, and Track changes.

Git version Control

Version Control

Approaches to use Git for version control

Git via command line.

Step one; Download and install Git.

Step two; Iniatialize a Git repository

Step three; Staging changes

Step Four; Committing Changes

Step five; Viewing Commit History

Tracking Changes with Git

Git Push

When to use:

Git pull

When to use git pull