DEV Community: Stacy Omwoyo

JOINS AND WINDOW FUNCTIONS ON POSTGRESQL.

Stacy Omwoyo — Mon, 02 Mar 2026 15:02:06 +0000

INRODUCTION

In relational databases such as PostgreSQL, data is often stored across multiple related tables. To extract meaningful insights that will be key in decision-making, we must combine and analyze this data efficiently. The two most powerful ways to go about it are;

Using Joins- combines data from multiple tables
Using Window Functions- performs calculations across rows related to the current row without collapsing the result set.

Understanding how these two concepts work is fundamental for data analysis, backend developpers and database engineers.

PART 1: JOINS IN PostgreSQL

What is a Join?

A join combines rows from two or more tables based on related columns between them. One table will contain a primary key while the other contains a foreign key. The values in said keys are identical, which creates the correlation.

There are various types of joins:

Inner Join
Right Join
Left Join
Natural Join
Cross Join
Self Join

1.INNER JOIN
Returns data from matching rows in both tables. Used when you only want records that exist in both tables.

select c.first_name, c.last_name, o.quantity 
from clients c
INNER JOIN orders o
on c.customer_id = o.customer_id;

2.RIGHT JOIN
Returns all the rows from the right table and matching rows from the left table. If no match exists, NULL is returned.

select c.first_name, c.last_name, o.quantity
from clients c 
RIGHT JOIN orders o 
on c.customer_id= o.customer_id ;

3.LEFT JOIN
Opposite of right join; returns all rows from the left table.

select c.first_name, c.last_name, o.quantity
from clients c 
LEFT JOIN orders o 
on c.customer_id= o.customer_id ;

4.FULL OUTER JOIN
Returns all rows when there is a match on either left or right table.

select c.first_name, c.last_name, o.quantity
from clients c 
FULL OUTER JOIN orders o 
on c.customer_id= o.customer_id ;

5.SELF JOIN
Joins a table to itself.

select e.name as Employee, m.name as manager
from employees e
inner join employees m on e.manager_id =m.employee_id;

6.CROSS JOIN
Can join more than two tables and return data from all the selected tables. This is very common in real-world business settings.

select b.title, o.quantity 
from books b 
INNER join orders o 
on b.book_id= o.book_id ;

7.NATURAL JOIN
Joins data from columns with the same name.

select * from books 
natural join orders;

PART 2: WINDOW FUNCTIONS IN POSTGRESQL.

What are Window Functions in PostgreSQL?

Window Functions perform calculations across a set of table rows related to the current row. Unlike the GROUP BY, they don't collapse rows. This is achieved by the use of the OVER() clause.

Window functions include the following:
1.ROW_NUMBER()
Assigns a unique number to each row. It is very useful in ranking and pagination.

SELECT name,
       ROW_NUMBER() OVER (ORDER BY amount DESC) AS row_num
FROM orders;

2.RANK()
Assigns numbers to values in a group of values with a similar feature. It skips numbers when duplicates appear.

SELECT name,
       RANK() OVER (ORDER BY amount DESC) AS rank
FROM orders;

3.DENSE_RANK()
Similar to the Rank function, only it does not skip duplicates when assigning numbers.

SELECT name,
       DENSE_RANK() OVER (ORDER BY amount DESC) AS rank
FROM orders;

4.PARTITION BY()
It divides rows into groups before applying the function.

SELECT customer_id,
       amount,
       RANK() OVER (PARTITION BY customer_id ORDER BY amount DESC) AS rank_per_customer
FROM orders;

5.RUNNING TOTAL (Cumulative Sum)
Calculates the cumulative total per row as desired.

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

6.LAG() and LEAD()
Used to compare rows. They are very useful in trend analysis as they work hand in hand. LAG looks backward, while LEAD looks forward.

