SQL CONCEPTS - Window Functions, Joins, Common Table Expressions & Subqueries

Gichanga Wangui — Sun, 18 Oct 2026 14:44:07 +0000

1. SQL Window Functions

These are functions that are used to perform calculations. They return one output row per one input row.
Examples of Window functions include:

ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE(), LAG(), LEAD(), FIRST_VALUE(), LAST_VALUE(), SUM(), AVG(), COUNT(), MIN(), and MAX().

ROW NUMBER() It assigns a unique sequential number to each row within an ordered partition, starting at 1. Example:-

Row number() over(
[Partition by column1, ..]
Order by column2,...)

RANK() It assigns a rank to each row within a result set based on a specified order. When used together with Desc, it ranks rows in descending order. Example:-

SELECT
Employee_name, salary,
Rank() over (Order by salary) as Rank
FROM Employees;

DENSE_RANK() It assigns a rank to each row within a partition of a result set. It works with descending and ascending formats. Example:-

Dense_Rank() Over (
[Partition by column_name]
Order by column_name
)

NTILE() It divides an ordered result set into a specified number of equal groups where each row is assigned a group number starting from 1. Example:-

SELECT ID,
      NTILE(3) OVER (ORDER BY ID) AS GroupNumber
FROM Patients;

LAG() It allows access of data from a previous row in the same result set without using a self-join. It is used for comparison purposes. Example:- Comparing current and previous month sales.

WITH MonthlySales AS (
   SELECT
       MONTH(OrderDate) AS MonthNum,
       SUM(TotalDue) AS TotalSales
   FROM Sales.SalesOrderHeader
   WHERE YEAR(OrderDate) = 2023
   GROUP BY MONTH(OrderDate)
)
SELECT
   MonthNum,
   TotalSales,
   LAG(TotalSales, 1, 0) OVER (ORDER BY MonthNum) AS PrevMonthSales,
   TotalSales - LAG(TotalSales, 1, 0) OVER (ORDER BY MonthNum) AS SalesDiff
FROM MonthlySales;

LEAD() It allows users to access data from a subsequent row in the same result set without using a self-join. It is used when comparing values between consecutive rows, calculating differences or changes over time and looking ahead in ordered data. Example:- SELECT Company, Year, Amount, LEAD(Amount, 1) OVER ( PARTITION BY Company ORDER BY Year DESC ) AS Next_Year_Amount FROM CompanySales;
FIRST_VALUE It returns the first value in an ordered set of rows within a partition. Example:- FIRST_VALUE(column_name) OVER ( [PARTITION BY partition_column] ORDER BY order_column [ASC|DESC] )
LAST_VALUE() It returns the last value in an ordered set of rows within a partition. Example:-

LAST_VALUE(column_name) OVER (
    [PARTITION BY partition_column]
    ORDER BY order_column [ASC|DESC]
)

SUM() It is used to calculate the total sum of values in a numeric column. Example:-

SELECT SUM(column_name) 
FROM table_name;

AVG() It calculates the average of values in a numeric column Example:-

SELECT AVG(Column_name)
FROM table_name;

COUNT() It returns the number of rows that match a specified condition Example:-

SELECT COUNT(*) AS TotalProducts
FROM Products;

MIN() & MAX()
Min() is used to find the smallest value in a specified column.
Max() is used to find the largest value in a specified column. Examples of Min():-

SELECT MIN(Price) AS SmallestPrice
FROM Products;

Examples of Max():-

SELECT MAX(Price) AS LargestPrice
FROM Products;

SQL JOINS

They are used to combine data from multiple tables in a relational database hence providing more meaningful information based on relationships between tables.

Inner Join It returns only the rows that have matching values in both tables. Example:-

SELECT columns
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

Left Join It returns all rows from the left table and the matched rows from the right table. Example:-

SELECT columns
FROM table1
LEFT JOIN table2
ON table1.common_column = table2.common_column;

Right Join It returns all rows from the right table and the matched rows from the left table. Example:-

SELECT columns
FROM table1
RIGHT JOIN table2
ON table1.common_column = table2.common_column;

Full Outer Join It returns all rows when there is a match in either of left or right table records. Example:-

SELECT columns
FROM table1
FULL JOIN table2
ON table1.common_column = table2.common_column;

Common Table Expressions

Basic CTEs
It is a temporary, named result set that can be referenced within a single SQL statement. It is created using the with clause and can be used in select, insert, update, or delete queries.
Multiple CTEs
They’re defined in a single SQL Query by listing them after one with clause and separating each CTE with a comma.
Recursive CTEs
It is an SQL query that contains an anchor query and a recursive query joined by union all, allowing SQL to iterate through hierarchical of level-based data until no new rows are produced.

How can CTEs be used with Joins and aggregations?

They can be used to join multiple tables in the CTE or in the main query.
Aggregate the joined data.
Reference the CTE multiple times in the same query for reuse.

Differences between CTEs and subqueries

The main difference is that CTEs are named, reusable temporary result sets that are used to improve readability and maintainability, while subqueries are inline queries nested within another query and are used for simple or one-off calculations.

SQL SUBQUERIES

A subquery is a query within another query.

Scalar subqueries – It is a query that returns exactly one row and one column.
Example:- SELECT, WHERE, HAVING, and ORDER BY.
Correlated subqueries - It is a query that depends on values from the outer query and is executed once for each row processed by the outer query.
Non-correlated subqueries – It is a query that runs independently and executes once.

Subqueries used in SELECT, FROM, and WHERE clauses are used to perform dynamic filtering, aggregation, or derived table operations.
Examples:-

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

My First GitHub Project: From a Local Folder to GitHub Using Git and SSH

Gichanga Wangui — Thu, 20 Aug 2026 17:13:34 +0000

Installation And Setup Of Relevant Environments And User Accounts Creation

We begin the project by downloading Git bash from Git Website website and installation of the downloaded software. We proceed to create an account by signing up on Github.
We then proceed to configure and initialize username and e-mail address on Git bash by running git config command so that the identity of the user is correctly setup.

SSH Setup

We create an SSH Key which is a form of authentication that is used when pushing project from the local machine to the Github Repository. We achieve this by running the

ssh-keygen -t ed25519 -C

We proceed to copy the generated SSH Key and paste it on Github under SSH and GPG Keys section.
Once the SSH Key is authenticated, we test the connection and if successful, we proceed to verify the details by keying in a verification message sent to the registered e-mail address which returns a “success” message if the process is done correctly.

Gitbash

Git bash is a terminal that will help in executing commands throughout the project.
We go to git bash and create create project folders and files like Data, README.md, and scripts among others using MkDIR and Touch commands.
To work on the README.md file, there are two methods using “Nano” or “Echo” commands.
Nano opens a “separate” working area where the user can proceed to document the project.
Echo command runs within the Git bash terminal.
If working on a project using Ms Excel project, we can move the file to the Data folder using clip command or by copying it from File explorer.

Working With Commits

~ Git commit -m which explains what is saved thereby making it easy to understand the History of the project.
~ Git Log helps the user see the history on the commit
~ Git branch / Git main which shows the branch where the project is located / saved.

Creating a Github Repository

We create a Github repository by tapping the + button and then choosing “New repository” and then giving the repository a name.

Connecting / Pushing The project to Github

We open Git bash and run git remote command in this format git remote add origin git@github.com followed by the Github username.

We then proceed to run Git remote command which will provide a communication channel for the project on the computer and Git hub and upload the project.

The final stage of the project is to verify the project which we achieve by refreshing the Github page.

If the project upload was successful will have the various files and folders displayed on the Github Repository.

DEV Community: Gichanga Wangui