DEV Community: jim kinyua

Understanding SQL Joins and Window Functions

jim kinyua — Mon, 02 Mar 2026 16:00:25 +0000

If you've ever stared at a SQL query and wondered what PARTITION BY actually does, this article is for you. I'm going to break down two of the most important SQL concepts Joins and Window Functions

PART 1: SQL JOINS

What is a Join?

A Join is how you combine data from two or more tables. Think of it like connecting two spreadsheets using a shared column like a customer ID that appears in both a Customers table and an Orders table.

The Tables We'll Use

Customers:

id	name
1	Alice
2	Bob
3	Carol

Orders:

id	customer_id	amount
1	1	500
2	1	300
3	2	200

Notice that Carol (id = 3) has no orders, and all orders belong to either Alice or Bob. Keep that in mind. it's going to matter a lot when we look at how different JOINs behave.

Understanding Left and Right Tables

Before we look at any specific JOIN type, there's one concept you need to lock in first. Every JOIN has a Left table and a Right table, and the difference matters.

The table written after FROM is the Left table
The table written after JOIN is the Right table

SELECT customers.name, orders.amount
FROM customers          -- LEFT table
LEFT JOIN orders        -- RIGHT table
  ON customers.id = orders.customer_id;

This isn't just terminology. SQL uses this position to decide which table's rows get priority when there's no matching data on the other side.

INNER JOIN

An INNER JOIN returns rows where there is a match in BOTH tables.

SELECT customers.name, orders.amount
FROM customers
INNER JOIN orders ON customers.id = orders.customer_id;

Result:

name	amount
Alice	500
Alice	300
Bob	200

Carol is gone. She has no orders, so she doesn't appear. INNER JOIN only shows you where both sides match.

LEFT JOIN

A LEFT JOIN returns ALL rows from the left table, and matches from the right. Where there's no match, you get NULL.

SELECT customers.name, orders.amount
FROM customers
LEFT JOIN orders ON customers.id = orders.customer_id;

Result:

name	amount
Alice	500
Alice	300
Bob	200
Carol	NULL

Carol is back. She has no orders, so her amount is NULL. Use LEFT JOIN when you don't want to lose rows from your main table.

RIGHT JOIN

The opposite of LEFT JOIN. All rows from the right table are kept, and matches come from the left. Less common, but useful in certain situations.

FULL OUTER JOIN

Returns all rows from both tables, with NULLs where there's no match on either side.

SELECT customers.name, orders.amount
FROM customers
FULL OUTER JOIN orders ON customers.id = orders.customer_id;

Use this when you want to see unmatched records from both sides.

A Simple Way to Remember Joins

INNER = Only the overlap between the two tables
LEFT = All of left + whatever matches on the right
RIGHT = All of right + whatever matches on the left
FULL = Everything from both, matched or not

Key Takeaways for Joins

Joins combine tables using a shared column
INNER JOIN is the most common — only matched rows
LEFT JOIN is your friend when you don't want to lose rows from your primary table
Always think: "What happens to rows with no match?" this is what determines which join to use

PART 2: WINDOW FUNCTIONS

What is a Window Function?

A window function performs a calculation across a set of rows related to the current row without collapsing them like GROUP BY does.

This is the key difference:

GROUP BY: Collapses multiple rows into one result per group
Window Function: Keeps all rows, and adds a calculated column to each one

Think of it like this. for each row, you "open a window" into the data, do a calculation across that window, and write the result back into that row without losing any rows.

The Syntax

function() OVER (PARTITION BY column ORDER BY column)

OVER is what makes it a window function. Without OVER, it's just a regular function.

The Table We'll Use

Sales:

employee	dept	sales
Alice	IT	500
Bob	IT	300
Carol	HR	400
Dave	HR	600

ROW_NUMBER() — Rank Rows Within a Group

SELECT employee, dept, sales,
  ROW_NUMBER() OVER (PARTITION BY dept ORDER BY sales DESC) AS rank
FROM sales;

Result:

employee	dept	sales	rank
Alice	IT	500	1
Bob	IT	300	2
Dave	HR	600	1
Carol	HR	400	2

