Advanced SQL in Healthcare Analytics: A Comprehensive Technical Guide Using Our Hospital Dataset

Introduction

In modern healthcare management, data is critical to improving patient outcomes, optimizing hospital operations, and managing financial performance. Using our hospital dataset—which encompasses tables for patients, doctors, department, diagnosis, wards, procedures, and visits—this technical article explores advanced SQL concepts. We will cover Window Functions, Joins, Common Table Expressions (CTEs), and Subqueries, demonstrating their practical applications through real-world hospital scenarios.

---

1. SQL Window Functions

Window functions perform calculations across a set of table rows that are related to the current row. Unlike aggregate functions used with GROUP BY, window functions do not collapse rows into a single summary row. Instead, every individual row retains its identity while displaying the calculated window result.

Core Anatomy of a Window Function

• OVER() Clause: Defines the window or set of rows over which the function operates.
• PARTITION BY: Divides the result set into partitions (groups) to which the window function is applied independently.
• ORDER BY: Determines the logical order of rows within each partition.
• Window Frames: Subsets of rows within a partition (e.g., ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) that define precisely which rows feed into the calculation for the current row.

---

Types of Window Functions

A. Ranking and Distribution Functions

• ROW_NUMBER(): Assigns a unique sequential integer to rows within a partition starting at 1, regardless of ties.
• RANK(): Assigns a rank to each row within a partition, leaving gaps in ranking numbers when there are ties.
• DENSE_RANK(): Assigns ranks without gaps; tied rows share the same rank, and the next rank is consecutive.
• NTILE(n): Distributes rows in an ordered partition into a specified number of roughly equal groups (n).

Hospital Scenario: Ranking patient visits by their total bill within each department to identify high-cost medical interactions.

SELECT 
    visit_id,
    department_id,
    total_bill_kes,
    ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY total_bill_kes DESC) AS row_num,
    RANK() OVER (PARTITION BY department_id ORDER BY total_bill_kes DESC) AS rank_val,
    DENSE_RANK() OVER (PARTITION BY department_id ORDER BY total_bill_kes DESC) AS dense_rank_val,
    NTILE(3) OVER (PARTITION BY department_id ORDER BY total_bill_kes DESC) AS bill_tertile
FROM morning.visits;

• Explanation: This query evaluates every hospital visit partitioned by department_id. It assigns a row number, rank, dense rank, and splits visits into three billing tiers (NTILE(3)) ordered from highest to lowest bill.
• Expected Result: Each department will list its visits sorted by bill amount. Ties in total_bill_kes receive identical RANK() and DENSE_RANK() values, but ROW_NUMBER() breaks ties arbitrarily to ensure uniqueness.

---

B. Value Functions

• LAG(): Accesses data from a previous row at a specified offset within the partition.
• LEAD(): Accesses data from a subsequent row at a specified offset.
• FIRST_VALUE(): Returns the value evaluated at the very first row of the window frame.
• LAST_VALUE(): Returns the value evaluated at the last row of the window frame.

Hospital Scenario: Tracking patient admission history by comparing a patient's current visit bill with their immediately preceding visit bill.

SELECT 
    patient_id,
    visit_id,
    visit_date,
    total_bill_kes,
    LAG(total_bill_kes, 1) OVER (PARTITION BY patient_id ORDER BY visit_date) AS previous_visit_bill,
    LEAD(total_bill_kes, 1) OVER (PARTITION BY patient_id ORDER BY visit_date) AS next_visit_bill
FROM morning.visits;

• Explanation: For every patient, LAG retrieves the bill from their chronological previous visit, and LEAD retrieves the bill from their next visit.
• Expected Result: Patients with multiple visits will show their historical billing trend side-by-side with current records, helping administrators monitor cost escalation per patient over time.

---

C. Aggregate Window Functions

• SUM() / AVG() / COUNT() / MIN() / `MAX()`: Compute running totals, moving averages, or running maximums without grouping away row-level details.

Hospital Scenario: Calculating a running total of hospital revenue accumulated date by date.

