DEV Community: Githinji Victor Maina

🚀 SQL Joins and Window Functions Explained (With Real Examples for Data Analysts)

Githinji Victor Maina — Wed, 04 Mar 2026 11:21:36 +0000

Learn SQL Joins and Window Functions with clear explanations, real examples, ranking queries, running totals, and performance tips.

If you’re serious about SQL, whether for Data Analysis, Backend Development, or Database Engineering ,you must understand two advanced concepts:

SQL Joins
SQL Window Functions

These features turn simple queries into powerful analytical tools.

This guide explains:

All major types of SQL JOINs
How window functions actually work
The difference between GROUP BY and window functions
Ranking, running totals, moving averages
Performance considerations

🔎 What Are SQL Joins?

SQL JOINs allow you to combine data from multiple tables using a related column.

Relational databases store data in separate tables to reduce redundancy. Joins reconstruct meaningful relationships.

Example tables:

Relationship:

1️⃣ INNER JOIN (Most Common SQL Join)

Returns only matching rows in both tables.

Use Case:
Get customers who have placed orders.

Important:
Rows without matches are excluded.

2️⃣ LEFT JOIN (Keep All Rows from Left Table)

Returns:

All customers
NULL for customers without orders

Use Case:
Find inactive customers.

3️⃣ RIGHT JOIN

Opposite of LEFT JOIN.

Less common because LEFT JOIN can usually replace it.

4️⃣ FULL OUTER JOIN

Returns all rows from both tables.

Includes:

Unmatched customers

Unmatched orders

5️⃣ SELF JOIN in SQL

Join a table to itself.

Example: Customers with the same membership tier.

⚡ SQL Window Functions Explained

Window functions perform calculations across related rows without collapsing them.

This is the major difference:

Window Function Syntax

1️⃣ ROW_NUMBER() – Ranking Without Ties

Use Case:

Pagination
Top-N queries

2️⃣ RANK() vs DENSE_RANK() (Common Interview Question)

Difference:

If two rows tie for first place:

RANK() → 1, 1, 3

DENSE_RANK() → 1, 1, 2

3️⃣ PARTITION BY (Like GROUP BY, But Smarter)

This shows:

Each individual order

The total spending per customer

Without grouping away details

4️⃣ Running Total (Cumulative Sum)

Used in:

Sales dashboards
Revenue tracking
Financial analysis

5️⃣ Moving Average (Advanced Window Function)

Perfect for:

Time-series analysis
Trend detection
Forecasting

🆚 Joins vs Window Functions

They solve different problems:

Example combining both:

Here:

JOIN connects tables
GROUP BY aggregates
Window function ranks

🧠 When Should You Use Window Functions?

Use them when you need:

Ranking
Running totals
Percentiles
Per-row analytics
Aggregates without losing detail

Avoid them when:

A simple GROUP BY is enough
Dataset is massive and not indexed properly

⚙️ Performance Tips

Index join columns
Avoid unnecessary joins
Use EXPLAIN plans
Partition carefully
Avoid large window frames when unnecessary

🎯 Final Takeaway

If you understand SQL JOINs, you can connect data.

If you understand SQL Window Functions, you can analyze data deeply.

Master both, and you move from writing queries to building analytics systems.

Data Storytelling in Power BI: Moving Beyond "Correct" to "Useful"

Githinji Victor Maina — Sun, 08 Feb 2026 10:31:03 +0000

In the world of data analytics, there's a massive gap between a correct calculation and a useful insight. You can write the most efficient, elegant DAX measure in the world, but if your stakeholders can’t use it to make a call, that data stays operationally silent.

As analysts, our real job is translation. We take the chaos of raw data, apply the logic of DAX, and produce a narrative that actually drives business action. Here is how I approach mastering that translation layer in Power BI.

Why Translation Is the Real Goal
Businesses don't actually care about star schemas or filter context—they care about outcomes.

The problem: Raw data is almost always siloed, inconsistent, and messy.
The goal: Turn that mess into answers for high-stakes questions: Which product line should we kill? Where is our marketing spend being wasted?