SELECT customer_id,
       amount,
       LAG(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS previous_order
FROM orders;

IT IS IMPORTANT TO NOTE THE FOLLOWING WHILE USING WINDOW FUNCTIONS
1.Ensure you use the PARTITION BY function carefully, failure to do so results in the whole table being treated as one group.
2.Check the ORDER BY function, as it controls the order of calculations in the window function.
3.Optimize performance; window functions can be slow on large datasets; use indexes if needed.

COMBINING JOINS AND WINDOW FUNCTIONS

In real-world analysis, both are often used together.
We can come up with a query that joins tables, aggregates totals and applies ranking in one go.

SELECT name,
       SUM(amount) AS total_spent,
       RANK() OVER (ORDER BY SUM(amount) DESC) AS spending_rank
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY name;

CONCLUSION

Joins and Window Functions are foundational tools in PostgreSQL. Where Joins allow you to merge related data across tables and Window Functions allow advanced analytical calculations while preserving row-level detail.

A mastery in both gurantees expertise in
-Sales Analysis
-Customer Ranking
-Time-series Analysis
-Business Intelligence reporting
-Data Engineering Workflows

Together they transform raw relational data into actionable insights that will come in handy when it comes to decision-making.

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

Stacy Omwoyo — Sun, 08 Feb 2026 14:38:26 +0000

Introduction

In the real world, data is rarely clean, complete or ready for analysis. Organizations collect information from multiple systems, in different formats, with inconsistencies, missing values and errors. Even with all the discrepancies, leaders still expect timely, accurate insights to guide decisions. This is where Power BI analysts come in handy; they bridge the gap between messy data and meaningful business action.
This article explores how analysts use Power BI to transform raw data into insights that drive operational, tactical and strategic decisions: data cleaning and modelling, DAX calculations and interactive dashboards

From Messy data to a reliable data model

Understanding the Business Question First.

Before starting with Power BI, effective analysts start with the business problem. For example,

_Why are sales declining in a certain region? _

-_ Where are costs exceeding the budget? _

This provides context for what data is needed and how it should be shaped. Without this step, even the most advanced dashboards risk answering the wrong questions.

Cleaning and transforming data with Power Query.

Most real data comes with issues such as:

Duplicate records
Missing or incorrect values
Multiple data sources that don't align

Using Power Query analysts:

Remove duplicates and irrelevant columns
Standardize formats (dates, currencies and categories)
Merge and append data from different systems
Create calculated columns for grouping or categorization.

This step is critical because decisions are only as good as the data behind them. Clean data builds trust among stakeholders and reduces the risk of misleading conclusions.

Designing the right data model.

Once data is cleaned, analysts design a data model, typically using a star schema.
Fact Tables- hold measurable data (sales, hours worked)
Dimension tables- provide context(customer, date, region)

A well-designed model:

Improves report performance
Simplifies DAX calculations
Ensures consistent results across reports

In practice, this means leaders can compare figures across departments and time periods with confidence.

Turning Data into Insights with DAX

DAX (Data Analysis Expresssions) is what allows analysis to move beyond static numbers. It enables dynamic calculations that respond to filters, slicers and user interactions.

Instead of asking, "What were the total sales this year?"
DAX, allows questins like:

How do sales compare year over year?
What is the rolling 3-month average?
Which products contribute the most to profits, not just revenue?

Translating metrics into business language

Good analysts design DAX measures that reflect how the business actually operates. For instance:

KPIs: revenue, growth, profit margin and customer retention rate
Time intelligence: month-to-date, year-to-date and prior year comparisons
Performance indicators: variance against targets or budgets

These measures turn raw figures into signals that decision-makers can quickly interpret and act on.

Enabling What-If and Scenario Analysis.

With DAX, analysts can also support What-If analysis such as:

How would profit change if prices increased by 5%?
What happens to costs if headcount grows by 10%?

This shifts Power BI from a reporting tool to a decision-support system, helping organizations plan rather than simply react.

Dashboards that drive action rather than just reporting

Designing for the audience

An effective dashboard is not about showing everything- it's about showing what matters. Analysts tailor dashboards based on the audience:
Excecutives- need high-level KPIs and trends

Managers- need performance by team, region or product.

Operational staff- need detailed, actionable views

Clear layout, minimal clutter and consistent visuals help users focus on insights rather than figures.

Interactivity and exploration

Power BI allows dashboard users to:

Filter by date, region and category
Drill down from summary to detail
Identify patterns, outliers and exceptions

This interactivity empowers non-technical users to explore data independently, reducing reliance on analysts for every question.

Linking Insights to Decisions

The true impact of a dashboard is measured by the actions it triggers such as:

Reallocating resources to underperforming regions
Adjusting pricing or promotions based on demand patterns
Identifying inefficiencies and cost-saving opportunities

When dashboards are aligned with business goals, they become part of everyday decision-making rather than passive reports.

Real-World Impact of Power BI Analysis

By translating messy data into clean models, meaningful DAX measures and intuitive dashboards, analysts enable organizations to

Make faster, evidence-based decisions
Improve transparency and accountability
Detect risks and opportunities early
Align teams around shared metrics and goals

In sectors such as finance, healthcare, retail and public services, this can mean improved profitability, better service delivery and more effective use of limited resources.

Conclusion

Power BI is more than a visualization tool; it is a platform where technical skills meet real-world impact. Analysts who understand data cleaning, modeling, DAX and dashboard design play a crucial role in transforming messy, fragmented data into clear insights that guide action.
Ultimately the value of Power BI lies not in charts or formulas but in the quality of decisions it enables. When used effectively, it becomes a powerful bridge between data and strategy.

Schemas and Data Modelling in Power BI

Stacy Omwoyo — Mon, 02 Feb 2026 11:54:59 +0000

Introduction

Data Modeling is a foundational step in building effective Power BI reports. Even with clean data and effective visuals, poor data modeling can lead to slow performance, incorrect calculations and misleading insights. Schemas and data models define how data is structured, related and queried within Power BI. This article explores key data modelling concepts, including star schema, snowflake schema and fact and dimension table relationships explains why modelling is critical for performance and accurate reporting on Power BI.

What is Data Modelling in Power BI?

In Power BI, data modeling is the process of organizing tables and defining relationships between them to represent real-world business processes. The data model determines how data is filtered, aggregated and analyzed when users interact with reports.
A well-designed model

Reflects business logic clearly
Makes DAX calculations simpler and more reliable
Improves report performance
Reduces ambiguity and errors in analysis

Power BI uses a tabular data model, which works best when data follows established schema design patterns.

Fact and Dimension Tables

Fact tables

A fact table contains measurable, quantitative data related to business events or transactions. Examples include:

Sales Transactions
Orders
Website events
Financial records

Fact tables usually:

Contain numerical values (revenue, quantity and cost)
Have many rows
Include foreign keys that link to dimension tables

Dimension Tables

They provide descriptive context for facts and are used for filtering and grouping.
For instance Customer, Product, Date and Location

Dimension tables usually:

Have descriptive attributes (names, categories)
Have fewer rows than fact tables
Are used in slicers and filters

Diagram: Fact vs Dimension Tables

This seperation allows Power BI to aggregate facts correctly while dimensions control how data is sliced and filtered.

Star Schema

Definition

A star schema consists of one central fact table connected directly to multiple dimension tables, forming a star-like structure.

Diagram: Star Schema

The Sales Fact table sits at the center.
All dimensions are directly linked to the fact table.
No relationship exists between dimension tables.

Why is Star Schema preferred in Power BI?

Star schemas are considered best practice because they:

Minimize the number of joins
Improves query performance
Makes DAX calculations easier
Reduce filter ambiguity

Power BI's VertiPaq engine is optimized for star schemas, making them the most efficient structure for analytics and reporting.

Snowflake Schema

Definition

A Snowflake Schema is a variation of the star schema where the dimension tables are further normalized into multiple related tables.

Diagram: Snowflake Schema

Here the product dimension is split into multiple tables (the product is further split into categories).

Pros and Cons
Pros:

Reduced data redundancy
Can be useful for very large data dimensions

Cons:

More complex relationships
Slower perfoRmance in Power BI
Harder to write and maintain DAX

In light of these drawbacks, snowflake schemas are generally discouraged in Power BI unless necessary.

Relationships in Power BI

Relationships define how tables interact and how filters propagate across the model.

Common relationship types

1.One-to-many (1:* ): it is the most recommended and common type
2.One-to-one
3.Many-to-many- should be used cautiously

Filter Direction

Best practice in Power BI

Filters should flow from Dimension to_ Fact._
Use single-direction filtering where possible.
Avoid unnecessary bi-directional relationships.

Active vs Inactive Relationships

Power BI allows multiple relationships between tables, but only one can be active at a time.
Inactive relationships can be activated using DAX functions such as USERRELATIONSHIP() when required.

Importance of Good Data Modeling

1.Performance optimization

Efficient models:

Reduce memory usage
Speed up report refresh and visuals
Improve user experience Star schemas with fewer relationships outperform complex, highly normalized models.

2.Accurate reporting and analysis

Poor modelling can lead to:

Double counting
Incorrect totals
Filters not behaving as expected Clear fact-dimension separation ensures calculations reflect real business logic.

3.Simpler DAX Calculations

Good models reduce the need for complex DAX logic. Measures become:

Easier to write
Easier to debug
Easier to maintain

4.Scalability and maintenance

Well-designed models are:

Easier to extend with new data
Easier for other analysts to understand
More reliable for long-term reporting

Conclusion

Schemas and data modeling are central to effective Power BI reporting. Understanding fact and dimension tables, applying star schemas, avoiding unnecessary snowflake structures, and defining clean relationships ensures optimal performance and accurate insights. Investing time in good data modeling enables analysts to fully leverage Power BI's analytical capabilities and deliver reliable, business-ready reports.

Introduction to MS Excel for Data Analytics.

Stacy Omwoyo — Sun, 25 Jan 2026 16:42:44 +0000

Microsoft Excel is one of the most widely used tools for data analysis worldwide. The good news is you don't have to
be an expert to start using it.
Excel is powerful, flexible and beginner-friendly, making it a great starting point for anyone interested in data analytics.
Today's article will tackle how MS Excel can be used for basic data analysis, using simple language and practical examples. I will add visuals to help you tag along and understand the process better.

What is Data Analysis?

It is simply looking at data to find useful information. This can include:

Comparing values (e.g., which product sells more)
Understanding trends (e.g., sales going up or down)
Spotting errors or missing information

Excel helps you do all of this without writing any code.

Getting started with Excel.

Before data is analyzed, it needs to be cleaned and well organized.

Adding headers

Headers describe the data in each column
Column A: Date
Column B: Product
Column C: Revenue

Formatting as a table

Excel allows you to convert your data into a table, which makes analysis easier.

Steps
1) Select your data
2) Go to Home; format as table
3) Choose table style

