SQL Deep Drive.

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

1. 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

1. 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;

1. 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

1. 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.