Forem: Jeffrey Njoroge

Joins and Window Functions

Jeffrey Njoroge — Mon, 02 Mar 2026 16:24:11 +0000

Hello and welcome! If you're just starting your SQL journey, you've probably heard about Joins and Window Functions. They sound complicated, but don't worry - I'll explain everything in simple terms with plenty of examples.

Part 1: Understanding Joins

What Are Joins?

Imagine you have two separate notebooks:

-** Notebook A**: Contains customer names and their IDs

Notebook B: Contains customer orders with customer IDs

If you want to see "which customer ordered what," you need to connect these notebooks. That's exactly what Joins do, they connect tables based on common information (like customer ID).

A Real-Life Analogy

Think of joins like a wedding guest list:

Table 1: Guest names

Table 2: Meal preferences

The Join: Matching each guest to their meal choice using their name as the link

The Different Types of Joins
1. INNER JOIN - "Show me matches only."

INNER JOIN is like inviting only couples who both RSVP'd. It shows only records that exist in both tables.

Example Scenario: You want to see which customers have actually placed orders.

-- Find customers who have placed orders
SELECT Customers.CustomerName, Orders.OrderDate
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

This query shows ONLY customers who have at least one order. Customers without orders won't appear.

*2. LEFT JOIN *- "Show me everything from the left, and matching data from the right."

*LEFT JOIN * keeps ALL records from the first (left) table, and shows matching data from the second table. If there's no match, you see NULL (empty).

Example Scenario: You want to see ALL customers, even those who never ordered.

-- Show all customers and their orders (even if they have no orders)
SELECT Customers.CustomerName, Orders.OrderDate
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Now you'll see every customer. If a customer has no orders, the OrderDate column will be blank (NULL).

*3. RIGHT JOIN *- The Opposite of LEFT JOIN
Same as LEFT JOIN, but keeps ALL records from the right table.

-- Show all orders and their customers
SELECT Customers.CustomerName, Orders.OrderDate
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

4. FULL OUTER JOIN - "Show me everything, everywhere."
Shows ALL records from both tables, matching where possible.

-- Show all customers and all orders, regardless of matches
SELECT Customers.CustomerName, Orders.OrderDate
FROM Customers
FULL OUTER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

5. CROSS JOIN - "Combine everything with everything"
Creates all possible combinations between two tables.

-- Match every color with every product
SELECT Products.ProductName, Colors.ColorName
FROM Products
CROSS JOIN Colors;

Window Functions (Smart Calculations Without Losing Details)

What Are Window Functions?
Imagine you have a list of students and their test scores. You want to:

See each student's score
AND see the class average on the same row
AND see each student's rank

With regular SQL, this is tricky. But with Window Functions, it's easy! Window functions let you perform calculations across rows while keeping all your original data visible.

OVER()

Every window function uses OVER(), think of it as saying "look at this group of rows."

1.RANK() - Who's on Top?
Example: Rank employees by salary within their department

SELECT 
    first_name,
    department,
    salary,
    RANK() OVER (PARTITION BY department ORDER BY salary DESC) as department_rank
FROM employees;

Simple Explanation:

PARTITION BY department

means "reset the ranking for each department"

ORDER BY salary DESC means "highest salary gets rank 1"

The result shows each person AND their rank in one row

Remember: Every SQL expert started exactly where you are now. The key is practice, curiosity, and building projects that interest you. Happy querying! that it forks !!

How Analysts Translate Messy Data, DAX, and Dashboards into Action Using Power BI 📊➡️🚀

Jeffrey Njoroge — Sun, 08 Feb 2026 17:23:12 +0000

The Magic Behind the Screen: Your Data’s Journey from Chaos to Clarity 🧙‍♂️

Imagine As a retail manager, you face messy data daily: mismatched spreadsheets for sales, feedback, inventory, and deliveries 📈📊🗂️. Power BI transforms this chaos into a clear dashboard that shows what to reorder, which stores need help, and which promotions are winning 🎯—turning overload into action.