Benefits of having a table:

Easy sorting and filtering
Automatic formatting

Basic Calculations Using Formulas

Excel formulas are designed to perform calculations quickly, in turn making your work easier.

When typing formulas into Excel always start with an equals sign (=)

SUM: Adds Values within a selected range
=SUM(CELL RANGE)

=SUM(M2:M633)

AVERAGE: Finds the mean value
=AVERAGE(CELL RANGE)

=AVERAGE(M2:M631)

COUNT: Counts cells with numbers

=COUNT(CELL RANGE)

Sorting and filtering Data

Sorting and filtering help you to focus on specific information

Sorting

Data can either be sorted from smallest to largest or from A to Z or vice versa.

Filtering

It allows you to only show certain values and hide unnecessary data.

Steps
Click the filter arrow in a column header

Select the values you want to see

Press enter

Using Charts to visualize data

Charts turn numbers into visuals, making data easier to understand.
Common Chart Types

Column Chart- Good for comparing values
Line Chart- Shows trends over time
Pie chart- Shows proportions

Steps to create a chart
1) Select your data

2) Go to Insert > Charts

3) Choose chart type

Basic Data Cleaning in Excel

Real-world data is often messy. Excel helps you clean it.

Removing Duplicates
1) Select your data. (Ctrl + A)

