DEV Community: Tonny Muthuri

SQL FUNCTIONS, CTEs AND SUB-QUERIES EXPLAINED

Tonny Muthuri — Mon, 19 Oct 2026 04:55:21 +0000

INTRODUCTION

It has been two weeks of intensive learning, exploring sql concepts from scratch at luxdevHQ.
Structured Query Language(SQL) is basically the language used to communicate with databases.
Sql is one of the core tools in data analysis.
It allows users to retrieve, clean, transform, combine, and analyze data stored in databases.
There are various Databases management systems used which have different functionalities such as Postgresql, Oracle, Mysql, Microsoft Sqlserver.
For the case of this article, we will be using Postgresql.

Sql Functions

Functions are built in operations that perform specific tasks on the data.
They help you calculate, transform, clean, and analyze data.
Some of the functions include;

i. Row_Number()
This function assigns unique sequential numbers to each row.
The most common uses is finding the top record within each group.

ii. Rank()
Ranking with gaps for ties.
It gives tied rows the same rank and skips the next rank.

iii. Dense Rank()
This ranks without gaps
Simply put, it does not skip a rank when there is a tie.

iv. Ntile(n)
It distributes into n buckets.
It also assigns each row a group number.

v. Lag()/Lead()
It accesses previous /next row.
They're very useful when comparing periods.

select
        visit_date,
        total_bill_kes,
        lag(total_bill_kes) over (order by visit_date ) as previous
from visits;

select
        visit_date,
        total_bill_kes,
        lead(total_bill_kes) over (order by visit_date ) as next
from visits;

vi. Count()
This function counts rows present in a given table.

select 
        insurance_provider, 
        count(patient_id)  no_patients
from visits
where insurance_provider not in('None')
group by insurance_provider
order by count(patient_id) desc
limit 1;

vii. Sum()
The function is used to add numeric functions.

select 
    insurance_provider, 
    sum(total_bill_kes) as revenue
from visits
group by insurance_provider
order by sum(total_bill_kes) desc
limit 1;

viii. First_Value()
This function returns the value from the first row in the window.

ix. Last_Value()
Returns the value from the last row in the window.

x. Max()
This function returns the largest value in the column.

select 
        insurance_provider,
        max(total_bill_kes)  as Highest
from visits 
group by insurance_provider
order by max(total_bill_kes)
limit 1;

x. Min()
Returns the smallest value in the column.

select 
        insurance_provider,
        Min(total_bill_kes)  as smallest
from visits 
group by insurance_provider
order by max(total_bill_kes)
limit 1;

Order by()
It is used to sort data.
The ORDER BY clause allows you to sort the result set of a query by one or more columns, either in ascending (ASC) or descending (DESC) order.

select 
    insurance_provider, 
    sum(total_bill_kes) as revenue
from visits
group by insurance_provider
order by sum(total_bill_kes) desc
limit 1;

Partition by
It is used with analytic (window) functions to divide the result set into partitions or logical groups, where calculations are independently performed for each partition.

select 
        insurance_provider,
        total_bill_kes,
        ntile(4) over (partition by insurance_provider order by total_bill_kes) as group
from visits;

Over()
Over() tells SQL to perform aggregate calculation across a set of rows without removing the individual rows.

Sql Joins

A join combines rows from two or more tables based on a related column between them, usually a key that exists in both.
i. Inner Join
Returns all rows from the left table plus matching rows from the right table.
Where there's no match the right table's columns come back as null.

ii. Left Join
A left join returns all rows from the left table (the table mentioned before the LEFT JOIN keyword), along with matching rows from the right table.
If there are no matching rows in the right table, NULL values are included in the result set.

iii. Right Join
A right join returns all rows from the right table (the table mentioned after the RIGHT JOIN keyword), along with matching rows from the left table. If there are no matching rows in the left table, NULL values are included in the result set.

iv. Full outer Join
A full join returns all rows from both tables, matching rows from both tables where available.
If there is no match, NULL values are included in the result set.

select 
        D.department_name,
        V.patient_satisfaction_score
from department d
full join visits v
on d.department_id = v.department_id;

v. Cross Join
A cross join returns the Cartesian product of the two tables, meaning it returns all possible combinations of rows from both tables. It doesn't require a join condition.

vi. Self Join
It's when a table is joined to itself.
It is useful when records in the same table have relationships with other records in that same table.

Common Table Expressions

