DEV Community: Victor ochieng

Introduction to Joins and Windows Funtions in SQL

Victor ochieng — Mon, 23 Mar 2026 09:05:50 +0000

Introduction

Joins define relational operations that primarily combine data from multiple tables based on a logical relationship which in most cases is a foreign key, potentially increasing row counts and expanding columns. When working with databases, specifically normalized databases, it is a best practice to distribute data across multiple tables subsequently enforcing integrity and eliminating redundancy. The different types of joints include the INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL OUTER JOIN, and CROSS JOIN.

INNER JOIN
This is the default join type that returns only matching records from the referenced tables in the query.
Example

SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;

LEFT JOIN
Returns all rows from the left table (the first table) and matched rows from the right (second table), with NULLs for unmatched right-side rows.
Example

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;

RIGHT JOIN
Returns all rows from the right table (the second table) and matched rows from the left (first table), with NULLs for unmatched left-side rows.
Example

SELECT Orders.OrderID, Employees.LastName, Employees.FirstName
FROM Orders
RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;

FULL JOIN
This type of join returns all rows from both tables with non-matches on either side returned as null values.
Example

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;

CROSS JOIN
This is a special join type that allows users to produce a Cartesian product, pairing every row from the first table with every row from the second. However, when using this join you should be careful to avoid row explosion.
Example

SELECT s.full_name, c.course_name
FROM students s
CROSS JOIN courses c;

WINDOWS FUNCTIONS

SQL window functions enable calculations over a group of rows associated with the current row, while preserving each individual row in the result set rather than aggregating them into a single summary value. We can think of windows functions as commands to analyze rows around me without merging or removing rows. The different windows functions are commonly used for tasks like aggregates, running totals, and ranking as they provide a holistic view of the data.
Below are the common SQL window functions presented in bullet format, organized by catego

Ranking Functions

ROW_NUMBER()
Assigns a unique sequential number to each row within a partition
No ties allowed
RANK()
Assigns the same rank to tied values
Skips rank numbers after ties
DENSE_RANK()
Assigns the same rank to tied values
Does not skip rank numbers

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

LAG()
Retrieves value from a previous row
LEAD()
Retrieves value from a following row
FIRST_VALUE()
Returns the first value in a window
LAST_VALUE()
Returns the last value in a window

Conclusion.

In summary, joins and window functions serve distinct yet complementary roles in SQL querying. Joins integrate data across related tables, enabling comprehensive relational analysis, while window functions enhance datasets with analytical insights without reducing row-level detail. Mastery of both concepts is essential for designing efficient queries, supporting advanced reporting, and performing meaningful data analysis in structured, normalized database environments.

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

Victor ochieng — Mon, 09 Feb 2026 14:24:42 +0000

Introduction

Raw data can essentially be branded as “useless” because it is nearly impossible to gain any qualitative or quantitative information regarding the population or group in question. It is in such cases that data analysts come into play to transform raw data into valuable insights that help in decision making. In essence, the primary role of data analysts is to transform this “noise” into a language that key business stakeholders understand. One major tool used to achieve this transformation is Power BI.

Transforming Messy Data into Actionable Insights.

In Power BI, there is one important and powerful tool called Power Query that is essentially crucial in data transformation. Power Query is primarily used to extract, transform and load data from various sources with its integration to excel, data analysts can clean and automate this data without necessarily having to do any sort of coding. With power query analysts can easily remove duplicates, set correct data types, and filter erroneous records.

Data Modelling and Relationships.

With clean and structured data, analysts can proceed with creating data models that define relationships and collectively define how business processes work. A strong data model is the backbone of a successful report; it ensures Power BI calculates totals accurately, filters logically, and stays snappy for the user. Conversely, a weak model is a recipe for disaster triggers, sluggish performance, misleading numbers, and a frustratingly confusing user experience.

Turning Data into Meaning with DAX

Data Analysis Expressions (DAX) picks models and creates calculations that collectively answer real business questions. With DAX, analysts calculate Key Performance Indicators such as revenue, profits, losses, and growth percentages, create and build reusable measures for consistent and reliable reporting besides controlling data filtering and evaluation. Analysts write DAX measures that reflect business logic ensuring results are accurate no matter how the report is filtered.

Dashboards X Actionable Insights.

Dashboards can be defined as visual interpretations of cleaned data and models that assist business executives and stakeholders understand what’s happening and decide on the direction to move. It is important to note that when creating dashboards, the focus should be on clarity rather than decoration. Analysts need to ensure that the dashboards are clear and consistent, especially with the charts and graphs, focus on metrics and KPI’s, and allow users to filter, and highlight the various trends and patterns.

Conclusion.

Working with Power BI is a learning journey, not a one-time destination. From messy data to DAX formulas and meaningful dashboards, every step becomes clearer with practice and experience. At the beginning, concepts like data modelling, relationships, and advanced calculations may feel overwhelming, but over time patterns emerge, understanding deepen, and confidence grows. As we continue exploring Power BI, each challenge becomes an opportunity to learn, refine our approach, and improve how we turn data into insight. With consistency and curiosity, clarity comes—and what once felt complex gradually becomes second nature.

