DEV Community: Barbara Morara

Understanding Joins and Window Functions in SQL

Barbara Morara — Mon, 02 Mar 2026 19:44:09 +0000

Two of the most powerful SQL concepts a data person, like a data analyst or a backend developer, will use are "Joins" and "Window Functions." "Joins" are used to combine data from multiple tables, and "Window Functions" are used to perform complex calculations on related rows of data without aggregating the results.

In this article, I will explain what "Joins" and "Window Functions" are, along with examples, in a very simple way.

Part 1: SQL Join

In relational databases, data is often stored in multiple tables. For example, you might have:
• customers table
• orders table

Each table stores different information, but they are connected using a common key like customer_id.

What Is a Join?

A JOIN combines rows from two or more tables based on a related column.

Think of it like matching records from one Excel sheet to another using a common ID

Types of Joins

1. INNER JOIN

Returns only the matching records in both tables.
SELECT c.customer_id, c.name, o.order_id, o.order_date FROM customers c INNER JOIN orders o ON c.customer_id=o.customer_id;

This query returns customers who have placed orders.

2. LEFT JOIN

Returns all records from the left table and matching records from the right table. If there is no match, NULL values appear.

SELECT c.customer_id, c.name, o.order_id FROM customers c LEFT JOIN orders o ON c.customer_id = o.customer_id;

This shows all customers, even those who haven’t placed any orders.

3. RIGHT JOIN

Returns all records from the right table and matching ones from the left.

SELECT c.customer_id, c.name, o.order_id FROM customers c RIGHT JOIN orders o ON c.customer_id = o.customer_id;

4. FULL OUTER JOIN

Returns all records from both tables, whether they match or not.

SELECT c.customer_id, c.name, o.order_id FROM customers c FULL OUTER JOIN orders o ON c.customer_id = o.customer_id;

How I Understand Joins

Joins are about relationships.
If two tables share a key, a join lets you bring their data together into one meaningful result.

Without joins, working with relational databases would be extremely limited.

Part 2: Window Functions

Window functions are more advanced and extremely powerful.

Unlike GROUP BY, window functions do not collapse rows. Instead, they perform calculations across a set of rows related to the current row.

That “set of rows” is called a window.

Basic Syntax

FUNCTION_NAME() OVER ( PARTITION BY column ORDER BY column )

•PARTITION BY → splits data into groups
•ORDER BY → defines row order inside each group

Common Window Functions

1. ROW_NUMBER()

Assigns a unique number to each row.
SELECT customer_id, order_id, ROW_NUMBER() OVER ( PARTITION BY customer_id ORDER BY order_date DESC ) AS row_num FROM orders;

This ranks each customer’s orders from newest to oldest.

2. RANK()

Assigns ranking, but allows ties.

SELECT product_id, revenue, RANK() OVER ( ORDER BY revenue DESC ) AS revenue_rank FROM products;

3. SUM() as a Window Function
SELECT customer_id, order_date, amount, SUM(amount) OVER ( PARTITION BY customer_id ORDER BY order_date ) AS running_total FROM orders;

This calculates a running total per customer.

GROUP BY vs Window Functions

GROUP BY reduces rows.
Window functions keep all rows and add calculated columns.

Example using GROUP BY:
SELECT customer_id, SUM(amount) AS total_spent FROM orders GROUP BY customer_id;

Example using Window Function:
SELECT customer_id, amount, SUM(amount) OVER (PARTITION BY customer_id) AS total_spent FROM orders;

The second query keeps each order visible while still showing total spent.

Why Joins and Window Functions Matter

If you’re working in analytics (Power BI, Excel, SQL Server, PostgreSQL, etc.), these two skills are essential:
•Joins help you combine datasets.
•Window functions help you analyze patterns within those datasets.

In real-world analysis:
•You join sales with customers.
•You calculate rankings.
•You create running totals.
•You detect duplicates.
•You identify top-performing products.

These are daily tasks for data analysts and backend engineers.

Summary of Key Points

1.Joins combine data from multiple tables using related columns.
2.INNER JOIN returns matching records only.
3.LEFT JOIN keeps all records from the left table.
4.Window functions perform calculations without grouping rows.
5.PARTITION BY divides data into logical groups.
6.Window functions are powerful for ranking, running totals, and advanced analytics.

