DEV Community: Faith Neno

Joins and Window Functions in SQL

Faith Neno — Fri, 06 Mar 2026 09:16:23 +0000

SQL (Structured Query Language) is essential for working with relational databases. Two powerful tools in SQL are Joins and Window Functions. They allow us to combine data from multiple tables and perform complex calculations efficiently.

JOINS
Joins are the foundation of multi table query processing in SQL.
It is a clause used to combine rows from two or more tables.
Joins allow us to work with multiple tables and allows us to join data from different tables.
We need to have a primary key and a foreign key for us to use joins.
Primary and foreign keys allows us to reference from our tables uniquely.
Types of Joins
EMPLOYEE TABLE

DEPARTMENT TABLE

We are going to use the above tables to understand joins.

1.INNER JOIN
Returns only the rows that match or matching values in both tables.
If you want to find the rows with matching values on both the tables you do an Inner Join using the SQL query below;

Result of the query;

2.LEFT JOIN
Returns all the rows from the left table and the matching rows from the right table
For example,lets use the LEFT JOIN to find all employees and their respective departments.
SQL Query;

Query results;

3.RIGHT JOIN
This join returns all rows from the right table.
If a row in the right table has no corresponding match in the left table, the columns from the left table will contain Null values in the result set.
Our table 'Employees' and 'Departments' has a shared column 'department_id', we can do a RIGHT JOIN to find all departments and their respective employees.
SQL Query;

Result query;

4.FULL OUTER JOIN
Combines both left and right join and it shows all rows from the table. If their are no matches it will contain a NULL.
Example;
Let's do a full outer join to find the list of employees and department.
SQL Query;

Result query;

7.NATURAL JOIN
Joins all tables using columns that have the same name.
select * from employees natural join departments;

WINDOW FUNCTIONS
SQL window functions perform calculations across a set of related rows (a "window") and return a single value for each original row, without collapsing the rows. They contrast with standard aggregate functions that return a single value for an entire group of rows.

Window Syntax
OVER() - defines the window
PARTITION BY - splits data into groups
ORER BY - defines calculation order

1.ROW_NUMBER() FUNCTION
Assigns each number to row.
Ranks without ties.
Lets assign the row numbers to the sales;

Result;

2.RANK()
Assigns ranks to rows, leaves gaps in the ranking where their are ties, within a window.
For example, let's assign row number to orders based on quantity from highest to lowest
SQL Query;

Result of query;

3.DENSE RANK()
Assigns the same rank to tied values.
Does not skip rank numbers.
For example let's assign row number to orders based on quantity from highest to lowest.
SQL Query;

Result Query;

Aggregate Window Functions
SUM()
Calculates totals across a partition.
AVG()
Computes average values over a window.
COUNT()
Counts rows within a partition.
MIN()
Returns minimum value in a window..
MAX()
Returns maximum value in a window

Navigation (Value) Functions
LEAD()
Retrieves value from a next row.
LAG
Retrieves value from the previous row.

Summary
Joins combine tables using a related column.
Window Functions perform calculations across a window of rows.

Translating Messy Data, DAX, and Dashboards into Action Using Power BI

Faith Neno — Tue, 10 Feb 2026 13:58:18 +0000

Introduction to Power BI
Power BI is a tool used to transform messy data into interactive insights that support business decision. The process involves three key stages: cleaning and preparing data, building calculations using DAX, and designing dashboards that communicate insights clearly.

Cleaning Data
Power Query is a tool that allows data connection, transformation, and loading.
First we need to load the Data to Power BI:
Open Power BI Desktop > Get Data > Excel > Select File > Load Sheet
Then Transform Data:
Click Transform Data.
Power query is used in transforming raw data through:

Remove duplicates and irrelevant columns: Select the columns > Remove Duplicates
Change Data Types: Texts, numbers, dates, currency
Handle Errors.
Standardize formats. -Filtering irrelevant records.

DAX Functions
DAX (Data Analysis Expressions) is a formula language used in Power BI to create
calculations and data analysis logic.
DAX is used to build measures, calculated columns, and calculated tables that help transform raw data into meaningful insights.

Examples:
SUM
Adds all numeric values in a column.
Total Profit = SUM('Kenya Crops'[Profit (KES)])

AVERAGE
Calculates the mean of a numeric column.
Average Revenue
Average Revenue = AVERAGE('Kenya Crops'[Revenue (KES)])

We use New Measure if you expect a single output while we use New Column if the expected output is multiple rows corresponding to the data.

Dashboard Creation
A Power BI dashboard should answer questions instantly, not just display numbers.