Schemas and Data Modelling in Power BI

Victor ochieng — Mon, 09 Feb 2026 14:16:47 +0000

Introduction

Data modelling just like the name suggests is a concept that encompasses restructuring data and creating insightful visuals from cleaned and structured data. In power BI data modelling entails organizing unstructured data into relatable tables, subsequently defining how those tables are connected to each other and their relationships. This step is crucial in streamlining reports and eliminating instances of confusing insights which collectively affect decision-making. In power BI these table relationships and schemas improve performance and efficiency.

Fact Tables and Dimension Tables
At the core of data modelling are fact tables and dimension tables. These are the baselines for a good data model since they are used to define relationships across different tables and allow analysts to create insightful reports.

Fact tables mostly contain foreign keys from other tables and primarily store measurable, quantitative data such as:

Sales amounts
Quantities sold
Revenue
Transaction counts

Dimension tables on the other hand are relatively smaller and are mostly used for grouping and data filtering. These tables are used to store descriptive information that provides context to the facts, such as:

Customer names
Product categories
Dates
Locations

Schemas.

Like mentioned earlier, schemas in power BI are used to show relationships between tables within a data model.

Star Schema

This is the most recommended and commonly used schema in Power BI. In this schema type, the fact table sits at the center of every other dimensional table that’s connected to it. It is important to note that the dimensional tables are not connected to each other. The main benefits of using this schema type in modelling relate to its simplicity and ease of understanding even to new users, faster querying capabilities and its compatibility with power BI’s DAX engine.

Snowflake Schema

This is a more complex variation of the star schema where dimensional tables are extensively normalized and may subsequently connect to each other without necessarily connecting to the fact tables. This design practically introduces complexities despite enhancing data redundancy. Furthermore, relationships become more complex and performance slowed due to the multiple joints.

Relationships in Power BI

Relationships define how tables are connected in a data model. In Power BI, relationships are usually:
One-to-many (most common)
Single-directional filtering (recommended)

Why Data Modelling and its role in enhancing Power BI functioning.

Some of the various ways data modelling improves reporting in power BI include:

Reducing data redundancy.
Dimension tables and fact tables are based on foreign and primary keys which define relationships and help eliminate repetitive data thereby enhancing data redundancy. It is crucial to note that the primary reason for modelling is structuring data and reducing instances of duplicates and making it rather tiresome to interpret large data sets.

Enhances Usability and report clarity
With reduced table numbers and refined relationships, data modelling and schemas, it becomes relatively easier for users to interpret reports and gain insights to support business decision making. Besides, calculations become more accurate and easier to compute throughout the analysis period.

Conclusion

Schemas and data modelling form the foundation of effective Power BI reporting. Understanding concepts such as fact and dimension tables, star and snowflake schemas, and table relationships allows analysts to build models that are both efficient and accurate. While Power BI can work with many data structures, investing time in good data modelling pays off through faster performance, clearer reports, and more reliable business insights.

Introduction to MS excel for data analytics.

Victor ochieng — Sun, 25 Jan 2026 17:32:33 +0000

“If you master 30% of excel, you could easily land a job faster than a primary school teacher or some graduates with specific undergrad majors,” a friend once told me this immediately after graduation from Maseno University.

The above words seemed untrue or rather exaggerated until I completed my excel session at LuxDevHq and realized there are so many things that MS excel could do specifically in the analytics field.
There are so many ways MS excel can be used in data analysis. Today we are going to delve into some of these aspects with a key focus on beginner concepts that are crucial to analytics and visualization.

Data Cleaning and Validation

Data cleaning entails fixing and removing errors in data to ensure accuracy and consistency before any analysis and reporting. Some of these errors include wrong entries, duplicates, inconsistent formats, etc. Duplicates are often removed only from columns that require unique entries such as employee ID. In the example below, we select the data range (employee id) and go to the data segment on the ribbon and click on the remove duplicates option.

In the cleaning process, we can also remove extra spaces and check on the cases. The formulas below can be used to achieve this objective depending on the requirement.

Trim (=TRIM(C2:C6) ) used to remove extra spaces.
Upper (=UPPER(B2:B6) ) used to change the case to capital letters while lower (=LOWER(B2:B6) ) makes them in small letters. Proper (=PROPER(B2:B6) ) on the other end achieves sentence case.

Validation

Data validation controls the type of data users can enter a cell. It is important because it reduces data entry errors, improves data quality, and makes analysis more reliable. Data validation is accessed from the Data tab by selecting the required cells and choosing Data Validation. Excel offers several validation types, including drop-down lists, number limits, date restrictions, text length limits, and simple custom rules to prevent incorrect data from being entered.

Ms Excel Functions for analysis.

Ms excel has so many functions that are crucial for computations that provide insight into the data provided. Some of these functions include:

SUM – Adds numbers together =SUM(A1:A10)
AVERAGE – Calculates the mean of numbers =AVERAGE(A1:A10)
COUNT – Counts cells that contain numbers =COUNT(A1:A10)
MAX – Finds the highest value =MAX(A1:A10)
MIN – Finds the lowest value =MIN(A1:A10) In this case, the column in question is column A with the range running from A1:A10