SELECT 
    visit_id,
    visit_date,
    total_bill_kes,
    SUM(total_bill_kes) OVER (ORDER BY visit_date, visit_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS cumulative_revenue
FROM morning.visits;

• Explanation: The SUM() function uses a framing clause (ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) to aggregate all bills from the earliest visit date up to the current row.
• Expected Result: A running balance of total hospital earnings streamed chronologically across all logged patient visits.

---

2. SQL Joins

SQL Joins combine columns from two or more tables based on a related column between them.

Overview of Join Types

• INNER JOIN: Returns only rows where there is a match in both tables.
• LEFT JOIN: Returns all rows from the left table, and matching rows from the right table (unmatched right columns return NULL).
• RIGHT JOIN: Returns all rows from the right table, and matching rows from the left table.
• FULL OUTER JOIN: Returns all rows when there is a match in either the left or right table.
• CROSS JOIN: Produces the Cartesian product (every combination of rows) of both tables.
• SELF JOIN: A table joined to itself, useful for hierarchical data or comparing rows within the same table.

---

Practical Hospital Comparison

Inner Join vs. Left Join

Suppose we want to list all doctors and their corresponding visits. An INNER JOIN excludes doctors who have no recorded visits, whereas a LEFT JOIN includes all doctors.

-- INNER JOIN: Only doctors with recorded visits
SELECT d.doctor_name, v.visit_id, v.total_bill_kes
FROM morning.doctors d
INNER JOIN morning.visits v ON d.doctor_id = v.doctor_id;

-- LEFT JOIN: All doctors, including those with zero visits
SELECT d.doctor_name, v.visit_id, v.total_bill_kes
FROM morning.doctors d
LEFT JOIN morning.visits v ON d.doctor_id = v.doctor_id;

• Explanation: The INNER JOIN filters out any doctor missing from the visits table. The LEFT JOIN preserves every doctor from the doctors table, displaying NULL for visit_id and total_bill_kes if a doctor has not handled any visits.

---

Self Join Example

Hospital Scenario: Finding pairs of patients who share the same age and county of origin.

SELECT 
    p1.patient_name AS patient_one,
    p2.patient_name AS patient_two,
    p1.patient_age,
    p1.county
FROM morning.patients p1
INNER JOIN morning.patients p2 
    ON p1.patient_age = p2.patient_age 
    AND p1.county = p2.county 
    AND p1.patient_id < p2.patient_id;

• Explanation: The patients table is joined to itself (p1 and p2) to pair patients matching in age and county. The condition p1.patient_id < p2.patient_id prevents duplicate inverse pairings and self-matching.

---

3. Common Table Expressions (CTEs)

A Common Table Expression (CTE) is a temporary named result set defined within the execution scope of a single SELECT, INSERT, UPDATE, or DELETE statement.

Types and Uses of CTEs

1. Basic CTE: Simplifies complex queries by breaking logic into readable named blocks.
2. Multiple CTEs: Chaining several temporary result sets using a single WITH clause separated by commas.
3. Recursive CTE: A CTE that references itself, ideal for organizational hierarchies or sequence generation.
4. CTEs with Joins and Aggregations: Pre-aggregating data before joining it back to dimension tables.

---

Practical Example: Multiple CTEs with Aggregations

Hospital Scenario: Identifying departments whose average patient satisfaction score is above the overall hospital average.

WITH DeptAvg AS (
    SELECT 
        department_id,
        AVG(patient_satisfaction_score) AS avg_satisfaction
    FROM morning.visits
    GROUP BY department_id
),
OverallAvg AS (
    SELECT AVG(patient_satisfaction_score) AS overall_satisfaction 
    FROM morning.visits
)
SELECT 
    d.department_name,
    da.avg_satisfaction,
    oa.overall_satisfaction
FROM DeptAvg da
CROSS JOIN OverallAvg oa
INNER JOIN morning.department d ON da.department_id = d.department_id
WHERE da.avg_satisfaction > oa.overall_satisfaction;

• Explanation:
• DeptAvg calculates the mean satisfaction score per department.
• OverallAvg computes the global hospital satisfaction average.

• The main query joins these CTEs with the department table to filter out departments performing above average.

• Expected Result: A clean, readable list of high-performing departments that exceed the facility's baseline patient satisfaction mark.

---

CTEs vs. Subqueries

• Readability: CTEs improve query structure by separating sub-queries into named logical steps at the top of the statement. Subqueries nested deeply inside FROM or WHERE clauses can be hard to maintain.
• Reusability: A CTE can be referenced multiple times within the main query body, whereas standard inline subqueries cannot be reused without rewriting them.
• Recursion: CTEs support recursive execution (WITH RECURSIVE), which subqueries cannot do natively.

---

4. SQL Subqueries

A subquery is a query nested inside another SQL statement (such as SELECT, FROM, or WHERE).

Types of Subqueries

• Scalar Subquery: Returns exactly one row and one column (a single value).
• Non-Correlated Subquery: Independent of the outer query and can be executed entirely on its own.
• Correlated Subquery: References columns from the outer query; it executes once for every row processed by the outer query.
• Nested Subquery: A subquery inside another subquery.

---

Subqueries Across Clauses

A. Subquery in the SELECT Clause (Scalar)

SELECT 
    visit_id,
    total_bill_kes,
    (SELECT AVG(total_bill_kes) FROM morning.visits) AS overall_avg_bill
FROM morning.visits;

• Explanation: Computes the overall average hospital bill dynamically as a scalar value attached to every individual visit row for side-by-side comparison.

B. Subquery in the FROM Clause (Derived Table)

SELECT 
    dept_summary.department_id,
    dept_summary.total_revenue
FROM (
    SELECT department_id, SUM(total_bill_kes) AS total_revenue
    FROM morning.visits
    GROUP BY department_id
) AS dept_summary
WHERE dept_summary.total_revenue > 50000;

• Explanation: Uses an inline subquery in the FROM clause as a temporary derived table to filter departments generating over KES 50,000 in total revenue.

C. Subquery in the WHERE Clause (IN, EXISTS, ANY, ALL)

• IN: Matches values against a set returned by a subquery.
• EXISTS: Tests for the existence of any rows in a subquery.
• ANY / `ALL`: Compares a value to a set of numeric values.

Hospital Scenario: Finding patients who have visited for emergency care using an EXISTS subquery.

SELECT p.patient_name, p.county
FROM morning.patients p
WHERE EXISTS (
    SELECT 1 
    FROM morning.visits v 
    WHERE v.patient_id = p.patient_id 
      AND v.admission_type = 'Emergency'
);

• Explanation: This correlated subquery checks the visits table for each patient to see if an emergency admission exists. If true, the patient details are returned.
• Expected Result: A targeted list of patients who have experienced emergency admissions, useful for clinical review and emergency preparedness audits.