2) Go to Data

3) Ensure you've checked the box that says "My data has headers"

4) Select "Ok"

Handling empty cells
For small data sets the missing data can be manually keyed in.

For larger data sets you can follow the following steps
1) Highlight the column that has missing values

2) Go to home and select the filter option
3) Go back to the headed row and select the drop-down; among the options you will be presented with is "Blanks."
4) Select Blanks then Enter

5) All the missing cells will be filtered out

6) Choose how to label the missing values; you can use "Unknown," because we are not aware of the data to key in at this juncture then Enter

7) Finally, hold the cursor to the bottom right of the labelled cell until a cross appears, then drag down to autofill the rest of the cells.

Why is Excel great for beginners?

Excel is great for data analytics newbies because:

It is easy to learn and operate
No programming is required
It's widely used in workplaces
Skills from Excel can be transfered to PowerBI and SQL

Conclusion

Microsoft Excel is a powerful starting point for anyone interested in data analytics. From organizing data and performing calculations to creating charts and finding insights, Excel makes data analysis accessible to beginners.

With practice and curiosity, the simple tools you've learned here can help you with the first steps of data analysis, which is really helpful when it comes to making informed decisions.

Git for Beginners.

Stacy Omwoyo — Sat, 17 Jan 2026 18:11:52 +0000