Pivot tables, Graphs and Interactive Dashboards.

Pivot tables are practically a solution to the rather “hectic” use of some functions. They are used to quickly summarize and analyze large sets of data. Essentially, they allow users to calculate totals, averages, counts, and percentages without using complex formulas. Pivot tables also make it easy to group, sort, and filter data, helping users compare information such as sales by month, department, or product.

Pivot table

Graphs, also known as charts, present data in a visual format that is easy to understand. Excel graphs such as bar charts, line charts, and pie charts help users identify trends, patterns, and comparisons briefly. Visual representation makes data clearer and more effective for reporting and presentations.

Combo chart(bar and line graph)

Pie chart

Interactive dashboards combine pivot tables, charts, and controls such as slicers and drop-down lists into a single view. They allow users to interact with data by filtering and updating results instantly. Dashboards automatically reflect changes in the data, providing quick insights and supporting better decision-making.

Dashboard

Conclusion
And there you have it! Even as a beginner just poking around Excel, you’ve got the tools to clean, organize, and make sense of your data like a mini data detective. Sure, you might fumble a formula or two (who hasn’t?), but with pivot tables, charts, and dashboards at your fingertips, you’re already on your way to turning numbers into insights. Lets, keep experimenting, don’t fear the functions, and remember—every Excel wizard started exactly where you are right now. Looking forward to your feedback.

Getting started with Git and GitHub.

Victor ochieng — Sun, 18 Jan 2026 08:45:04 +0000

Exploring new learning curves and opportunities isn’t something that I thought I would ever get myself doing in 2026. Today, I am writing about my first experience at LuxDevHq specifically on lessons learnt in the first week of the Data Science and Analytics class. Today we are going to go through a beginner friendly perspective of installing Git and linking it with the GitHub account.

Installation
Depending on the operating system your using, you download the right installation package from https://git-scm.com/install/windows and run it as an administrator following the prompts provided in the installation interface.
Once it’s installed you can search it open from the shortcut on the desktop or using the search bar you can search for the Git Bash app and open the terminal.

Git Configuration
After successful installation, a git profile needs to be set up before linking it with git hub.
The first step is to configure the name and email (Note:email used in profile set up should be the same as the one used during git hub profile setup).
For the name, use the following command.

git config –global user.name “name”

After successfully running the command above, we can configure the email and subsequently confirm if our profile was set up.

git config –global user.mail  “name@gmail.com”

Subsequently, Run the command below to confirm if the profile set up was successful.

git config --global --list

Public and Private Key generation and set up.

SSH keys are essential in git bash primarily for the authentication purposes between the various git servers without necessarily having to type the password and username anytime you want to connect to GitHub. Given the fact that we regularly must connect and access GitHub for pulling and pushing code besides tracking changes, having these keys practically provide identity of the git services and secure authentication.
The keys come in pairs, a public key and a private key with the public key uploaded on the GitHub account and configured in the settings while the private key remains in the local computer for verification purposes before access is granted. The most modern and secure type of key is ed25519 which replaces the rsa though still supported but outdated.

Generating a new private SSH Key
Run this command (replace the email with your own) Press enter without changing anything until you see the generated file path:

ssh-keygen -t ed25519 -C “(your email address)”

Getting the public version of the SSH Key.

The key generated is the private key that is stored locally on the device, we need to have a public key for the GitHub configuration and linkage.
Use the command for your system:

~/.ssh/id_ed25519.pub

Setting up the key on GitHub.

Copy the public key
Paste it into: GitHub → Settings → SSH and GPG keys

Test the Connection
To make sure everything is working, run this command in your terminal:

ssh -T git@github.com

Tracking changes on GitHub using Git.

To track changes, you first need to first create, and new repository online and subsequently clone it on the local device. Basically, we will be duplicating whatever we have online to the local device.
To clone the repository (URL is from the new repository created in the GitHub account online.),
Open Terminal and run:

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

The URL is from the new repository created in the GitHub account online.
This downloads the project to your computer.
Run the command below to get into the project folder created.

cd repository-name

Tracking
Run;

git status

The command checks for what has been changed or edited.

Adding Files
After creating or editing files:

git add .

This practically communicates to git that the changes made need to be saved.
On Git, the “commit” command is used to save the changes

git commit -m "My first commit"

Uploading files to GitHub

To achieve this, we use the “push” command

git push origin main

Downloading from GitHub

The “pull” command will help achieve this objective.

git pull origin main

This updates your local copy.

Viewing Past Changes (History)

git log

This commands practically tracks the logs and ensure all changes are logged and can be viewed.

Conclusion

This journey into Git and GitHub is just the beginning for me, and like many beginners, I’m still learning and figuring things out along the way. The lessons shared here reflect my early experiences and understanding so far. I’d really appreciate your thoughts, corrections, or additional tips in the comments—especially if you’re also learning or have gone through this process before. Let’s learn and grow together.