Effective dashboards:

Highlight KPIs at the top
Use the right visual for each question
Allow filtering using slicers
Tell a clear story

Example

Conclusion
Conclusion
Power BI enables analysts to bridge the gap between raw data and strategic decision-making. Power BI is more than charts and visuals. Analysts use it to:

Clean and structure messy data.
Build models that reflect real business processes.
Apply business logic using DAX.
Deliver dashboards that support confident decision-making.

Data Modelling and Schemas in Power BI

Faith Neno — Sun, 01 Feb 2026 12:00:39 +0000

Understanding Data modelling in Power BI
Data modelling is process of organizing data tables and defining relationships between them in a way that reflects real-world business processes. In Power BI, the data model sits between raw data sources and visualizations, acting as the foundation for calculations, filters, and aggregations.

Types of data models
1.Conceptual data modelling; It defines what data a business needs and how different concepts relate .i.e customers, product.
2.Logical data modelling; add columns and attributes facts and dimensions; how you find all this entities and how they are related.
3.Physical data modelling; This is how data is stored ,organized and accessed at the physical level i.e product key. This type of data model consists of a data management system (DBMS); certain specific properties comprise performance turning.

Terms
Dimensional model -Organizes data so its easy to retrieve for reporting purpose.
Fact table -Is an event that may or may not include measure
Dimension table -Category of information ,or a noun, descriptive.
Attribute -Column in dimension table. Descriptor of the object.

Attributes of good data model

Can be easily understood and consumed.
Provides predictable performance.
Large data are scalable.
Flexible and adaptable.

Fact and Dimension Tables
Fact Table
The fact table in a dimensional model stores the performance measurements resulting from an organization’s business process events.
The term fact represents a business measure.
Example: cost, discount, quantity sold

Fact table typically,

Have many rows (high granularity)
Store numeric values used in aggregations (SUM, AVERAGE, COUNT)

Dimension Table
Dimension Tables for Descriptive Context.
Dimension tables are integral companions to a fact table.
The dimension tables contains the textual context associated with a business process measurement event.
They describe the “who, what, where, when, how, and why” associated with the event.
Example:
Dimension tables often have many columns or
attributes. It is not uncommon for a dimension table to have 50 to 100 attributes.
Dimension tables;

have fewer rows than fact tables, but can be wide with many large text columns. Each dimension is deﬁned by a single primary key.
contains descriptive characteristics of business process nouns.
Dimension attributes serve as the primary source of query constraints, grouping and report labels.

Example; Date,region,Customer details.

Star Schema
Dimensional models implemented in relational database management systems are referred to as star schemas because of their resemblance to a star-like structure.
The fact table is at the center and dimension are around it.
Their no relationship between dimension tables.
Its easier to write DAX measures.

Snowflake Schema
This is a variation of star schema.
The dimension table is not one joint away, hence increasing model complexity .

Relationships in Power BI
Relationships define how tables interact data models.
Terms
Cardinality One-to-many , one-to-one, many-to-many
Cross-filter direction Single or both
Active vs inactive relationships

Importance Good Data Modelling

Accuracy in reporting.
Improved Data Quality and Integrity
Enhanced performance.
Scalability and flexibility.

In conclusion, a good data modelling is critical because it directly impacts Power BI's performance, accuracy and sclability. A well structured model ensure fast queries, correct calculations and intuitive reporting, while poor modelling leads to slow dashboards, incorrect insights and wasted memory.

MS Excel for Data Analytics: A Beginner-Friendly Introduction

Faith Neno — Sun, 25 Jan 2026 15:08:17 +0000

Microsoft Excel is a tool that is everywhere, in schools, offices and even personal projects.
Yet many people, myself included, avoided it because it looked complicated.
Microsoft tool is widely used in finance(budgets, expenses),sales reports, Inventory management and data analysis

This article breaks Excel down into simple, beginner-friendly concepts so you can start using it with confidence.

Download
If you do not have Microsoft excel downloaded,here is the link https://igetintopc.com/microsoft-office-2024-professional-plus-free-download/

The Excel Interface
Key terms used in this article:

1. Cell - A single box where a row and column meet (example: A1).
2 Column - A vertical group of cells labeled with letters (A, B, C…).
3 Row - A horizontal group of cells labeled with numbers (1, 2, 3…).
4 Worksheet Tabs- At the bottom: Sheet1, Sheet2, Sheet3, etc. Each tab is one sheet in the same Excel file.
5 Worksheet - One page inside an Excel file where you enter and analyze data.
6 Workbook - An Excel file that contains one or more worksheets.
7 Dataset - A collection of related data arranged in rows and columns.
8 Formula - An equation used in Excel to perform calculations (example: =A1+B1).
9 Function - A built-in formula in Excel that performs a specific task (example: SUM, AVERAGE).
Range - A group of selected cells (example: A1:A10).
10 Pivot Table - A tool that summarizes and analyzes large datasets by grouping and calculating data automatically.
11 Chart - A visual representation of data, such as a bar chart or line graph.
12 Data Cleaning - The process of fixing or organizing data so it is accurate and ready for analysis.

Data Cleaning and Formatting
Text Formatting:
Select the cells -> Go to the Home tab ->In the Font group:
-Click B for Bold.
-Click I for Italic.
-Click U for Underline.
-Change font type and font size.

Number Formatting
Number formats convert numerical data into readable formats like percentages,currency,date and time without changing their actual value.
Steps: Select the cells with numbers > Home tab > Number group. Then use the dropdown to choose Number, Currency, Percentage, Date and click OK

Conditional Formating
Select the dataset > click home > conditional formatting > highlight cell rules > choose a color

Removing duplicates
Select your dataset > Go to Data tab > Remove Duplicates > Select the columns that define a duplicate(e.g Email) > Click OK.

Excel will shows a message with how many duplicate rows were removed.

Sorting Data
Sorting changes the order of rows based on chosen columns.
Types of sorting:
∙ Text: A to Z or Z to A
∙ Numbers: Smallest to Largest or Largest to Smallest
∙ Dates: Oldest to Newest or Newest to oldest.

Click a cell in the column you want to sort > Go to Data > Sort A to Z or Sort Z to A. Excel will sort the entire table based on that column.

Basic Calculations & Core Functions
A formula is an instruction for Excel to calculate something.
Formulas must start with an EQUAL TO SIGN ( = )
The Arithmetic operators are as follows:
Addition
Adds numbers in a range.
=A1+B1 → 14
Subtraction =A1-B1 → 6
Multiplication =A1*B1 → 40
Division =A1/B1 → 2.5
Power(exponent) =A1^B1 → 10^4 = 10000

FUNCTIONS
SUM
Examples
Total salary for all employees (rows 2 to 7):
=SUM(f2:f7) where f is Salary

AVERAGE
Mean
e.g average salary of all employees
=AVERAGE(F2:F7)

MAX
=MAX(F2:F7)

MIN
=MIN(F2:F7)

COUNTIF
=COUNTIF(F2:F7,">80,000")

LOOKUP FUNCTIONS
VLookup
This function searches for a value vertically in the first column of a table and returns a value from another column in the same row.

HLookup
It searches for a value horizontally across the first row and returns a value from a specified row.
-Used only when data is arranged horizontally.

XLookup
It replaces VLOOKUP and HLOOKUP.
Can look left or right and handles errors automatically.

Date and Time Functions
Now
Returns the current date and time.
Today
Returns the current date.
=Today()
DateIf
Calculates the difference between two dates.

=DATEDIF(F2,TODAY(), "y")

PIVOT TABLES
Allows one to summarize, analyze, and explore large datasets like HR data without writing formulas.

Steps to create a Pivot Table

Click anywhere inside your dataset
Go to Insert → PivotTable
Excel automatically selects the dataset Table
Choose “New Worksheet”
Click OK
Drag fields into rows (departments), columns and values(salaries).

Using Filters in PivotTables
Filters affect the entire PivotTable.
Example ;
Show salary data for only Full-Time employees.
Steps;
• Drag Full-Time → Filters
• Select Yes
Now all results reflect only Full-Time staff.
You can also add multiple filters needed.

Pivot Charts
This are charts linked to PivotTables.
Steps:
• Click inside PivotTable
• Insert → PivotChart
• Choose chart type

Slicers

It provide visual filters.
Steps;
• Click PivotTable
• Insert → Slicer
• Select fields
• Click OK
Example-Add slicers for department.[You can now filter data needed according to department]

Creating Dashboard
A dashboard is a visual representation of of key data and metrics on one screen. It summarizes data in one place.
It is used to tell a story.

Dashboard Principles
• One screen only (no scrolling)
• Focus on key KPIs, not raw data
• Consistent colors and fonts
• Clear titles and labels
• Interactive but simple

Example

Summary
Excel is a foundational tool for data analytics. Mastering gives you a strong foundation before moving into advanced analytics tools like Power BI, Python, or SQL.

I hope this article was helpful and gave you a solid starting point.
Good luck on your data analytics journey!