Forem: George Mathenge

Simple guides to table joins and window functions in SQL.

George Mathenge — Mon, 02 Mar 2026 16:31:18 +0000

The postgresql joins clause is used to combine records from two or more tables in a database.A join is a means for combining fields from two tables by using values common to each.
There are five types of joins as shown below;

1.The inner join.
2.The cross join.
3.The left outer join.
4.The right outer join.
5.The full outer join.

THE INNER JOIN

It creates a new result table by combining column values of two tables(A and B) based upon the join-predicate.
The query compares each row of table A with each row of table B to find all pairs of rows, which satisfy the join predicate.
When the join-predicate is satisfied, column values for each matched pair of rows of table A and table B are combined into a result row.
Its the most common type of join.

The syntax used to perform this kind of join is as shown below;

SELECT A.column1,B.column2 ....
FROM A
INNER JOIN B

The image below shows how an in inner join works.

THE CROSS JOIN

It matches every row of the first table with every row of the second table.
If the inputs in tables have x and y columns respectively, the resulting table will have x+y columns.
Cross joins have the potential to generate extremely large tables and care has to be taken to use them in appropriate. example of synatx used to perform cross joins;

SELECT *
FROM Table1
CROSS JOIN Table2 ;

An image showing an example of a cross join;

THE LEFT OUTER JOIN

Its an extension of the inner join. SQL standard defines three types of outer joins;LEFT,RIGHT and FULL outer join. -In case of a left outer join an inner join is performed first then each row in table T1 that does not satisfy the join condition with any row in table T2,a joined row is added with null values in columns of T2.So the joined table has at least one row for each row in T1. The following is an example of sql script used to perform left outer join;

SELECT...
FROM Table1
LEFT OUTER JOIN Table 2
ON Conditional expression

An image showing how left outer join operates;

THE RIGHT OUTER JOIN

For this first an inner join is performed, then for each row in a table T2 that does not satisfy the join condition with any row in table T1, a joined row is added with null values in column of T1.
Its a converse of left outer join The following is a syntax for right outer join;

SELECT...
FROM Table1 
RIGHT OUTER JOIN Table2
On conditional expression...

The image below show a right outer join performed in a dataset;

FULL OUTER JOIN

First, an inner join is performed then for each row in table T1 that does not satisfy the join condition with any row in table T2, a joined row is added with null values i column of T2.
For each row of T2 that does not satisfy the join condition with any row in T1, a joined row with null values in the columns of T1 is added. The following is a syntax of full outer join;

SELECT...
FROM Table1
FULL OUTER JOIN Table2
ON Conditional expression...

The following image shows a full outer join performed on a dataset;

Windows functions in postgres sql.

windows function are also known as analytic functions.
They perform calculations across a set of table rows that are somehow related to the current row.
They do not collapse rows into a single output instead they allow each row to retain its unique identity while including calculated value as an additional column. Main types of window functions;

1.Ranking functions-These assign a rank or number to rows based on order.
The following re some of rank function under sql;

Row_Number(): Unique, sequential number for each row. syntax for row number function.

ROW_NUMBER() OVER (
    [PARTITION BY expr1, expr2,...]
    ORDER BY expr1 [ASC | DESC], expr2,...
    )

Rank() : Ranks with gaps for ties (eg 1,2,2,4).syntax for rank function

RANK() OVER (
    PARTITION BY <expr1>[{,<expr2>...}]
    ORDER BY <expr1> [ASC|DESC], [{,<expr2>...}]
        )

Dense_Rank():Ranks without gaps for ties.syntax for dense rank

DENSE_RANK() OVER (
   PARTITION BY expression1 [{,expression2...}]
   ORDER BY expression1 [ASC|DESC], [{,expression2...}]
    )

NTILE(n):It distributes rows of an ordered partition into a specified number of approximately equal groups, or buckets. The syntax of the NTILE() function is as follows:


    NTILE(buckets) OVER (
    [PARTITION BY partition_expression, ... ]
    ORDER BY sort_expression [ASC | DESC], ...
    )

Aggregate window function -They are standard math functions used over a window. They are classified into five functions mainly;
SUM()-used to calculate the total sum of values within a specified numeric column.It ignores NULL values in the column.syntax for sum is as follows.

 SELECT SUM(column_name) FROM table_name;

AVERAGE AVG()-function returns the average value of a numeric column.It ignores the NULL values in the column. Syntax for average function is shown below;

SELECT AVG(column_name)
     FROM table_name
     WHERE condition;

COUNT ()-it returns the number of rows returned by a query. It counts duplicate rows and rows that contain null values. Syntax for count is as shown below;

