DEV Community: lincon omondi

SQL Deep Drive.

lincon omondi — Sun, 18 Oct 2026 00:41:44 +0000

introduction

SQL window functions allow performing calculations across a set of rows that are related to the current row, without collapsing the result into a single value. They are commonly used for tasks like aggregates, rankings and running totals. The OVER clause defines the “window” of rows for the calculation. It can:

PARTITION BY: It divides the data into groups using PARTITION BY.
ORDER BY: It specifies the order of rows within each group using ORDER BY.
With this, functions such as SUM(), AVG(), ROW_NUMBER(), RANK() and DENSE_RANK() can be applied in a controlled way.
-- Aggregate windows functions
-- AVG()
SELECT patient_id, ward_id, insurance_provider, total_bill_kes,
AVG(total_bill_kes) OVER (
PARTITION BY insurance_provider
) AS avg_bill
FROM visits;

Types of Window Functions in SQL
SQL window functions are mainly of two types: aggregate window functions and ranking window functions.

Example: First, we create a demo table Employee and now we will apply window functions on it.

Screenshot-2026-08-31-122100
Aggregate Window Functions
Aggregate window functions calculate aggregates over a window of rows while retaining individual rows. Common aggregate functions include:

SUM(): Sums values within a window.
AVG(): Calculates the average value within a window.
COUNT(): Counts the rows within a window.
MAX(): Returns the maximum value in the window.
MIN(): Returns the minimum value in the window.
Example: Using AVG(), we will calculate the average salary within each department.
Ranking Window Functions
These functions provide rankings of rows within a partition based on specific criteria. Common ranking functions include:

RANK(): Assigns ranks to rows, skipping ranks for duplicates.
DENSE_RANK(): Assigns ranks to rows without skipping rank numbers for duplicates.
ROW_NUMBER(): Assigns a unique number to each row in the result set.
PERCENT_RANK(): Shows the relative rank of a row as a percentage between 0 and 1.
RANK() Function
RANK() functions are used to assign ranks to rows within a group based on a specific order. Ranking functions are commonly used to organize and analyze data. Some common ranking functions include:

Example: Using RANK(), we will rank employee by salary, allowing gaps in ranks when salaries are equal.
--RANK()
SELECT patient_id, total_bill_kes, payment_method,
RANK() OVER (
PARTITION BY payment_method
ORDER BY total_bill_kes DESC
) AS bill_rank
FROM visits;

SQL Joins (Inner, Left, Right and Full Join)

SQL Joins are used to combine data from two or more tables based on a related column.Improving data analysis by combining related information

Types of SQL Joins

INNER JOIN INNER JOIN is used to retrieve rows where matching values exist in both tables. It helps in:

Combining records based on a related column.
Returning only matching rows from both tables.
Excluding non-matching data from the result set.
Ensuring accurate data relationships between tables.
Syntax:

SELECT A.column1, A.column2, B.column1, ...
FROM tableA AS A
INNER JOIN tableB AS B
ON A.matching_column = B.matching_column;

example;

-- Inner join
SELECT A.doctor_id, A.follow_up_required
FROM visits AS A
INNER JOIN doctors AS B
ON A.doctor_id = B.doctor_id;

out put

LEFT JOIN LEFT JOIN is used to retrieve all rows from the left table and matching rows from the right table. It helps in:

Returning all records from the left table.
Showing matching data from the right table.
Displaying NULL values where no match exists in the right table.
Syntax:
SELECT A.column1, A.column2, B.column1, ... FROM tableA AS A LEFT JOIN tableB AS B ON A.matching_column = B.matching_column;
Example: In this example, the LEFT JOIN retrieves all rows from the student table and the matching rows from the student_course table based on the roll_no column.
output

select A.patient_id, A.patient_name, A.county
from patients as A
left join visits as B
on A.patient_id = B.patient_id;

RIGHT JOIN RIGHT JOIN is used to retrieve all rows from the right table and the matching rows from the left table. It helps in:

