DEV Community: SAMUEL

Joins and window functions in SQL.

SAMUEL — Thu, 05 Mar 2026 04:35:15 +0000

Join Operations.

Joins Joins are used to combine data fromtwo or more tables based on related column(s) between them.
The result of a join is a new table that includes columns from both tables, arranged side by side.

Types of join operations

INNER JOIN
LEFT JOIN (or LEFT OUTER JOIN)
RIGHT JOIN (or RIGHT OUTER JOIN)
FULL JOIN (or FULL OUTER JOIN)
CROSS JOIN
SELF JOIN
NATURAL JOIN

INNER JOIN. This is a type of join that returns only the rows from both tables that returns only from both tables where there is a match the specified columns in each table. It filters out the rows that do not have corresponding matches in both tables.
SELECT
Columns
FROM table 1
INNER JOIN table 2
ON table1.Column_name = table2.Column_name;

Example; List all the orders made and the names of customers that made them.

LEFT JOIN. LEFT JOIN is also known as LEFT OUTER JOIN. Returns all the rows from the left table and only matching rows from the right table. If there is no match in the right table, null values are returned for the columns of the right table.

SELECT
Columns
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Example; List all employees and all projects assigned.

RIGHT JOIN Right join is also known as RIGHT OUTER JOIN, returns all rows from the right table and the matching rows from the left table. If there is no match from the left table, NULL values are returned for the columns from the left table.

SELECT
Columns
FROM table1
RIGHT JOIN table2
ON table1.column = table2.column;

Example; List all employees and their departments.

FULL OUTER JOIN A FULL OUTER JOIN returns all the rows from both tables, including unmatched rows from both the left and right tables. If there is no match in either table, NULL values are returned for the columns of the respective table.

SELECT
Columns
FROM table1
FULL OUTER JOIN table2
ON table1.column = table2.column;
Example; List all employees and all projects assigned.

CROSS JOIN A CROSS JOIN returns every combination of rows from both tables
SELECT *
FROM table1
CROSS JOIN table2;
Example; Assign every employee to every department

SELF JOIN A SELF JOIN is when a table joins with itself. You need to use aliases to refer to the same table in two roles.
Example; List all employees and their managers.

NATURAL JOIN A NATURAL JOIN automatically joins tables using all columns that have the same name.
SELECT *
FROM table1
NATURAL JOIN table2;
Example;

Window Functions.

Window functions allow us to perform certain operations on a subset of related rows, called a window, and return a value for each row in that set.
Aggregate window functions are a group of aggregate functions, such as SUM(), COUNT(), AVG(), MAX(), and MIN(), that calculate aggregate values within a window and return a result to each row.
For example, you can use window functions to rank rows, calculate
cumulative totals, or find the difference between consecutive rows in a dataset. Unlike aggregate functions like SUM() or AVG(), which return one result for a group of rows, window functions return a value for each row while still providing information from the related rows.

ROW_NUMBER() Assigns a unique sequential number to each row within a window partition based on the ordering of a column by the ORDER BY clause. No two rows are given the same number.
SELECT
Column_X,
Column_Y,
ROW_NUMBER() OVER (
PARTITION BY Column_X
ORDER BY Column_Y) AS ALIAS
FROM
Table_name;

RANK() Assigns a rank to each row within a window partition based on the ordering of a column by the ORDER BY clause. Rows with the same values receive the same rank, and the next rank is skipped accordingly.
SELECT
Column_X,
Column_Y,
RANK() OVER (
PARTITION BY Column_X
ORDER BY Column_Y) AS ALIAS
FROM
Table_name;

DENSE RANK Assigns a rank to each row within a window partition based on the ordering of a column by the ORDER BYclause. Rows with the same values receive the same rank but no ranks are skipped.
SELECT
Column_X,
Column_Y,
DENSE RANK() OVER (
PARTITION BY Column_X
ORDER BY Column_Y) AS ALIAS
FROM
Table_name;

