Mastering SQL: Window Functions, Joins, Common Table Expressions (CTEs), and Subqueries Using a Hospital Database

Introduction

Structured Query Language (SQL) is a standard programming language used to store, retrieve, manipulate, and analyze data in relational databases.
In a hospital environment, SQL helps manage patient records, appointments, doctors, departments, medical bills, and payments. But, as hospital databases grow, basic queries using SELECT, WHERE, and ORDER BY may not be sufficient for complex analytical tasks.
Some example are, a hospital administrator may need to

• Rank doctors by revenue.

• Identify patients with outstanding balances.

• Determine which departments have the highest number of appointments.

• Compare monthly payments.

SQL provides several advanced techniques for solving these problems:

1. Window functions perform calculations across related rows without combining them into a single result row.

2. SQL joins combine information from two or more tables using related columns.

3. Common Table Expressions (CTEs) create named query results that simplify complex SQL statements.

4. Subqueries allow one SQL query to be embedded inside another query.

This article explains these concepts, their different types, their practical applications, and their differences using a hospital database in PostgreSQL.

SQL Window Functions

What are window functions?

SQL window functions (also known as analytic functions) perform calculations across a set of table rows that are related to the current row, without collapsing the result set into a single output row. This set of rows is called a window.

Unlike a standard GROUP BY clause, which compresses your rows to show only the summaries, window functions preserve the original granularity of your data. Every single record remains visible, and the calculated value appears as a new column alongside your existing details

Window functions are useful for:

• Ranking doctors by revenue.
• Finding the previous or next patient payment.
• Calculating running totals.
• Comparing current payments with earlier payments.
• Identifying the highest and lowest payments.
• Dividing patients into analytical groups.

The general syntax is:

SELECT column_name,
 window_function() OVER ( 
PARTITION BY grouping_column 
ORDER BY sorting_column 
) AS result 
FROM table_name;

The OVER() clause defines the window over which the function operates.

Understanding OVER(), PARTITION BY, ORDER BY, and window frames

These components determine how a window function processes data.

A. OVER()

The OVER() clause in SQL defines a "window" of rows around the current row to perform a calculation. It turns standard functions (like SUM, AVG, or ROW_NUMBER) into window functions, allowing you to calculate aggregates or rankings without losing your underlying row-level data

B. PARTITION BY

In SQL, the PARTITION BY clause is a sub-clause used inside window functions (via the OVER() clause) to divide a query's result set into smaller, isolated groups—called partitions.

The defining characteristic of PARTITION BY is that it performs group-level calculations while keeping every individual row intact. Calculations (like sums, averages, or rankings) are calculated independently within each partition and reset when moving to the next group.

C. ORDER BY

The SQL ORDER BY clause is used to sort the result set of a query in either ascending or descending order. Without it, a database does not guarantee the order of the returned rows, meaning they can appear random or change between query runs

1. Basic Syntax

The ORDER BY clause always goes at the very end of your SQL query (or right before a LIMIT / TOP clause if you are restricting rows).

SELECT column1, column2
FROM table_name
ORDER BY column_name [ASC | DESC];

• ASC (Ascending): Sorts data from smallest to largest (A to Z, 0 to 9, oldest date to newest). This is the default setting, so you do not have to explicitly type it.
• DESC(Descending): Sorts data from largest to smallest (Z to A, 9 to 0, newest date to oldest).

2. Order of Execution

It helps to know that SQL doesn't read your query from top to bottom. The database processes ORDER BY near the very end of its execution cycle:

SQL EXECUTION ORDER
 /* 
 * 1.FROM
 * 2.WHERE
 * 3.GROUP BY
 * 4.HAVING
 * 5.SELECT
 * 6.ORDER BY
 * 7.LIMIT
 */

Because it runs after the SELECTstatement, you can actually sort your data using column aliases that you created inside your SELECTclause.

D. Window frames

A window frame in SQL is a sub-selection of rows within a partition that determines exactly which records are included in a window function's calculation for the current row.

While PARTITION BY groups the data and ORDER BY sorts it, the window frame clause defines a sliding or fixed boundary around the current row. As SQL evaluates the query row-by-row, this "frame" shifts to look backward, forward, or at a fixed range of data.

1. The Anatomy of a Window Frame

