DEV Community: Rachel Muriuki

Mastering Joins and Window Functions in SQL

Rachel Muriuki — Tue, 03 Mar 2026 18:54:45 +0000

Joins in SQL

Joins allow one to combine rows from two or more tables based a related column. This is important when data is stored across multiple tables. There are several types of joins:

a.INNER JOIN

-Returns only the matching rows from both tables.
Syntax

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

Example

SELECT c.customer_id, c.first_name, o.order_id,o.quantity
FROM customers AS C
INNER JOIN orders AS O
ON c.customer_id = o.customer_id;

This displays only customers who have placed an order.

b.LEFT JOIN (LEFT OUTER JOIN)

-Returns all rows from the left table and only matching rows from the right table.
-If there's no match, NULL is returned on the right table columns.
Syntax

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

Example

SELECT c.customer_id, c.first_name, o.order_id, o.quantity
FROM customers AS C
LEFT JOIN orders AS O
ON c.customer_id = o.customer_id;

This displays all customers even those who didn't place an order.

c.RIGHT JOIN (RIGHT OUTER JOIN)

-Returns all rows from the right table and only matching rows from the left table.
-If there'so match, NULL appears on the left table
Syntax

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

Example

SELECT c.customer_id, c.first_name,o.order_id,o.quantity
FROM customers AS c
RIGHT JOIN orders AS O
ON c.customer_id = o.customer_id;

This displays all orders, even if there's no matching customer

d.FULL OUTER JOIN

-Returns all rows from both tables, matching where possible and filling NULL when there's no match.
Syntax

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

Example

SELECT c.customer_id, c.first_name, o.order_id, o.quantity
FROM customers AS C
FULL OUTER JOIN orders AS O
ON c.customer_id = o.customer_id;

This displays all customers and orders, even unmatched ones.

e.CROSS JOIN

-Returns all possible combinations of both tables, where every row from one table is paired with every row from the other table.
Syntax

SELECT columns
FROM table1
CROSS JOIN table2;

Example

SELECT c.first_name, o.order_id
FROM customers AS C
CROSS JOIN orders AS O;

f.SELF JOIN

-Joins the same table to itself, to compare rows within the same table.
Syntax

SELECT a.column, b.column2
FROM table_name a
JOIN table_name b
ON a.common_column = b.related_column

Example

SELECT 
    c1.customer_name AS customer, 
    c2.customer_name AS referred_by
FROM customers c1
JOIN customers c2
ON c1.referred_by = c2.customer_id;

c1 - represents the customer who was referred
c2 - represents the customer who did the referring
This finds the rows where the referred_by column of one customer (c1) matches the customer_id of another (c2)

Window Functions in SQL

Window Functions perform calculations across a set of rows that are related to the current row.
They are three types of window functions:

Aggregate window functions
Ranking window functions
Value/Analytical window functions

Syntax

SELECT columns
     windowfunction_name(expression)
     OVER (
          [PARTITION BY column_name]
          [ORDER BY column_name]
FROM table_name;
)

Partition By:-Divides the data into groups
Order By:-Specifies the order of rows within each group

1.Aggregate Window Functions

-They compute totals, average, counts across rows.

MIN()

-Shows the smallest value within a window

SELECT customer_id, order_id, total_amount,
     MIN(total_amount) 
     OVER(
        PARTITION BY customer_id) AS smallest_order,
FROM orders;

MAX()

-Shows the largest value within a window

SELECT customer_id, order_id, total_amount,
     MAX(total_amount) 
     OVER(
        PARTITION BY customer_id) AS biggest_order,
FROM orders;

COUNT()

-Counts rows but keeps all rows visible
-Used for showing how many records are in a group without GROUP BY

SELECT customer_id,
    COUNT(order_id) 
    OVER(
       PARTITION BY customer_id) AS total_orders
FROM orders;

This shows how many orders each customer has made

AVG()

-Gives the average value for a window of rows
-Used to find average spend, average score, etc.

SELECT customer_id, order_id, total_amount,
    AVG(total_amount) 
    OVER(
       PARTITION BY customer_id) AS avg_spending
FROM orders;

This shows the customer's average order value

SUM()

-Adds up values without grouping the rows
-Used for running totals or cumulative sums

SELECT customer_id, order_id,
    SUM(total_amount) 
    OVER (
       PARTITION BY customer_id 
       ORDER BY order_date) AS running_total
FROM orders;

This calculates running totals per customer

2.Ranking Window Functions

