DEV Community: David Gitonga

# SQL for Data Analysis: Window Functions, Joins, CTEs and Subqueries

David Gitonga — Fri, 16 Oct 2026 17:46:49 +0000

Introduction

As I continue learning SQL for data analysis, I have come across some concepts that help answer more complex questions than basic SELECT, WHERE, and GROUP BY.

In this article, I will explain four of them using examples from a hospital dataset: Window Functions, Joins, Common Table Expressions (CTEs), and Subqueries.

The goal is to understand what each one does and when to use it in a real situation.

The examples use assumed table and column names. They may need to be adjusted to match the actual hospital dataset.

1. SQL Window Functions

What are Window Functions?

Window functions allow us to calculate values across related rows without removing the individual records.

For example, a hospital analyst may want to rank patients by billing amount while still seeing each patient's record.

Basic Syntax

SELECT
    VisitID,
    Amount,
    SUM(Amount) OVER () AS TotalBilling
FROM Billing;

OVER() tells SQL which set of rows to use for the calculation.

PARTITION BY divides rows into groups.
ORDER BY determines the order of rows.
A window frame determines which rows are included in a calculation.

Example of a running total:

SUM(Amount) OVER (
    ORDER BY VisitID
    ROWS BETWEEN UNBOUNDED PRECEDING
    AND CURRENT ROW
)

This adds the current bill to all the preceding bills in the specified order.

Ranking Functions

1. ROW_NUMBER()

Assigns a unique number to each row.

ROW_NUMBER() OVER (
    PARTITION BY PatientID
    ORDER BY VisitDate
)

This numbers each patient's visits from earliest to latest.

2. RANK()

Ranks records and gives tied values the same rank, leaving gaps afterward.

RANK() OVER (ORDER BY Amount DESC)

This ranks hospital bills from highest to lowest.

3. DENSE_RANK()

Works like RANK(), but does not leave gaps between ranks.

DENSE_RANK() OVER (ORDER BY Amount DESC)

This is useful when ranking patients or departments by billing amount.

4. NTILE()

Divides ordered records into approximately equal groups.

NTILE(4) OVER (ORDER BY Amount DESC)

This divides billing records into four groups for cost analysis.

Navigation Functions

5. LAG()

Returns a value from a previous row.

LAG(VisitDate) OVER (
    PARTITION BY PatientID
    ORDER BY VisitDate
)

This helps identify a patient's previous visit.

6. LEAD()

Returns a value from a following row.

LEAD(VisitDate) OVER (
    PARTITION BY PatientID
    ORDER BY VisitDate
)

This helps compare a visit with the next recorded visit.

7. FIRST_VALUE()

Returns the first value in the specified window.

FIRST_VALUE(VisitDate) OVER (
    PARTITION BY PatientID
    ORDER BY VisitDate
    ROWS BETWEEN UNBOUNDED PRECEDING
    AND UNBOUNDED FOLLOWING
)

This identifies the first recorded visit for each patient.

8. LAST_VALUE()

Returns the last value within the specified window frame.

LAST_VALUE(VisitDate) OVER (
    PARTITION BY PatientID
    ORDER BY VisitDate
    ROWS BETWEEN UNBOUNDED PRECEDING
    AND UNBOUNDED FOLLOWING
)

This displays the latest recorded visit date for each patient.

Aggregate Window Functions

These functions calculate values across rows.

Function	What it does	Hospital example
`SUM()`	Adds values	Total billing
`AVG()`	Calculates the average	Average bill
`COUNT()`	Counts rows or values	Number of visits
`MIN()`	Finds the smallest value	Lowest bill
`MAX()`	Finds the largest value	Highest bill

Example:

SELECT
    VisitID,
    Amount,
    AVG(Amount) OVER () AS AverageBill
FROM Billing;

This displays each bill alongside the average bill across the dataset.

The main difference between a normal aggregate and a window aggregate is that GROUP BY usually reduces records to one row per group, while a window function keeps the individual rows.

2. SQL Joins

What are Joins?