Let’s break down how analysts use this tool to turn overwhelming information into actionable business decisions, even if you’re just starting your data journey 🚶‍♂️➡️🏃‍♂️.

Step 1: Transforming Messy Data 🧹✨
The Problem: Real-world data arrives in fragments—different formats, sources, and quality levels. A sales team logs deals in Salesforce, finance tracks invoices in Excel, and marketing measures campaigns through Google Analytics 🤯.

The Power BI Solution: Analysts use Power Query (Power BI's data cleaning tool) to:

🔗 Combine these disparate sources
✏️ Fix inconsistencies (like "NY," "New York," and "N.Y." all meaning the same thing)
🗑️ Remove errors and duplicates

Real-World Impact:
A restaurant chain combined weather data ☁️, local event calendars 🎪, and historical sales to predict daily customer traffic. Result? They optimized staff schedules, reducing labor costs by 18% during slow periods while maintaining service during rushes 📉💰.

Step 2: Creating Business Logic with DAX 💪📈

The Problem: Raw data alone doesn't answer business questions. How do you calculate year-over-year growth? Customer retention rates? Profit margins per product category 🤔?

The Power BI Solution: DAX (Data Analysis Expressions) is Power BI’s formula language. Think of it as Excel formulas, but more powerful! Analysts create measures like:

Total Profit = SUM(Sales[Revenue]) - SUM(Sales[Cost]) ➕➖

Year-over-Year Growth = (This Year Sales - Last Year Sales) / Last Year Sales 📅➡️📅

Beginner-Friendly Tip: You don’t need to write DAX from scratch! Power BI offers quick measures that generate the formulas for you as you click through options 🖱️✨.

Real-World Impact:
An e-commerce company created a "customer lifetime value" DAX measure that identified their most profitable customer segments 🏆. They shifted marketing budget accordingly, increasing overall ROI by 34% 📊💸.

Step 3: Building Intuitive Dashboards💡📱

The Problem: Even cleaned data with calculations remains inaccessible if it’s trapped in complex tables. Decision-makers need insights at a glance 👀.

The Power BI Solution: Analysts design dashboards that tell visual stories:

🎯 Key Performance Indicators (KPIs) up top: Current sales vs. target
📊 Interactive charts in the middle: Sales by region (click on a region to drill down 🔍)
📋 Detailed tables at the bottom: Supporting data for context

Real-World Impact:
A healthcare provider created an emergency room dashboard showing patient wait times ⏱️, staff availability 👩‍⚕️, and treatment room status. Hospital administrators reduced average wait times by 22% by reallocating resources in real-time based on the dashboard insights 🏥✅.

Step 4: From Insight to Action – The Decision Loop 🔄⚡

The Closed Loop: Power BI’s real power emerges when dashboards don’t just inform decisions but trigger actions:

Monitor 👀: A shipping company dashboard shows delivery delays spiking in the Midwest 🚚⚠️
Analyze 🔍: Clicking through reveals the issue—a specific warehouse is understaffed 👷‍♂️❌
Act ✅: The manager immediately reallocates staff from another location 🔄👷‍♀️
Review 📈: Next week, the dashboard confirms normal operations have resumed 🟢

Getting Started: Your First Small Win 🏁🎉

You don’t need to tackle company-wide data immediately. Try this beginner project:

Start with one data source 📂 (your team’s Excel file)
Ask one business question 🤔 (“How have our sales trends looked this quarter?”)
Create one visual 📉 (a simple line chart showing sales over time)
Share one insight 💬 (“Sales dip every Thursday—should we run Thursday promotions?”)

The Big Picture: Data Democratization 🌍🤝

When analysts master Power BI, they become translators between raw data and business strategy. They empower everyone—from executives to frontline employees—to make data-driven decisions 📊➡️🤝. The cashier 💁‍♂️, the warehouse manager 🏢, and the CEO 👔 all look at the same version of truth and can act accordingly.

The next time you see a polished Power BI dashboard, remember the journey:
Messy data 🗑️ → cleaned and combined 🧼 → enhanced with calculations 🧮 → visualized intuitively 🎨 → acted upon ✅ → measured again 🔄.

This continuous cycle turns information from a burden into your organization’s most strategic asset 💎.

Your Takeaway 🎁✨
Power BI skills aren’t about fancy charts or complex formulas—they’re about asking better questions and creating clearer answers. Whether you’re looking to reduce costs, increase revenue, or improve customer satisfaction, the path starts with transforming data into decisions 🛤️.

And that’s a skill that begins with one dashboard, one insight, and one action at a time 🚀📊✅.

Mastering Data Modeling in Power BI

Jeffrey Njoroge — Mon, 02 Feb 2026 06:48:30 +0000

A good Power BI report needs a strong data model. The data model controls how fast it runs, if the math is right in the data, and how easy it is to use. This guide will teach you the basics of data modeling to help you build better reports.

Why Data Modeling Matters

Before we diving in, let me show you where poor data model leads to:

Slow report performance and sluggish interactions
Inaccurate calculations and misleading insights
Complex DAX formulas that are hard to maintain
Frustrated business users who lose trust in the data

Meet the Two Key Players of data model

1.📊 Fact Tables("What Happened")

This is your EVENT LOG. It records things that happen.
Think of it like a grocery receipt: It records actions (sales) and numbers (quantity, price).

| Date       | Product      | Quantity | Price |
|------------|--------------|----------|-------|
| Jan 10     | Apples       | 5        | $10   |
| Jan 10     | Bread        | 2        | $6    |
| Jan 11     | Apples       | 3        | $6    |

2.📐 Dimension Tables ("Who/What/When")

These are your LOOKUP TABELS. They describe things.
Think of them like product information sheets:

Product Table:
| ProductID | ProductName | Category | Color  |
|-----------|-------------|----------|--------|
| 1         | Apples      | Fruit    | Red    |
| 2         | Bread       | Bakery   | Brown  |

Date Table:
| DateID | Date       | Day   | Month | Year |
|--------|------------|-------|-------|------|
| 101    | Jan 10     | Monday| Jan   | 2024 |

Now that we are done with the Two Key Players let look on the other players:

🌟The Star Schema
This is Power BI's favorite way to organize data. It's called a "star" because it looks like one with a fact table at the center.

example:

           [Date Dimension]
                ↓
[Product Dimension] → [Sales Fact Table] ← [Customer Dimension]
                ↑
        [Store Dimension]

Key idea💡: One central table (the fact table) with all your measurement numbers, surrounded by descriptive tables (dimensions) that connect directly to it.

Real Example: Coffee Shop Sales

Fact_Sales Table (The Main Counter):

| ReceiptID | DateID | ProductID | CustomerID | CupsSold | TotalPrice |
|-----------|--------|-----------|------------|----------|------------|
| 1001      | 20240115 | P101     | C205       | 2        | $8.50      |
| 1002      | 20240115 | P102     | C206       | 1        | $4.25      |

Why Power BI Loves the star scheme🌟

It thinks in stars - Built to work best with this pattern
Faster calculations - Less "travel time" between tables
Easier DAX formulas - Clearer relationships mean simpler code
Better performance - Optimized for this structure

The Snowflake Schema❄️

In a snowflake schema, your dimension tables get further split into more tables. It's called "snowflake" because when you draw it, the connections spread out like a snowflake's branches.

example:

[Supplier Table] → [Category Table] → [Product Table] → [Sales]
                    ↓
               [Sub-Category Table]

key idea💡:

Product table connects to category table
Category table connects to supplier table
Each table specializes in one type of information

Real Example: An Online Store

Dim_Product Table:
| ProductID | ProductName  | CategoryID | SupplierID |
|-----------|--------------|------------|------------|
| 101       | Blue T-Shirt | C1         | S5         |

Dim_Category Table:
| CategoryID | CategoryName |
|------------|--------------|
| C1         | Clothing     |

Dim_Supplier Table:
| SupplierID | SupplierName | CountryID |
|------------|--------------|-----------|
| S5         | TeeCo Inc    | USA1      |

When Might You See a Snowflake?

When connecting to existing databases (some are already built this way)
When multiple fact tables share the same dimension (like having one Date table for Sales AND Inventory)
When dimensions are extremely large (millions of rows )

Relationships: How Your Data Tables "Talk" to Each Other

Think of relationships in Power BI as introductions between your tables - they're how your data gets to know each other and work together!

Real-Life Example: Grocery Shopping

RECEIPT (Fact table)
| ItemID | StoreID | Price |
|--------|---------|-------|
| 1      | 5       | $3.99 |

STORE LIST (Dimension table)  
| StoreID | StoreName | City     |
|---------|-----------|----------|
| 5       | FreshMart | Boston   |

ITEM LIST (Dimension table)
| ItemID | ItemName  | Category |
|--------|-----------|----------|
| 1      | Apples    | Fruit    |

Beginner's Best Practices

Always use a Date table (Power BI needs this for time calculations)
Keep it simple - star schema is usually best
Name tables clearly: "Sales" not "Table1"
Connect with single arrows (from dimension → fact table)
Hide confusing columns from the report view

Don't try to build the perfect model on day one. Start with:

One fact table (your main event)
Two dimension tables (Date + one other, like Product)
Simple reports

That all folks!! for any question leave a comment below and follow me on github:https://github.com/JeffreyNjoroge Happy learning!!

Introduction to MS Excel for Data Analytics: Your First Step into Data

Jeffrey Njoroge — Mon, 26 Jan 2026 06:36:57 +0000

Have you ever stared at a spreadsheet full of numbers and wondered what stories those numbers could tell? Or felt overwhelmed by sales figures, survey results, or expense reports? Welcome to the world of data analytics—and you might be surprised to learn that you probably already have the perfect tool to get started: Microsoft Excel.
Excel isn't just for creating lists or basic budgets. It's a powerful, accessible gateway to understanding data.You can organize, explore, and uncover insights in data Here is how a excel looks like:

Why Start with Excel?

Before diving into complex software, Excel offers a familiar environment. Its grid of rows and columns is intuitive, and its most powerful features are hidden in plain sight, waiting to be used. For basic data analysis, Excel helps you:

Clean up messy data (like removing duplicates or fixing formatting).
Summarize information quickly (like finding totals, averages, or counts).
Spot patterns and trends (seeing what's going up, down, or staying the same).
Answer specific questions (like "What was our best-selling product last quarter?").

Your First Analytical Toolkit: Four Essential Excel Features
Let's break down the core features that turn Excel from a simple spreadsheet into an analysis engine.

Sorting & Filtering:
Sorting lets you rearrange this list alphabetically, by date, or from highest to lowest value in one click. Filtering is like putting on a pair of glasses that only show you what you want to see—like all purchases from a specific city or all transactions above $100. It instantly hides the irrelevant data so you can focus.
What you would see:

When you select your data and go to the Data tab, you'll see these buttons:

Sort buttons look like A→Z and Z→A arrows. The Filter button looks like a funnel.

Formulas: Excel’s Calculation Powerhouse
Formulas are how you ask Excel to do math for you. They start with an equals sign (=). Don't be intimidated! You only need a few to begin:

=SUM: For example, =SUM(l2:l633) - Adds up all numbers in cells l2 through l633.

which gives a total of:

=AVERAGE(l1:l633): Calculates the mean of those numbers,For example:

Which gives a total off:

=MAX(l2:l633) or =MIN(l2:l633): Finds the highest or lowest value.

Example:=MAX

And the answer is:

Example 2:=min

And the answer is:

PivotTable Creation Process
This is Excel's most famous analytical tool, and for good reason. A PivotTable might sound complex, but think of it as a dynamic summary report you create by dragging and dropping.
Got a year's worth of sales data? In minutes, you can use a PivotTable to:

Break down sales by month and region.
Compare product performance.
Count how many transactions each salesperson made.

Charts: Seeing the Story

A picture is worth a thousand numbers. Excel’s charts transform rows of data into visual stories. A simple line chart can show a trend over time. A bar chart can compare different categories. Seeing your data visually often reveals patterns that are easy to miss in a table.

A Simple Analytics Workflow in Excel
Let's follow a real-world example. You have a list of a café's weekly sales.

Clean: Use Remove Duplicates and Text to Columns to ensure data is tidy.
Explore: Sort by "Revenue" to see the best-selling days. Filter to look at only weekend sales.
Summarize: Use a PivotTable to calculate total revenue per beverage type.
Visualize: Create a pie chart from that PivotTable to see which drink is most popular.
Ask Questions: Write a formula like =AVERAGE to find the average weekday sale, then compare it to the weekend average.

In 15 minutes, you've gone from a raw list to knowing your top product and your busiest times.

The Bottom Line
MS Excel is the most widely used data analytics tool in the world for a reason. It’s a forgiving, powerful playground where you can develop your analytical mindset—learning to ask questions of data and find answers. The skills you build here, from logical thinking to cleaning data, are the very foundation of all data analysis.

You don't need to learn everything at once. Start small. Open a dataset you care about, and try to answer one simple question using Sort or a single formula. You'll be amazed at what you can discover.

Welcome to the start of your data journey!!!!!!!

Git 101: Your Friendly Guide to Version Control

Jeffrey Njoroge — Sun, 18 Jan 2026 08:37:18 +0000

What is Version Control Anyway?
Imagine you're writing an important document. You save version after version: essay.doc, essay_final.doc, essay_really_final.doc, essay_NO_REALLY_FINAL_v2.doc. Sound familiar? Version control is like a time machine for your code that saves you from this chaos. Git is simply a tool that manages these versions for you.

The Git Mindset: Your Project's Safety Net
Git creates a safety net for your projects by tracking every change. Made a change that broke everything? No problem—jump back to when it worked. Want to try a crazy idea? Create a separate "branch" to experiment without affecting the stable version.

Getting Started: Your First Repository
A repository (or "repo") is just a fancy word for a project folder that Git is tracking.

The Three Stages of Git

Think of Git having three areas:

Working Directory: Your actual file
Staging Area: A "preview" of what you're about to save
Repository: Where Git permanently stores your changes

Tracking Changes: The Basic Workflow

Step 1: Check Your Status
Always know what's happening:

This shows what files are modified, staged, or untracked.

Step 2: Add Changes to Staging

Step 3: Commit Your Changes
A commit is a saved snapshot with a message describing what changed:

Pro tip: Write clear commit messages! Future you will thank past you.

Step 4: View Your History

This shows your commit history - your project's timeline.

Working with Remote Repositories (GitHub/GitLab)

Pushing Your Code

Pushing means uploading your commits to a remote server (like GitHub):

Pulling Changes

Pulling downloads changes from the remote server to your computer:

Always pull before you start working to get the latest changes!

Best Practices for Beginners

Commit Often: Small, frequent commits are better than huge ones
Write Good Messages: Use the present tense ("Add feature" not "Added feature")
Pull Before You Push: Avoid merge conflicts
Use Branches: Keep your main branch stable
Don't Git Push --force: Unless you really know what you're doing

Your First Project Walkthrough
Here's what a typical workflow looks like:

Why Bother Learning Git?

Collaboration: Multiple people can work on the same project
Backup: Your code lives on GitHub's servers
Experimentation: Try ideas safely in branches
Portfolio: GitHub becomes your coding resume

Next Steps

Once you're comfortable with these basics, explore:

Branching and merging
Handling merge conflicts
Git ignore files (.gitignore)
Pull requests on GitHub

Remember: Every Git expert was once a beginner who accidentally deleted something. Don't be afraid to make mistakes—that's how you learn! The most important command to know is git status. When in doubt, check your status.