A Common Table Expression (CTE) is a temporary result set that simplifies and structures SQL queries.
It is defined using the WITH keyword and can improve query readability and reusability
i. multiple CTEs
This is where you create multiple CTEs in the same query.

with doctor_totals as (
    select
        doctor_id,
        SUM(total_bill_kes) AS total_bill
    from visits
    group by  doctor_id
),
average_bill as (
    select
        AVG(total_bill) as avg_bill
    from doctor_totals
)
select
    d.doctor_name,
    dt.total_bill
from doctor_totals dt
join doctors d
    on dt.doctor_id = d.doctor_id
cross join average_bill ab
where dt.total_bill > ab.avg_bill;

ii. Recursive CTEs
This type of CTE allows us to work with hierarchical or recursive data structures.

A CTE can be used to calculate aggregated metrics like total bill, avg_patient satisfaction score and then reused in further calculations or to filter out unnecessary data.
This can optimize performance by avoiding complex aggregation in the main query

Subqueries

A subquery is a SQL query written inside another query.
The outer query uses the result of the inner query as part of its logic.

i. Scalar Subqueries
Returns exactly one row and one column — a single value.

select
    visit_id,
    total_bill_kes
from visits
where total_bill_kes > (
    select AVG(total_bill_kes)
    from visits
);

ii. Correlated Subqueries
A correlated subquery references columns from the outer query, meaning it is executed once for each row of the outer query.
This makes it more powerful but potentially more expensive.
iii. Non-Correlated Subqueries
A subquery that does not depend on the outer query.
It can run independently of the outer query and usually executes once.

iv. Nested Subqueries
A nested subquery is a subquery inside another subquery.

JUMIA PRODUCT PERFORMANCE INTERACTIVE DASHBOARD

Tonny Muthuri — Fri, 11 Sep 2026 11:19:19 +0000

Introducton

Jumia is an e-commerce platform where different sellers list their products and customers purchase them online. The platform generates data as sellers list products and customers interact with it and purchase products.
In this project, we analyze 112 products listed on Jumia that would give Jumia and its sellers a better understanding of how price, promotions and customer's feedback influence product performance.

Dataset Overview

The dataset contained 6 key fields: Products, current price, Old price, Rating, Reviews and Discount.
I identified several data quality issues like missing values especially on the Rating and Review columns, Some columns such as Current price and Old price were in string/text form and not in their true data type which is supposed to number/currency.
Ratingd header is misspelled.

Data Cleaning and preparation

Identifying and removal of duplicates is esential before data analysis this helps to evade inaccuracy.
The other step is to remove Kshs, commas and extra spaces from Current and Old prices columns. Then convert to a number.

=Value(SUBSTITUTE(SUBSTITUTE(B2,"ksh",""),",",""))
On Review column we change the negative values to absolute values
IF(E2="","",ABS(VALUE(E2)))
In Rating we remove "out of 5" to chage its data type to a decimal number.
=IF(F2="","",VALUE(SUBSTITUTE(F2,"out of 5","")))

Data Enrichment