Without a clear translation process, dashboards become "data graves", places where information goes to be admired but never acted upon.

The Three Pillars of Translation
To move from messy data to business action, I focus on three distinct phases.

1. The Clean-Up (Power Query)
Before you can apply any logic, the data has to be structured and reliable. This is where we quietly do the heavy lifting that determines if the final insights are even trustworthy.

Typical tasks I focus on:

Removing the noise: Stripping out columns that don’t contribute to the analytical goal.
Standardization: Making sure "USA," "U.S.," and "United States" are treated as the same entity.
Validation: Fixing broken data types and handling missing values before they break your visuals.

2. The Logic Layer (DAX)
Think of DAX as the grammar of your data story. It defines how your metrics behave across filters, time periods, and business rules.

I generally focus on these patterns:

Time intelligence: Handling YTD, MoM, and YoY comparisons to show growth or decline.
Conditional logic: Automatically segmenting customers into categories like "high risk" or "low risk."
Dynamic measures: Ensuring metrics respond correctly to whatever slicers a user touches.

Without solid DAX, a dashboard might look interactive, but it won't answer the "why" behind the numbers.

3. The Action Layer (The Dashboard)
Dashboards aren't just reports, they’re decision interfaces. An effective Power BI dashboard should:

Put the KPIs front and center.
Use a clear visual hierarchy to guide the user's eye.
Keep cognitive load low so the user isn't overwhelmed.
Signal exactly where action is needed. I often use traffic-light indicators (red, yellow, green) because they help users instantly grasp performance health and urgency.

A Real-World Example: Reducing Customer Churn
Imagine you’re looking at a telecom dataset with thousands of rows of call-usage data.

The Messy Data
You start with mismatched timestamps, missing customer IDs, and service plan names that don't match up across tables.

The DAX Translation
Instead of just counting customers, we define "at-risk" users,those who haven't made a call in over 30 days but still have an active subscription.

Code snippet

This measure is built to respect filter context, making sure the calculation is right for every individual customer in the view.

The Action
When you put this on a map, the dashboard might reveal a cluster of at-risk users in a specific area, like Kiambu County. This allows marketing to stop guessing and launch a localized retention campaign that actually saves those accounts.

Common Traps to Avoid
1. "More Data = More Value"
The Mistake: Throwing every available field into a report. The Reality: This just causes cognitive overload. If a user has to hunt for the insight, your translation has failed.

2. Ignoring Context in DAX
The Mistake: Writing static measures that don't react to filters. The Reality: DAX's power comes from filter context. Your measures have to adapt as users drill down into regions, dates, or categories.

Best Practices I Live By
Design for the 5-Second Rule: A user should grasp the main point of a visual within five seconds.