If you truly understand Joins and Window Functions, you’ve crossed an important milestone in SQL mastery.

Joins teach you how data connects.
Window functions teach you how data behaves.

Master both — and you unlock advanced analytics capabilities that tools like Power BI and Tableau rely on under the hood.

How Analysts Translate Messy Data, DAX, and Dashboards into Action Using Power BI

Barbara Morara — Mon, 09 Feb 2026 11:33:00 +0000

In today’s information-driven world, lack of information is not what hurts the organization; what hurts the organization is how it interprets the information it has. As discussed, raw data is incomplete, inconsistent, and scattered across many sources, and here is where Power BI analysts come in.

While it is a visualization tool, it is more than that. Used well, Power BI is a decision-making machine that turns chaotic data into insights that leaders can act upon. In a sense, this article is a presentation of how analysts can use data cleaning, data analysis expression, and data visualization to drive business decisions.

1. From Messy Data to Reliable Information

Reality of Raw Data:
In actual businesses, data is never clean:

Missing Values
Similar Records
Misdated Information
Inconsistent use of names (e.g. "Nairobi", "NRB","Nai")
Negative Prices/Unrealistic Discounts

If this data is analyzed without cleaning, decisions made from the analysis will be wrong.

How analysts utilize Power BI to remedy this problem

Analysts can leverage Power Query for:

Remove Duplicates and Errors
Handle missing values logically
Standardized Columns & Formats
Merge data from various sources (e.g., Excel, database, API) Business Impact: Clean data helps to promote trust. Managerial decisions are easier to make with accurate reports.

2. Converting Data to Meaning with DAX

What is DAX really used for?

DAX, or Data Analysis Expressions, is less about complex formula writing and more about the articulation of business logic.
Analysts use DAX to answer questions like the following:

What are total sales this month vs. last month?
Which are the growing and which are the declining products?
What is the average time of delivery?
Do discounts affect the profit?

Example of business logic with DAX

Instead of just showing the numbers of sales, analysts produce:

YTD Sales
Profit margins
Percentages of growth
Performance rankings

Business impacts: DAX changes raw numbers into KPIs, which are understood and used by the executives.

3. Dashboards that Tell a Story, Not Just Show Charts

Why Dashboards Matter

A good dashboard should answer questions at a glance:

What is going on?
Why is it happening?
What should we do next?

Analysts create dashboards with:

Clear KPIs (Sales, Profit, Growth
Filters (Date, Region, Product)
Trends and comparisons
Alerts for poor performance

Business Impact: Decision-makers save time and act faster, as dashboards are intuitive and focused

4. Translating Insights into Action

Power BI in real business decisions
Power BI insights lead to actions such as:

Increasing stock levels for high-performing products
Cutting down on discounts affecting profit
Determining the underperforming
Improving delivery timelines
Adjusting sales strategies

Example

A dashboard indicates:

Sales are strong, but profit is declining
DAX shows heavy discounting in one region

Action:Management adjusts pricing strategy to increase profitability.
**Business Impact: **Power BI helps organizations act, not just observe.

5. Why Power BI Analysts Are Valuable

Those working in power BI analytics occupy an intermediate position between data and decision-makers. The value they offer lies in:

Understanding Business Problems
Correctly clean and model the data
Writing Meaningful DAX Measures
Designing dashboards that communicate clearly

They don't simply build reports; they build better decisions.

Power BI is more than just a technical tool. When used well, it can become a bridge between messy data and action-worthy meaning. Data cleaning, calculations in DAX, and dashboards all help create a better future for an organization, whether that future is full of opportunities or threats.
In a world full of data, the true power is in making an impact from the insights, and Power BI offers just that

Understanding Schemas and Data Modelling in Power BI

Barbara Morara — Mon, 02 Feb 2026 13:55:50 +0000

Introduction

Power BI is a powerful business intelligence tool that enables users to convert raw data into useful insights using interactive reports and dashboards. Nevertheless, the quality, performance, and accuracy of Power BI reports greatly rely on the way the data is modelled.
Data modelling is the process of structuring the data into tables, specifying the relationships between the tables, and selecting the correct schema. This article discusses the key concepts of data modelling for Power BI, which include schema, fact tables, dimension tables, relationships, star schema, and snowflake schema.

What Is Data Modelling in Power BI?

Data modelling in Power BI is defined as the process of organizing and structuring data in such a way that it can be easily analyzed and reported on. Data modelling in Power BI comprises:

Organizing the data in a logical table structure
Establishing relationships between the tables
Selecting the most appropriate schema
Ensuring the accuracy and efficiency of the data

Data modelling in Power BI helps in creating reports that are not only efficient but also easier to understand.

Fact Tables

A fact table holds data that is measurable and can be analyzed. This data is usually in numbers and is quite large in size.
Examples of data in a fact table:

Sales value
Number of units sold
Profit
Discount Fact tables usually have foreign keys that refer to dimension tables.

Dimension Tables
A dimension table holds data that describes the data in the fact table.

Examples of data in a dimension table:

Customer name
Product category
Date
Region or location Dimension tables are smaller and hold text data.

Relationships in Power BI

Relationships describe how tables are related in a data model. Power BI uses these relationships to merge data from different tables during analysis.

Common Types of Relationships

One-to-Many (1:*) – Most common (e.g., one customer → many sales)
One-to-One (1:1) – Rare
Many-to-Many (:) – Used cautiously

Relationship Direction

Single direction (recommended for simplicity and performance)
Both directions (can cause ambiguity if misused)

Well-defined relationships are essential to ensure correct calculations and avoid incorrect aggregations.

Schemas in Data Modelling

A schema is a method of organizing tables and relationships in a data model. The two most common schemas used in Power BI are Star Schema and Snowflake Schema.

Star Schema

A star schema is a data model that has a central fact table directly linked to multiple dimension tables. The data model looks like a star.

Features of Star Schema

There is only one fact table in the schema.
The dimension tables are not linked to each other.
The schema is simple and easy to understand.

Benefits of Star Schema

It has faster query execution times.
DAX calculations are simpler.
It is easier to maintain.
Recommended by Microsoft for Power BI.

Example

FactSales
DimCustomer
DimProduct
DimDate
DimRegion
DimRegion

Each dimension table is directly linked to the fact table.

Snowflake Schema

A snowflake schema is an extension of the star schema where the dimension tables are further normalized into multiple related tables.

Characteristics of Snowflake Schema

Dimension tables are divided into sub-dimension tables
More complex structure
Uses more relationships

Advantages of Snowflake Schema

Reduces data redundancy
Saves storage space
Disadvantages of Snowflake Schema
Slower performance compared to star schema
More complex relationships
Difficult for a beginner to understand

In Power BI, snowflake schemas are less preferred due to performance issues.

Feature	Star Schema	Snowflake Schema
Complexity	Simple	Complex
Performance	Faster	Slower
Number of Tables	Fewer	More
Ease of Use	Very Easy	Moderate to Difficult
Recommended in Power BI	Yes	Rarely

Why Good Data Modelling Is Critical in Power BI

Improves Performance
Data modeling can improve query speed and minimize relationships, hence improving user experience through quicker report loading.
Ensures Accurate Reporting
Good data modeling ensures that relationships are correct to avoid:
Double counting
Incorrect totals
Misleading business decisions
Simplifies DAX Calculations
Star schema modeling simplifies DAX formula writing, reading, and debugging.
Enhances Scalability
Data modeling can handle large volumes of data and can grow with business requirements.
Improves User Experience
Data modeling can ensure that users can understand data correctly and slice it correctly for easier decision-making.

Best Practices for Data Modelling in Power BI

Use star schema modeling
Dimension tables must be descriptive
Avoid many-to-many relationships
Use meaningful names
Validate relationships
Minimize bi-directional filter

Data modeling is a critical aspect of successful Power BI reporting. Understanding different schemas, fact tables, dimension tables, and relationships enables analysts to create successful data models for Power BI reporting. Among different schemas, star schema modeling is highly recommended for use in Power BI reporting because of its simplicity and ability to improve query speed. By using good data modeling practices, users can unlock the full potential of Power BI reporting.

Introduction to MS Excel for Data Analytics

Barbara Morara — Wed, 28 Jan 2026 15:58:39 +0000

Beginner-friendly guide

Microsoft Excel is one of the most widely used applications for data management. It is not necessary to be a programmer or a data scientist to begin analyzing data using Microsoft Excel. It has simple features such as tables, formulas, sorting, filtering, and charts that enable a beginner to understand the data, identify patterns, and make informed decisions.
This article is intended to provide information on the application of MS Excel as a simple data analytics tool.

What is Data Analytics?

Data analytics is the process of collecting, cleaning, organizing, and analyzing the data to extract valuable information.
In simple words, data analytics helps us find answers to the following questions:

How many were sold?
Which product performed the best?
What is the total, average, or maximum value? Excel assists us in finding the answers to these questions quickly and graphically.

Getting Started with MS Excel

When you start Excel, you see a sheet consisting of rows (numbered on the left side) and columns (lettered on the top). The intersection of each row and column is called a cell.
The most effective way to use Excel is when you organize your data in a table format.

This small data set will help us learn the basic concepts of data analysis.

Using Basic Formulas in Excel

One of the most powerful tools in Excel is the use of formulas. Formulas enable Excel to calculate data.

Calculating Total Sales

To calculate the total sales of each product:

Add a new column and name it Total
In the first cell of the Total column, type =B2*C2
Press Enter
Drag the formula down to calculate the totals for the other products

This multiplies the quantity by the price.

Using Common Excel Functions

SUM Function
The SUM function is used to add total sales.

This gives the total sales amount.

Average Function
The AVERAGE function finds the average value.

This is the average sales amount.

Sorting Data

Sorting data helps you arrange information in a useful way.
For instance, you can sort items from highest to lowest total sales as follows:

Select the data
Click Sort & Filter
Select Sort Largest to Smallest

This helps you identify the best-performing items.

Filtering Data
Filtering enables you to show data based on your needs.
Example: Display products with a quantity above 8.

Select your table
Click Filter
Use the drop-down arrows to apply conditions

Creating Simple Charts
Charts enable you to visualize data and understand insights easily.
To create a chart:

Select the data
Click Insert
Select a Column Chart or Bar Chart

Why Excel is Good for Beginners in Data Analytics

Excel is good for beginners in data analytics because:

It is easy to learn
No programming skills are required
Calculations are automatic
Data visualization is easy
It is used in most schools and work environments

Conclusion

Microsoft Excel is a powerful data analysis tool that is also suitable for beginners. It is easy to learn and use, and its mastery will provide a good foundation for more advanced data analysis tools in the future.
If you are a beginner in data analysis, then Excel is the best place to start.

A Beginner’s Guide to Version Control with Git: Tracking, Pushing, and Pulling Code

Barbara Morara — Sat, 17 Jan 2026 14:54:47 +0000

A problem that programmers, especially beginners, often encounter is:
you make changes to the code, something goes wrong, and you would like to go back to a previous state. That is where a “version control” tool comes in to help you.
In this article, you will learn:
-What version control is and why it is important
-Git Changes Tracking
-How to push code on GitHub
-How to pull code from GitHub

What Is Version Control?
Version Control is the process wherein changes that have been made to files are tracked. With versioning, you have the ability to:
-Record a history of your work
-See what was changed and when
-Restore previous versions of code
-Work together with others without overwriting their work

What Is Git?
Git is a version control system that you install and operate from your computer. It assists you with:
-Monitoring changes made in files
-Save copies of your project (known as "commits")
-Working Offline
-Manage your project effectively
Git operates locally. This means all the changes occur on your personal machine.

What Is GitHub?
GitHub is an online hosting service used for storing Git repositories. It enables you to:
-Online code backups
-Collaborate on projects with others
-Work together on team projects

Key Git Concepts for Beginners

Repository (Repo): A project folder managed with Git
Commit: A saved snapshot of changes
Staging Area: This refers to where changes to be committed to the project's source code will be
Push: Code Uploads to GitHub
Pull: Downloading updated code from GitHub

Tracking Changes with Git

To see the state of your files, use:

git status

This indicates which files have been updated or are ready to be committed.

To prepare files for saving, add them to the staging area:
git add .

To save the changes permanently, create a commit:
git commit -m "Describe what changed"

In a commit message, you should describe the update you implemented.

Pushing Code to GitHub

After committing, however, your changes exist only on your own computer.
To upload the files to GitHub,use:
git push

Pulling Code from GitHub

If an update is done on GitHub, the local project could become stale.
To update it, run:
git pull

Git can appear intimidating at first, but learning the fundamentals of version control, change tracking, pushing, and pulling code can make development a much safer and more organized process. Git can become a powerful and very simple tool if you practice using it frequently.

Happy coding