Mastering Advanced PostgreSQL: A Comprehensive Guide to Hospital Data Intelligence

Introduction

Modern healthcare systems generate vast amounts of relational data daily, from patient admissions and doctor shift schedules to treatment costs and departmental expenses. Transforming this raw data into meaningful clinical and financial insights requires more than basic SQL queries. Database administrators and data analysts must leverage advanced querying techniques to uncover patterns, improve operational efficiency, and support data-driven decision-making.

This article explores four essential PostgreSQL concepts: Window Functions, Joins, Common Table Expressions (CTEs), and Subqueries. Using a realistic Hospital Management Dataset, each concept is demonstrated through practical SQL examples that illustrate how to analyze healthcare data and extract actionable insights.Each concept is explained using practical scenarios based on the hospital management database, which contains the following tables: doctors, department, diagnosis, wards, patients, procedures, and visits.

SQL Window Functions

SQL window functions perform calculations across a set of table rows that are related to the current row, without collapsing the result set into a single summary row. Unlike standard GROUP BY operations which merge rows to give you a single total, window functions preserve the identity of each individual row while appending the calculated aggregate or analytical value right next to it.

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

These elements define how a window function evaluates rows.

The OVER() Clause

The OVER() clause specifies the window of rows used by a window function. Depending on the function and query, it can contain a partition, an ordering, and a window frame.

Result:

What it does: Calculates the average bill across all visits and displays that same average alongside every visit.
Why it is useful: Analysts can compare individual bills with the overall hospital average without losing the individual visit records.

The PARTITION BY Clause

PARTITION BY divides rows into groups for a window calculation without collapsing them into summary rows.

Result:

What it does: Calculates the average bill separately for each patient.
Result: Each visit remains in the output, with an additional column showing that patient's average bill across their recorded visits.
If you had used a standard GROUP BY patient_id, PostgreSQL would have collapsed your rows down to just one row per patient, losing the individual visit_id and total_bill_kes details.

The ORDER BY Clause

Within OVER(), ORDER BY determines the sequence in which rows are evaluated for functions such as ROW_NUMBER(), LAG(), and running totals.

Result:

What it does: Numbers each patient's visits from earliest to latest. visit_id provides a tie-breaker when visits have the same date.
Result: Each patient's first recorded visit receives number 1, the second receives number 2, and so on.

Window Frames

A window frame determines which rows SQL should include when performing a calculation for the current row. It is commonly used with functions such as SUM() and AVG() to calculate running totals and moving averages.
Think of it this way: as SQL moves through hospital visits one row at a time, a window frame tells it which rows to include in the calculation for each visit.
Example: Calculating running hospital revenue

For each row, SQL adds the current bill to all the bills before it.

• ROWS specifies that the frame is based on rows.
• UNBOUNDED PRECEDING means start from the first row.
• CURRENT ROW means stop at the current row. Therefore, the first row includes only its own bill, the second includes the first and second bills, and the third includes all three bills. In summary: A window frame tells SQL how far back or forward to look when calculating a value for each row. It is particularly useful for running totals, moving averages, and calculations involving a specific number of preceding or following records.

Ranking window functions

Ranking functions assign positions to rows according to an ordering criterion. They are useful for identifying the most experienced doctors, highest-cost visits, or patients with the most visits.

ROW_NUMBER()

Purpose: Assigns a unique sequential number to each row within a partition.
Hospital scenario: Number each patient's visits in chronological order.

Result:

Result: A patient with four visits receives sequence numbers 1, 2, 3, and 4. Numbering restarts at 1 for the next patient.
Even when two visits share the same date, visit_id helps establish a consistent order.

RANK()

Purpose: Assigns the same rank to tied values but skips subsequent rank numbers.
Hospital scenario: Rank doctors by years of experience.

Result:

Result: If the highest experience values are 20, 15, 15, and 10 years, their ranks are 1, 2, 2, and 4.
This function is useful when tied doctors should share the same competitive position.

DENSE_RANK()

Purpose: Assigns equal ranks to ties without leaving gaps.
Difference between RANK() and DENSE_RANK(): RANK() skips numbers after ties, while DENSE_RANK() continues with the next consecutive rank

NTILE()

Purpose: Divides ordered rows into a specified number of approximately equal-sized groups.
Hospital scenario: Segment visits into four billing tiers.

Result:

Result: Visits are distributed among four groups. Group 1 contains the highest bills, while group 4 contains the lowest bills, assuming descending order.
This can help the hospital identify visits with comparatively high billing amounts.
NTILE(4) creates four groups of roughly equal numbers of rows, not four groups with equal billing values.