Hospital data is usually stored in different tables. For example, patient details may be in Patients, visits in Visits, and charges in Billing.

Joins allow us to combine related records using a common column.

INNER JOIN

Returns only matching records from both tables.

SELECT v.VisitID, b.Amount
FROM Visits AS v
INNER JOIN Billing AS b
    ON v.VisitID = b.VisitID;

Use this when you only need visits with matching billing records.

LEFT JOIN

Returns all records from the left table and matching records from the right.

SELECT p.PatientID, v.VisitID
FROM Patients AS p
LEFT JOIN Visits AS v
    ON p.PatientID = v.PatientID;

This includes patients who have never visited the hospital. Their visit columns will contain NULL.

RIGHT JOIN

Returns all records from the right table and matching records from the left.

SELECT v.VisitID, b.BillID
FROM Visits AS v
RIGHT JOIN Billing AS b
    ON v.VisitID = b.VisitID;

This is useful when every billing record must appear, even if a matching visit is missing.

FULL OUTER JOIN

Returns matching and unmatched records from both tables.

SELECT v.VisitID, b.BillID
FROM Visits AS v
FULL OUTER JOIN Billing AS b
    ON v.VisitID = b.VisitID;

This can help identify missing links during a billing audit. Note that MySQL does not directly support FULL OUTER JOIN.

CROSS JOIN

Returns every possible combination of rows from two tables.

SELECT d.Department, s.ShiftName
FROM Departments AS d
CROSS JOIN Shifts AS s;

This can help generate department and shift combinations for staffing plans.

SELF JOIN

Joins a table to itself.

SELECT
    d.DoctorName AS Doctor,
    m.DoctorName AS Manager
FROM Doctors AS d
LEFT JOIN Doctors AS m
    ON d.ManagerID = m.DoctorID;

This displays each doctor alongside their manager.

Quick Comparison

Join	Main purpose
`INNER JOIN`	Only matching records
`LEFT JOIN`	All left-side records
`RIGHT JOIN`	All right-side records
`FULL OUTER JOIN`	All records from both sides
`CROSS JOIN`	Every combination
`SELF JOIN`	Relate records in the same table

One thing I have learned is that joins can increase the number of rows when multiple records match. This is important when calculating billing totals because careless joins can duplicate amounts.

3. Common Table Expressions (CTEs)

What is a CTE?

A CTE is a named query result created using WITH. It makes complex SQL easier to read by breaking it into smaller steps.

Basic CTE

WITH PatientBilling AS (
    SELECT
        PatientID,
        SUM(Amount) AS TotalBilling
    FROM PatientBillingData
    GROUP BY PatientID
)
SELECT *
FROM PatientBilling
WHERE TotalBilling > 5000;

This calculates each patient's total billing and then returns patients whose total exceeds 5,000. PatientBillingData is an illustrative table name.

Multiple CTEs

We can define more than one CTE when a problem requires several calculations.

WITH VisitCounts AS (
    SELECT PatientID, COUNT(*) AS TotalVisits
    FROM Visits
    GROUP BY PatientID
),
BillingTotals AS (
    SELECT v.PatientID, SUM(b.Amount) AS TotalBilling
    FROM Visits AS v
    JOIN Billing AS b
        ON v.VisitID = b.VisitID
    GROUP BY v.PatientID
)
SELECT vc.PatientID, vc.TotalVisits, bt.TotalBilling
FROM VisitCounts AS vc
JOIN BillingTotals AS bt
    ON vc.PatientID = bt.PatientID;

The first CTE counts visits, the second calculates billing totals, and the final query combines them.

Recursive CTEs

Recursive CTEs reference their own results to work through hierarchical data, such as a hospital's doctor-manager structure.

WITH RECURSIVE DoctorHierarchy AS (
    SELECT DoctorID, DoctorName, ManagerID, 0 AS Level
    FROM Doctors
    WHERE ManagerID IS NULL

    UNION ALL

    SELECT d.DoctorID, d.DoctorName,
           d.ManagerID, dh.Level + 1
    FROM Doctors AS d
    JOIN DoctorHierarchy AS dh
        ON d.ManagerID = dh.DoctorID
)
SELECT *
FROM DoctorHierarchy;

