Exploring Window Functions, Joins, Subqueries and CTEs in SQL

Introduction

As I explore databases and SQL as part of my learning curve in becoming a data analyst. It is inevitable to learn about Functions in SQL. Today, I land at exploring Window Functions, Joins, Subqueries and Common Table Expressions. These functions and tools come in handy when writing queries that are necessary in answering pertinent questions using data. I'll be using a fictional Hospital Data set to demonstrate the said functions and methods.

SQL Window Functions

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.

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.

Syntax:

SELECT column_name1, 
       window_function(column_name2) 
       OVER ([PARTITION BY column_name3] [ORDER BY column_name4]) AS new_column
FROM table_name;

window_function: Function used for calculation/ranking

• column_name1: Column(s) to display
• column_name2: Function column
• column_name3: PARTITION BY column
• column_name4: ORDER BY column
• new_column: Result alias
• table_name: Source table

SQL window functions are mainly of two types: aggregate window functions and ranking window functions.

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 multiple rows.
AVG(): Calculates the average value 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 hospital bill along side different insurance providers.

Query:

SELECT patient_id, ward_id, insurance_provider, total_bill_kes,
       AVG(total_bill_kes) OVER (
           PARTITION BY insurance_provider
       ) AS avg_bill
FROM visits;

Output:

This average value is displayed for each patient belonging to the same insurance_provider.

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 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 payment_method _by _total_bill, allowing gaps in ranks when total_bills are equal.

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;

output:

DENSE RANK() gives the same rank to rows with equal values. It then continues with the next number without skipping, keeping the ranking sequence continuous.
DENSE_RANK() works like RANK(), but it ensures the ranking sequence has no gaps.

ROW NUMBER() Function gives a unique number to each row in the result set. It increments by 1 for every row, even if values are the same, so no two rows have the same number.

Example: Using ROW_NUMBER(), we will assign a unique number to each row based on years_of_experience order.

--Rownumber()
SELECT doctor_name, doctor_gender, doctor_specialty, doctor_experience_years,
       ROW_NUMBER() OVER (
           PARTITION BY doctor_specialty
           ORDER BY doctor_experience_years DESC
       ) AS experience_row_no
FROM doctors;

output:

PERCENT RANK() shows where a row stands compared to others in the same group.

SQL Joins

SQL Joins are used to combine data from two or more tables based on a related column.

• Matching records using common columns.
• Improving data analysis by combining related information.
• Creating meaningful result sets from separate tables.z

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 of INNER JOIN:

To find doctors with patients with follow up needs.

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

output:

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;

In this example, the LEFT JOIN retrieves all rows from the patients table and the matching rows from the visits table based on the patient_id column.

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;

output:

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:

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.

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

Example:

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;

Full join combines the contents of both the procedures table as well as the visits table connecting the two tables with a common column procedure_id.

output:

Self Join

Self-join allows us to join a table itself. It is useful when a user wants to compare the data (rows) within the same table.

Cross Join

Cross join allows us to join each and every row of both the tables. It is similar to the cartesian product that joins all the rows

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:

Performing recursive operations, such as traversing hierarchical data.
Breaking down multi-step calculations into manageable parts.
Replacing nested subqueries in complex data retrieval tasks.
Consider our visits table containing patient visits details such as visit_id, patient_id, diagnosis_id, total_bill_kes and patient_satisfaction_score. We will use this table to demonstrate Common Table Expression (CTE) examples.

Example: Calculate Average bill by patient admission type
This example uses a CTE to find the average bill of the different admission types the patients were under, making the query simpler and easier to read.

Syntax:

WITH cte_name AS (
    SELECT query
)
SELECT *
FROM cte_name;

cte_name: A unique name for the CTE expression.
query: A valid SQL query that returns a result set, which will be treated as a virtual table within the main query.
SELECT: The main query that can reference the CTE by its name.

Query:

WITH avg_total_bill_kes AS (
    SELECT admission_type, AVG(total_bill_kes) AS avg_bill
    FROM visits
    GROUP BY admission_type
)
SELECT *
FROM avg_total_bill_kes;

