Forem: seme clive

UNDERSTANDING SQL:JOINS & WINDOW FUNCTIONS.

seme clive — Mon, 09 Mar 2026 21:50:56 +0000

INTRODUCTION
As we dive deeper in sql,lets learn some key functions in sql.
What are joins;
--is used to combine rows from two or more tables, based on a related column between them
--it allows as to work with multiple tables and allow as join data in different tables.
We have different types of joins with their different function.
They include as follows:
** TYPES OF JOINS*
**1.INNER JOIN*
-Is used to combine rows from two or more tables based on a related column. It returns only the rows that have matching values in both tables, filtering out non-matching records

*2.LEFT (OUTER) JOIN*
returns all rows from the left table, and only the matched rows from the right table
Unlike inner join which brings matched rows from all tables,left join only brings from the right table.

3.RIGHT(OUTER) JOIN
Returns all rows from the right table , and only the matched rows from the left table.Its atmost jthe oppossite of left join.
Here the command example;

4.FULL OUTER JOIN
Returns all rows when there is a match in either the left or right table
See two example of commands from my table that can execute the function;

5.SELF JOIN
This is when a table is joined on itself
We use aliases to refer to the same table
Example command line i used on my data table;

6.CROSS JOIN
Creates returns every combination of rows from both tables
Example of command;

These all about joints.As seen i have highlighted 6 ty
pes.

WINDOW FUNTIONS
Apparently window functions allow you perform calculations across set of rows that are related to the current row.
We have different types of window functions,
these are; >ROW NUMBER-assigns a unique sequential number to each row.
>SUM () OVER()-Running or partitioned totals
>AVG() OVER()-Running or partioned averages
>RANKS-Assigns a rank with gaps for ties
>DENSE RANK-Assigns rank without gaps for ties.
Below is how i used window funtions in assigning Ranks.;

Thats all about Joins and Window funtions.
On my next article i will be covering the entire SQL for data analytics.

WINDOW FUNCTIONS

--Returns all rows from the left table, and only the matched rows from the right table

UNDERSTANDING SQL:JOINS & WINDOW FUNCTIONS.

seme clive — Mon, 09 Mar 2026 21:50:56 +0000


![ ](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/mq5wwwbfeucgpkd7bnrn.png)

![ ](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/gyxi2e7b51ifrisxw8v1.pn
g)

**2.LEFT (OUTER) JOIN**
returns all rows from the left table, and only the matched rows from the right table
Unlike inner join which brings matched rows from all tables,left join only brings from the right table.

![ ](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/x6kf037mwgx1mtuno9u7.png)

**3.RIGHT(OUTER) JOIN**
Returns all rows from the right table , and only the matched rows from the left table.Its atmost jthe oppossite of left join.
Here the command example;

![ ](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/5swqqb6zry6b58tl2xy2.pn
g)

**4.FULL OUTER JOIN**
Returns all rows when there is a match in either the left or right table
See two example of commands from my table that can execute the function;

![ ](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/dzutot77ttkjt68qukv7.pn
g)

**5.SELF JOIN**
-This is when a table is joined on itself
-we use aliases to refer to the same table
Example command line i used on my data table;


![ ](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/54l9gib04pxxajbf8xa3.png)

**6.CROSS JOIN**
-creates returns every combination of rows from both tables
Example of command;

![ ](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/molju1t7pc006pnnntzp.png)

These all about joints.As seen i have highlighted 6 ty
pes.

**WINDOW FUNTIONS**
Apparently window functions allow you perform calculations across set of rows that are related to the current row.
We have different types of window functions,
these are; _>ROW NUMBER-assigns a unique sequential number to each row.
           >SUM () OVER()-Running or partitioned totals
           >AVG() OVER()-Running or partioned averages
           >RANKS-Assigns a rank with gaps for ties
           >DENSE RANK-Assigns rank without gaps for ties._
Below is how i used window funtions in assigning Ranks.;

