Window Functions, Joins, Common Table Expressions (CTEs), and Subqueries in SQL.

Introduction

SQL becomes much more useful when you start applying it to real-world data. Beyond retrieving records, it helps analysts compare results, identify patterns, calculate performance measures, and answer questions that support decision-making.

Using the Kenya Hospital Power BI report as a reference, this article explores four useful SQL techniques: window functions, joins, Common Table Expressions (CTEs), and subqueries. Each technique serves a different purpose, and understanding when to use it makes it easier to write queries that are clear, accurate, and useful.

The examples focus on hospital visits, billing, departments, doctors, and patient information, using PostgreSQL syntax where necessary.

Dataset and SQL Notes

The Kenya Hospital Power BI report references four tables relevant to these examples:

• visits_fact: is the facts table which Contains information like, such as visit_id, visit_date, total_bill_kes, and length_of_stay_days.
• dim_departments: Contains information like department_id,department_name.
• dim_doctors: Contains information likedoctor_id,doctor_name.
• dim_patients: Contains information like patient_id,patient_gender and county.
• dim_ward: contains information like ward_id and ward_name

1. SQL Window Functions

Window functions are useful when you want to analyse individual records while also considering other related records.

For example, you might want to see each hospital visit alongside the total billing for its department, rank visits according to their bills, or compare a patient's current bill with their previous visit.

Unlike GROUP BY, which combines records into summary rows, a window function keeps the original rows in the result.

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

The OVER() clause tells PostgreSQL which rows a window function should work with.

• PARTITION BY divides records into groups, such as separate groups for each department.
• ORDER BY determines the sequence in which records are considered by the function.
• A window frame specifies which rows within that sequence are included in a calculation.

For example, suppose you want to calculate total billing for each department without losing the individual visit records.

SELECT
    visit_id,
    department_id,
    total_bill_kes,
    SUM(total_bill_kes) OVER (
        PARTITION BY department_id
    ) AS department_billing
FROM visits_fact;

Here, PostgreSQL calculates the total bill for each department and displays that total alongside every visit belonging to the department. The department total is repeated for each of its visits.

A window frame is especially useful when calculating running totals. For example:

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 running_billing
FROM visits_fact;

This calculates a running total from the first visit in the specified order up to the current visit.

The ORDER BY inside OVER() controls the calculation, while an ORDER BY at the end of the query controls how the final results are displayed. They serve different purposes.

Ranking and Distribution Functions

ROW_NUMBER()

ROW_NUMBER() assigns a unique sequential number to each row within a specified group. It is useful when identifying the latest visit for each patient or arranging visits by billing amount.

SELECT
    visit_id,
    patient_id,
    visit_date,
    total_bill_kes,
    ROW_NUMBER() OVER (
        PARTITION BY patient_id
        ORDER BY visit_date DESC, visit_id DESC
    ) AS visit_recency_rank
FROM visits_fact;

Each patient's visits are numbered from the most recent to the oldest. If two visits share the same date, visit_id provides an additional ordering criterion, assuming it uniquely identifies each visit.

The numbering restarts for every patient, making it useful for identifying the most recent visit by filtering for visit_recency_rank = 1.

RANK()

RANK() assigns positions based on a value. When two or more records have the same value, they receive the same rank, and the next rank is skipped.

SELECT
    visit_id,
    total_bill_kes,
    RANK() OVER (
        ORDER BY total_bill_kes DESC
    ) AS bill_rank
FROM visits_fact;

If the bills are KSh 9,000, KSh 9,000, and KSh 7,000, the resulting ranks are 1, 1, and 3.

This is useful when comparing visits by billing amount and allowing ties.

DENSE_RANK()

DENSE_RANK() works like RANK(), except that it does not skip a rank after a tie.

SELECT
    visit_id,
    total_bill_kes,
    DENSE_RANK() OVER (
        ORDER BY total_bill_kes DESC
    ) AS bill_rank
FROM visits_fact;

Using the same bills of KSh 9,000, KSh 9,000, and KSh 7,000, the ranks would be 1, 1, and 2.

The difference matters when creating rankings where consecutive positions are preferred.

NTILE(n)

NTILE() divides ordered records into a specified number of approximately equal groups. It can be used to classify hospital visits into billing quartiles.

SELECT
    visit_id,
    total_bill_kes,
    NTILE(4) OVER (
        ORDER BY total_bill_kes
    ) AS bill_quartile
FROM visits_fact;