SELECT COUNT(column_name) FROM table_name;

MIN ()- finds the smallest value in a numeric or date column. syntax for min function is shown below;

SELECT MIN(column_name) FROM table_name;

MAX() -finds the largest value in a numeric or date column. syntax for max function is shown below;

SELECT MAX(column_name) FROM table_name;

VALUE(Navigation)functions_These functions help to access other rows within the result in the window,therefore helping in comparisons based on the data returned in the column.The following are functions classified under the value functions;

Lag function provides access to a row at a specified physical offset which comes before the current row.
You can access data of the previous row, or from the second row before the current row, or from the third row before current row, and so on.
The following illustrates the syntax of the LAG() function:

LAG(return_value [,offset[, default_value ]]) OVER (
    PARTITION BY expr1, expr2,...
    ORDER BY expr1 [ASC | DESC], expr2,...
   )

first /last value function_It returns data from the first value dispalyed in the window while the last returns data from the last cell displayed in the window. syntax for first value is shown below;

FIRST_VALUE ( [ scalar_expression ] ) [ IGNORE NULLS | RESPECT NULLS ]
    OVER ( [ partition_by_clause ] order_by_clause [ rows_range_clause ] )

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

George Mathenge — Mon, 09 Feb 2026 14:52:46 +0000

A data analyst main goal is to clean data and assist in decision making. They assist in spotting trends performance and communicate insights faster from provided by the data. For one to perform all this the analyst uses a strong software and one of them is the power BI( business intelligence)

Introduction
Power BI (Business Intelligence) is a Microsoft analytics platform that transforms raw data from various sources into interactive reports and dashboards, enabling users to visualize trends, gain insights, and make data-driven decisions without extensive coding.
It functions as a collective of different software collection of software services, apps, and connectors that work together to turn unrelated data sources into coherent, visually immersive reports and dashboards

How it functions

-Data transformation: It has a data transformation interface where one can connect to various data source e.g.. excel ,sql etc.

Business Intelligence (BI): Helps businesses understand performance, spot opportunities, and monitor health through live data.
Self-Service Analytics: Offers tools for users with varying data skills (even Excel users) to analyze and share data easily.
Actionable Insights: Makes complex data understandable for business users, empowering them to make informed decisions.

The image below show the data transformation interface:

Messy data
Its simply raw unformatted data, or inconsistent data sources—such as Excel files or databases—that contain errors, missing values, duplicates, or improper structures. This prevents accurate analysis, requiring cleaning via Power Query to transform it into a structured, usable format.

commonly used solutions in power bi:

Power query- its a powerful Microsoft data transformation and extraction (ETL) engine used to connect, clean, and reshape data from various sources. Integrated into Excel and Power BI, it enables users to automate data preparation without coding, allowing for easy updates to data, such as merging files or unpivoting tables, which saves significant time.
DAX (Data Analysis Expressions): Used to create calculated columns and measures to fix data logic issues.

3.Unpivot Columns: Used to turn columns into rows for proper modeling.

lets look at how DAX operates ;
DAX-Data Analysis Expressions (DAX) is a formula language used in Power BI to create custom, dynamic calculations and enhance data analysis capabilities.
operates on the data model that is already loaded into Power BI, enabling users to transform raw data into meaningful insights.
DAX formulas are used in various categories in power BI to create new data model;

Measures: Dynamic calculations (e.g., total sales, average profit) used in visuals that aggregate data based on the current filter context.

calculated columns: New columns added to existing tables whose values are computed and stored in the data model at data refresh time. These are best for static, row-level categories or attributes.

Commonly used functions in DAX;