-They assign ranks, row numbers or categories to each row within its partition.
-Examples include

ROW_NUMBER()

-Assigns a unique number to each row
-Used to find the first, second, third item in a list

SELECT customer_id, order_date,
     ROW_NUMBER() 
     OVER (
        PARTITION BY customer_id 
        ORDER BY order_date) AS order_number
FROM orders;

In this example, each customer's first order gets 1, second gets 2...

RANK()

-Gives a rank number based on order, but skips numbers when there are ties
-Used for ranking by price, score, sales

SELECT customer_id, total_amount,
    RANK() 
    OVER(
       PARTITION BY customer_id 
       ORDER BY total_amount DESC) AS order_rank
FROM orders;

In this example, if two orders have the same amount, they both get the same rank and the next rank is skipped eg.1,2,2,4

DENSE_RANK()

-Similar to RANK() but does not skip numbers
-Used for ranking when you want continuous ranks eg.(1,2,2,3)

SELECT customer_id, total_amount,
    DENSE_RANK() 
    OVER (
       PARTITION BY customer_id 
       ORDER BY total_amount DESC) AS dense_rank
FROM orders;

NTILE()

-Divides rows into n equal groups
-Used for dividing data into quartiles or percentiles

SELECT order_id, total_amount,
    NTILE(4) 
    OVER (
       ORDER BY total_amount DESC) AS quartile
FROM orders;

*In this example, orders are divided into 4 groups(top 25%, next 25%...)

3.Value/Analytical Window Functions

LAG()

-Looks at the previous row's value
-Used for comparing current and previous rows

SELECT customer_id, order_date, total_amount,
    LAG(total_amount) 
    OVER(
       PARTITION BY customer_id 
       ORDER BY order_date) AS prev_order
FROM orders;

This example shows what the previous order's amount was for each customer

LEAD()

-Looks at the next row's value
-Used for comparing to the next record

SELECT customer_id, order_date, total_amount,
      LEAD(total_amount) 
      OVER(
         PARTITION BY customer_id 
         ORDER BY order_date) AS next_order
FROM orders;

This example shows what the next order's amount will be

Conclusion

Joins help us bring related data together from different tables while window functions allow us to perform advanced calculations like rankings and totals.

Transforming Messy Data into Reliable Insights Using Power BI

Rachel Muriuki — Thu, 12 Feb 2026 09:17:17 +0000

How Analysts translate messy data, DAX and dashboards into action using Power BI

In every organization, data is being generated from sales transactions, website clicks, customer feedback. However this data is often inconsistent, incomplete and scattered across multiple systems. An analyst can turn this messy data into insights that drive business actions using Power BI.

1.Cleaning and Organizing Messy Data

Since data often arrives being messy, analysts perform a process known as ETL in Power Query. It consists of;
-Extracting data from multiple sources (Excel files, SQL databases)
-Transforming it through cleaning, structuring data, adding logic
-Loading it to Power BI for visualizing

2.Turning numbers into logic

Analysts use DAX(Data Analysis Expressions) to create calculations that turn raw numbers into meaningful insights
Example;

Customer Retention Rate

Using filters and time intelligence functions, analysts can calculate how many customers make repeat purchases

3.Designing dashboards that drive decisions

Dashboards are visual tools that show data in charts and graphs
A good dashboard:
-Highlights KPIs(Key Performance Indicators) which are the most important numbers
-Allows one to click on charts to see details by region, product or time
-Tells a story that guides the user to derive insights and make actions

4.From insight to actions

Creating visuals helps businesses take action.
How skills in Power BI translate into real impact:
-Data Cleaning-Improves accuracy of reports and decisions
-DAX Calculations-Quantifies performance, identifies trends and reveals root causes
-Interactive Dashboards-Helps leaders see trends and act fast
-Data modelling-Combines data from different departments into one view

5.Turning data into decisions

Analysts take messy data, organize it, calculate key metrics and build dashboards that show managers what's working and what's not.

Conclusion

Power BI is more than a reporting tool. It transforms data into insights that help organization measure results, understand key drivers and take informed actions.

SCHEMAS AND DATA MODELLING IN POWER BI

Rachel Muriuki — Mon, 02 Feb 2026 11:42:15 +0000

PowerBI is a powerful tool for turning raw data into insightful reports.
In power BI tables show what data you have, relationships show how it connects and schema shows why it all fits together.

Data Model