A window frame is written at the very end of the OVER() clause using this basic syntax:

<window_function>() OVER (
    PARTITION BY column1
    ORDER BY column2
    [ROWS|RANGE|GROUPS] BETWEEN <start_boundary> AND <end_boundary>
)

The Boundaries

To set the frame, you mix and match these five boundary markers:
• UNBOUNDED PRECEDING: The very first row of the partition.
• N PRECEDING: N rows before the current row.
• CURRENT ROW: The row currently being processed.
• N FOLLOWING: N rows after the current row.
• UNBOUNDED FOLLOWING: The very last row of the partition.

The main frame options are:

• ROWS: selects rows according to their physical positions.
• RANGE: selects rows based on ordering values and their peers.
• GROUPS: selects groups of rows with equal ordering values.

1. ROWS vs. RANGE vs. GROUPS

How SQL counts its boundaries depends on the modifier you choose

Modifier	Based On	Best Used For
ROWS	Physical row position. It counts individual rows regardless of whether they contain identical values.	Moving averages, such as calculating the average of the last three physical transactions.
RANGE	Logical values in the ORDER BY column. Rows with identical ordering values are treated as peers.	Time-series analysis, such as calculating values within a specified range of dates, regardless of the number of transactions.
GROUPS	Groups of rows with identical ordering values. Each unique group is treated as a single step.	Analyzing grouped data, particularly when handling tied values or transactions with identical ordering values.

Types of SQL window functions

A. Ranking Window Functions
These functions assign a sequential integer or statistical rank to each row within a designated partition based on a specific order.

1. ROW_NUMBER() - Assigns a unique, sequential integer to rows starting at 1. Never duplicates numbers if values match

2. RANK() - : Assigns ranks based on values. If duplicate values exist, they receive the same rank, and gaps are skipped for the next rank numbers (e.g., 1, 2, 2, 4).

3. DENSE_RANK() - Similar to RANK(), but it does not skip any numbers when there are duplicates

4. NTILE(N) - Divides an ordered partition into N roughly equal buckets or groups and assigns the bucket number to each row.

5. PERCENT_RANK() - Computes the relative rank of a row as a percentage value between 0 and 1.

B. Value / Offset Window Functions

Value (or offset) functions allow you to access data from a different row relative to the current row without using complex self-joins.
• LAG(): Accesses data from a previous row in the same result set, useful for calculating period-over-period growth.

• LEAD(): Accesses data from a subsequent row in the same result set.

• FIRST_VALUE(): Returns the first value in an ordered window frame.

• LAST_VALUE(): Returns the last value in an ordered window frame (note: usually requires altering the default window frame to look past the current row).

• NTH_VALUE(): Retrieves the value from the specific (N^{th}) row of the window frame.

C. Aggregate Window Functions

These are standard SQL aggregate functions applied using the OVER() clause. Instead of reducing the rows, they return a cumulative or moving calculation for each row.

• SUM(): Computes a cumulative running total when paired with an ORDER BY clause.

• AVG(): Calculates moving or partitioned averages.

• COUNT(): Calculates running or partitioned counts of rows.

• MIN() / MAX(): Identifies the lowest or highest value up to the current row or within the partition.

SQL Joins

What are SQL joins?

An SQL JOIN clause is used to combine rows from two or more tables based on a related column between them. It is fundamental to relational databases, allowing you to reassemble data that has been split up to avoid duplication.

A. INNER JOIN

The most common join type. It filters out any rows that do not strictly find a match in the opposing table. An INNER JOIN is useful when a report must include only records with valid matches in both tables.

Standard Syntax

SELECT table1.column_name, table2.column_name
FROM table1
INNER JOIN table2
ON table1.common_column = table2.common_column;

• INNER JOIN: Specifies the second table you want to merge.
• ON: Defines the condition or rule for matching rows, typically mapping a Primary Key from one table to a Foreign Key in another.

B. LEFT JOIN (or LEFT OUTER JOIN)

In SQL, a LEFT JOIN (or LEFT OUTER JOIN) returns all rows from the left table and the matching rows from the right table. If no match is found for a row in the right table, the database outputs NULL values for the right table's columns.

Standard Syntax

SELECT columns
FROM table_left
LEFT JOIN table_right 
  ON table_left.common_column = table_right.common_column;

