SQL for Hospital Data Analysis: Window Functions, Joins, CTEs, and Subqueries

Introduction

This article explains four core SQL areas using the hospital dataset: doctors, department, diagnosis, wards, patients, procedures, and visits. The visits table is the central fact table: each row records one patient visit, including the doctor, department, ward, diagnosis, procedure, admission type, length of stay, bill amount, payment method, and satisfaction score. [file:1]

1. SQL Window Functions

A window function performs a calculation across a set of related rows—called a window—without collapsing those rows into one output row. This differs from GROUP BY, which returns one row per group. Window functions are useful in a hospital setting when you need to retain individual visit details while also showing department totals, rankings, running totals, or comparisons with previous visits. [file:1]

How OVER() works

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

SELECT
    visit_id,
    department_id,
    total_bill_kes,
    AVG(total_bill_kes) OVER () AS hospital_average_bill
FROM visits;

What it does: It returns every visit and adds the average bill across all visits.

Expected result: Every row remains visible, but each row has the same hospital-wide average bill. This helps identify visits whose cost is above or below the hospital average.

PARTITION BY

PARTITION BY divides rows into groups before applying the window function. It is like GROUP BY, except it does not reduce the number of rows.

SELECT
    visit_id,
    department_id,
    total_bill_kes,
    AVG(total_bill_kes) OVER (
        PARTITION BY department_id
    ) AS department_average_bill
FROM visits;

What it does: It calculates the average bill separately for Cardiology, General Medicine, Orthopedics, and Pediatrics.

Expected result: A Cardiology visit receives the Cardiology average, while an Orthopedics visit receives the Orthopedics average.

ORDER BY in a window

ORDER BY determines the sequence of rows within each partition. It is essential for rankings and running calculations.

SELECT
    visit_id,
    department_id,
    visit_date,
    total_bill_kes,
    SUM(total_bill_kes) OVER (
        PARTITION BY department_id
        ORDER BY visit_date
    ) AS running_department_bill
FROM visits;

What it does: It calculates a running total of bills by department, ordered by visit date.

Expected result: For each department, the first visit shows its own bill, the second shows the sum of the first two bills, and so on.

Window frames

A window frame controls exactly which rows are included in the calculation. The default frame for ordered aggregate functions is usually:

RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

You can make the frame explicit:

SELECT
    visit_id,
    department_id,
    visit_date,
    total_bill_kes,
    AVG(total_bill_kes) OVER (
        PARTITION BY department_id
        ORDER BY visit_date
        ROWS BETWEEN 1 PRECEDING AND CURRENT ROW
    ) AS two_visit_moving_average
FROM visits;

What it does: For each visit, it averages the current bill and the immediately preceding bill within the same department.

Expected result: This produces a two-visit moving average, useful for identifying short-term changes in departmental billing.

Ranking functions

Function	Purpose	Tie behaviour
ROW_NUMBER()	Assigns a unique sequential number	No ties; every row receives a different number
RANK()	Ranks rows with gaps after ties	Tied rows share a rank; the next rank skips
DENSE_RANK()	Ranks rows without gaps	Tied rows share a rank; the next rank does not skip
NTILE(n)	Divides rows into n approximately equal groups	Useful for quartiles, quintiles, and segments

ROW_NUMBER()

SELECT
    visit_id,
    patient_id,
    department_id,
    total_bill_kes,
    ROW_NUMBER() OVER (
        PARTITION BY department_id
        ORDER BY total_bill_kes DESC
    ) AS bill_rank
FROM visits;

What it does: It numbers visits from highest to lowest bill within each department.

Expected result: The most expensive visit in each department receives 1, the next receives 2, and so on. Even if two bills are equal, they receive different row numbers.

RANK()

SELECT
    visit_id,
    department_id,
    total_bill_kes,
    RANK() OVER (
        PARTITION BY department_id
        ORDER BY total_bill_kes DESC
    ) AS bill_rank
FROM visits;

What it does: It ranks visits by bill amount within each department, allowing tied ranks.

Expected result: If two visits tie for the highest bill, both receive rank 1, and the next visit receives rank 3.