LEAD Allows the access of a value within a column from the following nth-row relative to the current row. The lead value for the last row within a partition will be NULL since there is nonext value.
SELECT
Column_X,
Column_Y,
Column_Z,
LEAD(Column_Z, n) OVER (
PARTITION BY Column_X
ORDER BY Column_Y) AS Alias
FROM
Table_name;

LAG Allows the access of a value within a column from the previous nth-row relative to thecurrent row. The lag value for the first row within a partition will be NULL since there is no previous value.

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

SAMUEL — Tue, 10 Feb 2026 05:15:06 +0000

Data analysts are tasked to provide actionable insights from messy data. Analysts use power BI to translate messy data into actionable insights.

Messy Data.

The common reality that anlysts face is data provided is often not clean. In the data provided for analysis, there will be be issues of inconsistent formats, duplicate values and missing values.

Data Cleaning and Transformation with Power Query.

Its at this stage that anlysts apply power query to clean the data for correct insights.
Data clean has to thorough as the saying goes "gabbage in, gabbage out" and so every anlyst has to ensure that the data used to do the analysis is correct and clean.

Power query application.

Power Query User Interface

For example from the the snip-short shown above there is evidence of empty cells. To correct this, the analyst can opt to replace the empty cells with "Unknown"

An analyst can also remove duplicates from the menu in this interface.

DAX Functions.

DAX(Data Analysis Expressions) is a formula 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.
DAX is designed specifically for analytical and business intelligence tasks such as totals, averages, percentages, rankings, comparisons, and time-based analysis.
DAX formulas are mostly used to show the main KPIs.

Aggregation
Aggregation is the process of combining multiple rows of data into a single summarized value. In Power BI, aggregation is used to answer questions like:

What is the total revenue?
What is the average yield?
What is the maximum profit?

Aggregation converts raw data into meaningful summaries like totals, averages, medians, minimums, maximums, and counts. It is the foundation of all reporting and dashboards.

Types of Aggregation functions in DAX

SUM AND SUMX
AVERAGE AND AVERAGEX
MEDIAN AND MEDIANX
MIN AND MINX
MAX AND MAXX
COUNT, COUNTROWS, AND COUNTX
ABS (Absolute Value)
POWER
SQRT (Square Root)
ROUND, ROUNDUP, ROUNDDOWN
MOD

logical Functions in DAX

Logical functions in DAX are used to make decisions based on conditions. They allow Power BI to answer “yes or no” questions, classify data into categories, apply business rules, and control how results are calculated and displayed.
Logical functions are commonly used in calculated columns, measures, and KPIs.

THE IF FUNCTION
NESTED IF STATEMENTS
IF WITH AND (AND FUNCTION)
IF WITH && (LOGICAL AND OPERATOR)
IF WITH OR (OR FUNCTION)
IF WITH || (LOGICAL OR OPERATOR)
THE SWITCH FUNCTION
NOT FUNCTION
ISBLANK FUNCTION
COALESCE FUNCTION

Dashboards.

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.

Conclusion

Power BI skills aren’t about fancy charts or complex formulas—they’re about asking better questions and creating clearer answers. Whether you’re looking to reduce costs, increase revenue, or improve customer satisfaction, the path starts with transforming data into decisions.

Schemas And Data Modelling In Power BI.

SAMUEL — Sun, 08 Feb 2026 09:29:30 +0000

Introduction.

Importance of data in modern business intelligence
In modern business, businesses correct alot of data from their daily operations, this data is the one that is analysed to turn it into actionable insights.

Power BI
Power BI is a tool created by Microsoft to turn raw data into interactive insights. Think of it as a supercharged Excel, but specifically designed for creating stunning reports and dashboards that anyone in your organization can use. The power of Power BI lies in its ability to pull data from many sources, clean it, analyze it, and then create visuals that are easy to understand and actionable.
Power BI reports start with great data modelling, even before a single visual is created. A strong model improves performance, ensures accurate reporting and provides a scalable foundation for analytics. In this article, we explore fact and dimension tables, star and snowflake schemas, relationships and the importance of good modelling in Power BI

Data modelling in power BI

