SQL Window Functions, Joins, CTEs, and Subqueries in Hospital Data Analysis.

1. Introduction

Structured Query Language (SQL) is used to retrieve, manipulate, and analyze data stored in relational databases. In a hospital environment, SQL can help management monitor patient admissions, analyze treatment costs, compare departmental performance, identify patients with repeated visits, and evaluate healthcare service delivery.
Four important SQL concepts make these analyses more effective:

• Window functions calculate values across related rows without combining those rows into a single result.
• Joins combine information from two or more tables using related columns.
• Common Table Expressions (CTEs) organize complex queries into named, reusable steps within a single statement.
• Subqueries place one SQL query inside another to support filtering, calculations, and data retrieval. This article explains how these concepts work, why they are useful, and how they can be applied to practical hospital scenarios.

2. Example hospital database

The following examples assume a hospital database with three tables. Replace these names and columns with those in your actual class dataset where necessary.

Patients Stores patient details. patient_id · patient_name · gender · date_of_birth 

Departments Stores hospital department information. department_id · department_name 

Admissions Stores patient visits and admission information. admission_id · patient_id · department_id · admission_date · discharge_date · total_bill · status 

The relationships are:

• One patient can have multiple admissions.
• One department can handle multiple admissions.
• Each admission belongs to a patient and a department. The examples use PostgreSQL-compatible syntax. Some syntax, especially window-frame clauses and date functions, may differ in other database systems.

3. SQL Window Functions

3.1 What are window functions?
A window function performs a calculation across a set of rows related to the current row. Unlike GROUP BY, which combines rows into summary records, a window function preserves the individual rows while adding calculated values.
For example, a hospital manager may want to see each patient's admission bill alongside the average bill for that patient's department.
General syntax:

function_name(expression) OVER ( PARTITION BY column_name ORDER BY column_name ROWS BETWEEN ... ); 

Not every window function requires all these clauses. The OVER() clause defines the window of rows over which the calculation is performed.

3.2 Understanding OVER(), PARTITION BY, ORDER BY and window frames
A. OVER()
OVER() tells SQL to perform a calculation as a window function.

SELECT admission_id, total_bill, AVG(total_bill) OVER () AS hospital_average_bill FROM Admissions; 

What it does: Calculates the average bill across all admissions and displays it alongside every admission.
Expected result: If the average bill is KSh 15,000, every row displays KSh 15,000 in hospital_average_bill, while each admission retains its own bill.
Hospital application: Compare individual admission costs against the hospital-wide average.
B. PARTITION BY
PARTITION BY divides rows into groups for a window calculation without removing individual rows.

SELECT admission_id, department_id, total_bill, AVG(total_bill) OVER ( PARTITION BY department_id ) AS department_average_bill FROM Admissions; 

What it does: Calculates a separate average for each department.
Expected result: Admissions belonging to the same department display the same department average, but different departments can have different averages.
Hospital application: Compare admission costs across departments such as maternity, outpatient and emergency services.
C. ORDER BY
Within a window, ORDER BY specifies the order in which rows are evaluated. It is essential for ranking functions and time-based comparisons.

SELECT admission_id, admission_date, total_bill, ROW_NUMBER() OVER ( ORDER BY admission_date ) AS admission_sequence FROM Admissions; 

What it does: Numbers admissions in chronological order.
Expected result: The earliest admission receives sequence number 1, followed by subsequent admissions.
Hospital application: Establish the sequence of admissions or compare current and previous patient visits.
Note that the ORDER BY inside OVER() does not necessarily sort the final displayed result. Use an outer ORDER BY if you need to control the output order.
D. Window frames
A window frame defines which rows within a window contribute to the calculation for the current row. Frames are particularly useful for running totals, moving averages and cumulative counts.
For example:

SELECT admission_id, admission_date, total_bill, SUM(total_bill) OVER ( ORDER BY admission_date, admission_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total FROM Admissions ORDER BY admission_date, admission_id; 

The frame means:

• UNBOUNDED PRECEDING: Start from the first row.
• CURRENT ROW: Stop at the current row.
• ROWS BETWEEN: Specify the exact rows included in the calculation. Expected result: The first admission contributes its bill, the second contributes its bill plus the first, and the cumulative total continues increasing.

Other useful frames include:
| Window Frame | Meaning | Example Use |
|---|---|---|
| ROWS BETWEEN 1 PRECEDING AND CURRENT ROW | Includes the current row and the previous row. | Two-admission moving total |
| ROWS BETWEEN 2 PRECEDING AND CURRENT ROW | Includes the current row and the two previous rows. | Three-row moving average |
| ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING | Includes all rows in the window. | Overall total for each department |

A critical distinction is that an ordered window's default frame can include all rows tied with the current row. Explicitly specifying ROWS makes row-by-row calculations more predictable.

3.3 Ranking window functions
Ranking functions assign positions to rows based on specified values.
A. ROW_NUMBER()
Assigns a unique sequential number to every row, even when rows have identical values.

SELECT admission_id, department_id, total_bill, ROW_NUMBER() OVER ( PARTITION BY department_id ORDER BY total_bill DESC, admission_id ) AS row_num FROM Admissions; 

Explanation: Each department starts numbering at 1. The highest bill receives the first position. admission_id breaks ties consistently when it is unique.
Expected result: If bills in one department are KSh 30,000, KSh 20,000 and KSh 20,000, their row numbers are 1, 2 and 3.
Hospital use: Select one latest admission per patient or identify the highest-cost admissions in each department.

B. RANK()
Assigns the same rank to tied values, then skips subsequent rank numbers.

SELECT admission_id, total_bill, RANK() OVER ( ORDER BY total_bill DESC ) AS bill_rank FROM Admissions; 

Explanation: Admissions with equal bills receive equal ranks.

Expected result:
| Total Bill (KSh) | Rank |
|---:|---:|
| 30,000 | 1 |
| 20,000 | 2 |
| 20,000 | 2 |
| 10,000 | 4 |
The next rank after two admissions ranked 2 is 4.
Hospital use: Rank departments or admissions by cost while recognizing tied positions.

C. DENSE_RANK()
Assigns the same rank to tied values without skipping rank numbers.

SELECT admission_id, total_bill, DENSE_RANK() OVER ( ORDER BY total_bill DESC ) AS dense_bill_rank FROM Admissions; 

Expected result: For bills of KSh 30,000, KSh 20,000, KSh 20,000 and KSh 10,000, the ranks are 1, 2, 2 and 3.
Hospital use: Identify the three highest distinct bill levels without gaps in the ranking.

Comparison of ranking functions
| Function | Unique Row Number? | Ties Share a Rank? | Skips Ranks After Ties? |
|---|---|---|---|
| ROW_NUMBER() | Yes | No | No |
| RANK() | No | Yes | Yes |
| DENSE_RANK() | No | Yes | No |

D. NTILE()
Divides ordered rows into a specified number of approximately equal groups, called buckets.

SELECT admission_id, total_bill, NTILE(4) OVER ( ORDER BY total_bill ) AS cost_quartile FROM Admissions; 

Explanation: Divides admissions into four groups based on their bills. If there are 100 admissions, each bucket will contain 25 rows.
Expected result: Bucket 1 contains the lowest bills, while bucket 4 contains the highest bills, assuming ascending order.
Hospital use: Categorize admissions into low-, moderate- and high-cost groups. The buckets represent relative positions in the dataset, not predefined medical cost thresholds.

3.4 Navigation window functions
Navigation functions retrieve values from other rows in the same window.
A. LAG()
Retrieves a value from a preceding row without requiring a self-join.

SELECT patient_id, admission_date, total_bill, LAG(total_bill) OVER ( PARTITION BY patient_id ORDER BY admission_date, admission_id ) AS previous_bill FROM Admissions; 

Explanation: Retrieves the previous admission's bill for each patient.
Expected result: The first admission for each patient has NULL for previous_bill. Later admissions show the previous bill.
Hospital use: Compare a patient's current admission cost with the cost of their previous visit.

B. LEAD()
Retrieves a value from a subsequent row.

SELECT patient_id, admission_date, total_bill, LEAD(total_bill) OVER ( PARTITION BY patient_id ORDER BY admission_date, admission_id ) AS next_bill FROM Admissions; 

Explanation: Looks forward to the next admission for the same patient.
Expected result: The last admission for each patient has NULL for next_bill unless a default value is specified.
Hospital use: Analyze changes between successive visits or examine the cost pattern of patients who return for further treatment.

C. FIRST_VALUE()
Returns the value from the first row in the window frame.

SELECT admission_id, department_id, admission_date, total_bill, FIRST_VALUE(total_bill) OVER ( PARTITION BY department_id ORDER BY admission_date, admission_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS first_admission_bill FROM Admissions; 

Explanation: Returns the bill associated with the earliest admission in each department.
Expected result: Every admission in a department displays the same first-admission bill.
Hospital use: Compare subsequent admission costs against the cost of the department's first recorded admission.
D. LAST_VALUE()
Returns the value from the last row in the current window frame.

SELECT admission_id, department_id, admission_date, total_bill, LAST_VALUE(total_bill) OVER ( PARTITION BY department_id ORDER BY admission_date, admission_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS last_admission_bill FROM Admissions; 

Explanation: Returns the bill associated with the latest admission in each department.
Expected result: Every admission in a department displays the same latest-admission bill.
Why specify the frame? Without UNBOUNDED FOLLOWING, LAST_VALUE() often returns the current row's value or the last value within the default frame, rather than the final value in the whole department.
Hospital use: Compare the earliest and latest recorded admission costs.

3.5 Aggregate window functions
Aggregate functions calculate summary values over a window while preserving individual rows.
A. SUM()
Calculates the total of numeric values.

SELECT admission_id, department_id, total_bill, SUM(total_bill) OVER ( PARTITION BY department_id ) AS department_total_bills FROM Admissions; 

Expected result: Every admission displays its bill and the total bills for its department.
Hospital use: Compare individual admission costs with total departmental billing.

B. AVG()
Calculates the arithmetic mean.

SELECT admission_id, department_id, total_bill, AVG(total_bill) OVER ( PARTITION BY department_id ) AS department_average_bill FROM Admissions; 

Expected result: Every row displays the average admission bill for its department.
Hospital use: Identify departments whose admission costs are above or below their own departmental average.

C. COUNT()
Counts rows or non-null values, depending on the expression.

SELECT admission_id, department_id, COUNT(*) OVER ( PARTITION BY department_id ) AS department_admission_count FROM Admissions; 

Expected result: Each admission displays the total number of admissions in its department.
Hospital use: Measure admission volume per department while retaining individual admission records.
COUNT(*) counts rows, including rows containing nulls in other columns. COUNT(column_name) counts only rows where that column is not null.

D. MIN()
Returns the minimum value in a window.

SELECT admission_id, department_id, total_bill, MIN(total_bill) OVER ( PARTITION BY department_id ) AS lowest_department_bill FROM Admissions; 

Expected result: Each admission displays the lowest non-null bill in its department.
Hospital use: Identify the minimum recorded admission cost for each department.
E. MAX()
Returns the maximum value in a window.

SELECT admission_id, department_id, total_bill, MAX(total_bill) OVER ( PARTITION BY department_id ) AS highest_department_bill FROM Admissions; 

Expected result: Each admission displays the highest non-null bill in its department.
Hospital use: Identify departments with unusually high individual admission costs.

3.6 Comparing aggregate windows with GROUP BY
A window function preserves rows, while GROUP BY produces a summary for each group.
Using GROUP BY:

SELECT department_id, AVG(total_bill) AS average_bill FROM Admissions GROUP BY department_id; 

Using a window function:

SELECT admission_id, department_id, total_bill, AVG(total_bill) OVER ( PARTITION BY department_id ) AS average_bill FROM Admissions; 

The first query returns one row per department. The second returns one row per admission, including the departmental average.

4. SQL Joins

4.1 What is a join?
A join combines rows from two or more tables based on a related column or join condition.
For example, the Admissions table contains patient_id, while Patients contains the corresponding patient names. A join makes it possible to retrieve both the admission details and patient names in one query.

4.2 INNER JOIN
Returns only rows with matching values in both tables.

SELECT a.admission_id, p.patient_name, a.admission_date, a.total_bill FROM Admissions a INNER JOIN Patients p ON a.patient_id = p.patient_id; 

Explanation: The query returns admissions whose patient IDs match records in the Patients table.
Expected result: Admissions with no matching patient record are excluded.
Hospital application: Generate a report of admissions linked to valid patient records.

4.3 LEFT JOIN
Returns every row from the left table and matching rows from the right table. If no match exists, the right-side columns contain NULL.

SELECT p.patient_id, p.patient_name, a.admission_id, a.admission_date FROM Patients p LEFT JOIN Admissions a ON p.patient_id = a.patient_id; 

Explanation: Every patient is included, even if they have never been admitted.
Expected result: Patients with admissions appear alongside their admission details. Patients without admissions have NULL in the admission columns.
Hospital application: Identify patients who have never been admitted.
To return only patients without admissions:

SELECT p.patient_id, p.patient_name FROM Patients p LEFT JOIN Admissions a ON p.patient_id = a.patient_id WHERE a.admission_id IS NULL; 

**4.4 RIGHT JOIN
**Returns every row from the right table and matching rows from the left table.

SELECT p.patient_name, a.admission_id, a.patient_id, a.total_bill FROM Patients p RIGHT JOIN Admissions a ON p.patient_id = a.patient_id; 

Explanation: Every admission is retained, including admissions whose patient ID does not match a patient record.
Expected result: A matching patient name appears where available; otherwise, patient_name is NULL.
Hospital application: Audit admission records to identify those that may reference missing patient information.
A RIGHT JOIN can usually be rewritten as a LEFT JOIN by reversing the order of the tables.

4.5 FULL OUTER JOIN
Returns all matching and non-matching rows from both tables.

SELECT p.patient_id AS registered_patient_id, p.patient_name, a.admission_id, a.patient_id AS admission_patient_id FROM Patients p FULL OUTER JOIN Admissions a ON p.patient_id = a.patient_id; 

Explanation: The result includes all patients and all admissions. Matching records are combined, while unmatched records remain with NULL values for the missing side.
Hospital application: Reconcile patient registration records with admission records to identify missing links and unused registrations.

4.6 CROSS JOIN
Returns the Cartesian product of two tables: every row from the first table is paired with every row from the second.

SELECT p.patient_name, d.department_name FROM Patients p CROSS JOIN Departments d; 

Explanation: Each patient is paired with every department.
Expected result: If there are 5 patients and 4 departments, the result contains 20 rows.
Hospital application: Generate all possible patient-department combinations for planning or reporting templates. This does not mean that a patient actually visited every department.
Use caution with large tables because a cross join can produce a very large result.

4.7 SELF JOIN
A self join joins a table to itself using different aliases. It is useful when records in one table have relationships with other records in that same table.
Assume the Staff table contains staff_id, staff_name and manager_id.

SELECT staff.staff_name AS staff_member, manager.staff_name AS manager_name FROM Staff staff LEFT JOIN Staff manager ON staff.manager_id = manager.staff_id; 

Explanation: The table is used twice: once to identify each staff member and once to retrieve their manager's name.
Expected result: Each staff member appears with their manager, where one is assigned. Senior managers without a manager remain in the output with a NULL manager name.
Hospital application: Display reporting relationships among hospital staff, nurses, doctors and department managers.

4.8 Comparison of SQL joins
| Join Type | Records Retained | Hospital Use |
|---|---|---|
| INNER JOIN | Matching records only | Admissions with valid patient records |
| LEFT JOIN | All left records and matching right records | All registered patients, including those without admissions |
| RIGHT JOIN | All right records and matching left records | All admissions, including unmatched ones |
| FULL OUTER JOIN | All records from both sides | Reconciliation of registrations and admissions |
| CROSS JOIN | Every possible combination | Planning combinations of patients and departments |
| SELF JOIN | Matching records within one table | Linking staff members to managers |

Important: Joins can produce multiple rows for one entity when relationships are one-to-many. For example, a patient with five admissions can appear five times in a patient-admission join. This is expected and should be considered when calculating totals.

5. Common Table Expressions (CTEs)

5.1 What is a CTE?
A Common Table Expression is a named result set defined using the WITH keyword. It exists for the duration of a single SQL statement and can make complicated queries easier to read.
General syntax:

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

5.2 Basic CTE
A basic CTE defines one intermediate result that the main query can use.

WITH high_cost_admissions AS ( SELECT admission_id, patient_id, department_id, total_bill FROM Admissions WHERE total_bill > 50000 ) SELECT * FROM high_cost_admissions; 

Explanation: The CTE selects admissions with bills greater than KSh 50,000. The main query retrieves those records.
Expected result: Only admissions whose total_bill exceeds KSh 50,000 are returned.
Hospital application: Identify high-cost admissions for financial review.
A CTE improves readability, but it does not automatically make a query faster. PostgreSQL may inline or materialize a CTE depending on the query and its options.

5.3 Multiple CTEs
A query can define multiple CTEs, with later CTEs referencing earlier ones.

WITH department_totals AS ( SELECT department_id, SUM(total_bill) AS total_revenue FROM Admissions GROUP BY department_id ), department_averages AS ( SELECT AVG(total_revenue) AS average_department_revenue FROM department_totals ) SELECT dt.department_id, dt.total_revenue, da.average_department_revenue FROM department_totals dt CROSS JOIN department_averages da; 

Explanation:

1. department_totals calculates the total recorded bills for each department.
2. department_averages calculates the average of those department totals.
3. The final query compares every department's total with the average across departments. Expected result: Each department appears with its total revenue and the average revenue across all departments. Hospital application: Identify departments performing above or below the average in recorded billing. The query measures recorded bills, not necessarily cash collected. Payment data would be needed to calculate actual collections.

5.4 Recursive CTEs
A recursive CTE references its own result. It is useful for traversing hierarchical relationships.
Assume a Staff table contains staff_id, staff_name and manager_id.

WITH RECURSIVE staff_hierarchy AS ( SELECT staff_id, staff_name, manager_id, 1 AS hierarchy_level FROM Staff WHERE manager_id IS NULL UNION ALL SELECT s.staff_id, s.staff_name, s.manager_id, sh.hierarchy_level + 1 FROM Staff s JOIN staff_hierarchy sh ON s.manager_id = sh.staff_id ) SELECT * FROM staff_hierarchy ORDER BY hierarchy_level, staff_id; 

Explanation:

• The anchor query selects staff members who have no manager.
• The recursive query finds staff whose manager appears in the previous result.
• UNION ALL combines the initial rows and subsequent levels.
• Recursion stops when no more matching staff are found.
• Expected result: _Staff are listed by their level in the hospital's reporting hierarchy. _Hospital application: Map the reporting structure from senior management to department managers and other staff. Important: The example assumes a valid hierarchy. Cycles or malformed reporting relationships can cause problems and should be prevented or handled explicitly. 5.5 CTEs with joins CTEs can prepare data before joining it with another table.

WITH patient_admission_counts AS ( SELECT patient_id, COUNT(*) AS admission_count FROM Admissions GROUP BY patient_id ) SELECT p.patient_name, COALESCE(pac.admission_count, 0) AS admission_count FROM Patients p LEFT JOIN patient_admission_counts pac ON p.patient_id = pac.patient_id; 

Explanation: The CTE counts admissions for each patient. The main query joins those counts to the patient register.
Expected result: Every patient is listed with their number of admissions. Patients with no admissions receive a count of zero because of COALESCE().
Hospital application: Identify patients with frequent hospital visits.

5.6 CTEs with aggregations
CTEs can separate detailed filtering from summary calculations.
WITH monthly_admissions AS (

 SELECT DATE_TRUNC('month', admission_date)::date AS admission_month, COUNT(*) AS total_admissions, SUM(total_bill) AS total_billed FROM Admissions GROUP BY DATE_TRUNC('month', admission_date)::date )...