What is Git, How to use it and Why version control matters.

If you're new to tech or programming like me, then you've probably come across phrases like:

Did you commit your changes?
Push it to GitHub.
Pull the latest version.

And honestly, it feels like you were left out of an inside joke.
This article explains Git, GitHub, and Version control in layman's terms: why they matter, what they do, and how to use them as a complete beginner.

What is Version Control?

Version Control is a system that tracks your changes over time.
It allows you to:

See what changed, when, and why
Go back to a previous version safely
Work with others without overwriting each other's work
Experiment without fear of breaking everything

This is where Git comes in.

What is Git ?

Git is a version control tool that helps you save, track and manage changes to your project over time.
Instead of creating multiple messy files, Git:

Records every meaningful change
Saves it with a message explaining what you did
Lets you rewind to any point in time: time travel within your project
Scales from solo projects to global teams

How to push code to GitHub as a beginner

Pushing means sending your local project to GitHub

GitHub is an online platform that stores Git projects. It makes sharing and collaboration easier

Step 1: Create a new repository on GitHub

Go to GitHub.com
Create a new repository
Copy the repository URL

Step 2: Connect to your local project

Inside your Project folder on GitHub

git init

This code tells Git to start tracking this project.

Step 3: Add everything in the folder

git add.

Step 4: Commit your Changes

git commit -m "Added data cleaning script."

Step 5: Copy the Repository URL from GitHub

https://github.com/yourusername/repository-name.git

Step 6: Run

git branch -M main
git remote add origin https://github.com/yourusername/repository-name.git

Step 7: Push your code

git push -u origin main

Et Voila, your code is now on GitHub

From here on out, the next time you make changes, all you need is the code below:

git add.
git commit -m "Describe what you changed."
git push

How to Pull code from Git

Now we will tackle how to pull code; where pulling is Getting the latest version of a project

If you don't have the project or this is your first time: use clone

Copy the repository link from GitHub. It often looks like this:

https://github.com/username/repo-name.git

Run the code

git clone https://github.com/username/repo-name.git

By doing this, you:
-Download the project
-Set up Git automatically
-Connects to GitHub

If you already have the project on your computer, then use Pull:

Go into the project folder on Bash

cd repo-name

Pull the latest changes

git pull

By doing this, you pull the latest updates from GitHub and merge them into your local files.

Cloning applies when getting the project for the first time
Pulling applies when updating an existing project

How to track projects using Git.

Each time you work on your project, you will repeat this cycle:

Check what changed

git status

This tells you:
-Which files were modified
-Which files are new
-What is not yet tracked
This, simply put, is your dashboard.

Stage the changes:
To stage everything, use this code

git add.

To stage a specific file, use this code.

git add filename.py

This tells git what you want to save.

Commit the changes:

git commit -m "Describe what you changed."

This is like saving a checkpoint in a game

View project history:

git log

This shows:

All past commits
Who made them
When they were made

Breaking the giant into smaller, bite-sized pieces made it less scary and way simpler to digest and follow through. At its core, Git aims to protect your work, track your progress, and facilitate collaboration without chaos.

Whether you are learning software development, data science, analytics or working on research projects, Version Control isn't optional; it's a superpower.