Returning all records from the right-side table.
Showing matching data from the left-side table.
Displaying NULL values where no match exists in the left table.
Performing outer joins, also known as RIGHT OUTER JOIN.
Syntax:
SELECT A.column1, A.column2, B.column1, ... FROM tableA AS A RIGHT JOIN tableB AS B ON A.matching_column = B.matching_column;

Example: In this example, the RIGHT JOIN retrieves all rows from the student_course table and the matching rows from the student table based on the roll_no column.
Syntax:

select A.diagnosis_id, A.diagnosis_name, B.total_bill_kes
from diagnosis as A
left join visits as B
on A.diagnosis_id = B.diagnosis_id;

output

FULL JOIN FULL JOIN is used to combine the results of both LEFT JOIN and RIGHT JOIN. It helps in:

Returning all rows from both tables.
Showing matching records from each table.
Displaying NULL values where no match exists in either table.
Providing complete data from both sides of the join.

SELECT student.name, student_course.course_id FROM student FULL JOIN student_course ON student_course.roll_no = student.roll_no;
Syntax:

select A.procedure_id, A.procedure_name, B.patient_id, B.total_bill_kes
from procedures as A
left join visits as B
on A.procedure_id = B.procedure_id;

3.Common Table Expression (CTE) in SQL
A Common Table Expression (CTE) is a temporary result set in SQL that you can reference within a single query. CTEs simplify complex queries, make them easier to read and can be reused multiple times within the same query. It is used for:

4.SQL Subqueries
A subquery in SQL is a query nested inside another SQL query. It allows complex filtering, aggregation and data manipulation by using the result of one query inside another. They are an essential tool when we need to perform operations like:

Apply aggregate functions like SUM, COUNT or AVG dynamically.
Update data using values from other tables.
Delete rows based on conditions returned by another query.

Example: Create a demo SQL database and tables, on which we use the SQL Subqueries.
Syntax:

SELECT * FROM visits
WHERE total_bill_kes >  ( SELECT AVG(total_bill_kes) FROM visits );

Note: Subqueries do not have a single fixed syntax, as they can be used in different clauses like SELECT, WHERE, FROM and HAVING

SQL Clauses for Subqueries
Clauses that can be used with subqueries are:

Filters rows in the outer query based on the results returned by the WHERE clause.
Treats the subquery as a temporary (derived) table that can be queried like a normal table using the FROM clause.
Filters grouped or aggregated results using values produced by the HAVING clause.

Types of Subqueries
Consider the following two tables for examples:
Single-Row Subquery
A single-row subquery is a subquery that returns only one value.

Returns exactly one row as the result.
Commonly used with comparison operators such as =, >, <
Example:

SELECT * FROM Employees
WHERE Salary = (SELECT MAX(Salary) FROM Employees);

Multi-Row Subquery.

A multi-row subquery is a subquery that returns more than one value.

Returns multiple rows as the result.
Requires operators that can handle multiple values, such as IN, ANY or ALL
example
SELECT * FROM patient WHERE patient_id IN (SELECT patient_id FROM patient WHERE county = 'Nairobi');
Correlated Subquery
A correlated subquery is a subquery that depends on the outer query for its values.

A dependent subquery: it references columns from the outer query.
Executed once for each row of the outer query, making it slower for large datasets.
Example:

SELECT e.Name, e.Salary FROM Employees e WHERE e.Salary > (SELECT AVG(Salary) FROM Employees WHERE DepartmentID = e.DepartmentID);
syntax:

SELECT A.patient_id, A.patient_name
FROM patient A
WHERE A.patient_age > (SELECT AVG(patient_age) 
                  FROM patient
                  WHERE patient_id = patient_id);

output

Conclusion

Joins

Allow combining data from multiple tables based on related keys.
Types: INNER, LEFT, RIGHT, FULL OUTER, CROSS.
Essential for relational data analysis and integrating datasets.
Subqueries

Nested queries inside another query.
Can be correlated (dependent on outer query) or non-correlated.
Useful for filtering, aggregating, or transforming data before main query execution.
Window Functions