Output:

The WITH clause defines a CTE named avg_total_bill_kes.
The main query references this CTE to retrieve the average bill for each admission type.

Recursive Common Table Expression

A recursive CTE references itself to retrieve hierarchical data, such as employee-manager relationships. Use MAXRECURSION to prevent infinite loops. It consist of two parts:

Anchor member: The initial query that selects the base case (e.g., top-level managers).
Recursive member: The query that references the CTE itself, pulling the next level of data.

Uses of CTEs

CTEs (Common Table Expressions) help simplify and organize complex SQL queries.

Breaks complex queries into small, reusable parts.
Makes queries easier to read and understand.
Helps work with hierarchical data using recursion.
Limitations
While useful, CTEs come with a few practical constraints.

Temporary: A CTE works only while the query runs, then it disappears.
Performance: On very large data, CTEs can be slower if reused many times.
Restrictions: Some databases limit using CTEs with INSERT, UPDATE or DELETE.

CTE vs. Subqueries

CTE Can be referenced multiple times while Subqueries are typically used once.
CTE Improves readability for complex queries. Subqueries can become difficult to read when nested.
CTEs are Optimized for multiple references. On the other hand, subqueries may be less efficient for repeated operations.

SQL Subquery

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.

Query:

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

output:

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.

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 patients
WHERE patient_age = (SELECT MAX(patient_age) FROM patients);

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

-- Multi-Row Subquery
SELECT * FROM patients
WHERE patient_id IN (SELECT patient_id FROM patients WHERE county = 'Nairobi');

output:

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.

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

output:

Fetching Data Using Subquery in WHERE Clause

This example uses a subquery in the WHERE clause. The inner query finds roll numbers of students in Section 'A' and the outer query displays their details from the Student table.

Scalar Subqueries

When a subquery returns only a single data value, which will be a single row with single column, then it is called a Scalar subquery.

Nested Subqueries

When there are SQL SELECT queries inside a Subquery then it can be called Nested Subqueries. There can be upto 32 levels of nested queries in a single Outer or Main SQL Query statement.

Query:

Select * from visits Where patient_id IN (Select patient_id from patients where patient_age > 
(Select patient_id from patients where patient_id=10));

output:

Benefits of Using Subqueries in SQL Server

Helps to avoid Code duplication

Subqueries are useful in avoiding Code duplication or not repeating complex logic in multiple places. This reduces the risk of errors and improves code maintainability.

Subqueries improve Code Readability

Subqueries can break large code and complex logic into easy to understand and more manageable code blocks.

Useful to perform Complex calculations or data manipulations

Subqueries can be used to perform complex calculations or logic by dividing a single statement query into subqueries to get data from different tables or calculating aggregate values based on dynamic business cases.

Helps increase Flexibility

Subqueries can be used to filter data dynamically based on the result from other queries, which provides greater flexibility.

Provides data retrieval from multiple tables
Subqueries are useful when we need to combine data from multiple tables in a single query.

Conclusion

Subqueries and functions are an important part of data analysis and querying. Here are a few thing I have taken note
While using Window Function Partition carefully: Without Partition by, the whole table is treated as one group.
Check Order by : It controls the calculation order in the window.
Optimize performance: Window functions can be slow on large datasets; use indexes if needed.

As for JOINS We can also write JOIN instead of INNER JOIN. JOIN is same as INNER JOIN. Same case for LEFT OUTER JOIN and RIGHT OUTER JOIN instead of LEFT JOIN or RIGHT JOIN, we can use JOIN, since they are the same.

Some databases such as MySQL does not support FULL OUTER JOIN. To get the same result, you can simulate it using a UNION of a LEFT JOIN and a RIGHT JOIN.

All in all there is no end to learning, everyday as I practice I learn something and or refine my skills. Which is excepted. Sometimes the weight of what lies ahead can be heavy but as long as I am building skills day by day that is what matters.