The result assigns each visit to one of four groups, starting with the lower bills and moving towards the higher bills. Depending on the number of records, the groups may not contain exactly the same number of visits.

Navigation Functions

Navigation functions help you compare records across an ordered set without having to join the table to itself.

LAG()

LAG() retrieves a value from a previous row in the specified order. In hospital billing analysis, it can help compare a patient's current bill with their previous bill.

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

The first visit for each patient has no previous bill, so the result is NULL. Subsequent visits display the bill from the preceding visit.

LEAD()

LEAD() does the opposite of LAG(): it retrieves a value from a later row.

SELECT
    visit_id,
    patient_id,
    visit_date,
    LEAD(visit_date) OVER (
        PARTITION BY patient_id
        ORDER BY visit_date, visit_id
    ) AS next_visit_date
FROM visits_fact;

This shows the next recorded visit date for each patient. The final visit in each patient's sequence has no subsequent visit, so its result is NULL.

FIRST_VALUE()

FIRST_VALUE() returns the first value in an ordered window. It can help identify the earliest recorded visit for each department.

SELECT
    visit_id,
    department_id,
    visit_date,
    FIRST_VALUE(visit_date) OVER (
        PARTITION BY department_id
        ORDER BY visit_date, visit_id
        ROWS BETWEEN UNBOUNDED PRECEDING
        AND UNBOUNDED FOLLOWING
    ) AS department_first_visit
FROM visits_fact;

Every visit in a department displays the department's earliest visit date. The explicit frame makes it clear that the calculation considers the entire department partition.

LAST_VALUE()

LAST_VALUE() retrieves the last value in the current window frame. Its behaviour can be confusing if the frame is not specified, because PostgreSQL's default frame generally ends at the current row's last peer.

SELECT
    visit_id,
    department_id,
    visit_date,
    LAST_VALUE(visit_date) OVER (
        PARTITION BY department_id
        ORDER BY visit_date, visit_id
        ROWS BETWEEN UNBOUNDED PRECEDING
        AND UNBOUNDED FOLLOWING
    ) AS department_last_visit
FROM visits_fact;

With the full frame specified, each visit displays the latest recorded visit date for its department.

Aggregate Window Functions

Aggregate functions can also be used as window functions. This makes it possible to calculate totals, averages, counts, minimums, and maximums without combining the original records into summary rows.

SUM()

SUM() adds values across a window. When combined with an ordered frame, it can calculate a running total of hospital bills.

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 running_billing
FROM visits_fact;

The running total increases as each visit is processed in date order. This can help track how recorded billing accumulates over time.

AVG()

AVG() calculates the average value across a window.

SELECT
    visit_id,
    department_id,
    total_bill_kes,
    AVG(total_bill_kes) OVER (
        PARTITION BY department_id
    ) AS department_avg_bill
FROM visits_fact;

Each visit is displayed alongside the average bill for its department. By default, AVG() ignores NULL bill values.

COUNT()

COUNT() can count the number of rows in a window or the number of non-NULL values in a particular column.

SELECT
    visit_id,
    department_id,
    COUNT(*) OVER (
        PARTITION BY department_id
    ) AS department_visit_count
FROM visits_fact;

The result shows how many visit records belong to each department. Unlike COUNT(column_name), COUNT(*) counts rows even when some columns contain missing values.

MIN()

MIN() identifies the smallest value within a window.

SELECT
    visit_id,
    department_id,
    total_bill_kes,
    MIN(total_bill_kes) OVER (
        PARTITION BY department_id
    ) AS department_lowest_bill
FROM visits_fact;

Each visit displays the lowest non-NULL bill recorded in its department.

MAX()

MAX() identifies the largest value within a window.

SELECT
    visit_id,
    department_id,
    total_bill_kes,
    MAX(total_bill_kes) OVER (
        PARTITION BY department_id
    ) AS department_highest_bill
FROM visits_fact;

This allows an analyst to compare individual bills with the highest bill recorded in the same department.

Window Functions Compared with GROUP BY

Although window functions and GROUP BY can both calculate aggregates, they produce different results.

SELECT
    department_id,
    SUM(total_bill_kes) AS total_billing
FROM visits_fact
GROUP BY department_id;

This query returns one row for each department.

By comparison:

SELECT
    visit_id,
    department_id,
    total_bill_kes,
    SUM(total_bill_kes) OVER (
        PARTITION BY department_id
    ) AS department_billing
FROM visits_fact;

This query retains every visit and adds the department's total billing to each row.