Value Window Functions

Value functions retrieve values from particular rows within a window. They are especially useful for analyzing changes in patients' visits and identifying first or last recorded events.

LAG()

Purpose: Retrieves a value from a preceding row without requiring a self-join.
Hospital scenario: Compare each patient's current bill with their previous visit bill.

Result:

Result: The first visit for each patient has NULL in previous_visit_bill because no earlier visit exists. Subsequent visits show the preceding bill.
For example, patient 1 has bills of KES 3,500 and KES 4,200, so the previous-bill values are NULL and 3,500.

LEAD()

Purpose: Retrieves a value from a following row in the window.
Hospital scenario: Examine the bill recorded for a patient's next visit.

Result:

Result: Each visit is displayed alongside the next recorded visit's bill for that patient. The last visit has NULL because no later visit exists.
Difference between LAG() and LEAD(): LAG() looks backward; LEAD() looks forward.

FIRST_VALUE()

Purpose: Returns the value from the first row in the window frame.
Hospital scenario: Display the bill from each patient's earliest visit alongside every visit.

Result:

LAST_VALUE()

Purpose: Returns the value from the last row in the window frame.
Hospital scenario: Display the bill from each patient's latest recorded visit alongside all their visits.

Result: Every visit for a patient displays the bill from that patient's latest recorded visit.
Important: LAST_VALUE() can be confusing because its result depends on the window frame. With an ordered default frame, it may return the current row's value or the last value among its peers rather than the final value in the entire partition. Specifying UNBOUNDED FOLLOWING ensures that the frame extends to the partition's final row.

Aggregate Window Functions

Aggregate window functions calculate summaries while preserving individual rows. They can support hospital revenue analysis, cost monitoring, and patient utilization reporting.

SUM()

Purpose: Adds values within a window.
Hospital scenario: Calculate the cumulative billing revenue generated by visits.

Result:

As shown in the results, starting with visit 1,001 (3,500 KES), the cumulative revenue accumulates chronologically row by row—adding visit 1,002 (2,500 KES) to reach 6,000 KES, visit 1,003 (28,000 KES) to reach 34,000 KES, and so on,allowing hospital management to track financial growth alongside individual visit bills.

AVG()

Purpose: Calculates the average of values within a window.
Hospital scenario: Compare every visit's bill with the average bill for that patient's visits.

Result:

As shown in the results, since Patient 1 has bills of 3,500 and 4,200, the average amount (3,850) is displayed across both of their rows, allowing hospital staff to track individual visit bills alongside each patient's average treatment cost.

COUNT()

Purpose: Counts rows or non-NULL values within a window.
Hospital scenario: Count the recorded visits for each patient.

Result:

As shown in the results, since Patient 1 has two recorded visits (1,001 and 1,011), the total count of 2 is repeated next to both rows, allowing hospital staff to view granular visit logs alongside lifetime patient visit frequency.

COUNT(*) counts rows, including rows containing NULL in individual columns. By contrast, COUNT(column_name) counts only rows where that column is not NULL.

MIN()

Purpose: Returns the minimum value in the window.
Hospital scenario: Identify the lowest recorded bill for each patient.

Result:

As shown in the results, Patient 1 has bills of 3,500 and 4,200, so the lowest amount (3,500) is displayed across both of their rows, allowing hospital staff to track individual bills alongside each patient's lowest overall cost.

MAX()

Purpose: Returns the maximum value in the window.
Hospital scenario: Identify the highest recorded bill for each patient.

Result:

As shown in the results, since Patient 1 has bills of 3,500 and 4,200, the maximum amount (4,200) is displayed across both of their rows, allowing hospital staff to track individual visit bills alongside each patient's peak treatment cost.

SQL Joins

SQL joins combine related data from two or more tables using a specified condition. Hospital databases commonly store patient details, doctors, departments, and visits separately to reduce duplication and maintain data consistency.
For example, the visits table contains identifiers such as patient_id, doctor_id, and department_id. Joins allow an analyst to retrieve meaningful names and department information instead of working only with identifiers.

INNER JOIN

Purpose: Returns only rows with matching values in both joined tables.
Hospital scenario: Retrieve visits with the corresponding patient details.

Result:

What it does: Matches every visit to the patient with the same patient_id.
Result: Visits with matching patient records appear in the output. Visits without a matching patient record are excluded.
This is useful when an analysis requires complete matches between the tables.

LEFT JOIN

Purpose: Returns every row from the left table and matching rows from the right table. Where no match exists, right-table columns contain NULL.
Hospital scenario: Find all registered patients, including those who have never visited.

Result:

Result: All patients are retained. Patients without visits have NULL for visit_id and visit_date.
This is useful for identifying patients with no recorded visits and checking the completeness of hospital records.

RIGHT JOIN

Purpose: Returns every row from the right table and matching rows from the left table.
Hospital scenario: Retain every visit record, including visits without a matching patient record.

Result:

Result: Every row from visits appears. Where a visit has no matching patient record, the patient columns contain NULL.
This can help audit inconsistent records, although a properly enforced foreign key should ordinarily prevent such orphaned visits.
A RIGHT JOIN can often be rewritten as a LEFT JOIN by reversing the table order.

FULL OUTER JOIN

Purpose: Returns all matching rows plus unmatched rows from both tables.
Hospital scenario: Audit patient records and visit records to identify unmatched entries on either side.

Result:

This is useful for data-quality investigations and reconciliation.

CROSS JOIN

Purpose: Returns every possible combination of rows from two tables, producing a Cartesian product.
Hospital scenario: Generate all possible combinations of departments and wards for planning or review.

Result:

This query performs a CROSS JOIN between the department and wards tables, producing a Cartesian product where every single department is paired with every available ward. As shown in the results, each of the four departments (Cardiology, General Medicine, Orthopedics, and Pediatrics) is combined with all four ward IDs (100, 200, 300, and 400), creating 16 total combinations that allow hospital planners to evaluate every possible department-to-ward pairing.
Caution: A cross join can produce a very large result when the tables contain many rows.

SELF JOIN

Purpose: Joins a table to itself using different aliases.
Hospital scenario: Find pairs of doctors with the same number of years of experience.

The query performs a SELF JOIN on the doctors table by aliasing it as doc1 and doc2, attempting to pair doctors who share the exact same number of experience years while eliminating duplicate reverse pairs using doc1.doctor_id < doc2.doctor_id. As shown in the results, because all four doctors in the dataset currently possess unique experience years (12, 9, 15, and 8 yearsrespectively), no records satisfy the matching condition, resulting in an empty result set with zero rows returned.

Common Table Expressions (CTEs)

What is a CTE?

A Common Table Expression (CTE) is a named result set defined using the WITH clause. It exists for the duration of the SQL statement and can make complex queries easier to understand, organize, and maintain.
In hospital analytics, CTEs are useful for calculating department revenue, identifying high-cost visits, summarizing patient activity, and breaking complex reports into smaller logical steps.

Basic CTEs

Purpose: Give a query result a temporary name so it can be referenced by the main query.
Hospital scenario: Identify visits with bills exceeding KES 10,000.

What it does: The CTE selects visits whose total bill exceeds KES 10,000. The main query retrieves those records from the named result set.
The CTE is not a permanent table. It is defined within this statement and can be referenced by the statement that follows it.

Multiple CTEs

Purpose: Break a complicated analysis into multiple named steps.
Hospital scenario: Calculate total billing revenue for each department and return departments with revenue above KES 10,000.

Result:

How it works:

1. department_visits joins department details to individual visit billing records.
2. department_revenue groups the records by department and calculates total revenue.
3. The final query filters the summary to departments exceeding KES 10,000 and sorts them by revenue. Result: A list of qualifying departments, with their total billing revenue ordered from highest to lowest. This structure is easier to maintain than placing every operation in one deeply nested query.

Recursive CTEs

Purpose: A recursive CTE repeatedly references its own result to process hierarchical data or generate sequences.
Hospital scenario:Generate a calendar containing every date between the hospital’s earliest and latest recorded visits. This helps identify days when patients visited the hospital and days when no visits were recorded

CTEs combined with joins and aggregations

CTEs can work with joins and aggregate functions to produce multi-stage hospital reports.
Hospital scenario: Count visits and calculate total billing revenue for each department.

Result:

What it does: The CTE summarizes visit activity for each department. The outer query joins those summaries to the department table.
Expected result: Every department is listed, including departments without visits. Departments without visits display zero for total_visits and NULL for the revenue and average-bill fields.
COALESCE() replaces a NULL value with a specified alternative; here, it ensures that departments without recorded visits display zero visits.

SQL Subqueries

What is a subquery?

A subquery is a query embedded within another SQL statement. It can appear in the SELECT, FROM, or WHERE clause and can return a single value, a single column of values, or a table-like result.
In hospital analytics, subqueries can identify patients with emergency visits, doctors with above-average workloads, and visits whose bills exceed specified benchmarks.

Scalar Subqueries

Purpose: Return a single value: one row and one column.
Hospital scenario: Display each visit alongside the average bill across all visits.

