Mastering Intermediate & Advanced SQL: A Practical Healthcare Analytics Guide

In clinical operations and healthcare administration, raw relational tables alone cannot answer complex analytical questions like tracking readmission trends, analyzing department staffing hierarchies, or auditing medication billing variances.

This guide demonstrates how to apply Window Functions, Joins, Common Table Expressions (CTEs), and Subqueries using a standard hospital database schema.

We explore four foundational SQL areas:

1. Window Functions
2. SQL Joins
3. Common Table Expressions (CTEs)
4. SQL Subqueries

Dataset Schema Reference: kenyan_hospital_health_records

All practical queries in this article target the table below, representing real clinical visits, billing records, and attending personnel:

1. SQL Window Functions

Concept Overview

A window function performs calculations across a set of table rows that are related to the current query row. Unlike standard GROUP BY aggregations—which collapse multiple rows into a single summary row—window functions preserve individual row identities while displaying aggregate or relative metrics alongside them.

Window Mechanics: OVER(), PARTITION BY, ORDER BY, and Frames

Every window function uses the OVER() clause:

FUNCTION(...) OVER (
    PARTITION BY partition_column
    ORDER BY sort_column
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
)

• OVER(): Signals to the database engine that the preceding function operates over an analytical window rather than a collapsed group. An empty OVER() treats the entire result set as one single partition.
• PARTITION BY: Divides the rows into logical subsets or "partitions" (e.g., by hospital_department or doctor_name). The calculation runs independently within each subset and resets at partition boundaries.
• ORDER BY: Dictates the sequence in which rows are processed and evaluated inside each individual partition.
• Window Frame (ROWS / RANGE BETWEEN ...): Defines the physical or logical boundary of rows relative to the current row included in the computation.
  • ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: From the start of the partition up to the active row (standard for running totals).
  • ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING: A sliding 3-row moving window (the previous row, current row, and next row).

Ranking Functions: ROW_NUMBER(), RANK(), DENSE_RANK(), and NTILE()

Ranking functions assign an ordered integer to each row within a partition.

• ROW_NUMBER(): Generates a strict, non-repeating sequence (1, 2, 3, 4,) for each row in a partition. If two rows have identical values in the sort column, they are assigned consecutive numbers arbitrarily.
• RANK(): Assigns sequential ranks but leaves gaps after ties (e.g., 1, 2, 2, 4).
• DENSE_RANK(): Assigns sequential ranks without gaps after ties (e.g., 1, 2, 2, 3).
• NTILE(n): Divides an ordered partition as evenly as possible into n ranked buckets (e.g., quartiles where n=4).

Practical Query: Department Billing Tier Audit

SELECT 
    patient_id,
    hospital_department,
    first_name || ' ' || last_name AS patient_name,
    bill_amount_kes,
    ROW_NUMBER() OVER (
        PARTITION BY hospital_department 
        ORDER BY bill_amount_kes DESC
    ) AS row_num,
    RANK() OVER (
        PARTITION BY hospital_department 
        ORDER BY bill_amount_kes DESC
    ) AS rnk,
    DENSE_RANK() OVER (
        PARTITION BY hospital_department 
        ORDER BY bill_amount_kes DESC
    ) AS dense_rnk,
    NTILE(4) OVER (
        PARTITION BY hospital_department 
        ORDER BY bill_amount_kes DESC
    ) AS cost_quartile
FROM kenyan_hospital_health_records;

• What the Query Does: Partitions all 672 records by hospital_department and sorts them from highest to lowest bill amount. It tags each record with its row position, ranking, dense ranking, and cost quartile.
• Expected Result: In Cardiology, Patient 9656 (KES 49,732.40) and Patient 7362 (KES 49,720.43) receive ranks 1 and 2. Patients falling into cost_quartile = 1 represent the top 25 most expensive cases in that specific department, enabling targeted insurance audits.

Value Functions: LAG(), LEAD(), FIRST_VALUE(), and LAST_VALUE()

Value functions allow rows to read values from neighboring rows without requiring complex self-joins.

• LAG(col, offset): Reads data from n rows behind the current row within the partition.
• LEAD(col, offset): Reads data from n rows ahead of the current row within the partition.
• FIRST_VALUE(col): Returns the value of the specified column from the very first row in the window frame.
• LAST_VALUE(col): Returns the value from the final row in the window frame. (Note: Must be paired with ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING or ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to prevent the default frame from terminating at the current row).