DENSE_RANK()

SELECT
    visit_id,
    department_id,
    total_bill_kes,
    DENSE_RANK() OVER (
        PARTITION BY department_id
        ORDER BY total_bill_kes DESC
    ) AS dense_bill_rank
FROM visits;

What it does: It ranks visits like RANK() but does not skip ranks after ties.

Expected result: If two visits tie at rank 1, the next visit receives rank 2, not 3.

Comparison: Use ROW_NUMBER() when you need a unique position, RANK() when tied values should share a position and gaps are acceptable, and DENSE_RANK() when tied values should share a position without gaps.

NTILE()

SELECT
    visit_id,
    patient_id,
    total_bill_kes,
    NTILE(4) OVER (
        ORDER BY total_bill_kes DESC
    ) AS bill_quartile
FROM visits;

What it does: It divides all visits into four bill-value groups.

Expected result: Quartile 1 contains the highest-billing visits, while quartile 4 contains the lowest-billing visits. Hospital managers can use this to review the most expensive 25% of visits.

Value comparison functions

LAG()

SELECT
    visit_id,
    patient_id,
    visit_date,
    total_bill_kes,
    LAG(total_bill_kes) OVER (
        PARTITION BY patient_id
        ORDER BY visit_date
    ) AS previous_bill
FROM visits;

What it does: For each patient, it retrieves the bill from the patient’s previous visit.

Expected result: A patient’s first visit has NULL for previous_bill; later visits show the previous amount. This can support follow-up cost analysis.

LEAD()

SELECT
    visit_id,
    patient_id,
    visit_date,
    total_bill_kes,
    LEAD(total_bill_kes) OVER (
        PARTITION BY patient_id
        ORDER BY visit_date
    ) AS next_bill
FROM visits;

What it does: For each patient, it retrieves the bill from the next visit.

Expected result: The patient’s final visit has NULL for next_bill; earlier visits show the following visit’s bill.

FIRST_VALUE()

SELECT
    visit_id,
    patient_id,
    visit_date,
    total_bill_kes,
    FIRST_VALUE(total_bill_kes) OVER (
        PARTITION BY patient_id
        ORDER BY visit_date
    ) AS first_visit_bill
FROM visits;

What it does: It shows the bill from each patient’s earliest visit.

Expected result: Every visit for the same patient displays that patient’s first recorded bill.

LAST_VALUE()

