DEV Community: Mark Maina

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

Mark Maina — Sun, 18 Oct 2026 21:38:25 +0000

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

Basic CTE: Simplifies complex queries by breaking logic into readable named blocks.
Multiple CTEs: Chaining several temporary result sets using a single WITH clause separated by commas.
Recursive CTE: A CTE that references itself, ideal for organizational hierarchies or sequence generation.
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.

Data Relationships, Data Modelling Schemas, and Joins in Power BI

Mark Maina — Sun, 13 Sep 2026 12:13:01 +0000

Introduction

A successful Power BI solution depends on more than creating attractive charts and dashboards. The underlying data model determines how accurately data can be analysed, how easily DAX measures can be written, and how efficiently reports perform as the volume of data increases. Data modelling is therefore one of the most important stages in building a Power BI solution.

A well-designed model organizes data into logical tables, defines relationships between those tables, and controls how filters move through the model. It also reduces unnecessary duplication and makes reports easier to understand and maintain.

This article explains three common modelling approaches:Flat Table, Star Schema, and Snowflake Schema and examines fact and dimension tables, relationships, filter direction, Power Query joins, and the difference between merging tables and creating model relationships.

Flat Table

A flat table stores most or all required information in one table.

🟩 SALES
┌─────────┬──────────┬─────────┬────────────┬────────────┐
│ OrderID │ Customer │ Product │ Date       │ SalesAmount│
├─────────┼──────────┼─────────┼────────────┼────────────┤
│ 1001    │ John     │ Laptop  │ 01-Jan-26  │ $1,200     │
│ 1002    │ Mary     │ Monitor │ 02-Jan-26  │ $500       │
│ 1003    │ John     │ Mouse   │ 03-Jan-26  │ $50        │
└─────────┴──────────┴─────────┴────────────┴────────────┘

The major advantage is simplicity. There are no relationships to configure, making the approach suitable for small datasets, prototypes, or simple reports.

However, flat tables often contain substantial data redundancy. Customer names, product descriptions, locations, and other attributes may be repeated across thousands or millions of transactions. This increases model size and makes changes harder to manage.

For Power BI, a flat table can work well for small datasets, but it becomes less attractive as data volume and analytical requirements increase.

Star Schema

A star schema separates business events into a central fact table and descriptive information into surrounding dimension tables.

                         🟢 DimDate
                             │
                             │ 1:*
                             ▼
🔵 DimCustomer ── 1:* ── 🟣 FactSales ── *:1 ── 🟠 DimProduct
                             ▲
                             │
                             │ 1:*
                             │
                       🔴 DimLocation

The fact table contains transactions, while dimensions describe those transactions. This is generally the preferred structure for Power BI because it provides clear filter paths, good performance, and straightforward DAX.

Its main disadvantages are that it requires more modelling knowledge and relationships must be configured correctly.

Snowflake Schema

A snowflake schema extends the star schema by further normalizing dimensions.

🟢 DimCountry
     │
     │ 1:*
     ▼
🔴 DimLocation ── 1:* ── 🟣 FactSales ── *:1 ── 🟠 DimProduct
                           ▲
                           │
                           │ *:1
                           │
                     🔵 DimCustomer

For example, instead of storing Country directly in DimLocation, a separate DimCountry table may contain country information.

Snowflake schemas can reduce redundancy, especially in large hierarchical dimensions, but they introduce additional relationships and longer filter paths. Consequently, they can make DAX and troubleshooting more complicated.

Comparison: A flat table is simple but potentially redundant; a star schema balances simplicity and performance; a snowflake schema provides greater normalization but increases model complexity.

2. Fact Tables and Dimension Tables

The distinction between fact and dimension tables is fundamental to dimensional modelling.

Fact Tables

A fact table records measurable business events. It normally contains:

Foreign keys to dimensions
Transaction or event identifiers
Numeric values
Quantities
Costs
Revenue
Discounts
Other measures

Examples include:

FactSales
FactOrders
FactTransactions

The most important concept when designing a fact table is grain or granularity. Grain defines exactly what one row represents.

For example:

One row in FactSales represents one product sold on one sales transaction.

If this rule is unclear, calculations can become inconsistent because different rows may represent different levels of detail.

Dimension Tables

Dimension tables contain descriptive information used to categorize and filter facts.

Examples include:

DimCustomer
DimProduct
DimDate
DimLocation

For example, DimProduct might contain ProductID, ProductName, Category, Brand, and ProductGroup.

A typical business model might therefore look like:

                 🟢 DimDate
                     │
                     │
🔵 DimCustomer ── 🟣 FactSales ── 🟠 DimProduct
                     │
                     │
                 🔴 DimLocation