*What happened here?
*
PARTITION BY dept splits the data into two groups: IT and HR.
ORDER BY sales DESC sorts each group from highest to lowest sales.
ROW_NUMBER() assigns 1, 2, 3... to each row within that group.

Both Alice and Dave get rank 1 — because the ranking restarted for each department. That's the power of PARTITION BY.

RANK() vs DENSE_RANK() vs ROW_NUMBER()

These three are easy to confuse. Here's the difference when two people tie:

SELECT employee, sales,
  ROW_NUMBER() OVER (ORDER BY sales DESC) AS row_num,
  RANK()       OVER (ORDER BY sales DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY sales DESC) AS dense_rnk
FROM sales;

employee	sales	row_num	rnk	dense_rnk
Dave	600	1	1	1
Alice	500	2	2	2
Carol	500	3	2	2
Bob	300	4	4	3

ROW_NUMBER: Always unique. No ties. Just counts 1, 2, 3, 4.
RANK: Ties get the same number, then skips. Alice and Carol both get 2, next is 4.
DENSE_RANK: Ties get the same number, but does NOT skip. Next after 2 is 3.

SUM() OVER()

SELECT employee, dept, sales,
  SUM(sales) OVER (PARTITION BY dept) AS dept_total
FROM sales;

Result:

employee	dept	sales	dept_total
Alice	IT	500	800
Bob	IT	300	800
Dave	HR	600	1000
Carol	HR	400	1000

Every row still exists. But now each row also shows the total for their department. With GROUP BY, you'd lose the individual rows. With SUM() OVER(), you keep them all.

LAG() and LEAD()

LAG() lets you look at the value from the previous row. LEAD() looks ahead to the next row. Perfect for comparing month-over-month changes.

SELECT employee, sales,
  LAG(sales)  OVER (ORDER BY sales) AS previous_sales,
  LEAD(sales) OVER (ORDER BY sales) AS next_sales
FROM sales;

Result:

employee	sales	previous_sales	next_sales
Bob	300	NULL	400
Carol	400	300	500
Alice	500	400	600
Dave	600	500	NULL

The first row has no previous row, so it's NULL. The last row has no next row, so that's NULL too.

ROWS BETWEEN

This is more advanced, but incredibly useful for moving averages. It is mostly used to control the Size of Your Window

SELECT employee, sales,
  AVG(sales) OVER (
    ORDER BY sales
    ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
  ) AS moving_avg
FROM sales;

This calculates the average of the current row and the one before it. Change the numbers to control how many rows back or forward your window looks.

Key Takeaways for Window Functions

Window functions do NOT collapse rows — this is the biggest difference from GROUP BY
OVER() is what makes it a window function
PARTITION BY is like GROUP BY but keeps all rows
ORDER BY inside OVER() controls the order within the win

How to Turn Messy Data, DAX Headaches, and Ugly Dashboards into Decisions Using Power BI

jim kinyua — Mon, 09 Feb 2026 15:20:17 +0000

Let’s be honest, no dataset has ever arrived on an analyst's desk completely clean. Not once. In the real world, data usually shows up in a chaotic state. Dates saved as text, revenue strings mixed with currency symbols, twelve different spellings for the same county, blanks that should be zeros, and zeros that are actually missing values.

Inevitably, this is followed by a stakeholder asking, "Can you build a dashboard by Friday?" The answer is usually "sure," but the gap between receiving that raw data and delivering a "wow" dashboard is where the actual work happens. It is a journey that moves from invisible data engineering to strategic business storytelling.

The Invisible Foundation: Power Query

The first reality of analytics is that building the dashboard is often the easiest part; dragging charts onto a canvas takes minutes. The real labor lies in ensuring those charts tell the truth, and that work begins in Power Query. This is the "cleaning room" where we fix data types (because revenue should never be text), standardize categories, remove duplicates, and handle null values properly. It is also where we create derived fields, such as "Age Group" or "Price Band," to make analysis easier later on.

If you skip this step, you will inevitably try to fix data quality issues using DAX measures. This is a mistake. DAX is a calculation engine, not a cleanup tool, and it will punish you with slow performance and overly complex formulas if your data isn't prepared correctly.