Data modelling refers to the process of structuring data into tables, defining relationships between them, and optimizing them for analytical queries. In power BI, the data model sits at the heart of the solution, powering calculations written in DAX and driving how filters and aggregations behave across visuals. Power BI models are optimized for fast read and aggregation operations.
Poor medelling leads to slow, inaccurate, or confusing reports.

Schemas

A schema represents a logical grouping of tables that are related to each other. It acts as a blueprint defining how fact tables (metrics) and dimension tables (attributes) connect to enable efficient reporting, analysis and data modelling.

Star Schema
A star schema consists of a central fact table that stores quantitative data and multiple dimension tables that provide descriptive attributes. The layout resembles a star, with the fact table at the centre and dimension tables outward.

The fact table Sales is in the center. It holds the numeric data you want to analyze, like sales or profits. Connected to it are dimension tables with descriptive details, such as product names, customer location or dates

Fact Tables
Fact tables store observations or events and can be sales orders, stock balances, exchange rates, temperatures and more. A fact table contains dimension key columns that relate to dimension tables and numeric measure columns.
Dimension tables contain a relatively small number of rows, whereas fact tables contain a large number of rows.

Snowflake Schema
The snowflake is a schema that consists of one fact table that is connected to many dimension tables, which are connected to other dimension tables.

Relationships in power BI
Relationships define how tables connect and how filters propagate through the model.

Types of relationships include
One to one Relationship: Each row in Table A relates to exactly one row in Table B, and vice versa.
Example: A Person table and a Passport table, where each person has only one passport.
One to many: A row in Table A can relate to many rows in Table B, but each row in Table B relates to only one row in Table A.
Example: A Customer table and an Orders table, where one customer can place many orders.
Many to many: Rows in Table A can relate to many rows in Table B, and vice versa.
Example: A Students table and a Courses table, where students can enroll in multiple courses, and courses can have multiple students.

Good data modelling in power BI Isn't about complexity, it's about clarity. A good schema with proper relationships gives you faster reports with accurate numbers.
A strong model-built around a star schema, clean fact/dimension separation and well-defined relationships leads to high performance, reliable insights, simpler DAX and long‑term scalability

Data Analysis Using MS Excel.

SAMUEL — Sun, 25 Jan 2026 21:21:43 +0000

Microsoft Excel is a sreadsheet program developed by Microsoft. It allows one to:

Collect data.
Organize data.
Analyze and calculate numbers.
Visualize data using tables and charts.

This article will cover;

Understanding microsoft excel.
Identifying rows, columns, and cells in a worksheet
Entering and organizing data in table format
Use of basic excel functions such as SUM, AVERAGE and COUNT
Cleaning data and removing duplicates.
Creating pivot tables.
Building charts to visualize trends and comparisons.

Getting started with Excel.

Workbook: An Excel file containing more than one worksheet.
Worksheet: An individual sheet where data is entered.
Columns: Vertical sections labeled with letters (A, B,...) used to organize data.
Rows: Horizontal sections labeled with numbers (1, 2, ...) where records are stored.
Cell: An intersection of a row and a column (A1, B1,...).
Range: A group of two or more selected cells, e.g., A1:A10, represents a range of cells in column A.
Ribbon and key tabs: A toolbar at the top of Excel that contains commands grouped into tabs such as Home, Insert, Formulas, and Data.
Formula: An equation that performs calculations on values. Formulas use arithmetic operations and must start with =
Filter: A tool used to show or hide specific rows based on specified criteria.
Sort: A tool used to arrange data in either ascending or descending order.

Below is an overview of the Excel interface showing key components.

Entering and organizing data.

Creating a worksheet: Open Excel and type your data into rows and columns.
Using headers: Label your columns.
Formatting cells: Adjust column width, bold headers, and use borders to make data neat.

Cleaning and Preparing Data

Sorting data: Arrange numbers from smallest to larget or alphabetically.

Filtering data: Shows only the rows you need.

Removing duplicates: Delete repeated entries to keep data accurate

Using Basic Formulas.