![ ](https://dev-to-uploads.s3.amazonaws.com/uploads/articles/z0wxa0lmvher5d87ogzd.png)

Thats all about Joins and Window funtions.
On my next article i will be covering the entire SQL for data analytics.


**<u>

## WINDOW FUNCTIONS
</u>**





--Returns all rows from the left table, and only the matched rows from the right table

Data Transformation with Power BI; From Power Query, Dax to Dashboards

seme clive — Sun, 08 Feb 2026 20:45:18 +0000

From my previous post, had defined power-bi and talked more about schemas. Schemas fall part of power bi as the create connection between and define relationships within tables in power bi.
Today we dive down to data transformation with power-bi, that's from cleaning, querying down to visualization.
As an analyst I can use power bi to transform data using two processes; ETL (Extract Transform Load)
2.ELT (Extract Load Transform)
Though most analysts ought for process 2, ELT (Extract Load Transform), in turning huge unstructured data into actionable insights for decision making.

1.Extract Load Transform.
The first process is loading the data to power bi.
Mostly being a CSV file of Excel.

As seen here you can Extract data from importing on the sources listed: power-bi sematic, SQL server, excel, csv file...etc.
Here is where we Extract from data sources and load the data to power bi.

2.Transform Data (Power Query/Dax Formulars)
Here now is where all the shenanigans are done on the data.
Is where we clean, remove duplicates, transform columns, create new measures and columns as well as creating connections and relationships within data columns.
Its here where we introduce Dax formulas as we manipulate our data
(Dax-is a formular expression language used in power-bi)

From above you can see an interface of power bi showing where data is transformed.

Dax/Dax Functions
DAX is designed specifically for analytical and business intelligence tasks such as totals,
averages, percentages, rankings, comparisons, and time-based analysis.
Kindly note Dax formulars are not hard, they derived mostly from excel functions concepts.
One of the main functions is the;
1.Aggregation Function
these means summarizing many rows to one value.analysts oftenly use these functions in the analysis of the data.
Thes\y include: (SUM, AVERAGE, MEDIAN,
MIN, MAX, COUNT, VAR, STDEV).
Iterator functions ending in X (SUMX, AVERAGEX, MEDIANX, MINX, MAXX, COUNTX,
VARX, STDEVX) evaluate an expression row by row first, then aggregate.
Mathematical helpers like ABS, POWER, SQRT, ROUND, ROUNDUP, ROUNDDOWN,
and MOD help shape and control numeric results for reporting.

2.LOGICAL FUNCTIONS IN DAX (POWER BI)
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.
This function is "the IF FUNCTION"
IF is used for simple true or false decisions.
Nested IF handles multiple outcomes but can reduce readability.
AND / && require all conditions to be true.
OR / || require at least one condition to be true.
SWITCH is the preferred option for many conditions.
ISBLANK and COALESCE help handle missing data.
Logical functions are essential for classification, KPIs, and business rules.

3.FILTER FUNCTIONS IN DAX (POWER BI)
Filter functions in DAX control which rows of data are included in a calculation. They are the
backbone of meaningful analysis in Power BI because almost every business question depends
on filtering data correctly.
CALCULATE modifies filter context and performs calculations.
FILTER selects rows and returns a table.
Use simple filters in CALCULATE when possible for better performance.
Use FILTER for complex conditions.
ALL and REMOVEFILTERS ignore filters.
ALLEXCEPT keeps specific filters.
KEEPFILTERS preserves existing context.

All above functions play a major role in data transformation.
Some other functions may look complex in writing i.e the "nested IFS" but
after mastering they look simpler like the SUM function.
After all the transformation and calculations KPIs we head direct to visualization where you are able to create dashboard similar to these one below, that's according to the data requirements.

Dax/Dax Functions

Power BI For Visualization and Data Manipulation.

seme clive — Sun, 01 Feb 2026 21:05:32 +0000

First what is__ Power BI?
Power BI is a tool created by Microsoft to turn raw data into interactive insights. IN other words, it's also described as a visualization tool.
Those who already know how to use Microsoft Excel may find it faster to pick up Power BI, as the interface has a lot in common with Excel.
It's like the advanced excel,as its capable of handling huge amounts of data.

What's Data Modelling?
Data modeling is the process of creating a simplified diagram of a software system and the data elements it contains, using text and symbols to represent the data and how it flows. Data models provide a blueprint for designing a new database or reengineering a legacy application. Overall, data modeling helps an organization use its data effectively to meet business needs for information.
Data models define how data is structured, related, and ultimately implemented. They serve as blueprints for organizing information, ensuring consistency, and aligning database structures with business needs.
Types of Data models;

Conceptual Data Models-these data model defines what system contains. The purpose is to organize, scope and define business concepts and rules. 2.Logical Data Model; Defines HOW the system should be implemented regardless of the DBMS.The purpose is to developed technical map of rules and data structures. 3.Physical Data Model;This Data Model describes HOW the system will be implemented using a specific DBMS system.

What is a Database Schema?
A database schema is a formal description of how data is structured or organized within a database.
Components of Database Schema

Physical Database Schema
Logical Database Schema >> as defined before ...

Types Of Database Schemas:
Here we going to look at two main schemas;

star schema-is a data modelling structure used to facts table & is surrounded by dimensional tables. Is commonly used in data warehousing

2.Snowflake schema; is an extension of star schema where dimensional tables are subdivided to more sub-dimensional tables.
It supports complex queries.

KEY NOTE';
Importance of Data Modeling: Essential for understanding business requirements, ensuring data consistency, improving system performance, and reducing errors.

Fact and Dimension Tables: Core components of data modeling, with Fact tables holding quantitative data and Dimension tables providing contextual details.

Star Schema: A simple, denormalized structure that offers fast query performance, ideal for straightforward analytics tasks.

Snowflake Schema: A complex, normalized structure that reduces data redundancy and saves storage space, suited for detailed analysis.

Normalization vs. Denormalization: Techniques to balance between storage efficiency and query performance.

Excel For Data Analytics: Beginner Friendly Overview.

seme clive — Sun, 01 Feb 2026 19:03:07 +0000

_Excel is one of the main tools used in data analysis.
With Excel, you can manipulate data, summarize it with pivot tables, visualize it, and perform quick statistics to summarize it.
Mostly excel is used for small data sets not that huge, though it allows you to perform all function of data transformation on it.

Here is the Interface of excel work book...
Basically, it's a 2019 excel version

1. Excel Skills to learn...

_Perform data cleaning by removing blank spaces as well as incorrect and outdated information
Format and adjust data using conditional formatting Perform data calculations using formulas
Organize data using sorting and filtering.
Create visualizations using graphing and charting.
Calculate, summarize, and analyze data using pivot tables.
Aggregate data for Analysis.

1.Basic Formatting and Sum Function
Here will learn more about formatting data, being one of the processes of cleaning data sets using excel and sum function.
Some of the formatting functions include:

Conditional formatting.
Format as table.
Sort and filter.
Find and select.

For Sum function see below.

2.Max, Min, and Avg Functions

Excel has many built-in functions, or predefined formulas, that are used to perform both simple and complex calculations. Five basic, built-in functions we will cover are Sum, Average, Count, Max, and Min.

Step 1: Add a New Worksheet

Click the plus “+” button at the bottom left of the workbook screen to add a new worksheet.

Right click the new Sheet2 and rename it “Functions.”

Step 2: Explore the Excel Function Library

Click on the Formulas tab in the toolbar to view the types of built-in functions in the Function Library group.
You may have to expand the toolbar by clicking the down arrow at the far right of the tool bar to see the Function Library group.
Click the dropdown arrow under the AutoSum Function. Note the five basic functions in this subgroup are as follows:
-Average: averages the numeric values in the referenced cells
-Count Numbers counts how many referenced cells there are
-Max: returns the highest numeric value in the set of referenced cells
*MAX-

*MIN

*Average

*Count

These are some of the basic concepts of excel.

Diving deeper into excel is where you find more complex functions as ;vlook-up, xlook-up, hlook-up,,,among others.
Again, pivots tables and dashboards lie as some of the other
functions which can be performed on excel.
On my next post ,i will share more on my data manipulation with excel,from cleaning to dashboards using large data set.
See you on the next.

Min: returns the lowest numeric value in the set of referenced
cells
Step 3: Using the SUM function

Aggregate data for analysis_

All About Git & Git-Hub: Beginner's guide on relationship between git& git-hub, installation, linking both tools to launching.

seme clive — Sun, 18 Jan 2026 22:50:45 +0000

Introduction: Understanding Git and Git-hub
If you are a beginner, it's important to understand the difference between the two tools, git and git-hub:
So, what's Git? What's it used for?
Basically, Git is a popular version control system which is used to>track code changes

coding collaborations between developers
tracking who made the changes
On the other hand, What's git-hub?
Git-hub is a proprietary developer platform that allows developers to create, store, manage, and share their code. It uses Git to provide distributed version control and GitHub itself provides access control, bug tracking, software feature requests, task management, continuous integration, and wikis for every project.
That's how i got to understand it 1st as a beginner, let's go now to the steps of installation, opening account up to linking, how I hacked it:

_Step 1: **Opening a Git-hub Account,
These the first step before anything else, you need to sign 9up and create your account.
Here is the link; https://github.com/

Step 2: **Downloading Gitbash APP and Launching it; **
The download should be the latest version; https://git-scm.com/install/

Launch the app
Confirm the version;

*Step 3: Git configuration/set identity; *
Here you need to set your identity for git to know who you are and configure email used to open git-hub account on git.
That is your username and user email as seen below;

*Step 4: Confirm the Configured Identity: *
You have to command git to see the user identity created. These to verify that all were created,
Here is the command line;

Note: make sure email used in signing up the git-hub account is the one you configuring git with.
Step 5:Generating an SSH key/Connecting Git to Git-hub;
What is an ssh keY?Its a key that proves wat the machine is in order to access git-hub (it's a key that assist link git and git-hub)
As seen on my command,it shows user already exists as i had already configured, for a new user creatin one it will give you a pass key which you copy paste on git-hub

*Step 6: Paste key on git-hub ssh agent; *
After creating the passkey, login to git-hub,go to settings-ssh & gpg keys.
paste the key on the ssh agent. Then connect, it will send a mail confirming the connection.

Step 7;Open a project folder on git hub.
Step

**_