Use GROUP BY when you need a summary and window functions when you need to keep the individual records visible.

2. SQL Joins

Hospital data is often stored in several related tables. Visit records may contain billing amounts and dates, while separate tables hold department names, doctor names, and patient characteristics.

A JOIN brings information from these tables together using matching keys. The type of join determines which records appear in the result.

INNER JOIN

An INNER JOIN returns only records that have matching values in both tables.

SELECT
    v.visit_id,
    d.department_name,
    v.total_bill_kes
FROM visits_fact v
INNER JOIN dim_departments d
    ON v.department_id = d.department_id;

This query adds department names to visit records. Visits without a matching department are excluded.

LEFT JOIN

A LEFT JOIN keeps every record from the left table, even when there is no matching record in the right table.

SELECT
    v.visit_id,
    d.department_name
FROM visits_fact v
LEFT JOIN dim_departments d
    ON v.department_id = d.department_id;

All visits remain in the result. If a visit has no matching department, department_name appears as NULL.

This is particularly useful when checking for missing or incorrect relationships in hospital data.

RIGHT JOIN

A RIGHT JOIN keeps every record from the right table and includes matching records from the left table.

SELECT
    v.visit_id,
    d.department_name
FROM visits_fact v
RIGHT JOIN dim_departments d
    ON v.department_id = d.department_id;

Every department appears, including departments with no recorded visits. In those cases, the visit fields are NULL.

The same result can often be written using a LEFT JOIN with the tables reversed, which some analysts find easier to read.

FULL OUTER JOIN

A FULL OUTER JOIN returns matching records and unmatched records from both tables.

SELECT
    v.visit_id,
    d.department_name
FROM visits_fact v
FULL OUTER JOIN dim_departments d
    ON v.department_id = d.department_id;

This can help reconcile visit records against department records without losing unmatched entries from either side.

PostgreSQL supports FULL OUTER JOIN directly, so no workaround is required.

CROSS JOIN

A CROSS JOIN returns every possible combination of rows from two sets.

For example, you could create a list of all department and gender combinations:

SELECT
    d.department_name,
    g.gender
FROM dim_departments d
CROSS JOIN (
    SELECT DISTINCT patient_gender AS gender
    FROM dim_patients
) g;

The output contains every department paired with every distinct gender in the patient table, even if some combinations have no recorded visits.

This can be useful when building a reporting structure that needs to display zero-count categories. Actual visit counts would need to be calculated separately.

SELF JOIN

A self join connects a table to itself. It is useful when comparing records within the same dataset.

For example, the following query finds pairs of visits belonging to the same patient, where one visit occurred before the other.

SELECT
    a.patient_id,
    a.visit_id AS earlier_visit,
    b.visit_id AS later_visit
FROM visits_fact a
JOIN visits_fact b
    ON a.patient_id = b.patient_id
   AND (a.visit_date, a.visit_id)
       < (b.visit_date, b.visit_id);

A patient with several visits may appear in multiple pairs. Using both the visit date and visit ID provides a consistent ordering when the identifiers are unique and the dates are available. Visits with the same date can also be ordered by visit_id.

Checking Join Keys

Before running these examples, confirm the actual relationship keys in the hospital data model. The key names shown above are assumptions, not verified column names.

Where possible, use stable identifiers rather than joining on department or doctor names. Names can be duplicated or entered differently, and this can produce extra rows that inflate billing totals.

After joining tables, compare row counts and totals against the original dataset to check that the results remain accurate.

3. Common Table Expressions (CTEs)

A Common Table Expression, or CTE, allows you to give a query result a temporary name and then use it in the statement that follows.

CTEs are especially useful when a query involves several steps. Rather than putting all the logic into one long statement, you can separate the work into smaller sections that are easier to understand and maintain.

Basic CTE

Suppose you want to identify visits with bills of at least KSh 20,000.

WITH high_cost_visits AS (
    SELECT
        visit_id,
        department_id,
        total_bill_kes
    FROM visits_fact
    WHERE total_bill_kes >= 20000
)
SELECT *
FROM high_cost_visits
ORDER BY total_bill_kes DESC;

The CTE first identifies the visits that meet the billing threshold. The main query then displays those records, starting with the highest bill.

The KSh 20,000 threshold is only an example and should not be interpreted as an official hospital classification.

Using Multiple CTEs

You can define more than one CTE when different calculations need to be performed before the final result is produced.