Perform calculations across a set of rows related to the current row without collapsing results.
Examples: ROW_NUMBER(), RANK(), DENSE_RANK(), LEAD(), LAG(), SUM() OVER(...).
Enable running totals, rankings, and comparisons within partitions.

JCars Logistics Power BI Business Intelligence Assessment

lincon omondi — Fri, 02 Oct 2026 17:29:09 +0000

Introduction

Jcars logistic is company dealing with cars in Kenya. The company came up with sample raw data to insight the business progress. From keeping track on the cars sold, the revenue, the car model with the higher and least purchase revenue in the company. To answer the key questions; how many cars sold in specific location, number of cars sold in total, sales rep with the most and least sales and the revenue of the cars sold; we will analyze the raw data.

Understanding the data.

Examining dataset is essential before working on it. Knowing the key aspect. Transforming my data to query after uploading to power BI. Categorizing my data from the columns and rows; transactions, people, location, vehicles, customer, measure and date.

Data cleaning

Here are the steps;

Promoting headers
From the incorrect headline of my dataset. I started by promoting headers; my first row as headline to create the difference between my heading and the rest of data.
Changing type of different columns
Changing the type of information in column into their unique type, example; the; text columns to text, date column to date and all numerical columns to number.
Numerical formatting
The use of percentages and whole number in currency. Applying the currency ambiguity and monetary values;

Correcting the negative recorded revenue.
changing the currency from the USD to KES "Kenya currency."

These are few steps I took to clean my raw data and duplicate my newly cleaned data for any inconvenience later.

Data modelling

_Data modeling in Power BI is the process of organizing data from different tables and defining how those tables relate to each other, so you can analyze the data correctly and build reports and dashboards. _

Building the data

visualizing the thoughts and understanding key columns to form the fact table and the dimensional tables before generating the primary and foreign keys.

Creating the facts table and dimension table from the queries.

Starting by duplicating my cleaned data and renamed it as fact table.
After duplication I reference the new queries from the original cleaned data. Reference queries are generally more efficient, especially if you’re building layered transformations.

Dim customers: customer name, customer type and customer age
Dim sales rep: sales rep
Dim location: region, county, city and branch
Dim vehicle: vehicle type, car model, vehicle year, fuel type, transmission and color
Dim date: order date
Dim lead source: lead source
Dim payment method: payment method and status

In each dimensional query I selected all the columns and removed the duplicates.

After the duplicate I added the index column from 1 so I can generate the primary keys.

After the process I came up with a cleaner relationship and merging of queries.

Analysis and Insights and Management tools

calculation of relevant question

key findings using Dax;