The fact table answers questions such as "How much was sold?", while dimensions answer "Who bought it?", "What was sold?", "When?", and "Where?"

3. Relationships in Power BI

A relationship defines how two tables are connected using a common column. Relationships are necessary because business information is normally distributed across multiple tables.

For example, DimCustomer contains customer details while FactSales contains transactions. CustomerID connects the two.

One-to-Many (1:*)

This is the most common relationship in a Power BI star schema.

🟢 DimCustomer                    🟣 FactSales
CustomerID (Unique)   1 ─────── * CustomerID (Repeated)

One customer can have many sales transactions. Therefore, CustomerID occurs once in DimCustomer but can occur many times in FactSales.

This should normally be the default relationship for dimension-to-fact modelling.

One-to-One (1:1)

Each record in one table corresponds to exactly one record in another.

🟢 CustomerProfile
CustomerID     1 ───── 1     🟣 CustomerAccount
CustomerID

This may be appropriate when two tables contain different sets of attributes for exactly the same entities. However, it should not be used simply because two tables happen to contain unique IDs.

If the tables represent the same business entity, combining them may sometimes be simpler.

Many-to-Many (:)

Multiple records in one table can correspond to multiple records in another.

🟠 Students       * ───── *       🟣 Courses

For example, one student can take many courses, while each course can contain many students.

Many-to-many relationships can be valid, but they should be used carefully because they can create ambiguous filtering and unexpected aggregation results. A bridge table is often preferable for complex many-to-many scenarios.

Keys and Referential Integrity

A primary key uniquely identifies a row in a table. A foreign key references a key in another table.

For example:

DimCustomer
CustomerID
101
102
103

versus:

FactSales
CustomerID
101
101
102
101
103

DimCustomer[CustomerID] must be unique, while FactSales[CustomerID] can repeat because the same customer can make multiple purchases.

Referential integrity means foreign-key values should correctly correspond to records in the related dimension. Missing or unmatched keys can produce blank or unexpected results in reports.

Power BI relationships can also be active or inactive. An active relationship is used automatically when filters propagate. An inactive relationship can be activated within specific DAX calculations using functions such as USERELATIONSHIP.

4. Filter Direction

Filter direction determines how filtering moves between related tables.

Single-Direction Filtering

This is generally recommended for star schemas.

🟢 DimProduct ────────► 🟣 FactSales
       Filter              Results

Suppose DimProduct[Category] = "Electronics" is selected. The filter travels from DimProduct to FactSales, restricting sales to electronics products.

This creates a predictable model with clear filter paths.

Both/Bidirectional Filtering

Bidirectional filtering allows filters to move in both directions.

🟢 DimProduct ◄──────► 🟣 FactSales

Although useful in specific scenarios, it should not be enabled unnecessarily. With several related tables, bidirectional relationships can create ambiguous filter paths, unexpected results, and additional model complexity.

For most dimensional models, filtering from dimensions toward facts provides the clearest design.

5. Joins in Power Query

A join combines information from two tables based on matching columns. In Power Query, this operation is performed using Merge Queries.

Consider:

Customers

CustomerID	CustomerName
1	John
2	Mary
3	David

Orders

OrderID	CustomerID	Amount
101	1	500
102	1	200
103	2	300
104	4	150

Customer 4 does not exist in the Customers table.

Inner Join

Returns only matching records from both tables.

Customers ∩ Orders
        🟢
      MATCHES

Expected customers: John and Mary.

CustomerID	CustomerName	OrderID	Amount
1	John	101	500
1	John	102	200
2	Mary	103	300

Left Outer Join

Keeps all records from the left table and matching records from the right table.

🟢 Customers  █████████████
🟣 Orders          ███████

David remains even though he has no order.

Right Outer Join

Keeps all records from the right table and matching records from the left table.

Order 104 remains even though Customer 4 has no matching customer record.

Full Outer Join

Keeps all records from both tables.

🟢 Customers  ████████
              ███████████  🟣 Orders

The result includes customers without orders and orders without matching customers.

Left Anti Join

Returns records from the left table that have no match in the right table.

Customers − Orders

Result:

CustomerID	CustomerName
3	David

This is useful for identifying customers who have never placed an order.

Right Anti Join

Returns records from the right table that have no match in the left table.

Orders − Customers

Result:

OrderID	CustomerID	Amount
104	4	150

This is particularly useful for data-quality checks because it identifies orders containing an invalid or missing customer reference.

6. Power Query Joins vs Power BI Relationships

Although both concepts connect data, a Power Query merge and a Power BI relationship serve different purposes.