The first part identifies the top-level doctors. The recursive part finds the doctors reporting to them and continues through the hierarchy.

This syntax is for PostgreSQL and other databases supporting WITH RECURSIVE. SQL Server uses different recursive CTE syntax.

CTEs with Joins and Aggregations

CTEs can summarize data before joining it to another table.

For example, we can calculate department billing using SUM() and GROUP BY, then join the result to a department table to display department names and totals.

CTEs vs Subqueries

A subquery is a query inside another query. A CTE gives a query a name before the main query uses it.

Both can solve similar problems, but CTEs often make long queries easier to understand and maintain. Neither is automatically faster; performance depends on the database and execution plan.

4. SQL Subqueries

What is a Subquery?

A subquery is a query placed inside another SQL query. It helps us use the result of one calculation to answer another question.

Scalar Subquery

Returns one value.

SELECT VisitID, Amount
FROM Billing
WHERE Amount > (
    SELECT AVG(Amount)
    FROM Billing
);

This returns bills that are higher than the average bill.

Non-Correlated Subquery

Runs independently of the outer query because it does not reference the outer query's columns.

SELECT VisitID, Department
FROM Visits
WHERE Department IN (
    SELECT Department
    FROM Doctors
    WHERE Department = 'Cardiology'
);

This finds visits in a department returned by the inner query.

Correlated Subquery

References a column from the outer query.

SELECT p.PatientID, p.PatientName
FROM Patients AS p
WHERE EXISTS (
    SELECT 1
    FROM Visits AS v
    WHERE v.PatientID = p.PatientID
);

This returns patients who have at least one visit.

Nested Subquery

A subquery placed inside another subquery.

SELECT PatientID
FROM Billing
GROUP BY PatientID
HAVING SUM(Amount) > (
    SELECT AVG(TotalAmount)
    FROM (
        SELECT SUM(Amount) AS TotalAmount
        FROM Billing
        GROUP BY PatientID
    ) AS PatientTotals
);

This example assumes billing records contain PatientID. It compares each patient's total billing with the average of patient-level totals.

IN

Checks whether a value matches any value returned by a subquery.

SELECT PatientID
FROM Visits
WHERE Department IN (
    SELECT Department
    FROM Doctors
    WHERE Department = 'Cardiology'
);

This finds visits in Cardiology when that department is present in the doctor records.

EXISTS

Checks whether a subquery returns at least one row.

SELECT p.PatientID
FROM Patients AS p
WHERE EXISTS (
    SELECT 1
    FROM Visits AS v
    WHERE v.PatientID = p.PatientID
);

This returns patients with recorded visits.

ANY

The comparison needs to be true for at least one value returned by the subquery.

SELECT VisitID, Amount
FROM Billing
WHERE Amount > ANY (
    SELECT Amount
    FROM Billing
    WHERE Amount < 5000
);

This finds bills that exceed at least one bill below 5,000.

ALL

The comparison must be true for every value returned by the subquery.

SELECT VisitID, Amount
FROM Billing
WHERE Amount > ALL (
    SELECT Amount
    FROM Billing
    WHERE Amount < 5000
);

This finds bills that exceed every bill below 5,000.

Subqueries in SELECT, FROM and WHERE

In SELECT: Add a calculated value to each row.

SELECT VisitID,
       (SELECT AVG(Amount) FROM Billing) AS AverageBill
FROM Billing;

In FROM: Create a derived table that can be queried.

SELECT PatientID, TotalBilling
FROM (
    SELECT PatientID, SUM(Amount) AS TotalBilling
    FROM Billing
    GROUP BY PatientID
) AS PatientTotals;

In WHERE: Filter records based on another query's result.

SELECT VisitID, Amount
FROM Billing
WHERE Amount > (SELECT AVG(Amount) FROM Billing);