WITH department_totals AS (
    SELECT
        department_id,
        SUM(total_bill_kes) AS total_billing
    FROM visits_fact
    GROUP BY department_id
),
department_counts AS (
    SELECT
        department_id,
        COUNT(*) AS visit_count
    FROM visits_fact
    GROUP BY department_id
)
SELECT
    t.department_id,
    t.total_billing,
    c.visit_count
FROM department_totals t
JOIN department_counts c
    ON c.department_id = t.department_id;

The first CTE calculates total billing for each department, while the second counts the visits. The final query combines these results into one department-level summary.

Recursive CTEs

A recursive CTE refers to its own result to generate additional rows. It is often used for hierarchical data, such as organisational structures, or for generating sequences of numbers and dates.

The following example generates seven consecutive integers, from 0 to 6:

WITH RECURSIVE day_offsets(n) AS (
    SELECT 0

    UNION ALL

    SELECT n + 1
    FROM day_offsets
    WHERE n < 6
)
SELECT n
FROM day_offsets
ORDER BY n;

The query begins with 0 and repeatedly adds 1 until it reaches 6.

This technique can be adapted to generate date offsets for a reporting calendar. In PostgreSQL, the RECURSIVE keyword is required for a recursive CTE. Other database systems may use different syntax or impose different recursion limits.

Combining CTEs, Joins, and Aggregations

CTEs can also simplify hospital reporting queries that involve several measures.

WITH visit_summary AS (
    SELECT
        department_id,
        COUNT(*) AS visits,
        SUM(total_bill_kes) AS billed_kes,
        AVG(length_of_stay_days) AS avg_stay_days
    FROM visits_fact
    GROUP BY department_id
)
SELECT
    d.department_name,
    s.visits,
    s.billed_kes,
    s.avg_stay_days
FROM visit_summary s
JOIN dim_departments d
    ON d.department_id = s.department_id
ORDER BY s.billed_kes DESC;

The CTE calculates the number of visits, total billing, and average length of stay for each department. The main query adds the department names and sorts the departments by billing.

This structure keeps the aggregation separate from the descriptive information, making the query easier to follow.

CTEs Compared with Subqueries

CTEs and subqueries can often solve the same problem, but they organize the SQL differently.

A subquery is placed inside another statement. A CTE is defined at the beginning using WITH, giving the intermediate result a meaningful name.

For a simple calculation, a subquery may be shorter. When a query contains several stages, CTEs often make the logic easier to follow and maintain.

Neither method is automatically faster. PostgreSQL's query planner determines how the query is executed. Depending on the query and PostgreSQL version, a CTE may be inlined into the main query or materialized, so a CTE is not necessarily stored as a separate physical table.

4. SQL Subqueries

A subquery is a SELECT statement placed inside another SQL statement. It allows the result of one query to help determine what another query returns.

Subqueries can calculate a single value, provide a list for filtering, check whether related records exist, or produce a temporary result that the outer query can use.

Scalar Subquery

A scalar subquery returns a single value. For example, you can display the hospital-wide average bill alongside each visit.

SELECT
    visit_id,
    total_bill_kes,
    (
        SELECT AVG(total_bill_kes)
        FROM visits_fact
    ) AS hospital_avg_bill
FROM visits_fact;

The inner query calculates the average bill, and the outer query displays that value for each visit.

A scalar subquery must return one column and at most one row. If it returns multiple rows in a context requiring a single value, PostgreSQL will report an error.

Non-Correlated Subquery

A non-correlated subquery does not depend on values from the outer query. It can be written and understood independently.

SELECT
    visit_id,
    total_bill_kes
FROM visits_fact
WHERE total_bill_kes > (
    SELECT AVG(total_bill_kes)
    FROM visits_fact
);

The inner query calculates the overall average bill. The outer query then returns visits whose bills exceed that average.

This provides a simple way to identify visits with above-average billing.

Correlated Subquery

A correlated subquery refers to a value from the outer query. This allows the inner calculation to be evaluated in relation to each outer record.

For example, you can find visits whose bills are higher than the average bill for their own department.

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

The inner query calculates the average bill for the department associated with the current visit. The outer query keeps only visits that exceed that department's average.

This is different from comparing every visit against the hospital-wide average. PostgreSQL's query planner may also transform how the correlated query is executed.

Nested Subqueries

A nested subquery contains another subquery. This is useful when one result depends on a calculation performed inside another.

SELECT
    visit_id,
    department_id,
    total_bill_kes