Power Query Merge

A merge happens during the data preparation/transformation stage.

Customers + Orders
       ↓
 Power Query Merge
       ↓
Combined Table

The result can physically bring columns from one table into another. For example, CustomerName can be added to an Orders query.

Power BI Relationship

A relationship is created in the data model after data loading.

DimCustomer ───── 1:* ───── FactSales

The tables remain separate. Power BI understands that the tables are connected and uses the relationship for filtering and calculations.

Therefore:

Merge combines data; relationships connect data logically.

A merge is appropriate when a column is genuinely required in the same query, when transforming source data, or when preparing a table for a specific analytical requirement.

However, excessive merging can create very wide tables, duplicate descriptive information, increase redundancy, and make the model harder to maintain.

Keeping fact and dimension tables separate is usually preferable in a BI model because each table has a clear responsibility and relationships allow the same dimension to be reused across multiple facts.

7. Recommended Power BI Model

For a typical business intelligence project, I would recommend a Star Schema with a central fact table and several dimension tables.

                         🟢 DimDate
                            │
                            │ 1:*
                            ▼
🔵 DimCustomer ── 1:* ── 🟣 FactSales ── *:1 ── 🟠 DimProduct
                            ▲
                            │
                            │ *:1
                            │
                       🔴 DimLocation

The recommended relationship design would normally be:

One-to-many relationships from dimensions to facts.
Single-direction filtering from dimensions toward fact tables.
Unique primary keys in dimension tables.
Foreign keys in fact tables.
Clearly defined fact-table grain.
Separate fact and dimension tables rather than unnecessary merges.
Inactive relationships only where there is a genuine business requirement.

The star schema is preferable because it provides a strong balance between performance, simplicity, scalability, and maintainability. It reduces redundancy compared with a flat table while avoiding much of the complexity introduced by a heavily normalized snowflake structure.

It also makes DAX easier to understand. Measures such as:

Total Sales = SUM(FactSales[SalesAmount])

can naturally respond to filters from DimCustomer, DimProduct, DimDate, or DimLocation.

From a reporting perspective, the model is intuitive: users select descriptive fields from dimensions and measures from the fact table. From a performance perspective, the model avoids unnecessary duplication and provides straightforward filter propagation. From a maintenance perspective, changes to customer, product, or location attributes can be managed in their respective dimensions rather than repeatedly across transaction records.

Conclusion

Data modelling is the foundation of an effective Power BI solution. While flat tables can be useful for small and simple datasets, a star schema is generally the strongest choice for production BI models. It separates measurable business events from descriptive attributes, creates predictable relationships, and supports efficient DAX calculations.

Understanding relationship cardinality is equally important. One-to-many relationships should normally form the backbone of a dimensional model, while one-to-one and many-to-many relationships should be introduced only when the business structure justifies them. Single-direction filtering should generally be preferred because it creates predictable filter propagation.

Power Query joins serve a different purpose from model relationships. Joins are primarily used during data preparation to combine or compare datasets, whereas relationships connect tables within the analytical model without physically combining them.

DEV Community: Mark Maina

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

Introduction

1. SQL Window Functions

Core Anatomy of a Window Function

Types of Window Functions

A. Ranking and Distribution Functions

B. Value Functions

C. Aggregate Window Functions

2. SQL Joins

Overview of Join Types

Practical Hospital Comparison

Inner Join vs. Left Join

Self Join Example

3. Common Table Expressions (CTEs)

Types and Uses of CTEs

Practical Example: Multiple CTEs with Aggregations

CTEs vs. Subqueries

4. SQL Subqueries

Types of Subqueries

Subqueries Across Clauses

A. Subquery in the SELECT Clause (Scalar)

B. Subquery in the FROM Clause (Derived Table)

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

Data Relationships, Data Modelling Schemas, and Joins in Power BI

Flat Table

Star Schema

Snowflake Schema

2. Fact Tables and Dimension Tables

Fact Tables

Dimension Tables

3. Relationships in Power BI

One-to-Many (1:*)

One-to-One (1:1)

Many-to-Many (:)

Keys and Referential Integrity

4. Filter Direction

Single-Direction Filtering

Both/Bidirectional Filtering

5. Joins in Power Query

Customers

Orders

Inner Join

Left Outer Join

Right Outer Join

Full Outer Join

Left Anti Join

Right Anti Join

6. Power Query Joins vs Power BI Relationships

Power Query Merge

Power BI Relationship

7. Recommended Power BI Model

Conclusion

A. Subquery in the `SELECT` Clause (Scalar)

B. Subquery in the `FROM` Clause (Derived Table)

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