When no match exists, columns from the right table contain NULL.

C. RIGHT JOIN

In SQL, a RIGHT JOIN (or RIGHT OUTER JOIN) is used to combine rows from two tables, returning all records from the right table and the matched records from the left table. If there is no match for a row from the right table, the result will contain NULL values for the columns of the left table.

Standard Syntax

SELECT columns
FROM table_left
RIGHT JOIN table_right 
ON table_left.common_column = table_right.common_column;

D. FULL OUTER JOIN

In SQL, a FULL OUTER JOIN (or simply FULL JOIN) returns all records from both tables, regardless of whether they have a matching row in the other table.

It essentially combines the functionality of a LEFT JOIN and a RIGHT JOIN. When a match is found based on the join condition, the rows are merged. If there is no match, the missing side is filled with NULLvalues

Basic Syntax

SELECT table1.column1, table2.column2
FROM table1
FULL OUTER JOIN table2 
ON table1.common_column = table2.common_column;

E. CROSS JOIN

An SQL CROSS JOIN combines every row from the first table with every row from the second table. It creates a Cartesian product of the two tables, meaning it generates all possible combinations of your data.
Unlike other joins (like INNER JOIN or LEFT JOIN), a CROSS JOIN does not require a join condition (no ON clause) because it is explicitly pairing everything together

Basic Syntax

You can write a cross join explicitly using the CROSS JOIN keyword, or implicitly using a comma.

SELECT columns
FROM table1
CROSS JOIN table2;

F. SELF JOIN

An SQL Self Join is a regular join where a table is joined with itself. There is no specific SELF JOIN keyword; instead, you reference the same table twice and use table aliases to give them distinct names. This tricks SQL into treating them as two separate tables so you can compare rows within the same dataset.

Basic Syntax

SELECT 
    t1.column_name, 
    t2.column_name
FROM 
    your_table AS t1
INNER JOIN 
    your_table AS t2 
ON 
    t1.common_column = t2.related_column;

A self-join is useful for hierarchical data, referral relationships, and comparing records within the same table.

The appropriate join depends on which records must remain in the output and whether unmatched records are important to the analysis.

Common Table Expressions (CTEs)

What is a CTE?

A Common Table Expression (CTE) is a temporary, named result set in SQL that you can reference within a single SELECT, INSERT, UPDATE, or DELETEstatement. Defined using the WITH keyword, CTEs act like virtual, query-scoped tables that disappear as soon as the query finishes executing. They are widely preferred over traditional subqueries because they heavily improve query readability, modularity, and code organization.

CTEs make complex queries easier to read by breaking them into logical steps.

The basic syntax is:

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

A CTE is not automatically a permanent table. It does not create a persistent database object.

Multiple CTE

Multiple CTEs allow a query to be divided into several named stages. Multiple CTEs are useful when each calculation depends on a previous step. They make a long query easier to maintain and troubleshoot.

SYNTAX STRUCTURE

WITH first_cte AS (
    SELECT department_id, AVG(salary) AS avg_sal
    FROM employees
    GROUP BY department_id
), -- <-- Separated by a comma

second_cte AS (
    SELECT department_id
    FROM first_cte -- <-- Can reference the preceding CTE!
    WHERE avg_sal > 75000
) -- <-- No comma before the main query

-- The final main statement (SELECT, INSERT, UPDATE, or DELETE)
SELECT e.employee_id, e.first_name, e.salary, e.department_id
FROM employees e
JOIN second_cte s ON e.department_id = s.department_id;

The Rules of Multiple CTEs

1. Single WITH Clause: You only write WITH before the first CTE. Do not repeat it for subsequent ones.
2. Comma-Separated: Place a comma between the closing parenthesis of one CTE and the name of the next. 3.** No Trailing Comma**: Do not put a comma between your final CTE definition and the main query.
3. Order Matters (No Forward References): A CTE can reference any CTE defined before it, but it cannot reference a CTE defined after it.

Recursive CTEs

A recursive CTE references its own result to process hierarchical or sequential data.

It typically contains:

• An anchor query that identifies the starting rows.
• A recursive query that finds the next related rows.
• A UNION ALLcombining the anchor and recursive results.

Structural Anatomy of a Recursive CTE

A recursive CTE always consists of three fundamental components combined using the UNION ALL operator:

1. The Anchor Member (Base Query): The initial query that returns the starting rows of the recursion (e.g., the top-level boss or the number 1).

2. The Recursive Member: The query that references the CTE itself. It joins the table back to the previous iteration's results to find the next level of data.

3. The Termination Condition: A condition (usually a WHEREclause) in the recursive member that stops the query from executing infinitely.

Basic Syntax

WITH RECURSIVE cte_name AS (
    -- 1. Anchor Member
    SELECT columns FROM table WHERE condition 

    UNION ALL

    -- 2. Recursive Member + 3. Termination Condition
    SELECT t.columns 
    FROM table t
    INNER JOIN cte_name c ON t.parent_id = c.id
    WHERE [termination_condition]
)
SELECT * FROM cte_name;

The main advantage of a CTE is often clarity rather than guaranteed performance improvement.

SQL Subqueries

What is a subquery?

An SQL subquery is a SELECTstatement nested inside another SQL statement and enclosed in parentheses (). The outer statement (often called the main query) consumes the results generated by the inner subquery to perform filtering, calculations, or data transformations.

Subqueries can be nested inside SELECT, INSERT, UPDATE, or DELETE statements, and they can reside within clauses like WHERE, FROM, SELECT, and HAVING.

Types of Subqueries by Result Shape

The SQL optimizer categorizes subqueries based on the structural format of the data they return:

• Scalar Subquery: Returns exactly one row and one column (a single value). It can step in anywhere a standard literal value or column expression is used.

• Multi-Row (Column) Subquery: Returns a single column containing multiple rows of values. This type requires set membership operators like IN, ANY, or ALL in the outer query.

• Table Subquery (Derived Table): Returns multiple columns and multiple rows. It acts as a temporary in-memory table and is primarily utilized within the FROM clause.

• Non-correlated subqueries:A non-correlated subquery does not refer to columns from the outer query. It can be evaluated independently of the outer query's individual rows.

• Correlated subqueries:A correlated subquery references a column from the outer query. Its result depends on the current outer row. A correlated subquery is useful when a condition must be evaluated relative to each outer record. Depending on the query and indexes, PostgreSQL may optimize it into an efficient execution plan; correlation does not automatically mean the query will be slow.

• Nested subqueries:A nested subquery contains another subquery inside it. Nested subqueries are useful for multi-level comparisons, although a CTE may make the same logic easier to read.

• Subqueries using IN: In SQL, a subquery using the IN operator allows you to filter the results of an outer query based on a dynamic list of values returned by an inner query. It acts as a powerful, flexible alternative to writing multiple OR conditions

Basic syntax

SELECT column_name(s)
FROM table_1
WHERE column_to_check IN (
    SELECT matching_column 
    FROM table_2 
    WHERE condition
);

• Subqueries using EXISTS: In SQL, the EXISTS operator is a boolean condition used within a WHERE clause to check whether a subquery returns any rows at all. It evaluates to TRUE if the subquery returns at least one row, and FALSE if it returns no rows. Because EXISTSonly looks for the presence of a matching row, the database stops processing the subquery the exact moment it finds its first match. This makes it highly efficient, particularly when filtering large datasets

Basic Syntax

SELECT column_names
FROM table_1 AS t1
WHERE EXISTS (
    SELECT 1 
    FROM table_2 AS t2
    WHERE t2.id = t1.id
);

• Subqueries using ANY: The SQL ANY operator is used in a WHERE or HAVING clause to compare a single value against a set of values returned by a multi-row subquery. The condition evaluates to TRUE if the comparison holds true for at least one value in the subquery's result set. In most SQL dialects (like MySQL, PostgreSQL, SQL Server, and Oracle), SOME is a direct alias for ANY, meaning they function exactly the same way.

Basic Syntax

SELECT column_names
FROM table_name
WHERE column_name comparison_operator ANY (subquery);

The comparison operator must be a standard operator: =, >, <, >=, <=, <>, or !=.

• Subqueries using ALL: In SQL, the ALL operator is used to compare a single scalar value against every single value returned by a multi-row subquery. A condition using ALL evaluates to TRUE only if the comparison holds true for all records in the subquery result set. It must always be preceded by a standard comparison operator (like >, <, =, >=, <=, <>).

Syntax