Total cars sold =sum (fact table [unit sold]
Total sales revenue =sum (fact table [units sold] * fact table [unit selling price]
Total cost =sum (fact table [unit sold] * fact table [unit cost]
Gross profit =[total sales revenue]- [total cost]
Gross profit margin =divide ([gross profit], [total sales revenue]

My final dashboard insight

The purpose of the dashboard is to bring clear analysis vision of the key data in simple way. It's a professional way to keep track and performance.
I used table slicers and charts to easily monitor and understand the trend without the spreadsheets.

Conclusion

Understand and visualizing the data is essential before any step.it helps you create or make good decision beforehand on it. Every data has different styles. with the help of questions also matter with accordance to which Dax formula you can use.

Getting Started With Excel For Data Analytics: From Basic To Data Cleaning & Analysis.

lincon omondi — Sun, 30 Aug 2026 00:15:17 +0000

Introduction.

Excel is a spreadsheet used for data collection, editing and analysis.
it can as well describe the workflow scheme of a specific data.

What you should know while leaning excel from excel basic;

Familiarizing with the excel interface
knowing from the workbook, worksheets and excel command ribbon

Excel command ribbon is a key navigation tool when using excel.
how to use each tool and its function.

The workbook is for organizing and data entry.

Here are components used;

1.cell -space within the highlighted boxes.

2.column - is the vertical part from your excel workbook.
(top to bottom)

3.Rows - is the horizontal part of your excel workbook.
(left to right)

Data cleaning

Data cleaning refers to correcting raw dataset to a well separate data.
It is advisable to separate two worksheets on a excel workbook from the original raw data and the newly formatted data.

Data cleaning is essential for removing duplicates, missing values and autocorrecting spelling mistakes.

Data Formatting

Data formatting is used to specify specific content within data, distinguish it and analyzing it making it easier to be read.
Specific components should be used in reference; like when using dates, monetary values can be formatted as currency.

freezing the row and column

Freezing is used to retain the marked header for easy comparison of the specific data within the freeze column.
It is essential in maintaining data context preventing scrolling confusion.it keeps header row visible while reviewing the records.

Formulas

The use of formulas is important aspect in relation to data accumulation records.
We execute formulas command to gain required combination arithmetic operations.
Every formula should start with =.
_example _

(=A1+B1) when looking for addition
(=A1-B1) when looking for difference

Conclusion

Excel is a powerful Microsoft tool used in everyday life to simplify and organize accurate data by formulating and analyzing using different excel skills.

Practical Understanding of GitHub Using {GIT And SSH}.

lincon omondi — Sat, 22 Aug 2026 20:43:15 +0000

Introduction.

First understanding the difference between git and GitHub. understanding how they relate and its functions.

Procedure

step 1: Downloading Git and sign up for GitHub account

use recommended setup and follow instruction given to install depending on the operating system on your computer.

Step 2: Configurations of Git

Tell git who you are and make sure it knows you.
Tell git your name and email by using the following commands;

git config --global user.name "Your Name" 2.git config --global user. Email "you@example.com"

step 3: Generating SSH key

The ssh key will be used to link your Git and GitHub.
when running the command it will generate two keys; private and public.
public key is used to pair files.
command used is;
ssh-keygen -t ed25519 -C "you@example.com"

step 4: Adding the public ssh key to GitHub

Identify your public SSH key from your Git and copy it.

Open you GitHub and go to settings.

Click on the _SSH and GPG keys and add new SSH key.

Fill the tittle with name and paste your key.

Add the key and verify your using Gmail account.

step 5: create local folder from your computer with data

Create folder from your file explorer with data.

Name your folder.

Go to git and use ls command to list files name to ensure the created file name is edited.

Step 6: Git repository

Use git commit -m "_your file name_ command to turn the folder to git repository so that you can push to GitHub.

Step 7: pushing the file to GitHub

Add origin to match your GitHub account and the file using
git remote add origin git@github.com:" _your GitHub username and your file name_

Since we created SSH key my git and GitHub are secure and connected.

Specify the folder and make it default before pushing by this command;
git branch

Use; git push -u to push your folder and put the passphrase if needed.

Refresh your GitHub page and confirm the uploaded files.

Things to note

Never share your private SSH key
Always your directories where the files were created

Conclusion.

GitHub is a social network used to upload files and projects from your local computer using git. you can access your project or make it public for people to access. understand each step to make it easier and make it a routine.

DEV Community: lincon omondi

SQL Deep Drive.

introduction

SQL Joins (Inner, Left, Right and Full Join)

Conclusion

JCars Logistics Power BI Business Intelligence Assessment

Introduction

Understanding the data.

Data cleaning

Data modelling

Building the data

Analysis and Insights and Management tools

My final dashboard insight

Conclusion

Getting Started With Excel For Data Analytics: From Basic To Data Cleaning & Analysis.

Introduction.

What you should know while leaning excel from excel basic;

Data cleaning

*Data Formatting *

freezing the row and column

Formulas

Conclusion

Practical Understanding of GitHub Using {GIT And SSH}.

Introduction.

Procedure

step 1: Downloading Git and sign up for GitHub account

Step 2: Configurations of Git

step 3: Generating SSH key

step 4: Adding the public ssh key to GitHub

step 5: create local folder from your computer with data

Step 6: Git repository

Step 7: pushing the file to GitHub

Things to note

Conclusion.

Data Formatting