The main difference is where the result of the subquery is used: as a selected value, as a dataset, or as a filtering condition.

Conclusion

These four SQL concepts help me move beyond basic queries and answer more practical data analysis questions.

Window functions help with ranking, comparisons, and calculations across rows.
Joins bring related data together from different tables.
CTEs make complex queries easier to organize.
Subqueries allow one query to use the results of another.

For me, the important part is not just memorizing the syntax. It is understanding the problem first and choosing the SQL technique that solves it.

As I continue working with hospital data, these concepts will help me analyse patient visits, billing, department activity, and missing records more effectively.

SQL #DataAnalytics #DataAnalysis #Database #LearningInPublic

Building an Interactive Excel Dashboard for E-commerce Product Analysis , A Jumia case Study

David Gitonga — Sat, 05 Sep 2026 14:19:46 +0000

Introduction

For my first data analytics project, I have had the chance to work with a Jumia products dataset and used Excel to turn the raw data into something that could actually be used to understand product performance.

The main goal of the project was not just to clean it and make a pretty dashboard. I wanted to use the data to answer some simple questions about product pricing, discounts, ratings and reviews.

some of the questions answered within the project were:

Do products with bigger discounts get more reviews?
Do highly rated products get more engagements?
Is there a relationship between price and rating?
Which products have the highest rating and reviews?
which products might need better pricing or marketing strategies? This project also gave me a chance to practice some of the basic data analytics skill I have been learning, especially data cleaning, Excel formulas, PivotTables, Charts and Dashboard creation

THE DATASET

The dataset contains products information from Jumia. The main columns I worked with were:

Product
Current price
Old price
Discount
Review
Rating The data was not completely ready for analysis, so I first had to inspect it and make some changes before building the dashboard

One important thing I kept in mind is that reviews are being used as a measure of engagement, not as actual sales data. A product having more reviews does not automatically mean its more products.

The Raw Data

I started by creating a Raw_Data sheet.

I kept this sheet as the original version of the dataset so that I could always go back to the starting point if something went wrong during cleaning.

While checking the data, I noticed some issues such as duplicate records, price stored with currency text, price range and some missing reviews and rating values.

I also found some products that appeared more than once with exactly the same information.

Cleaning the Data

After checkinh the raw dat, I created a Cleaned_Data sheet.
The main cleaning steps include:

Removing exact duplicate records. -Working with the price fields so the be used for calculations
Making sure discount values could be used in calculation
Checking rating and review values
Keeping missing values blank instead of automatically treating them as zero

I also added some calculated fields that would help with the analysis.

One of them was Discount Amount,which shows the difference between the old price and current price.
For example:
=old price-Current Price
I also used categories to make some of the analysis easier to understand, such as rating categories, discount categories and price categories.

Using Excel for the Analysis

once the data was prepared, i moved on to the analysis section.
I calculated some of the basics statistics such as:

Total number of products
Average current price
Average old price
Average discount
Average rating
Total reviews
Highest product price
Lowest product price I also used correlation to look at the relationship between some of the variables. which I looked at: Discount vs Reviews Rating vs Reviews Current Price vs Rating

The purpose was to see whether there was a noticeable relationship between these variables.
However, correlation doesn't mean that one variable caused another. It only helps show whether two variables tend to move together.

PivotTables and Charts

After the calculation, I created PivoTables to make it easier to summarize the data.
Some of the PivotTables looked at:

Rating categories
Discount categories
Price categories
Reviews by discount category
Top products by rating
Top products by reviews

I then used these results to create charts.

Building the Dashboard

The final step was putting the important information into or dashboard.
I included KPI sections for things like:

Total Products
Average Price
Average Discount
Average Rating
Total Reviews

I also included charts for the different relationships I investigated and tables showing some of top-performing products.

I added slicers so that the dashboard could be filtered by categories such as Rating category, Discount Category and price category.

Conclusion

Overall, this project was a good way for me to practice the basic data analytics process using Excel.

I started with raw Jumia product data, Cleaned and prepared it, analyzed the information, created PivotTables and finally build an interactive dashboard.