Aggregation functions they summarize multiple rows of data into a single, scalar value, such as total sales, average, or count. They enable data analysis by condensing large datasets into meaningful, actionable insights for reports and visualizations, typically used in measures or calculated columns. Examples SUM,SUMX, AVERAGE,MIN, MAX etc. Total Sales = SUM(Sales[Amount]
Filter Functions - These control the data context for calculations. Key functions include CALCULATE, FILTER, ALL, RELATED, and VALUES.
Text Functions- Used for manipulating text strings. Functions include CONCATENATE, LEFT/RIGHT/MID, UPPER/LOWER, TRIM, and SUBSTITUTE.
Time Intelligence Functions - Used for time-based calculations, such as TOTALYTD/TOTALMTD, SAMEPERIODLASTYEAR, DATEADD, and DATESBETWEEN.
Table Manipulation Functions -These functions return a table. Examples are SUMMARIZE, ADDCOLUMNS, TOPN, and DISTINCT.
*Logical Functions -These handle conditional logic. Examples are IF, SWITCH, AND/OR/NOT, and IFERROR.
Information Functions -These check the type or state of a value, such as ISBLANK, ISNUMBER/ISTEXT/ISLOGICAL, and USERELATIONSHIP.

DASHBOARDS
After cleaning and adding the right KPI'S in your data then it comes the visualization part. Power bi uses dashboard to easen easy understanding of the data.

What are dashboards in power BI;
a single-page interactive canvas in the Power BI service that summarizes key metrics, known as KPIs, using visual tiles pinned from various reports. The main aspects of a dashboard are as follows;

Real-time Data: Dashboards update in real-time, providing the latest data without requiring user interaction.
Interactivity: visuals are linked; clicking one takes you to the underlying report for detailed analysis.
Single Page Only: Unlike reports which can have multiple pages, a dashboard is limited to one page ("canvas").

An example of dashboard created from an already analyzed data table:

When creating dashboards avoid the following;

Too many visuals: Do not cram every chart onto one page. Limit to about 3–5 key visuals to avoid overwhelming viewers.
Unnecessary Complexity: Avoid overly complex charts that require training to understand.
Ignoring Key Metrics: Failing to highlight the most important KPIs immediately.
Too Many Slicers: Using too many filters/slicers on a page slows down report

5.Missing Labels: Never leave axes, titles, or legends unlabeled.

CONCLUSION

Translating messy data before actual data analysis is critical in every analytics.PowerBi offers clean platform and easy visualization of data to assist in decison making.

kindly share your views and areas of rectification in the comment section.
kind regards.

Schemas and Data Modelling in Power BI - Core Concepts:

George Mathenge — Sun, 08 Feb 2026 18:37:08 +0000

what's data modelling;
Its a concept used in power bi in organizing tables and defining relationships between them. Its the art of analyzing defining of different data types where relationships between bits of da ta are created.
Some of the benefits of data modelling:

1.saving time and money
It saves time and lots of process in investments through appropriate planning.
2.Well structured data system
With data modelling one is able to connect between business and able to do analysis easily.
3.Reducing errors
it reduces errors by ensuring non redundant data doesn't enter in your data system.

4.Tracking and setting target.
Give's indicators and timely tailored performances targets to your business objectives.

An example of data modeling in power BI as shown in the diagram below.

SCHEMAS
They are logical framework and organization of tables, relationships and data structure with a sematic model acting as the blue print.
We have different types of schemas;

1.Star Schema
This most used type of schema used in powerbi.Star schema is a mature modeling approach widely adopted by relational data warehouses. It requires modelers to classify their model tables as either dimension or fact.
An example of star schema.

What is a fact table;
A fact table contains dimension key columns that relate to dimension tables, and numeric measure columns. The dimension key columns determine the dimensionality of a fact table, while the dimension key values determine the granularity of a fact table.
Generally, dimension tables contain a relatively small number of rows. Fact tables, on the other hand, can contain a large number of rows and continue to grow over time.

2.Snow flake schema
snowflake schema is a type of data modeling technique used in data warehousing to represent data in a structured way that is optimized for querying large amounts of data efficiently It is a variant of the star schema, where the centralized fact table is connected to multiple dimensions. It has several key characteristics;

Hierarchical Structure: The schema has a hierarchical structure organized around a central fact table.
Multiple Levels: The schema can have multiple levels of dimension tables, allowing for more granular analysis of data.
Joins: The schema typically requires more complex SQL queries involving multiple table joins. -Joins: The schema typically requires more complex SQL queries involving multiple table joins.

Example of a snowflake kind of schema.

schema patterns between star and snow flake schemas.

Among the two the most used is the star schema is the most used as its easy to do understand and its well defined to allow faster querying and smooth report interaction.

Beginners guide to version control with Git and GitHub.

George Mathenge — Sun, 18 Jan 2026 09:03:02 +0000

Git is a strong and powerful version control system which developers track changes in their codes. They do also collaborate with others ad manage projects efficiently. Version control so important because it allows you keep a history of work done and revert to previous versions and work with teams with necessarily overwriting each other code. For starters you need to install GitBash.GitBash provides a command line interface that allows you to interact with Git easily.You then connect Git Bash with your git hub account for easy storage of your codes and collaborating with others.
You start pushing code to GitHub by saving your changes and uploading them to a remote repository. Its ensures your work is backed up and easily accessible from anywhere. You can also pull a code from GitHub to download updates made to a repository keeping you local project upto date.
Git also make it easy for one to trac changes done. It records what was modified and when it was changed and why, helping one understand the project and its evolution.