SELECT
    visit_id,
    patient_id,
    visit_date,
    total_bill_kes,
    LAST_VALUE(total_bill_kes) OVER (
        PARTITION BY patient_id
        ORDER BY visit_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS last_visit_bill
FROM visits;

What it does: It shows the bill from each patient’s latest visit.

Expected result: Every visit for the same patient displays that patient’s final recorded bill. The explicit frame is important because, without it, LAST_VALUE() may only look at rows up to the current row.

Aggregate window functions

Aggregate window functions calculate totals, averages, counts, and extremes while preserving individual rows.

SELECT
    visit_id,
    department_id,
    total_bill_kes,
    SUM(total_bill_kes) OVER (
        PARTITION BY department_id
    ) AS department_total_bill,
    AVG(total_bill_kes) OVER (
        PARTITION BY department_id
    ) AS department_average_bill,
    COUNT(*) OVER (
        PARTITION BY department_id
    ) AS department_visit_count,
    MIN(total_bill_kes) OVER (
        PARTITION BY department_id
    ) AS department_minimum_bill,
    MAX(total_bill_kes) OVER (
        PARTITION BY department_id
    ) AS department_maximum_bill
FROM visits;

What it does: For every visit, it displays the department’s total bill, average bill, number of visits, lowest bill, and highest bill.

Expected result: Each department’s summary values repeat on every visit from that department, while the original visit-level data remains visible. This is useful for comparing an individual visit against its department’s performance.

2. SQL Joins

A join combines rows from two or more tables based on a related column. In this hospital database, joins are needed because visit details are separated across patients, doctors, department, diagnosis, wards, and procedures. [file:1]

INNER JOIN

An INNER JOIN returns only rows with matching values in both tables.

SELECT
    v.visit_id,
    p.patient_name,
    d.doctor_name,
    dep.department_name,
    v.total_bill_kes
FROM visits v
INNER JOIN patients p
    ON v.patient_id = p.patient_id
INNER JOIN doctors d
    ON v.doctor_id = d.doctor_id
INNER JOIN department dep
    ON v.department_id = dep.department_id;

What it does: It returns only visits that have a matching patient, doctor, and department.

Expected result: Each row shows a visit with the patient’s name, treating doctor, department, and bill. Visits with missing matching records are excluded.

Use case: Generate a clean operational report of completed visits with full patient, doctor, and department details.

LEFT JOIN

A LEFT JOIN returns all rows from the left table and matching rows from the right table. If there is no match, columns from the right table are NULL.

SELECT
    p.patient_id,
    p.patient_name,
    v.visit_id,
    v.visit_date,
    v.total_bill_kes
FROM patients p
LEFT JOIN visits v
    ON p.patient_id = v.patient_id;

What it does: It returns every patient, including patients with no recorded visit.

Expected result: Patients with visits show their visit details; patients without visits show NULL in the visit columns.

Use case: Identify registered patients who have not attended any hospital visit.

To find only patients without visits:

SELECT
    p.patient_id,
    p.patient_name
FROM patients p
LEFT JOIN visits v
    ON p.patient_id = v.patient_id
WHERE v.visit_id IS NULL;

RIGHT JOIN

A RIGHT JOIN returns all rows from the right table and matching rows from the left table.

SELECT
    d.doctor_id,
    d.doctor_name,
    v.visit_id,
    v.visit_date
FROM visits v
RIGHT JOIN doctors d
    ON v.doctor_id = d.doctor_id;

What it does: It returns every doctor, including doctors with no visits.

Expected result: Doctors with visits show their visits; doctors without visits show NULL visit details.

Use case: Identify doctors who have not handled any recorded patient visit. The same result can usually be written as a LEFT JOIN by reversing the table order.

FULL OUTER JOIN

A FULL OUTER JOIN returns all rows from both tables, matching where possible and using NULL where no match exists.

SELECT
    p.patient_id,
    p.patient_name,
    v.visit_id,
    v.total_bill_kes
FROM patients p
FULL OUTER JOIN visits v
    ON p.patient_id = v.patient_id;

What it does: It returns all patients and all visits, whether or not they match.

Expected result: Patients without visits and visits without a matching patient both appear, with NULL on the unmatched side.

Use case: Data-quality auditing—for example, detecting orphaned visit records or patients missing visit data. Note that PostgreSQL supports FULL OUTER JOIN; some database systems use slightly different syntax or do not support it directly. [file:1]

CROSS JOIN

A CROSS JOIN returns every possible combination of rows from the two tables. It does not require an ON condition.

SELECT
    dep.department_name,
    pr.procedure_name
FROM department dep
CROSS JOIN procedures pr;

What it does: It pairs every department with every procedure.

Expected result: If there are 4 departments and 15 procedures, the result contains 60 rows.

Use case: Build a planning matrix, such as reviewing which procedures could potentially be offered in each department. Use it carefully because the output grows quickly.

SELF JOIN

A self join compares rows within the same table by using two aliases for that table.

SELECT
    d1.doctor_name AS doctor,
    d2.doctor_name AS more_experienced_colleague,
    d2.doctor_experience_years
FROM doctors d1
INNER JOIN doctors d2
    ON d2.doctor_experience_years > d1.doctor_experience_years;

What it does: For each doctor, it finds colleagues with more experience.

Expected result: Dr. Faith Chebet, with 8 years of experience, is paired with all three more experienced doctors; Dr. Peter Otieno, with 15 years, has no more experienced colleague and therefore does not appear. [file:1]

Use case: Compare doctors, patients, or departments against other records in the same table—for example, identifying senior colleagues for mentorship.

Join comparison

Join type	Rows returned	Hospital use case
INNER JOIN	Only matching rows	Report visits with complete patient, doctor, and department details
LEFT JOIN	All left-table rows plus matches	Find patients with no visits
RIGHT JOIN	All right-table rows plus matches	Find doctors with no visits
FULL OUTER JOIN	All rows from both tables	Audit unmatched patients or visits
CROSS JOIN	Every combination of rows	Create a department-procedure planning matrix
SELF JOIN	Rows from one table compared with itself	Compare doctors by experience

3. Common Table Expressions

A Common Table Expression, or CTE, is a named temporary result set defined using WITH. It makes complex queries easier to read, test, and reuse within one statement.

Basic CTE

WITH cardiology_visits AS (
    SELECT
        visit_id,
        patient_id,
        doctor_id,
        total_bill_kes
    FROM visits
    WHERE department_id = 10
)
SELECT
    cv.visit_id,
    p.patient_name,
    cv.total_bill_kes
FROM cardiology_visits cv
INNER JOIN patients p
    ON cv.patient_id = p.patient_id;

What it does: The CTE first selects all Cardiology visits, then the main query joins those visits to patient names.

Expected result: A list of Cardiology visits with patient names and bill amounts.

Use case: Isolate one department before performing further analysis.

Multiple CTEs

WITH department_summary AS (
    SELECT
        department_id,
        COUNT(*) AS total_visits,
        SUM(total_bill_kes) AS total_revenue,
        AVG(patient_satisfaction_score) AS average_satisfaction
    FROM visits
    GROUP BY department_id
),
high_revenue_departments AS (
    SELECT
        department_id,
        total_visits,
        total_revenue,
        average_satisfaction
    FROM department_summary
    WHERE total_revenue > 50000
)
SELECT
    dep.department_name,
    h.total_visits,
    h.total_revenue,
    ROUND(h.average_satisfaction, 2) AS average_satisfaction
FROM high_revenue_departments h
INNER JOIN department dep
    ON h.department_id = dep.department_id
ORDER BY h.total_revenue DESC;

What it does: The first CTE summarizes visits and revenue by department. The second CTE filters for departments earning more than KES 50,000. The final query adds department names.

Expected result: A list of high-revenue departments, their visit counts, total revenue, and average patient satisfaction.

Use case: Break a large analytical question into readable stages.

Recursive CTE

A recursive CTE references itself and is useful for hierarchical or sequential data, such as generating a sequence of dates or walking through an organizational structure.

WITH RECURSIVE visit_sequence AS (
    SELECT
        1 AS visit_number
    UNION ALL
    SELECT
        visit_number + 1
    FROM visit_sequence
    WHERE visit_number < 10
)
SELECT *
FROM visit_sequence;

What it does: It generates numbers from 1 to 10.

Expected result: Ten rows numbered 1 through 10.

Hospital use case: Generate a date series for a dashboard, such as all dates in June 2026, then left join it to daily visit counts so that days with zero visits still appear.

CTEs with joins and aggregation

WITH patient_visit_summary AS (
    SELECT
        patient_id,
        COUNT(*) AS total_visits,
        SUM(total_bill_kes) AS total_spent,
        AVG(patient_satisfaction_score) AS average_satisfaction
    FROM visits
    GROUP BY patient_id
)
SELECT
    p.patient_name,
    p.county,
    pvs.total_visits,
    pvs.total_spent,
    ROUND(pvs.average_satisfaction, 2) AS average_satisfaction
FROM patient_visit_summary pvs
INNER JOIN patients p
    ON pvs.patient_id = p.patient_id
ORDER BY pvs.total_spent DESC;

What it does: It aggregates each patient’s visit count, total spending, and satisfaction score, then joins the result to patient demographic details.

Expected result: One row per patient who has visited, ordered from the highest total spending to the lowest.

Use case: Identify high-value or frequent patients for follow-up, loyalty programmes, or service-quality review.

CTEs versus subqueries

Aspect	CTE	Subquery
Definition	A named temporary result set introduced with WITH	A query embedded inside another query
Readability	Usually clearer for multi-step logic	Can become difficult to read when deeply nested
Reuse	Can be referenced multiple times within the same query	Normally written again if needed in several places
Recursion	Supports recursive CTEs	Does not naturally support recursion
Scope	Exists only for the duration of the main query	Exists only within the enclosing query
Best use	Multi-step analysis, reusable logic, hierarchical data	Simple filtering, calculated values, or small derived tables

Example using a subquery instead of a CTE:

SELECT
    dep.department_name,
    ds.total_visits,
    ds.total_revenue
FROM department dep
INNER JOIN (
    SELECT
        department_id,
        COUNT(*) AS total_visits,
        SUM(total_bill_kes) AS total_revenue
    FROM visits
    GROUP BY department_id
) ds
    ON dep.department_id = ds.department_id;

This produces the same department summary as a CTE, but the CTE version is generally easier to maintain when the logic becomes longer.

4. SQL Subqueries

A subquery is a query placed inside another SQL statement. Subqueries can return a single value, one column, one row, or an entire table-like result.

Scalar subquery

A scalar subquery returns exactly one value.

SELECT
    visit_id,
    patient_id,
    total_bill_kes,
    total_bill_kes - (
        SELECT AVG(total_bill_kes)
        FROM visits
    ) AS difference_from_average
FROM visits;

What it does: It calculates how much each visit’s bill differs from the hospital-wide average bill.

Expected result: Positive values indicate above-average bills; negative values indicate below-average bills.

Use case: Flag unusually expensive or inexpensive visits.

Non-correlated subquery

A non-correlated subquery can run independently because it does not refer to the outer query.

SELECT
    visit_id,
    patient_id,
    total_bill_kes
FROM visits
WHERE total_bill_kes > (
    SELECT AVG(total_bill_kes)
    FROM visits
);

What it does: It returns visits with bills above the hospital average.

Expected result: Only higher-than-average visits appear.

Use case: Identify costly visits for cost-control review.

Correlated subquery

A correlated subquery refers to a column from the outer query, so it is evaluated in relation to each outer row.

SELECT
    v.visit_id,
    v.department_id,
    v.total_bill_kes
FROM visits v
WHERE v.total_bill_kes > (
    SELECT AVG(v2.total_bill_kes)
    FROM visits v2
    WHERE v2.department_id = v.department_id
);

What it does: For every visit, it compares that visit’s bill with the average bill in the same department.

Expected result: Only visits costing more than their department’s average are returned.

Use case: Detect unusually expensive visits relative to the department, rather than the hospital as a whole.

Nested subqueries

A nested subquery is a subquery inside another subquery.

SELECT
    patient_id,
    patient_name
FROM patients
WHERE patient_id IN (
    SELECT patient_id
    FROM visits
    WHERE department_id = (
        SELECT department_id
        FROM department
        WHERE department_name = 'Cardiology'
    )
);

What it does: It first finds the Cardiology department ID, then finds patients who visited that department.

Expected result: A list of patients treated in Cardiology.

Use case: Answer layered questions, such as “Which patients were treated in the department named Cardiology?”

Subqueries in SELECT

A subquery in SELECT is commonly scalar and adds a calculated value to every row.

SELECT
    visit_id,
    total_bill_kes,
    (
        SELECT MAX(total_bill_kes)
        FROM visits
    ) AS highest_hospital_bill
FROM visits;

What it does: It displays each visit alongside the highest bill in the hospital.

Expected result: Every row has the same highest_hospital_bill value.

Subqueries in FROM

A subquery in FROM acts as a derived table and must be given an alias.

SELECT
    dep.department_name,
    ds.total_visits,
    ds.total_revenue
FROM (
    SELECT
        department_id,
        COUNT(*) AS total_visits,
        SUM(total_bill_kes) AS total_revenue
    FROM visits
    GROUP BY department_id
) ds
INNER JOIN department dep
    ON ds.department_id = dep.department_id;

What it does: It first summarizes visits by department, then joins the summary to department names.

Expected result: One row per department showing visit count and total revenue.

Subqueries in WHERE

Subqueries in WHERE filter rows based on another query’s result.

SELECT
    patient_id,
    patient_name
FROM patients
WHERE patient_id IN (
    SELECT patient_id
    FROM visits
    WHERE total_bill_kes > 20000
);

What it does: It returns patients who had at least one visit costing more than KES 20,000.

Expected result: Only patients associated with high-cost visits appear.

IN

IN checks whether a value matches any value returned by a subquery.

SELECT
    p.patient_name,
    p.county
FROM patients p
WHERE p.patient_id IN (
    SELECT v.patient_id
    FROM visits v
    WHERE v.admission_type = 'Inpatient'
);

What it does: It returns patients who have had at least one inpatient admission.

Expected result: A list of admitted patients.

Use case: Identify patients who required hospital admission.

EXISTS

EXISTS returns TRUE if the subquery returns at least one row. It is often efficient because the database can stop as soon as it finds one matching row.

SELECT
    p.patient_id,
    p.patient_name
FROM patients p
WHERE EXISTS (
    SELECT 1
    FROM visits v
    WHERE v.patient_id = p.patient_id
      AND v.follow_up_required = 'Yes'
);

What it does: It returns patients with at least one visit requiring follow-up.

Expected result: A list of patients needing follow-up attention.

Use case: Support follow-up scheduling and patient-outreach workflows.

ANY

ANY compares a value with at least one value returned by a subquery.

SELECT
    visit_id,
    department_id,
    total_bill_kes
FROM visits
WHERE total_bill_kes > ANY (
    SELECT total_bill_kes
    FROM visits
    WHERE department_id = 20
);

What it does: It returns visits whose bill is greater than at least one General Medicine visit bill.

Expected result: Visits that exceed the minimum General Medicine bill.

Use case: Compare a department’s costs against another department’s cost range.

ALL

ALL compares a value with every value returned by a subquery.

SELECT
    visit_id,
    department_id,
    total_bill_kes
FROM visits
WHERE total_bill_kes > ALL (
    SELECT total_bill_kes
    FROM visits
    WHERE department_id = 20
);

What it does: It returns visits whose bill is greater than every General Medicine visit bill.

Expected result: Only visits more expensive than the highest General Medicine bill.

Use case: Identify exceptional high-cost cases relative to a benchmark department.

Practical Combined Example

The following query combines a CTE, window function, join, and subquery to identify each department’s highest-billing visit.

WITH ranked_visits AS (
    SELECT
        v.visit_id,
        v.patient_id,
        v.department_id,
        v.total_bill_kes,
        ROW_NUMBER() OVER (
            PARTITION BY v.department_id
            ORDER BY v.total_bill_kes DESC
        ) AS bill_rank
    FROM visits v
)
SELECT
    dep.department_name,
    p.patient_name,
    rv.visit_id,
    rv.total_bill_kes
FROM ranked_visits rv
INNER JOIN department dep
    ON rv.department_id = dep.department_id
INNER JOIN patients p
    ON rv.patient_id = p.patient_id
WHERE rv.bill_rank = 1;

What it does: The CTE ranks visits by bill amount within each department. The final query keeps only the visit ranked 1 in each department and adds department and patient names.

Expected result: One row per department, showing that department’s most expensive visit and the patient involved.

Hospital use case: Hospital administrators can use this report to review the most expensive case in each department, investigate whether the cost was justified by the diagnosis or procedure, and identify opportunities for cost management.

Conclusion

SQL window functions, joins, CTEs, and subqueries are essential tools for turning raw hospital records into useful operational and clinical insights. Window functions allow analysts to rank visits, calculate running totals, compare each visit with departmental averages, and examine patient visit patterns without losing visit-level detail. Joins bring together information stored across the patients, doctors, department, diagnosis, wards, and procedures tables to create complete reports. CTEs and subqueries make complex analysis easier to structure, especially when calculating departmental revenue, identifying high-cost visits, finding patients who need follow-up, or detecting data-quality issues. Applied together, these SQL concepts support better hospital decision-making in areas such as resource planning, cost control, patient follow-up, service-quality monitoring, and performance evaluation.