A data model defines how your tables connect and interact in Power BI. It tells Power BI:
-Which tables hold raw facts eg. Sales
-Which tables describe those facts eg. Products

Fact Tables

They store measurable, quantitative data such as Sales transactions.
They contain numeric columns like Revenue, Quantity and contain foreign keys such as ProductID, CustomerID.

Dimension Tables

They contain descriptive textual information that adds context to facts
Examples of such columns:
-Customers
-ProductName
-Category

Relationships

PowerBI connects fact and dimension tables through relationships using shared keys like ProductID

Types oF relationships:

i)One-to-Many

One record in a dimension connects to many in a fact table
Example: One Customer can make many orders

ii)Many-to-One

Multiple records in one table are connected to a single record in another table
Example:Multiple cities belong to one state

iii)Many-to-Many

Used when both tables can have repeating values
Example: A student can take many courses and each course has many students

Schemas

Schema is how your tables are arranged and linked together

Star Schema

It's simple, looks like a star and yet powerful
One central fact table (eg. Sales) connects directly to dimension tables (eg.Customers, Products, Regions)
It is easy to understand, performs fast, ideal for slicing and filtering

Snowflake Schema

It adds extra detail tables branching off the dimensions
Used to avoid repeating data and save space
The only limitations are that it involves more tables and relationships, resulting to slow performance.

Why Good Modelling Matters

It ensures accurate visuals and measures that show correct results
It simplifies DAX queries and reduces processing time
It's easy to add new data sources later
It offers clarity to anyone reviewing your model
It's easy to maintain incase of issues

A well designed schema ensures reports load quickly, calculations stay accurate and business users can explore data confidently.

SCHEMAS AND DATA MODELLING IN POWER BI

Rachel Muriuki — Mon, 02 Feb 2026 11:42:15 +0000

PowerBI is a powerful tool for turning raw data into insightful reports.
In power BI tables show what data you have, relationships show how it connects and schema shows why it all fits together.

Data Model

A data model defines how your tables connect and interact in Power BI. It tells Power BI:
-Which tables hold raw facts eg. Sales
-Which tables describe those facts eg. Products

Fact Tables

They store measurable, quantitative data such as Sales transactions.
They contain numeric columns like Revenue, Quantity and contain foreign keys such as ProductID, CustomerID.

Dimension Tables

They contain descriptive textual information that adds context to facts
Examples of such columns:
-Customers
-ProductName
-Category

Relationships

PowerBI connects fact and dimension tables through relationships using shared keys like ProductID

Types oF relationships:

i)One-to-Many

One record in a dimension connects to many in a fact table
Example: One Customer can make many orders

ii)Many-to-One

Multiple records in one table are connected to a single record in another table
Example:Multiple cities belong to one state

iii)Many-to-Many

Used when both tables can have repeating values
Example: A student can take many courses and each course has many students

Schemas

Schema is how your tables are arranged and linked together

Star Schema

Snowflake Schema

Why Good Modelling Matters

A well designed schema ensures reports load quickly, calculations stay accurate and business users can explore data confidently.

Intoduction to MS Excel for Data Analytics

Rachel Muriuki — Sun, 25 Jan 2026 15:13:02 +0000

Microsoft Éxcel is one of the most beginner friendly tools for cleaning, visualizing and analyzing data.
In this article, we'll go through the basics of using Excel for data analytics.

Step 1: Importing and Viewing Data

Loading your dataset into Excel can be done into two ways;

Copy and paste data directly into a worksheet
Go to Data → Get Data → From Text/CSV → Select file to import data from → Load dataset

Each column represents a variable (like "ID", "Name" or "Gender") and each row represents a record.

Step 2: Cleaning Data

Raw data is usually messy, containing missing values, duplicates, data type errors, extra spaces or wrong alignment. Excel has simple tool to handle that:

Remove Duplicates:

Select your whole data(Ctrl+A) → Go to Data → Remove Duplicates

Data Types:

Select column → Right Click → Format Cells

*Unique numbers such as ID, Phone-numbers, Postal addresses should be formatted as Texts.

Alignment:

Numbers and Dates should be right-aligned while Texts should be left-aligned

Step 3:Exploring with Sorting and Filtering

Sorting:Arrange data in ascending/descending order.

Select column → Go to Home → Sort & Filter → Sort A to Z or Sort Z to A

Filtering:Allows one to display only the rows that meet a certain criteria.

Select column → Go to Home → Sort & Filter → Turn on Filter, then select drop-down arrow to show only relevant records