Practical Query: Tracking Chronological Doctor Encounters and Cost Variances

SELECT 
    patient_id,
    doctor_name,
    date_of_visit,
    bill_amount_kes,
    LAG(bill_amount_kes, 1) OVER (
        PARTITION BY doctor_name 
        ORDER BY date_of_visit ASC
    ) AS previous_patient_bill,
    LEAD(bill_amount_kes, 1) OVER (
        PARTITION BY doctor_name 
        ORDER BY date_of_visit ASC
    ) AS subsequent_patient_bill,
    FIRST_VALUE(bill_amount_kes) OVER (
        PARTITION BY doctor_name 
        ORDER BY date_of_visit ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS first_recorded_bill
FROM kenyan_hospital_health_records
WHERE doctor_name = 'Dr. Kiplagat';

• What the Query Does: Orders Dr. Kiplagat's 52 consultations chronologically. It pulls the bill from the preceding patient visit and the bill from the subsequent visit onto the active record.
• Expected Result: For Dr. Kiplagat's first visit on 2020-02-28 (Patient 5098), previous_patient_bill displays NULL. The subsequent visit on 2020-06-19 displays KES 26,535.91 in previous_patient_bill and KES 31,284.09 in subsequent_patient_bill, allowing revenue analysts to identify fluctuations between sequential encounters.

Aggregate Window Functions: SUM(), AVG(), COUNT(), MIN(), and MAX()

When combined with an OVER() clause, standard aggregate functions produce running totals, departmental moving averages, and cohort extremes directly alongside individual patient rows.

Practical Query: Running Revenue and Department Benchmarking

SELECT 
    patient_id,
    hospital_department,
    date_of_visit,
    bill_amount_kes,
    -- Running revenue total across time within the department
    SUM(bill_amount_kes) OVER (
        PARTITION BY hospital_department 
        ORDER BY date_of_visit ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS running_dept_revenue,
    -- Department average benchmark
    ROUND(AVG(bill_amount_kes) OVER (
        PARTITION BY hospital_department
    ), 2) AS dept_average_bill,
    -- Department maximum charge
    MAX(bill_amount_kes) OVER (
        PARTITION BY hospital_department
    ) AS dept_max_bill,
    -- Department minimum charge
    MIN(bill_amount_kes) OVER (
        PARTITION BY hospital_department
    ) AS dept_min_bill,
    -- Cumulative count of patients treated
    COUNT(patient_id) OVER (
        PARTITION BY hospital_department 
        ORDER BY date_of_visit ASC
        ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
    ) AS cumulative_patient_count
FROM kenyan_hospital_health_records;

• What the Query Does: Retains every encounter while calculating a cumulative running total of revenue and patient throughput over time, juxtaposed against the overall department average, floor, and ceiling.
• Expected Result: A finance dashboard showing how each incoming patient increments total department cash flow, alongside an immediate visual check of whether an individual bill exceeded dept_average_bill.

2. SQL Joins

Concept Overview

Joins combine columns from two or more tables or subquery projections based on logical relationships between shared keys. In healthcare databases, choosing the appropriate join type guarantees data integrity and prevents dropping clinical records or introducing duplicate entries.

INNER JOIN       LEFT JOIN        RIGHT JOIN       FULL OUTER JOIN
  [ A ∩ B ]        [ A + (A ∩ B) ]  [ (A ∩ B) + B ]  [ A ∪ B ]

Join Types & Healthcare Applications

Normalized Staging Tables for Joins

To demonstrate relational joins using the hospital records, we project two normalized dimension tables from the dataset alongside the encounter fact table:

-- Dimension Table 1: Master Hospital Departments
CREATE TEMPORARY TABLE master_departments AS
SELECT DISTINCT hospital_department AS department_name
FROM kenyan_hospital_health_records
UNION ALL
SELECT 'Radiology' AS department_name; -- Deliberately unassigned department for testing outer joins

-- Dimension Table 2: Registered Insurance Payers
CREATE TEMPORARY TABLE master_insurers AS
SELECT DISTINCT insurance_provider AS insurer_name
FROM kenyan_hospital_health_records
WHERE insurance_provider IS NOT NULL
UNION ALL
SELECT 'Madison Insurance' AS insurer_name; -- Deliberately unassigned insurer

Practical Queries: Joins in Action

1. INNER JOIN: Matching Verified Insured Encounters

SELECT 
    k.patient_id,
    k.first_name || ' ' || k.last_name AS patient_name,
    k.bill_amount_kes,
    m.insurer_name
FROM kenyan_hospital_health_records k
INNER JOIN master_insurers m 
    ON k.insurance_provider = m.insurer_name;

• Explanation: Returns only the 536 records that have an active insurance plan matching the insurer directory. Uninsured/cash patients (NULL) are excluded.

2. LEFT JOIN: Identifying Inactive Departments

SELECT 
    md.department_name,
    COUNT(k.patient_id) AS total_encounters,
    COALESCE(SUM(k.bill_amount_kes), 0) AS total_billed_kes
FROM master_departments md
LEFT JOIN kenyan_hospital_health_records k 
    ON md.department_name = k.hospital_department
GROUP BY md.department_name;

• Explanation: Evaluates every department in the hospital. Active units (e.g., Cardiology, 104 encounters) display their counts, while the newly introduced Radiology department displays 0 encounters and KES 0.00 billed. An INNER JOIN would have omitted Radiology.

3. FULL OUTER JOIN: Complete Operational Ledger Reconciliation

SELECT 
    md.department_name AS catalog_department,
    k.hospital_department AS record_department,
    k.patient_id,
    k.bill_amount_kes
FROM master_departments md
FULL OUTER JOIN kenyan_hospital_health_records k 
    ON md.department_name = k.hospital_department
WHERE k.patient_id IS NULL OR md.department_name IS NULL;

• Explanation: Surfaces edge cases on both ends: cataloged departments that treated zero patients, and clinical records tied to unrecognized department labels.

4. CROSS JOIN: Generating Coverage Matrices

SELECT 
    d.department_name,
    s.shift_slot
FROM (SELECT DISTINCT hospital_department AS department_name FROM kenyan_hospital_health_records) d
CROSS JOIN (
    VALUES ('Morning (07:00 - 15:00)'), 
           ('Evening (15:00 - 23:00)'), 
           ('Night (23:00 - 07:00)')
) AS s(shift_slot)
ORDER BY d.department_name, s.shift_slot;

• Explanation: Generates a 21-row scheduling grid (7 departments ,times 3 duty shifts) to seed hospital on-call rosters.

5. SELF JOIN: Cross-Infection Contact Tracing

SELECT 
    a.hospital_department,
    a.date_of_visit,
    a.patient_id AS patient_a_id,
    a.diagnosis AS patient_a_diagnosis,
    b.patient_id AS patient_b_id,
    b.diagnosis AS patient_b_diagnosis
FROM kenyan_hospital_health_records a
JOIN kenyan_hospital_health_records b 
    ON a.hospital_department = b.hospital_department 
   AND a.date_of_visit = b.date_of_visit
   AND a.patient_id < b.patient_id -- Prevents self-matching and duplicate inverted pairs
WHERE a.diagnosis = 'Typhoid' OR b.diagnosis = 'Typhoid';

• Explanation: Joins the dataset to itself to detect instances where multiple patients shared a department on the same calendar day with a communicable condition (Typhoid), supporting hospital infection-control audits.

3. Common Table Expressions (CTEs)

Concept Overview

A Common Table Expression (CTE) is a temporary, named result set defined using the WITH keyword that exists only for the duration of the query. CTEs replace deeply nested subqueries with linear, top-down modules.

Basic and Multiple CTEs

Practical Query: Benchmarking Department Collections Against Regional Targets

WITH DepartmentSummary AS (
    -- CTE 1: Compute total billing and patient volume per department
    SELECT 
        hospital_department,
        COUNT(patient_id) AS total_patients,
        SUM(bill_amount_kes) AS department_revenue,
        AVG(bill_amount_kes) AS avg_patient_bill
    FROM kenyan_hospital_health_records
    GROUP BY hospital_department
),
FacilityBenchmark AS (
    -- CTE 2: Calculate facility-wide average department revenue
    SELECT 
        AVG(department_revenue) AS mean_dept_revenue
    FROM DepartmentSummary
)
-- Main Outer Query: Combine CTEs with Joins to highlight high-performing units
SELECT 
    ds.hospital_department,
    ds.total_patients,
    ROUND(ds.department_revenue, 2) AS department_revenue_kes,
    ROUND(ds.avg_patient_bill, 2) AS avg_patient_bill_kes,
    ROUND(ds.department_revenue - fb.mean_dept_revenue, 2) AS revenue_variance_kes
FROM DepartmentSummary ds
CROSS JOIN FacilityBenchmark fb
ORDER BY department_revenue_kes DESC;

• What the Query Does: Defines two sequential CTEs (DepartmentSummary and FacilityBenchmark) and references both in the final SELECT block.
• Expected Result: A comparative executive summary showing each department's total revenue alongside its variance from the facility-wide department mean text{KES 2,513,809.84).

Recursive CTEs

Recursive CTEs reference themselves in an iterative loop to traverse hierarchical, parent-child, or sequential data until a termination condition is reached.

A recursive CTE consists of:

1. The Anchor Member: Initial query that sets the base result set.
2. UNION ALL: Operator combining iterations.
3. The Recursive Member: Query referencing the CTE name, repeated until it returns an empty set.

Practical Query: Generating a Step-Down Patient Discharge Schedule

WITH RECURSIVE PatientFollowUpSchedule AS (
    -- Anchor Member: Identify critical admitted patients requiring follow-up
    SELECT 
        patient_id,
        first_name || ' ' || last_name AS patient_name,
        date_of_visit AS scheduled_review_date,
        1 AS review_cycle
    FROM kenyan_hospital_health_records
    WHERE treatment_plan = 'Admission' AND diagnosis = 'Diabetes'
      AND date_of_visit >= '2025-01-01'

    UNION ALL

    -- Recursive Member: Project subsequent reviews at 30-day intervals (up to cycle 4)
    SELECT 
        p.patient_id,
        p.patient_name,
        (p.scheduled_review_date + INTERVAL '30 days')::DATE AS scheduled_review_date,
        p.review_cycle + 1
    FROM PatientFollowUpSchedule p
    WHERE p.review_cycle < 4
)
SELECT 
    patient_id,
    patient_name,
    review_cycle,
    scheduled_review_date
FROM PatientFollowUpSchedule
ORDER BY patient_id, review_cycle;

• What the Query Does: Uses recursion to project a structured 4-cycle, 120-day outpatient management roadmap for diabetic patients admitted in 2025.
• Expected Result: Four sequential rows per candidate patient, stepping forward 30 days per review cycle to automate preventative care scheduling.

CTEs vs. Subqueries

4. SQL Subqueries

Concept Overview

A subquery is a SELECT statement nested inside another SQL statement. Subqueries can execute independently or evaluate iteratively against outer query attributes.

Subquery Classifications

• Scalar Subquery: Returns exactly one row and one column (a single atomic value).
• Non-Correlated Subquery: Completely self-contained. Executes once; its output is handed off to the outer query.
• Correlated Subquery: References columns from the outer query row. Executes once for every candidate row evaluated by the outer query.
• Nested Subquery: A subquery enclosed within another subquery.
• Multi-Row Set Operators (IN, EXISTS, ANY, ALL):
  • IN: Tests if a candidate value matches any value in a list or subquery set.
  • EXISTS: Evaluates boolean presence (True/False). Short-circuits immediately upon discovering the first matching record.
  • > ANY: Matches if the value is greater than the minimum value returned by the subquery.
  • > ALL: Matches only if the value is greater than the maximum value returned by the subquery.

Subquery Structural Placement

1. In the SELECT Clause (Scalar Subquery)

SELECT 
    patient_id,
    first_name || ' ' || last_name AS patient_name,
    bill_amount_kes,
    hospital_department,
    ROUND(
        (SELECT AVG(bill_amount_kes) 
         FROM kenyan_hospital_health_records 
         WHERE hospital_department = k.hospital_department), 
        2
    ) AS department_mean_bill,
    ROUND(
        bill_amount_kes - (SELECT AVG(bill_amount_kes) 
                           FROM kenyan_hospital_health_records 
                           WHERE hospital_department = k.hospital_department), 
        2
    ) AS variance_from_dept_mean
FROM kenyan_hospital_health_records k;

• Explanation: Evaluates the department average as a scalar metric for each patient, displaying each bill's variance against its departmental mean.

2. In the FROM Clause (Derived Table)

SELECT 
    derived.doctor_name,
    derived.total_patients_seen,
    ROUND(derived.total_billed_kes, 2) AS total_billed_kes
FROM (
    SELECT 
        doctor_name,
        COUNT(patient_id) AS total_patients_seen,
        SUM(bill_amount_kes) AS total_billed_kes
    FROM kenyan_hospital_health_records
    GROUP BY doctor_name
) AS derived
WHERE derived.total_patients_seen > 50;

• Explanation: Aggregates doctor throughput inside an in-memory virtual table (derived), allowing the outer query to filter for high-volume physicians.

3. In the WHERE Clause (EXISTS vs. IN vs. ALL)

-- Query A: Correlated Subquery using EXISTS
-- Identifies doctors who have handled high-complexity surgeries exceeding KES 45,000
SELECT DISTINCT 
    k1.doctor_name
FROM kenyan_hospital_health_records k1
WHERE EXISTS (
    SELECT 1 
    FROM kenyan_hospital_health_records k2
    WHERE k2.doctor_name = k1.doctor_name 
      AND k2.treatment_plan = 'Surgery'
      AND k2.bill_amount_kes > 45000.00
);

-- Query B: Non-Correlated Subquery using IN
-- Finds patients treated in departments that experienced pending bills exceeding KES 49,000
SELECT 
    patient_id,
    first_name || ' ' || last_name AS patient_name,
    hospital_department,
    bill_amount_kes
FROM kenyan_hospital_health_records
WHERE hospital_department IN (
    SELECT DISTINCT hospital_department
    FROM kenyan_hospital_health_records
    WHERE payment_status = 'Pending' 
      AND bill_amount_kes > 49000.00
);

-- Query C: Subquery using > ALL
-- Identifies encounters with bills greater than ALL pediatric encounters
SELECT 
    patient_id,
    first_name || ' ' || last_name AS patient_name,
    hospital_department,
    bill_amount_kes
FROM kenyan_hospital_health_records
WHERE bill_amount_kes > ALL (
    SELECT bill_amount_kes 
    FROM kenyan_hospital_health_records
    WHERE hospital_department = 'Pediatrics'
);

• Explanation:
  • EXISTS halts processing for that doctor as soon as a single match is found, making it more efficient than scanning every row.
  • IN builds a discrete list of departments and checks for membership.
  • > ALL finds outlier bills across adult wards that exceed the single highest bill recorded anywhere in Pediatrics (KES 49,855.09).

Comparative Summary: CTEs vs. Window Functions vs. Subqueries

-- Analytical Need: Calculate Department Average Billing and Row Variances

-- Window Function Approach (Most concise, 1 table scan):
SELECT bill_amount_kes, AVG(bill_amount_kes) OVER(PARTITION BY hospital_department) FROM records;

-- CTE Approach (Modular, reusable in subsequent joins):
WITH DeptAvg AS (SELECT hospital_department, AVG(bill_amount_kes) AS avg_b FROM records GROUP BY 1)
SELECT r.bill_amount_kes, d.avg_b FROM records r JOIN DeptAvg d ON r.hospital_department = d.hospital_department;

-- Scalar Correlated Subquery Approach (Repeated execution per row):
SELECT bill_amount_kes, (SELECT AVG(bill_amount_kes) FROM records r2 WHERE r2.dept = r1.dept) FROM records r1;

Conclusion & Practical Engineering Best Practices

1. Window Functions replace complex self-joins when computing cumulative sums, rankings, or relative row metrics without collapsing the result set.
2. Inner vs. Outer Joins must be chosen deliberately to avoid dropping unmatched records (such as uninsured cash patients or newly added empty departments).
3. CTEs make multi-stage transformations easier to read and maintain compared to deeply nested subqueries, and enable tree traversal via recursion.
4. EXISTS is generally preferable to IN for correlated subqueries over large datasets because it short-circuits as soon as the first match is identified.