SELECT column_names
FROM table_name
WHERE column_name comparison_operator ALL (subquery);

-Subqueries in the SELECT clause:A subquery in the SELECT clause (also known as a scalar subquery or a projection subquery) allows you to generate a dynamic, calculated column for every row returned by the outer query.
Instead of just pulling a standard column from a table, you insert an entirely separate SELECT statement directly into your main column list.

The Golden Rule

A subquery placed in the SELECT clause must return exactly one value (one row and one column) per outer row. If it returns multiple columns or multiple rows, the database engine will throw a syntax runtime error. If it returns no rows, it evaluates to NULL.

-Subqueries in the FROM clause:In SQL, a subquery in the FROM clause acts as a temporary, virtual table that the main query can use to select, filter, or join data. This is often called a derived table or an inline view.

The Two Golden Rules

When placing a nested SELECT statement inside your FROM clause, you must follow these two syntax rules:

1. Enclose it in parentheses: The entire subquery must live inside (...).
2. Assign an Alias: You must give the subquery a name (an alias) using the AS keyword (though AS is optional in some SQL dialects, the alias name itself is mandatory).

Syntax Structure

SELECT 
    derived_table.column1, 
    derived_table.column2
FROM (
    -- The Inner Subquery
    SELECT column1, column2 
    FROM original_table
    WHERE condition
) AS derived_table; -- Mandatory Alias

• Subqueries in the WHERE clause: A subquery in a WHERE clause (also known as a nested query or inner query) is a SELECT statement embedded inside the filtering condition of an outer SQL query. It acts as a dynamic filter, calculating a temporary value or dataset that the outer query uses to evaluate rows. Depending on how many rows and columns the inner query returns, you must couple it with the appropriate SQL operator.

Best Practices When Using Advanced SQL

The following practices improve query accuracy, performance, and maintainability.

1. Understand the relationships between tables. Identify primary keys, foreign keys, and the correct join columns before writing a complex query.
2. Prevent duplicate counting. One-to-many joins can multiply rows. Aggregate at the appropriate level before joining when necessary.
3. Use meaningful aliases. Aliases such as p, pay, and doc make queries easier to read when used consistently.
4. Choose the correct window ordering. If multiple rows have the same date or value, include a unique secondary sort column when deterministic results are required.
5. Specify window frames explicitly when necessary. This is especially important for running totals and functions such as LAST_VALUE().
6. Handle NULL values. Remember that SUM() over no input rows can return NULL, and COUNT(column) ignores NULL values. Use COALESCE() when a zero is appropriate.
7. Choose joins based on reporting requirements. An inner join excludes unmatched records, while an outer join preserves them.
8. Use CTEs to improve readability. Break complex queries into understandable stages instead of building one unnecessarily complicated statement.
9. Check query performance. Use PostgreSQL's EXPLAIN and, where appropriate, EXPLAIN ANALYZE to understand execution plans and identify bottlenecks.
10. Protect patient information. Hospital databases contain confidential information. Access should be authorized, and reports should expose only the data necessary for their purpose.
11. Validate results against the source data. Compare totals, row counts, and sample records to ensure that a query has not excluded or duplicated information.
12. Use appropriate indexes. Indexes on frequently joined keys and filtered columns can improve performance, although their value depends on the query and the data.

Conclusion

Window functions, joins, Common Table Expressions, and subqueries are important SQL techniques for solving complex data problems.

Window functions allow analysts to rank records, compare payments over time, and calculate totals while preserving individual rows. Joins combine related information from different tables, enabling meaningful hospital reports. CTEs organize complex calculations into readable stages and support recursive analysis of hierarchical data. Subqueries provide a flexible way to filter, compare, and calculate values based on other query results.
In a hospital environment, these techniques can support patient payment analysis, appointment monitoring, doctor performance reporting, departmental planning, and financial reconciliation.

The most important skill is not memorizing SQL syntax. It is understanding the business question, identifying the correct relationships between tables, selecting the appropriate SQL technique, and verifying that the results accurately represent the underlying data.

By practising these techniques in PostgreSQL and applying them to a hospital dataset, SQL learners can develop the analytical skills needed to transform raw records into reliable and useful information for decision-making.

“The goal is to turn data into information, and information into insight.”
— Carly Fiorina, former CEO of Hewlett-Packard