To make our dataset more complete and valuable we add a few columns;
Discount Amount- The amount that a customer saves when a product is sold at a reduced price.
(Old price- current price)
Rating Category- Poor<3, Average- 3-4.5, Excellent>4.5
=IF(F2="","Missing",IF(F2<3,"Poor",IF(F2<=4.5,"Average","Excellent")
Discount Category- Low Discount<20%, Medium Discount=20%-40%, High Discount>40%
=IF(D2="","Missing",IF(D2<20%,"Low Discount",IF(D2<=40%,"Medium Discount","High Discount")))
Price Category- We categorize price based on Quartiles
Q1-493, Q3-1670
And named their cells as Price_Q1 and Price_Q3
=IF(B2="","Missing",IF(B2<=Price_Q1,"Low Price",IF(B2<=Price_Q3,"Medium Price","High Price")))

Data Analysis

After cleaning and preparing data we moved to analyzing it

Deriving KPIs

Variable	values
Total products	112
Average Current Price	Ksh 1,187
Average Old Price	Ksh 1,811
Average Discount	Ksh 624
Average Rating	3.9
Total reviews	723
Most Expensive Price	Ksh 3750-32pcs portable codeless drill
Least Expensive Price	Ksh 38- Single head knitting crothet sweater needle set

Relationship analysis

I created 3 scatter charts and derived R-squared and their correlations.
Discount vs Reviews
The analysis shows weak negative correlation.
Correl= -0.137
R² = 0.0187
Current price vs Rating
Week positive correlation
Correl= 0.1101
R² =0.0121
Rating VS Reviews
Week Positive correlation
Correl= 0.0572
R²= 0.0033

Dashboard Creation

The complete workbook, including the raw and cleaned data, analysis sheets, PivotTables, charts, and final interactive dashboard, is available in my GitHub repository below.
https://github.com/tonnymuthuri6-lang/JUMIA-PRODUCT-PERFORMANCE-DASHBOARD

GETTING STARTED WITH EXCEL FOR DATA ANALYTICS: FROM BASICS TO DATA CLEANING.

Tonny Muthuri — Tue, 01 Sep 2026 05:18:06 +0000

INTRODUCTION.

Many times, I thought Excel was just a simple tool that could only work on simple tasks. After interacting with it for the past one week, I have come to realize how powerful it is.
So, what exactly is Excel? Excel is a spreadsheet program developed by Microsoft that helps you organize, manage and analyze data.

Excel Use Cases.

-Used in data entry
-Data cleaning
-Analyzing data
-Data visualization

Overview.

There are many tools and commands displayed on Excel's interface.
Ribbon- It's a toolbar that contains all the commands used in excel.
For example, Home, Formulas, insert, Review, View, Page layout, Draw.

Terminologies used in Excel.

-Cell- A basic unit of a worksheet where you can enter or display data.
Each cell has an address that identifies each cell.

-Formula bar- Area above the worksheet where you can view, enter and display formulas.

-Rows-This are horizotal lines of cells that run from left to right.

-Columns- This are vertical lines of cells that run from top to bottom in a worksheet.

-Name box- It contains the address the selected cell.

Data Types.

-Numbers- Values that can be used in calculations ie, 2,5,7,54
-Texts-refers to letters, words, sentences or combination of characters

used to describe
-Date- this are values that reprent a specific day, month or year ie. 30/04/2026.
To access data types, highlight column, right click then format cells.

Data Validation

This is typically restricting what a user can enter into a data cell. We use data validation to prevent errors when entering data, to maintain consistency across data set, ensuring correct data type is entered and to improve data quality before analysis
Follow this steps to validate your data:

-select the column or cells you want to validate
-On the data ribbon, select Data
-click on Data Validation the choose the validation criteria.

Data Filtering and Sorting.

Filteringis displaying only the data that meets a specific condition while temporarily hiding the rest.
We use Sort & Filter command to filter.
Types of Filtering:
-Text filtering
-Date filtering
-Number filtering

Sorting is arranging data in a particular order ie
-Number sorting- its arranging numbers in a particular order that is ascending or descending.
-Text sorting- its arranging text data in a specific order can be either ascending or descending.

Data Preparation and Cleaning.

Data cleaning is one of the most essential steps in data analysis.
Whenever we deal with data, we are bound to encounter data inconsistencies such as duplicate values, missing values or even spelling mistakes.

Conditional Formatting

It's an excel feature used to automatically change the appearance of cells based on specific rules or conditions.
Used to identify outliers, trends, patterns,duplicates and missing information

Key Functions used in data Cleaning

1. Text Functions.

Upper() Function- converts texts to uppercase.

Lower() Function- converts texts to lowercase.

Proper() Function- capitalizes the first letter in a word.

Trim() Function- It removes extra spaces before and after the text.
We use LEN()to count how many characters are in a text
Mid()- used to extract characters in the middle of a text ie.
MID(F2,2,3)

-F2- Representing text
-2- representing the starting position
-3-representing the number of characters to be removed.

Aggregate Functions.

Product()- Multiplies numbers in a range
Max()- used t find the highest value in a range
Median()- Returns the value in the middle for the sorted data.
Mode()- used to find most frequent value in a range

Conditional Aggregations.

COUNTIF- used for condiotional counting ie.

=COUNTIF(E2, E870, "Sales")

COUNTIFS()- Used when we have more than one condition ie
finding male employees who have masters
=COUNTIFS(I2:I870,"M",N2:N870,"Masters")
SUMIF- used to add values that meet a specific criteria
we start with criteria range then sum range.
ie Total employee salary in HR department
SUMIF(E2:E870,"Human Resource", F2:F877)
SUMIFS()- Used in more than two conditions
we start with criteria range, criteria, then the sum range
ie . Salary for females in Human resource department.
SUMIFS(F2:F877,E2:E870,"Human Resource,I2:I870,"F")

Date/Time Functions

Today()- it returns today's date
ie
=TODAY() click enter.
Now()- Gets Todays date and time

-YEAR()- Used to extract year from a date
ie =YEAR()

-MONTH()- Used to extract a month from date
-DAY()- Extracts a day from a date ie.
=Day()
-DATEDIF()- Calculates the difference between two dates

My First GitHub Project.

Tonny Muthuri — Sat, 22 Aug 2026 21:47:36 +0000

Introduction

People, especially beginners, face challenges when making changes to their projects and pushing those changes to Github. Some of the challenges faced include Git command, authentification, configuration etc.
This article aims to explain the process of building a Github project, from creating local directories/folders to uploading the project to Github using Git and secure shell(ssh).
I will use a project titled Poor Performance in 2025 National Exams as a case example to illustrate the steps involved in creating a project.
The process is outlined in the following steps:

STEP 1: Creating directories

The first step of a project is to establish a well organized directory structure. This helps keep project files organized logically, making them easier to manage, access and maintain throughout the development process.
We create our project directories within Desktop and OneDrive.
Desktop is basically a special directory used by windows to store files and shortcuts while OneDrive is a microsoft's storage store.
In our case we start by navigating to the desktop directory using Git.

For example, cd desktop means 'go to desktop'
Next, Use ls to list all the directories and files in desktop.
Once you have navigated the desktop directory, Proceed to making your first project directory.
We use mkdir commandto create a new directory.
For example,

mkdir "Poor-performance-in-2025-National Exam"

.
Next, we create additional directories within our newly created project directory. In this case we will create a directory called Data, which will store the datasets required for the analysis.

For example,

mkdir data

STEP 2: Creating Files

Files are essential part of building a project. In this step, we will create a README.md file.
A README.md gives an overview of the project and describes its purpose, structure and usage in a clear and understandable way. README.md is written using Markdown language.
we use touch to create files.
For example,

touch README.md

STEP 3: Printing on README.md

we use echo or nanoto add content on README.md file and to describe our project.
In our case example, we will add project title and an overview.
*Using echo

echo "# Poor Performance in 2025 National Exam" > README.md echo "# Project Overview" >> README.md

The > operator overwrites the file, while >> adds content to the existing file without deleting what is already there.

*using nano README.md

# Poor performance in 2025 National Exams ## Project Overview This project aims to identify key factors that led to poor academic performance in 2025. ## Tools Used -Excel -Power Bi -Python ## Challenges Faced -Missing Data -Outliers -Duplicates -Data Accessibility -Unstructured data

STEP 4: Commit the project

A commit is a saved checkpoint in the history of repository. It records all the changes that have been made at a particular point in time.
First, we stage all eligible changes.
Using:

git commit add.

Once the changes have been staged,we save them as a commit and give a commit message.

git commit -m " Add Poor performance in 2025 National Exam Analysis"

The -m in our command allows us to provide a message describing the changes included in the commit.
To view the history of commits in our repository we use:

git log

or to show our commit in a short format

git log --oneline

STEP 5: Checking and Connecting Git to Github

Github is an online platform where Git repositories can be stored and shared.
To securely connect Git to Github, we need to usea Secure shell(ssh). SSH is typically a protocal used to create secure connection between Git and Github.
To check whether ssh keys already exists before generating one use:

ls -al ~/.ssh

If a SSH key does not already exist, Generate ed25519 ssh key from the Git using:

ssh-keygen -t ed25519 -C "tonn*********@gmail.com"

Git generates two keys:
-public key
-private key
Copy the public one.

cat ~/.ssh/*******.pub

Add the copied ssh to your Github (Setings > ssh and gpg keys).
Fill in the ssh key form:
Title- give the key a descriptive name
Key type- choose authentication key
Key- paste the copied public ssh key here
Then click add ssh key.
To test the ssh connection you need to return to GitBash and run:

ssh-T git@github.com

STEP 6: Connecting Git repository to a remote repository

Open the Github and create a new repository. Once the repository has been created, copy the ssh.
We can then connect our local repository to a remote repository using this command:

git remote add origin "paste the ssh here"

To check the remote repositories connected to our local Git repository we use this command:

git remote -v

The final stepis to push (sending changes from Git to Github)

git push -u origin main

The command uploads the created commits from our local main branch to the Github repository called Origin
The final step is to refresh your github.