The Backbone: Data Modeling

Most beginners make the mistake of dumping all their data into one massive, flat table. While this might work for simple spreadsheets, Power BI’s engine is optimized for a Star Schema. This means separating your data into a Fact table (containing transactions, visits, or sales numbers) and Dimension tables (containing descriptive context like dates, counties, products, or departments).

When your relationships are modeled correctly in a star schema, filters flow logically, totals don't double-count, and performance improves significantly. A bad model forces you to write complex DAX to work around the structure; a good model allows for elegant, simple DAX.

The Logic: Context Over Formulas

Once the model is solid, we move to DAX. On the surface, it looks simple—a formula like Total Revenue = SUM(Sales[Revenue]) seems straightforward. However, the real power of DAX is context. That single measure will return different results based on slicers, filters, relationships, and the visual it is placed in.

For example, a measure like Revenue per Visit = DIVIDE([Total Revenue], [Total Visits]) does more than just report a number; it measures performance. Understanding "filter context" how the user's interaction with the report changes the calculation on the fly—is the moment DAX stops being frustrating and starts making sense.

The Art of Visualization: How to Choose the Right Chart

The visual layer is where your data meets the user’s eye. The wrong visual can obscure the truth, while the right one illuminates it. To choose the best visual, you must first identify the question you are trying to answer.

Here is a framework for selecting the right visual.

1. Comparison
If you want to compare values across categories (e.g., Sales by Department), use a Bar Chart. If you are comparing values over time (e.g., Sales by Month), use a Line Chart or Area Chart to show the trend

2. Correlation
If you need to see if two variables are related for instance, "Does higher patient visits always mean higher revenue?" you should use a Scatter Plot. With "Total Visits" on the X-axis and "Total Revenue" on the Y-axis, you can instantly see positive correlations, outliers, or underperforming counties.

3. Composition
If you need to show how parts make up a whole (e.g., Market Share), use a Donut Chart or Treemap. Use these sparingly; too many slices make them unreadable.

4. KPIs
If you just need to show a single, critical number (e.g., Total Year-to-Date Revenue), use a Card or a KPI Visual that shows the number alongside a trend indicator.

Dashboards are storytelling tools. Each visual should answer exactly one question. If a chart requires a paragraph of explanation, it is likely the wrong chart.

From Insight to Action

The ultimate goal of this entire process is not to build a dashboard that looks "clean," but to drive action. We transform messy data into business strategy by highlighting anomalies and trends that require intervention.

For example, seeing high visits but low revenue might indicate a pricing issue. Noticing high medication usage in a specific age group drives inventory planning. A declining trend over time signals operational risk. A good analyst reports the numbers; a great analyst explains what they mean and what to do next.

Final Thoughts

Power BI is not just a dashboard tool; it is a thinking tool. The workflow—moving from Messy Data to Clean Transformations, building a Strong Model, writing Smart DAX, designing Clear Visuals, and finally driving Business Action—is the true craft of analytics.

It isn’t about knowing every DAX function by heart. It’s about knowing when to clean, how to simplify logic, and how to explain insights to stakeholders. That is what makes a good analyst, and honestly, that is what makes analytics fun.

Schemas and Data Modelling in Power BI

jim kinyua — Mon, 02 Feb 2026 12:34:24 +0000

Everyone loves building flashy visuals and writing clever DAX measures in Power BI, and those things definitely matter. But the truth is, the single biggest factor in whether your report performs well, scales nicely, and gives trustworthy results is data modelling.

Get the model right, and everything else becomes easier: calculations run faster, measures are simpler to write, filters behave as expected, and totals add up correctly. Get it wrong, and you’ll spend endless hours fighting performance issues, chasing phantom duplicates, or explaining why the numbers don’t match what the business expects.

Data modelling is basically about answering three core questions:

Where do the numbers live? (sales amounts, quantities, revenue)
Where do the descriptive attributes live? (product names, customer details, dates etc)
How do all these tables connect?

Fact Tables vs. Dimension Tables