FROM visits_fact
WHERE department_id IN (
    SELECT department_id
    FROM visits_fact
    GROUP BY department_id
    HAVING AVG(total_bill_kes) > (
        SELECT AVG(total_bill_kes)
        FROM visits_fact
    )
);

The innermost query calculates the hospital-wide average bill. The middle query identifies departments whose average bill is higher than that figure. Finally, the outer query returns visits from those departments.

Breaking the logic into these stages makes it easier to understand how the final records are selected.

IN

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

SELECT
    visit_id,
    department_id
FROM visits_fact
WHERE department_id IN (
    SELECT department_id
    FROM visits_fact
    GROUP BY department_id
    HAVING COUNT(*) >= 100
);

This query returns visits from departments with at least 100 recorded visit rows.

One important detail is that NOT IN can behave unexpectedly when its subquery returns NULL. In such cases, NOT EXISTS may be a safer alternative.

EXISTS

EXISTS checks whether a subquery returns at least one row. It is useful when you want to find records with a related entry without needing to retrieve the matching row's details.

SELECT
    d.department_id,
    d.department_name
FROM dim_departments d
WHERE EXISTS (
    SELECT 1
    FROM visits_fact v
    WHERE v.department_id = d.department_id
);

The query returns departments that have at least one matching visit. Departments without visits are excluded.

ANY and ALL

ANY and ALL allow a value to be compared with a set of values returned by a subquery.

ANY makes the comparison true if it succeeds for at least one value. For example:

SELECT
    visit_id,
    total_bill_kes
FROM visits_fact
WHERE total_bill_kes > ANY (
    SELECT total_bill_kes
    FROM visits_fact
    WHERE department_id = 3
);

This returns visits whose bills are higher than at least one bill in department 3.

ALL requires the comparison to be true for every value in the returned set.

SELECT
    visit_id,
    total_bill_kes
FROM visits_fact
WHERE total_bill_kes > ALL (
    SELECT total_bill_kes
    FROM visits_fact
    WHERE department_id = 3
);

When the comparison values are non-NULL and the subquery is non-empty, this returns visits with bills higher than every bill in department 3.

Be careful with NULL values and empty result sets, because SQL's comparison rules affect the outcome. In PostgreSQL, ALL evaluates to true when the subquery returns no rows, while ANY evaluates to false.

Subqueries in SELECT, FROM, and WHERE

Subqueries can appear in different parts of a query depending on what you need to accomplish.

In the SELECT clause, a scalar subquery can add a calculated value to every row:

SELECT
    visit_id,
    total_bill_kes,
    (
        SELECT AVG(total_bill_kes)
        FROM visits_fact
    ) AS avg_bill
FROM visits_fact;

In the FROM clause, a subquery can produce a derived table for the outer query:

SELECT
    x.department_id,
    x.avg_bill
FROM (
    SELECT
        department_id,
        AVG(total_bill_kes) AS avg_bill
    FROM visits_fact
    GROUP BY department_id
) x
WHERE x.avg_bill > 10000;

This first calculates the average bill for each department, then returns departments whose average exceeds KSh 10,000.

In the WHERE clause, a subquery can filter records based on a calculated value, a list of qualifying values, or the existence of related records. The average-bill, IN, and EXISTS examples above demonstrate these approaches.

Choosing the Right SQL Technique

Each of these techniques solves a different type of problem, and choosing the right one depends on the result you want.

• JOINs: Use them when you need to combine related tables, such as adding department names to hospital visit records.
• GROUP BY: Use it when you want summary results, such as total billing for each department.
• Window functions: Use them when you need rankings, running totals, previous-visit comparisons, or department-level statistics while retaining individual records.
• CTEs: Use them to organise complex queries into clear, manageable stages.
• Subqueries: Use them when one query needs a value, list, existence check, or derived result from another query.

For hospital reporting, getting the query to run is only part of the work. You also need to check that the results are accurate.

After joining tables, compare the row counts and billing totals with the original data. Confirm that the relationship keys are correct, check for missing values, and make sure a one-to-many relationship has not duplicated visits. These checks are particularly important when the results will be used for operational reporting or management decisions.

Conclusion

Window functions, joins, CTEs, and subqueries are useful in different ways when working with data.

Joins bring related information together, window functions reveal patterns across individual visits, CTEs make complex queries easier to organise, and subqueries allow one calculation to inform another.

When used together, these techniques can help analysts explore hospital billing, compare departments, examine visit patterns, and prepare more reliable reports. The key is to understand what each query is doing, verify the underlying data relationships, and validate the results before drawing conclusions.