SUM: Add up values.
AVERAGE: Find the average of numbers.
COUNT: Count how many entries you have.
MIN/MAX: Find the smallest and largest values.

Conditional Formatting.

Highlighting values: Automatically highlighting cells with a color based on a set condition e.g. duplicate values.
Color Scales: Show trends with gradients (e.g., red for low, green for high).

Creating charts and Graphs.

Bar chart: Compare categories (e.g sales by month)
Line chart: Show trends overtime.
Pie chart: Show percentages of a whole.

PivotTables.

A pivottable summarizes large data sets quickly.
Creating a pivottable:Select data → Insert → PivotTable.

Rearranging fields: Drag and drop to see totals by category, month or product

Microsoft Excel is a powerful and beginner-friendly tool for data analytics. It helps users organize data, clean it, perform calculations, and visualize insights using charts.

Excel is a great starting point for anyone getting into data analysis.

Git: A Guide to Version Control, Push, and Pull.

SAMUEL — Sun, 18 Jan 2026 10:19:53 +0000

Introduction to Git and version Control.

What version control is and why it matters. Git is a system that helps manage a project. Insted of keeping one copy of your work version control with git creates a history of snapshots called commits. Each commit records the state of your project at a specific moment, so you can always revisit, compare or restore past versions.

Benefits of using Git.

Safety & Reliability. Automatic backups using commit is a snapshot of your project so you never lose your work. Easy recovery if something breaks, you can roll back to the version.
History & Tracking. There is a detailed change logs of who mdae changes, what changed and when. It helps compare versions and see differences between any two points and helps maintain accountability.
Collaboration. Multiple developers can work on the same project without overwriting each other's code. Developers can merge changes safely and git provides tools to handle overlapping edits.
Git makes coding safer, smarter and more collaborative.

Difference between local and remote repositories

A local repository is stored on your computer and lets you work offline, while a remote repository is hosted on a cloud server like Github and enables collaboration, back up and sharing across multiple developers.
In a local repository you make changes stage them using git add and the then git commit. You push your commits using git push to the remote repository to share with others.

Setting up Git

Installing Git on your system.

Download and install git

Configuring username and email.

git config --global user.name "your Name"
git config --global user.email"Your.email@example.com"

SSH Key Generation.

ssh-keygen -t ed25519 -C "your.email@example.com
eval "$(ssh-agent -s)"
cat ~/.ssh/id_ed25519.pub

This commands wil generate a key that you use to connect to your git hub account/ remote repository.

Creating your first local Repository

A repository is a folder that holds and tracks all your project.

Create a folder and file for your project
mk dir folder1
cd folder1
Create a file_file1.txt

Initializing a repository.

When you start an new project and want Git to track it, you need to initialize a repository. This sets up the hidden .git folder where Git store all the history, configuration, and metadata for your project.
To tell Git to start tracking your
git init

Tracking changes

Checking repository status

The git status shows you the current state of your working directory and staging are. It helps you understand what's going on in your project before committing changes. git status

Staging files.

This isthe process of preparing changes before committing them. This ia a waiting are where you select which edit will be included in the next snapshot (commit) git add file1.txt

committing changes

A commit is saving a snapshot of you project. Once you have staged files, committing records into the repository's history.
git commit -m "Describe your changes here"

Pushing code to a remote repository.

Connecting to a remote

Link your local repository to GitHub:
we then have to connect out remote repository (github repo) to our locally hosted repository. This ensure that changes commited locally reflect on our github
git remote add origin"Your_remote_repo_url

Staging and commiting changes

git add . git commit -m "Your commit message"

Pushing Commits

This is pushing changes from your local repository to our remote repository in github.
git push -f origin main

origin - Remote repository
main - the branch you are pushing
-u - Sets the upstream branch.

Pulling changes from Github.

When someone else makes changes—or when you switch computers—you’ll want the latest version of the project. This is called pulling
git pull - This command will fetch and merge the changes.
When to pull:

When starting work on a project
When teammates have made changes
Before making your own changes

Git is a powerful tool that becomes intuitive with practice.