This project also showed me that even a relatively simple dataset can provide useful insights when it is properly when it is properly organized and analyzed.

This is one of my first steps in building my data analytics portfolio, and I plan to continue improving my skills by working on more projects using Excel, SQL, Python and other data analytics tools.

My GitHub project

The complete project, including the dataset, Excel dashboard and project documentation, is available in my GitHub repository. [https://github.com/gitosh-star/JUMIA_PRODUCTS_PERFOMANCE_DASHBOARD]

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

David Gitonga — Sat, 29 Aug 2026 18:32:41 +0000

EXCEL OVERVIEW

What is Excel? These is a spreadsheet tool for organizing, analyzing, calculating, and visualizing data. Wiledy used for personal and professional data management.

EXCEL INTERFACE

This is an Excel workbook that usually subdivided into work sheets

Ribbon: Toolbar with commands(Home, insert, data..)
Quick Access Toolbar: save, undo, redo.
Formula Bar: displays cell content
Rows, columns, cells.

Data validation

The restrictions on the type of data user can input into a cell. (Dropdown lists, Numbers , ranges, date limits, text length)

THE DATA CLEANING

Data cleaning is the process where you prepare raw datasets so they are accurate, consistent, and ready for analysis. This is a very crucial set because one gets to correct small errors , duplicates which may lead to misleading results in the formulas. Clean data ensures reliability and save time when performing deeper analysis.

Raw uncleaned data

Cleaned data

DATA CLEANING TECHNIQUES

Removing duplicates: Prevents inflated results in analysis.
Mombasa
Mombasa

Standardizing text this ensures consistency

NAIROBI
NairoBi

checking dates: corrects inconsistent format and invalid entries, fixing words format:

use PROPER() to standardize capitalization in names.

DATA FORMARTTING & CLEANING

Number Formatting

this changes how numbers display without altering their actual value Conditional formatting
This highlights cells automatically based on trends or criteria

CONCLUTION

In conclusion data cleaning in excel is more the a technical step, it is the foundation of reliable analysis. This is a effective data cleaning turns Excel from a simple spreadsheet tool into a powerful engine for analytics.

Workflow Flow From Local Folder To GitHub

David Gitonga — Fri, 21 Aug 2026 19:40:00 +0000

THE GITHUB PROJECT

Imagine while creating your repository on GitHub, you remember that you have to include your excel file or any file that is all the way tacked in your folders and you want to use Git. How do you go about it.

GIT

How does Git work;

It use Linux , where you use SSH (Secure shell) which let you opens an encrypted terminal session to a remote machine in our case GitHub.

THIS IS THE TYPICAL WORKFLOW FROM START TO FINISH

Get Git bash first
Great now open
we need a local folder in our Git Bash we run the following commands;

- ssh - to create a remote login
- ssh-keygen -t <> -c <adress> - with the correct server_address
- ls -al ~/.shh- check if you have added the new sshkey
- ls - which is listing the files and  directories in the active directory
- cd - which opens a directory
- mkdir <name> - make a new directory
- touch file.text - to create an empty file
- nano file.text - write a comment or context of the data use in place or -echo-
{CREAT REPOSITORY ON GITHUB COPY SSH}
- git remote add origin <ssh_key> - link to the remote server
- git commit - commit the link
- git status - check the branch
- git fetch - download a latest change 
- git push - to GitHub

CONCLUTION

Git is easy once you understand the commands.
Refresh your GitHub and watch as you have connected.

Onboarding Session At Lux DevHq

David Gitonga — Tue, 11 Aug 2026 12:10:02 +0000

Introduction to lux dev

This are all the things I've learned today about how to write articles it's roles and how to ensure you write a good article
asterisks for bold
dash for italics

greater than for quotes

line of code

SELECT * FROM Cohort9,application;

writing a block of code

SELECT student name_
use three up commas for a block of code

photo session

how to upload select from the upload icon, devto converts it to marklaguage

how to add a URL link

This is the link to the luxdev git hub link
link:luxdevHQ