Fact tables
They hold the measurable events. These are the things that happened in the business. Think sales transactions, orders, inventory movements, clicks, or support tickets. They’re usually full of numeric values (facts) and foreign keys that reference other tables.

Dimension tables
They provide the "who, what, where, when, why" context. They contain descriptive fields and are typically much smaller than fact tables.

The Star Schema
In a star schema, you have the following

One central fact table
Several dimension tables connected directly to it (usually via single-direction relationships from dimension to fact)

Hence the name Star.
It's important to note that the star schema is the gold standard for Power BI (and most modern BI tools). It’s simple, fast, and plays perfectly to how Power BI’s engine (VertiPaq) works.

The Snowflake Schema
A snowflake schema is basically a normalized star schema. Instead of keeping everything in one dimension table, you split dimensions into multiple related tables (e.g., Product → Category → Subcategory → Department).

Comparison Between Star and Snowflake

Feature	Star Schema	Snowflake Schema
Complexity	Simple and intuitive	More complex
Query performance	Faster (fewer joins)	Slightly slower
DAX development	Much easier	More complicated

Bottom line: Use a star schema in Power BI unless you have a massive warehouse where normalization savings outweigh speed.

Why Good Data Modelling Actually Matters

A good model is not a “nice to have” but will affect every aspect of your reporting experience. Performance comes first. The column-store engine (VertiPaq) in Power BI is highly optimized for star schema models. This means there are fewer tables to join, and the relationships are simpler, so queries run much faster — especially as users begin to slice and dice the data. A flat table or a “snowflake” mess will slow things down considerably

Accuracy is a must. When relationships are defined correctly (with the right cardinality and cross-filtering), filters work as expected, totals aren’t double-counted, and grand totals match what’s expected at lower levels. Get this wrong, and you’ll get inconsistent or wrong numbers — the quickest way to undermine trust in your reports.

Simpler DAX formulas are a massive benefit. In a properly modeled star schema, most calculations can be simple aggregations (SUM, COUNT, AVERAGE) with CALCULATE filters as needed. No more complex workarounds, heavy use of iterators, or “treat the table like an Excel sheet.” Debugging is simpler too — usually, problems are visible in seconds, not hours.

Finally, maintainability and scalability. A model that’s clean and logical is easy for others (or for you, later) to understand. It scales well as you add more data or subject areas, and you can often reuse the same model in multiple reports or even share it in a Power BI dataset for the whole team.

In short: Take the time to model your data properly. The difference between a report that feels snappy and reliable and one that’s always fighting you is huge. The visuals and DAX formulas are great, but they’re only as good as the foundation they’re built on.

Git for Beginners: How to Push & Pull Code, Track Changes, and Understand Version Control

jim kinyua — Sun, 18 Jan 2026 07:38:53 +0000

Git Basics for Beginners: Push, Pull, and Track Code Changes

Git is a version control tool that helps developers track changes in their code and collaborate safely. Instead of creating multiple versions of the same file, Git records what changed, when it changed, and who made the change. This makes it easy to work in teams and recover from mistakes.

Git works in a project folder called a repository. You work locally on your computer, then sync your changes to a remote repository such as GitHub.

Creating a Git Repository

Start tracking a new project:
git init

Copy an existing project:
git clone https://github.com/username/project.git

Tracking Changes

Check the current state of your project:
git status

Stage a specific file:
git add filename.js

Stage all changes:
git add .

Save a checkpoint:
git commit -m "Add login validation"

Pushing and Pulling Code

Send your commits to the remote repository:
git push

First-time push:
git push -u origin main

Get the latest updates from the remote repository:
git pull

Always pull before starting work to avoid conflicts.

A Typical Daily Git Workflow

git pull

write code

git status

git add .

git commit -m "Fix payment validation bug"

git push

Viewing History and Changes

View commit history:
git log

See what changed in files:
git diff

Common Beginner Mistakes

Forgetting to pull before working, writing unclear commit messages, committing broken code, or being afraid to make mistakes. Git is designed to recover from errors safely.

Final Thoughts

Git is simply about tracking changes, saving checkpoints, and syncing work with others. Learn these core commands first: git status, git add, git commit, git push, and git pull. Everything else builds on them.