The inner query calculates the hospital-wide average bill. The outer query displays that value alongside each individual visit.
Result: Each visit retains its own bill and has an additional column containing the overall average.
A scalar subquery that returns more than one row causes an error when used in a scalar context.

Non-correlated Subqueries

Run independently of the outer query; they do not reference columns from that query.
Hospital scenario: Identify visits whose bills exceed the hospital-wide average.

How it works: The inner query calculates the average once conceptually as an independent expression. The outer query compares each visit's bill with that average.
Result: Only visits with bills greater than the overall average appear.

Correlated Subqueries

Purpose: Reference a column from the outer query, making the inner query dependent on the current outer row.
Hospital scenario: Count the total recorded visits for each doctor.

What it does: For each doctor, the subquery counts records in visits that have the same doctor_id.
Result: Every doctor appears with their recorded visit count. Doctors without visits receive a count of zero.
The reference d.doctor_id connects the inner query to the current doctor in the outer query. This is what makes the subquery correlated.

Nested Subqueries

Purpose: Place a subquery inside another subquery to perform multiple levels of filtering or calculation.
Hospital scenario: Find visits whose bills exceed the average bill for visits belonging to patients who have had at least two recorded visits.

How it works:

1. The innermost query groups visits by patient and identifies patients with at least two visits.
2. The middle query calculates the average bill across visits belonging to those patients.
3. The outer query returns visits whose bills exceed that calculated average. Result: A list of visits with bills above the average for the specified group of patients. This query demonstrates nested filtering, although a CTE might make the same logic easier to read in a larger reporting task.

Subqueries using IN

Purpose: Test whether a value matches any value returned by a subquery.
Hospital scenario: Find patients who have had an emergency admission.

What it does: The inner query identifies patients with an emergency visit. The outer query retrieves their demographic information.
Result: Patients with at least one matching emergency visit are returned. A patient with multiple emergency visits still appears only once in this outer query because the outer table is patients.

Subqueries using EXISTS

Purpose: Test whether a subquery returns at least one row.
Hospital scenario: Find patients who have at least one recorded visit.

What it does: For each patient, the subquery checks whether a matching visit exists.
Result: Only patients with at least one recorded visit are returned.

Subqueries using ANY

Purpose: Compare a value with the results of a subquery and return true if the comparison is true for at least one returned value.
Hospital scenario: Find visits whose bills exceed at least one bill recorded for a particular patient, here patient ID 1.

What it does: Returns visits whose bills are greater than at least one non-NULL bill belonging to patient 1.
Result: A visit qualifies if its bill exceeds at least one bill in the comparison set.
For example, if patient 1 has bills of KES 2,000 and KES 5,000, a bill of KES 3,000 satisfies > ANY because it exceeds KES 2,000, even though it does not exceed KES 5,000.
If the subquery returns no rows, the ANY comparison is false.

Subqueries using ALL

Purpose: Compare a value with every value returned by a subquery.
Hospital scenario: Find visits whose bills exceed every non-NULL bill recorded for patient 1.

What it does: Returns visits whose bills are greater than the highest bill in the comparison set, provided the set is nonempty.
Result: If patient 1's bills are KES 2,000 and KES 5,000, a visit with a bill of KES 6,000 qualifies, but a bill of KES 3,000 does not.
If the subquery returns no rows, the ALL comparison is true by definition. This is an important difference to consider when writing filters.

Subqueries in SELECT, FROM, and WHERE

Subqueries can be positioned in different parts of a SQL statement depending on the type of result.

Subquery in SELECT

A scalar subquery can provide an additional calculated column.

Subquery in FROM

A subquery in the FROM clause creates a derived table that the outer query can process.
Hospital scenario: Calculate each patient's total billing, then find the average total billing per patient.

What it does: The inner query calculates total bills for each patient. The outer query averages those patient-level totals.
Result: A single value representing the average total billing per patient among patients represented in the derived table.
This differs from the average individual visit bill because each patient is first represented by their cumulative billing total.

Subquery in WHERE

A subquery can filter records based on a comparison or membership condition.

Return visits meeting a condition derived from another query, such as exceeding the overall average bill.

Conclusion

Advanced PostgreSQL techniques enable healthcare analysts to transform relational data into meaningful operational and financial insights.
Window functions support ranking, comparisons, and cumulative calculations while preserving individual records.
Joins combine related information across tables and help identify unmatched records.
CTEs organize complex queries into manageable stages and support recursive processing. Subqueries allow filtering and calculations based on the results of other queries.
The most effective SQL solution depends on the question being asked, the structure of the data, and the expected output. Understanding not only how to write a query but also why it works is essential for producing reliable and useful analysis.