Annotate your DAX: Use comments (//) to document your logic. You'll thank yourself during maintenance six months from now.

Focus on trends, not just totals: A single number is just a fact; a trend over time is a narrative.

Final Thoughts
The ability to translate messy data into action is what separates a technician from a consultant. When you focus on clean structures, sound logic, and decision-driven design, you turn Power BI from a simple reporting tool into a strategic asset.

What part of the workflow do you find the most challenging; the initial data cleaning or getting the DAX logic right? Let’s talk about it in the comments.

Why Your Power BI Reports Feel Slow (And How to Fix It)

Githinji Victor Maina — Mon, 02 Feb 2026 14:29:04 +0000

Introduction:
It’s Not Just About Pretty Charts If you’re anything like me, when you first open Power BI, the temptation is to jump straight into the canvas and start dragging and dropping charts. We want to see the insights immediately. But this week, I learned that the real magic doesn't happen on the report canvas, it happens in the Model View.

We’ve been working with hospital and crop production datasets, and it became clear very quickly: if you don’t structure your data correctly "under the hood," your report will be a nightmare to build and painfully slow to load. This structure is what we call Data Modelling.

The Building Blocks: Facts and Dimensions Before we talk about schemas, we have to talk about the two types of tables that live in a model. When I first looked at our hospital data, it was easy to get overwhelmed. But data modelling simplifies this by splitting things into two buckets:

Fact Tables (The "What"): These are the busy tables. They capture things that happen. In our hospital example, a Patient Admission is a fact. It has dates, patient IDs, and numbers we can count. These tables are usually long and narrow because events happen constantly.
Dimension Tables (The "Who, Where, and When"): These tables describe the facts. They give context. For example, a Patients table tells us the name and age of the person admitted. A Locations table tells us which county the crops were grown in.

The Star of the Show: The Star Schema This is the gold standard in Power BI. Imagine a star shape. In the center, you have your massive Fact Table (like Crop_Yields). Radiating out from it, like points on a star, are your Dimension Tables (like Counties, Crop_Types, Years).

Visualizing the workflow: This is the process I followed to manually set up relationships for the hospital dataset.

Why do we like this? Because it’s efficient. When you filter by a specific "County," Power BI only has to make one hop from the Dimension table to the Fact table to give you the numbers. It’s simple, clean, and fast.

The Snowflake Schema: The Complicated Cousin Sometimes, your dimensions have dimensions. For example, imagine you have a Products table, which links to a Sub-Category table, which links to a Category table. Visually, this looks like a snowflake branching out. While this saves space in a database, it’s annoying for Power BI. To get an answer, the software has to run through a chain of relationships. It’s like playing a game of "Telephone" ,it takes longer and uses more processing power. The Verdict? Avoid snowflaking if you can. Flatten your dimensions to keep that Star shape.

Why Should You Care? You might be thinking, "Can't I just load one giant table with everything in it?" You could, but you shouldn't.

Performance: A good model makes your report snappy. A bad one makes users wait ten seconds for a graph to load.
DAX Simplicity: We started looking at DAX aggregation and filter functions this week. If your model is a mess, your DAX formulas have to be incredibly complex to work around it. A clean Star Schema allows you to write simple, elegant DAX.

Conclusion Data modelling is the invisible foundation of a great report. It’s the difference between a dashboard that works and one that crashes. As I continue diving into DAX and visualization, I’m realizing that time spent fixing the model is never time wasted.

From Chaos to Clarity: How I Built a Dynamic Sales Dashboard in Excel

Githinji Victor Maina — Sun, 25 Jan 2026 22:55:05 +0000

Introduction
In the world of data science, we often get excited about Python, SQL, and Machine Learning. But the truth is, the world runs on spreadsheets. Microsoft Excel remains the most widely used tool for business intelligence and rapid prototyping.

For my latest project at the Lux Tech Academy, I was challenged to take a raw, messy sales dataset and transform it into a "Mastery Dashboard." The goal? To answer complex business questions like "Which sales reps are giving away too much discount?" and "What happens to our profit if inflation hits 10%?"

Here is how I built it,from data cleaning to advanced scenario modeling.

Step 1: The Clean Up (Data Preparation)
Real-world data is never clean. My dataset had 600+ rows of transactional data, but it was full of errors.

Missing Data: I found blank entries for cities and salespersons. I used Excel's Go To Special > Blanks feature to identify and fill these gaps efficiently.

Negative Prices: Some unit prices were negative (a common data entry error). I fixed this using the =ABS() function to convert them to positive values.

Lead Time: To measure shipping efficiency, I created a new column LeadTimeDays using the formula =RequiredDate - OrderDate.

Step 2: The Logic (Advanced Formulas)
To get real insights, raw data isn't enough. I needed to calculate profitability. I used structural formulas to derive:

Gross Revenue: Price × Quantity × (1 - Discount)

Margin %: Gross Profit / Gross Revenue

This allowed me to see not just how much we were selling, but how much money we were actually making.

Step 3: The "What-If" Machine (Scenario Modeling)
This was the most powerful part of the project. I didn't want a static report; I wanted a tool that could predict the future.

I built a Scenario Control Panel that allows a user to type in an "Inflation Rate" or a "Discount Cap." By linking my data columns to these input cells using absolute references (e.g., $AF$2), the entire dataset updates instantly.

Scenario A: If inflation rises by 5%, our costs adjust automatically.

Scenario B: If we cap discounts at 20%, we can see exactly how much revenue we save.

Step 4: Visualizing the Story (The Dashboard)
Finally, I brought it all together in an interactive dashboard.

Slicers: I added buttons to filter data by Channel (Retail vs. Online) and Product Category.

KPI Cards: Big, bold numbers at the top show Total Revenue, Margin %, and On-Time Delivery rates.

Outlier Detection: I built a chart specifically to catch Sales Reps who were giving discounts higher than 20%, helping management spot revenue leakage.

Conclusion
This project reinforced that Excel is more than just rows and columns—it is a powerful decision-support system. By combining data cleaning, logic formulas, and interactive dashboarding, I was able to turn a CSV file into a business solution.

A Beginner’s Guide to Git Bash and GitHub: Pushing, Pulling, and Version Control

Githinji Victor Maina — Sun, 18 Jan 2026 09:43:52 +0000

Introduction
If you are starting your journey in tech, you have probably heard the words "Git" and "GitHub" thrown around constantly. Maybe you have even installed them but aren't quite sure how they actually work.

In this guide, I will walk you through the absolute essentials: how to set up your environment, track changes, and push your code to the internet. By the end of this article, you will understand the core workflow that professional developers use every day.

What is Version Control?

Before we type any commands, we need to understand Version Control.

Imagine you are writing a university essay. You might have files like:

Essay_Final.docx
Essay_Final_v2.docx
Essay_Final_REALLY_DONE.docx

This is messy. Git solves this by tracking changes to a single file over time. It allows you to save "snapshots" (called commits) of your project history. If you make a mistake, you can simply revert to an earlier snapshot.

Git: The tool installed on your laptop (Local).
GitHub: The website where you upload your code (Remote).

Step 1: Configuration (The One-Time Setup)
Since you have already installed Git Bash, the first thing you must do is introduce yourself to Git. This ensures that every "save" you make is tagged with your name.

Open Git Bash and run these two commands:

To check if it worked, type:

Step 2: Initializing a Repository
To start tracking a project, you need to initialize it. Open Git Bash in your project folder and run:

This command creates a hidden .git folder. It tells Git: "Start watching this directory for changes.

Step 3: The Staging Area & Committing
Git doesn't save changes automatically. You have to tell it exactly what to save. This is a two-step process: Staging and Committing.

1. Check Status
First, let's see which files Git has noticed are new or changed:

If your text is red, it means the file is not yet staged.

2. Stage Changes (git add)
Think of this as putting a letter in an envelope. You are preparing the file to be sent.

(The . tells Git to add ALL changed files at once).
3. Commit Changes (git commit)
Now, we seal the envelope. This saves the snapshot permanently.

Step 4: Connecting to GitHub
Now that your code is saved locally, let's put it on the internet.

Go to GitHub.com and create a new repository (click the + icon).
Copy the URL provided (it looks like https://github.com/username/repo.git).
Back in your terminal, link your local folder to GitHub:

Pushing Code
"Pushing" uploads your commits from your computer to GitHub.

origin: The nickname for your online repository.
main: The branch you are working on.

If you refresh your GitHub page, you will now see your files!

Step 5: Pulling Changes
If you work on a team, or if you edit a file directly on the GitHub website, your laptop will be "behind" the online version.
To update your local computer, we use Pull:

This downloads the latest changes and merges them into your code automatically.

Bonus: Branching Basics
As you get advanced, you won't always work on the **main **code directly. You will use Branches.
A branch is like a parallel universe where you can test new features without breaking the main project.

Create a branch: git branch feature-login
Switch to it: git checkout feature-login
Merge it back: git merge feature-login

Summary of Commands
Here is a cheat sheet for your daily workflow:

Conclusion
Version control is a superpower for developers. It saves you from losing work and helps you collaborate with others. Start by mastering add, commit, and push, and you will be well on your way to becoming a professional developer.
Happy Coding!