To replace blanks;
Select column → Go to Data → Under Data Tools → Click Data Validation → Set Allow to List → In the source box, type the values you want to allow(separated by commas) → Click OK

This prevents invalid data entry or flags errors during processing

Step 4: Using Basic Function Formulas

The formulas let you summarize and analyze data efficiently
Functions are grouped into;

i)Text Functions

They're used to manipulate, extract or clean text in cells.
=LEFT(text,num_chars)
num_chars returns the first characters from a text string

Other useful text functions: RIGHT(),MID(),LEN(),TRIM(),PROPER(),UPPER(),LOWER()

ii)Aggregate Functions

They perform calculations on a range of numbers.
=SUM(range) -Adds up all numbers in a range
=AVERAGE(range) -Finds the mean value of numbers in a range

Other useful aggregate functions: MAX(),MIN(),COUNT()

iii)Date & Time Functions

They allow you to calculate, format or extract information from dates and times
=TODAY() -Returns today's date
=DATEDIF(start_date,end_date,"unit") -Calculates the difference between two dates in days, months or years

Other useful functions: NOW(),DAY(),MONTH(),YEAR()

Step 5:Summarizing & Visualizaing the Data

Visual data such as charts help us visualize trends or comparisons
i)Select your dataset
ii)Go to Insert → PivotTable → From Table/Range → Click on a New Worksheet → OK
iii)On the PivotTable Fields → Drag fields into Rows, Columns and values
iv)Go to Insert → Recommended Charts
v)Choose from column, line, pie or bar charts

Getting Started with Git and GitHub using SSH Keys

Rachel Muriuki — Sun, 18 Jan 2026 08:31:17 +0000

If you're just starting your developer journey, learning Git and Github is one of the best decisions you can make.
In this article, i'll show you how to set up Git, configure it with your name & email and connect it securely to GitHub using SSH.

What is Git?

Git is a version control tool that runs locally on your computer.
It let's you;
-Track file changes
-Create 'checkpoints' called commits
-Work on branches (separate copy) without affecting the main project

What is GitHub

GitHub is a cloud platform where you can store your Git repositories online, share them with others and collaborate on projects.
It let's you;
-Upload your code(push)
-Download updates(pull)
-Work together with others in real time

1.Setting up Git

How to install and configure Git.

For Windows

Download Git (Install for Windows)
Run the installer and choose "Git default editor" as your terminal
Once installed, open Git Bash and type:

git --version

You should see something like:

git version 2.xx.x

For macOS

Open terminal and run:

git --version

If Git isn't installed, macOS will prompt you to install it via Xcode tools.

For Linux (Ubuntu/Debian)

Run:

sudo apt update
sudo apt install git

2.Configure your Git identity

Before you start using Git, tell it who you are by using your name and email which you used to create your GitHub account.
Run these commands replacing with your details:

git config --global user.name "Your Name"
git config --global user.email "Your Email"

To confirm your details:

git config --global --list

3.Connecting Git to GitHub Using SSH Keys

SSH(Secure Shell) Keys let you connect to GitHub without typing your password every time.
How to set up:

(i).Generate a New SSH key

ssh-keygen -t ed25519 -C "Your Email"

Press Enter to save it in the default path.

(ii).Generate SSH Agent

eval "$(ssh-agent -s)"

(iii).Add Key to the Agent

ssh-add ~/.ssh/id_ed25519

(iv).Copy your Public Key

cat ~/.ssh.id_ed25519.pub

Copy the whole key that starts with

ssh-ed25519

(v).Add the Key to GitHub

1.Go to GitHub → Settings → SSH and GPG keys
2.Click New SSH key
3.Paste your copied key
4.Save it

(vi).Test the Connection

ssh -T git@github.com

If you see:

Hi yourusername! You've successfully authenticated.

You're all set.

4.Using Version Control with Git

Initialize a Repository

Go to your project folder and intialize Git:

git init

This creates a hidden .gitfolder.

Track changes

Check what's changed:

git status

Add files to be tracked:

git add.

Commit (save) the changes:

git commit -m "Initial commit"

Push code to GitHub

1.Create a new repository on GitHub
2.Copy the repo's SSH url
3.Connect it to your local repo:

git remote add origin git@github.com:username/repo.git

4.Push your code:

git push -u origin main

Now your project is on GitHub.

Pull updates from GitHub

When you make changes on GitHub, pull them locally:

git pull origin main

See your commit history

To view your project's history:

git log

To see a short summary:

git log --oneline