SQL Concepts: Window Functions, Joins, CTEs and Subqueries

Introduction

Structured Query Language (SQL) is used to retrieve, manipulate and analyse data stored in relational databases. In a hospital environment, SQL can help management analyse patient visits, compare departments, monitor outstanding bills and evaluate service delivery.

This article explains four important SQL concepts:

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

Hospital Dataset

Column	Description
patient_id	Patient identifier
first_name	Patient's first name
last_name	Patient's last name
gender	Patient's gender
age	Patient's age
phone_number	Patient's phone number
date_of_visit	Date of hospital visit
diagnosis	Recorded diagnosis
treatment_plan	Recommended treatment
doctor_name	Responsible doctor
hospital_department	Hospital department
insurance_provider	Insurance provider
bill_amount_kes	Bill amount in Kenyan shillings
payment_status	Payment status

1. SQL Window Functions

1.1 Meaning and Purpose

Window functions perform calculations across a set of related rows while retaining individual rows in the output.
They are useful for ranking records, calculating running totals, comparing billing amounts and analysing changes over time.

1.2 The OVER() Clause

The OVER() clause specifies the window of rows over which a function operates.

SELECT
    patient_id,
    bill_amount_kes,
    AVG(bill_amount_kes) OVER () AS overall_average_bill
FROM hospital_records;

The query displays each patient's bill alongside the average bill across all records.
Every row contains the individual bill and the same overall average.
Hospital application: Compare individual billing records with the hospital-wide average.

1.3 The PARTITION BY Clause

PARTITION BY divides rows into groups for calculation without combining them into summary rows.

SELECT
    patient_id,
    hospital_department,
    bill_amount_kes,
    AVG(bill_amount_kes) OVER (
        PARTITION BY hospital_department
    ) AS department_average_bill
FROM hospital_records;

The query calculates a separate average bill for each department.
Each patient record remains visible, but records in the same department share the same departmental average.
Hospital application: Compare individual bills against the relevant departmental average.

1.4 The ORDER BY Clause

Within OVER(), ORDER BYdetermines the sequence used for ranking, comparisons and ordered calculations.

SELECT
    patient_id,
    date_of_visit,
    ROW_NUMBER() OVER (
        ORDER BY date_of_visit, patient_id
    ) AS record_sequence
FROM hospital_records;

The query numbers records in chronological order. The patient identifier acts as a tie-breaker.
Each record receives a sequential number beginning at 1.

1.5 Window Frames

A window frame determines which rows within a window are included in a calculation for the current row.

SELECT
    patient_id,
    date_of_visit,
    bill_amount_kes,
    SUM(bill_amount_kes) OVER (
        ORDER BY date_of_visit, patient_id
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND CURRENT ROW
    ) AS running_billing_total
FROM hospital_records;

The query calculates a running total of bills, starting from the first record and adding each subsequent record.
The cumulative total increases as rows are processed in the specified order.

Common frame boundaries include:
UNBOUNDED PRECEDING: the beginning of the partition.
CURRENT ROW: the current row, subject to frame semantics.
UNBOUNDED FOLLOWING: the end of the partition.
n PRECEDING: a specified number of preceding rows in a ROWS frame.
n FOLLOWING: a specified number of following rows in a ROWS frame.

1.6 Ranking Functions

1.6.1. ROW_NUMBER()

ROW_NUMBER() assigns a unique sequential number to every row according to the specified ordering.

SELECT
    patient_id,
    hospital_department,
    bill_amount_kes,
    ROW_NUMBER() OVER (
        PARTITION BY hospital_department
        ORDER BY bill_amount_kes DESC, patient_id
    ) AS bill_position
FROM hospital_records;

Records are numbered within each department, starting with the highest bill.
Each department has its own numbering sequence. Tied bills still receive different numbers.
Hospital application: Identify the highest-billed records in each department.

1.6.2 RANK()

RANK() assigns the same rank to tied values but leaves gaps after ties.

SELECT
    patient_id,
    bill_amount_kes,
    RANK() OVER (
        ORDER BY bill_amount_kes DESC
    ) AS bill_rank
FROM hospital_records;

Records are ranked by bill amount in descending order.
If two records tie for rank 1, the next record receives rank 3.
Hospital application: Rank billing records while recognising equal values.

1.6.3DENSE_RANK()

DENSE_RANK() assigns the same rank to tied values without leaving gaps.

SELECT
    patient_id,
    bill_amount_kes,
    DENSE_RANK() OVER (
        ORDER BY bill_amount_kes DESC
    ) AS dense_bill_rank
FROM hospital_records;

Records are ranked by bill amount, with tied values sharing the same rank.
If two records receive rank 1, the next distinct amount receives rank 2.
Hospital application: Rank distinct billing amounts without gaps.
ROW_NUMBER() assigns a different number to every row. RANK() allows ties and leaves gaps. DENSE_RANK() allows ties without gaps.

1.6.4 NTILE()

NTILE(n) divides ordered rows into approximately equal groups.

SELECT
    patient_id,
    bill_amount_kes,
    NTILE(4) OVER (
        ORDER BY bill_amount_kes
    ) AS billing_quartile
FROM hospital_records;

The query divides records into four groups according to billing amount.
Every record receives a group number from 1 to 4. The groups contain approximately equal numbers of rows.
Hospital application: Analyse the distribution of lower- and higher-billing records.
Equal billing amounts may fall into different groups because NTILE() divides rows rather than distinct values.

1.7 Offset Window Functions

1.7.1 LAG()

LAG() retrieves a value from a preceding row.

SELECT
    patient_id,
    date_of_visit,
    bill_amount_kes,
    LAG(bill_amount_kes) OVER (
        ORDER BY date_of_visit, patient_id
    ) AS previous_record_bill
FROM hospital_records;

The query retrieves the bill from the preceding record.
The first row has NULL for the previous bill because no preceding row exists.
Hospital application: Compare consecutive records to identify billing changes.
To compare previous visits for the same patient, use PARTITION BY patient_id if the dataset contains multiple visits per patient.

1.7.2 LEAD()

LEAD() retrieves a value from a following row.

SELECT
    patient_id,
    date_of_visit,
    bill_amount_kes,
    LEAD(bill_amount_kes) OVER (
        ORDER BY date_of_visit, patient_id
    ) AS next_record_bill
FROM hospital_records;

The query retrieves the bill from the next record.
The last row has NULL because no subsequent row exists.
Hospital application: Compare current and subsequent records in chronological billing analyses.
LAG() looks backwards, while LEAD() looks forwards.

1.8 First and Last Value Functions

1.8.1 FIRST_VALUE()

FIRST_VALUE() returns a value from the first row in the window frame.

SELECT
    patient_id,
    hospital_department,
    bill_amount_kes,
    FIRST_VALUE(patient_id) OVER (
        PARTITION BY hospital_department
        ORDER BY bill_amount_kes DESC, patient_id
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND UNBOUNDED FOLLOWING
    ) AS highest_bill_patient_id
FROM hospital_records;

The query identifies the record with the highest bill in each department.
Every record in a department displays the identifier of the highest-billed record.
Hospital application: Identify high-billing records for further financial review.

1.8.2 LAST_VALUE()

LAST_VALUE() returns a value from the last row in the current window frame.

SELECT
    patient_id,
    hospital_department,
    bill_amount_kes,
    LAST_VALUE(patient_id) OVER (
        PARTITION BY hospital_department
        ORDER BY bill_amount_kes DESC, patient_id
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND UNBOUNDED FOLLOWING
    ) AS lowest_bill_patient_id
FROM hospital_records;

The query identifies the final record in each department when bills are sorted in descending order.
Every record in a department displays the identifier of the lowest-billed record, assuming non-null billing amounts.
Hospital application: Identify the lowest-billed record in each department.
The explicit frame is important because the default ordered frame may end at the current row or its peer group rather than the final row in the partition.

1.9 Aggregate Window Functions

1.9.1 SUM()

SUM() calculates the total of values within a window.

SELECT
    patient_id,
    hospital_department,
    bill_amount_kes,
    SUM(bill_amount_kes) OVER (
        PARTITION BY hospital_department
    ) AS department_total_billing
FROM hospital_records;

The query calculates total recorded billing for each department while preserving individual records.
Every record in a department displays the same departmental total.
Hospital application: Monitor departmental billing totals alongside individual records.

1.9.2 AVG()

AVG() calculates the average of values within a window.

SELECT
    patient_id,
    hospital_department,
    bill_amount_kes,
    AVG(bill_amount_kes) OVER (
        PARTITION BY hospital_department
    ) AS department_average_bill
FROM hospital_records;

The query calculates the average bill for each department.
Each record displays its bill and the average bill for its department. Null billing amounts are ignored.
Hospital application: Compare individual bills against departmental averages.

1.9.3 COUNT()

COUNT() counts rows or non-null values within a window.

SELECT
    patient_id,
    hospital_department,
    COUNT(*) OVER (
        PARTITION BY hospital_department
    ) AS department_record_count
FROM hospital_records;

The query counts records associated with each department.
Every record in a department displays the same departmental record count.
Hospital application: Understand the distribution of records across departments.

COUNT(*) counts rows, while COUNT(bill_amount_kes) counts only rows where the bill is not null. Record counts should not automatically be interpreted as unique patient counts.

1.9.4 MIN()

MIN() returns the minimum non-null value within a window.

SELECT
    patient_id,
    hospital_department,
    bill_amount_kes,
    MIN(bill_amount_kes) OVER (
        PARTITION BY hospital_department
    ) AS minimum_department_bill
FROM hospital_records;

The query identifies the lowest recorded bill in each department.
Each record displays the minimum non-null bill for its department.
Hospital application: Review the distribution of recorded billing values.

1.9.5 MAX()

MAX() returns the maximum non-null value within a window.

SELECT
    patient_id,
    hospital_department,
    bill_amount_kes,
    MAX(bill_amount_kes) OVER (
        PARTITION BY hospital_department
    ) AS maximum_department_bill
FROM hospital_records;

The query identifies the highest recorded bill in each department.
Each record displays the maximum non-null bill for its department.
Hospital application: Identify high-billing records for financial review.

2. SQL Joins

2.1 Meaning and Purpose

A SQL join combines rows from two tables or query results based on a relationship between them.

Hospital databases often store patient details, billing information, doctor records and department information separately. Joins allow these datasets to be combined for reporting and analysis.

The following examples create two departmental summaries using CTEs.

WITH department_totals AS (
    SELECT
        hospital_department,
        SUM(bill_amount_kes) AS total_billing
    FROM hospital_records
    GROUP BY hospital_department
),
unpaid_totals AS (
    SELECT
        hospital_department,
        COUNT(*) AS unpaid_record_count
    FROM hospital_records
    WHERE payment_status = 'Unpaid'
    GROUP BY hospital_department
)
SELECT *
FROM department_totals;

The first CTE calculates total billing by department. The second calculates the number of records marked as unpaid.

2.2 INNER JOIN

An INNER JOIN returns only rows that match the join condition in both inputs.

WITH department_totals AS (
    SELECT hospital_department,
           SUM(bill_amount_kes) AS total_billing
    FROM hospital_records
    GROUP BY hospital_department
),
unpaid_totals AS (
    SELECT hospital_department,
           COUNT(*) AS unpaid_record_count
    FROM hospital_records
    WHERE payment_status = 'Unpaid'
    GROUP BY hospital_department
)
SELECT
    d.hospital_department,
    d.total_billing,
    u.unpaid_record_count
FROM department_totals d
INNER JOIN unpaid_totals u
    ON d.hospital_department = u.hospital_department;

The query combines billing totals with unpaid-record counts for departments appearing in both summaries.
Departments without unpaid records are excluded.
Hospital application: Review billing totals alongside outstanding-payment records.

2.3 LEFT JOIN

A LEFT JOIN preserves every row from the left input and includes matching rows from the right input. Missing matches produce null values for right-side columns.

WITH department_totals AS (
    SELECT hospital_department,
           SUM(bill_amount_kes) AS total_billing
    FROM hospital_records
    GROUP BY hospital_department
),
unpaid_totals AS (
    SELECT hospital_department,
           COUNT(*) AS unpaid_record_count
    FROM hospital_records
    WHERE payment_status = 'Unpaid'
    GROUP BY hospital_department
)
SELECT
    d.hospital_department,
    d.total_billing,
    COALESCE(u.unpaid_record_count, 0) AS unpaid_record_count
FROM department_totals d
LEFT JOIN unpaid_totals u
    ON d.hospital_department = u.hospital_department;

The query preserves all departments with recorded billing.
Departments without unpaid records display zero because COALESCE() replaces the missing count with zero.
Hospital application: Prepare a complete departmental report, including departments without unpaid records.

2.4 RIGHT JOIN

A RIGHT JOIN preserves all rows from the right input and includes matching rows from the left input.

WITH department_totals AS (
    SELECT hospital_department,
           SUM(bill_amount_kes) AS total_billing
    FROM hospital_records
    GROUP BY hospital_department
),
unpaid_totals AS (
    SELECT hospital_department,
           COUNT(*) AS unpaid_record_count
    FROM hospital_records
    WHERE payment_status = 'Unpaid'
    GROUP BY hospital_department
)
SELECT
    d.hospital_department,
    d.total_billing,
    u.unpaid_record_count
FROM department_totals d
RIGHT JOIN unpaid_totals u
    ON d.hospital_department = u.hospital_department;

The query preserves every department represented in the unpaid-record summary.
Every department with unpaid records appears, even if it has no matching billing summary.
Hospital application: Preserve all departments from an unpaid-record report during reconciliation.
With these particular CTEs, every department in the unpaid summary should also appear in the billing summary. Consequently, the result will normally match the inner join.

2.5 FULL OUTER JOIN

A FULL OUTER JOIN preserves matching rows and unmatched rows from both inputs.

WITH department_totals AS (
    SELECT hospital_department,
           SUM(bill_amount_kes) AS total_billing
    FROM hospital_records
    GROUP BY hospital_department
),
unpaid_totals AS (
    SELECT hospital_department,
           COUNT(*) AS unpaid_record_count
    FROM hospital_records
    WHERE payment_status = 'Unpaid'
    GROUP BY hospital_department
)
SELECT
    COALESCE(
        d.hospital_department,
        u.hospital_department
    ) AS hospital_department,
    d.total_billing,
    u.unpaid_record_count
FROM department_totals d
FULL OUTER JOIN unpaid_totals u
    ON d.hospital_department = u.hospital_department;

The query preserves departments from both summaries, including unmatched departments.
Matching departments display both values. Unmatched departments have a null value for the missing side.
Hospital application: Reconcile billing reports against unpaid-record reports.

2.6 CROSS JOIN

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

WITH departments AS (
    SELECT DISTINCT hospital_department
    FROM hospital_records
),
statuses AS (
    SELECT DISTINCT payment_status
    FROM hospital_records
)
SELECT
    d.hospital_department,
    s.payment_status
FROM departments d
CROSS JOIN statuses s
ORDER BY
    d.hospital_department,
    s.payment_status;

The query combines every department with every distinct payment status.
If the dataset contains five departments and three payment statuses, the query returns 15 combinations.
Hospital application: Build the structure for a department-by-payment-status monitoring matrix.
Some combinations may not exist in the actual data. A later left join to observed counts can show zero for those combinations.

2.7 SELF JOIN

A self join joins a table to itself.

SELECT
    a.patient_id AS patient_a,
    b.patient_id AS patient_b,
    a.diagnosis,
    a.hospital_department
FROM hospital_records a
INNER JOIN hospital_records b
    ON a.diagnosis = b.diagnosis
   AND a.patient_id < b.patient_id
WHERE a.diagnosis IS NOT NULL;

The query pairs records belonging to different patient identifiers when their diagnoses match.
Each row represents a pair of records with the same recorded diagnosis. The comparison condition prevents the same pair from appearing in reverse order, assuming patient identifiers are unique and comparable.
Hospital application: Explore diagnoses shared across patient records to support service planning.
Matching diagnoses do not prove that patients have identical clinical circumstances, and common diagnoses can produce many pairs.

3. Common Table Expressions (CTEs)

3.1 Meaning and Purpose

A Common Table Expression (CTE) is a named result set defined using the WITH clause. It exists for the duration of the SQL statement in which it is defined.
CTEs improve readability by separating complex queries into logical steps.

3.2 Basic CTE

WITH department_billing AS (
    SELECT
        hospital_department,
        SUM(bill_amount_kes) AS total_billing
    FROM hospital_records
    GROUP BY hospital_department
)
SELECT *
FROM department_billing
ORDER BY total_billing DESC;

The CTE calculates total billing for each department. The main query displays the totals in descending order.
One row per department with its total billing amount.
Hospital application: Identify departments with the highest and lowest recorded billing totals.

3.3 Multiple CTEs

A query can define several CTEs separated by commas. Later CTEs can reference earlier ones.

WITH department_billing AS (
    SELECT
        hospital_department,
        SUM(bill_amount_kes) AS total_billing
    FROM hospital_records
    GROUP BY hospital_department
),
department_payments AS (
    SELECT
        hospital_department,
        COUNT(*) FILTER (
            WHERE payment_status = 'Paid'
        ) AS paid_record_count,
        COUNT(*) FILTER (
            WHERE payment_status = 'Unpaid'
        ) AS unpaid_record_count
    FROM hospital_records
    GROUP BY hospital_department
)
SELECT
    b.hospital_department,
    b.total_billing,
    p.paid_record_count,
    p.unpaid_record_count
FROM department_billing b
INNER JOIN department_payments p
    ON b.hospital_department = p.hospital_department
ORDER BY b.total_billing DESC;

The first CTE calculates departmental billing totals. The second calculates counts for paid and unpaid records. The final query combines the summaries.
One row per department with billing totals and payment-status counts.
Hospital application: Produce a consolidated departmental billing report.

3.4 Recursive CTEs

A recursive CTE refers to its own result and is useful for generating sequences or processing hierarchical data.
A recursive CTE generally contains:

1. An anchor query, which establishes the starting rows.
2. A recursive query, which generates additional rows from the previous result. Example: Generate Monthly Reporting Periods

WITH RECURSIVE months(month_start, last_month) AS (
    SELECT
        DATE_TRUNC('month', MIN(date_of_visit))::date,
        DATE_TRUNC('month', MAX(date_of_visit))::date
    FROM hospital_records
    WHERE date_of_visit IS NOT NULL

    UNION ALL

    SELECT
        (month_start + INTERVAL '1 month')::date,
        last_month
    FROM months
    WHERE month_start < last_month
)
SELECT month_start
FROM months
ORDER BY month_start;

The anchor query identifies the earliest and latest months in the dataset. The recursive query adds one month at a time until it reaches the last month.
A chronological list of month-start dates spanning the observed reporting period.
Hospital application: Generate a reporting calendar that can be joined to monthly visit counts to identify months without recorded visits.
A recursive CTE requires a termination condition. Without one, the query may continue generating rows indefinitely or until execution is stopped.

3.5 CTEs with Joins

CTEs can prepare data for a join, making a multi-step query easier to understand.

WITH department_billing AS (
    SELECT
        hospital_department,
        SUM(bill_amount_kes) AS total_billing
    FROM hospital_records
    GROUP BY hospital_department
),
department_unpaid AS (
    SELECT
        hospital_department,
        COUNT(*) AS unpaid_records
    FROM hospital_records
    WHERE payment_status = 'Unpaid'
    GROUP BY hospital_department
)
SELECT
    b.hospital_department,
    b.total_billing,
    COALESCE(u.unpaid_records, 0) AS unpaid_records
FROM department_billing b
LEFT JOIN department_unpaid u
    ON b.hospital_department = u.hospital_department;

The first CTE calculates billing totals, and the second calculates unpaid-record counts. The main query combines them.
One row per department with recorded billing, including departments without unpaid records.
Hospital application: Compare billing totals and unpaid-record counts across departments.

3.6 CTEs with Aggregations

CTEs can separate an aggregation from the analysis performed on the aggregated results.

WITH department_billing AS (
    SELECT
        hospital_department,
        SUM(bill_amount_kes) AS total_billing
    FROM hospital_records
    GROUP BY hospital_department
)
SELECT
    hospital_department,
    total_billing
FROM department_billing
WHERE total_billing > (
    SELECT AVG(total_billing)
    FROM department_billing
)
ORDER BY total_billing DESC;

The CTE calculates billing totals for each department. The main query returns departments whose totals exceed the average departmental total.
Only departments with above-average totals are returned.
Hospital application: Identify departments with higher recorded billing totals than the average across departments.
This compares departmental totals, not individual bills.

4. SQL Subqueries

A subquery is a query nested inside another SQL statement. It can return one value, a set of rows or a derived table.

4.1 Non-correlated subqueries

A non-correlated subquery does not depend on the outer query.

SELECT patient_id, diagnosis, bill_amount_kes
FROM hospital_records
WHERE bill_amount_kes > (
    SELECT AVG(bill_amount_kes)
    FROM hospital_records
);

Returns records with bills above the overall average. The inner query can be evaluated independently.

4.2 Scalar subqueries

A scalar subquery returns one value: one row and one column.

SELECT patient_id, bill_amount_kes,
       (SELECT AVG(bill_amount_kes)
        FROM hospital_records) AS overall_average_bill
FROM hospital_records;

Displays the overall average beside each record. A scalar subquery returning multiple rows causes an error in this context.

4.3 Correlated subqueries

A correlated subquery references a value from the outer query.

SELECT h.patient_id, h.hospital_department, h.bill_amount_kes
FROM hospital_records h
WHERE h.bill_amount_kes > (
    SELECT AVG(x.bill_amount_kes)
    FROM hospital_records x
    WHERE x.hospital_department = h.hospital_department
);

Returns records whose bills exceed the average for their own department. The inner query references the outer row's department.

4.4 Nested subqueries

A nested subquery contains another subquery.

SELECT patient_id, hospital_department, bill_amount_kes
FROM hospital_records
WHERE hospital_department IN (
    SELECT hospital_department
    FROM hospital_records
    GROUP BY hospital_department
    HAVING AVG(bill_amount_kes) > (
        SELECT AVG(bill_amount_kes)
        FROM hospital_records
    )
);

Calculates the overall average, identifies departments whose average exceeds it, and returns records from those departments.

4.5 Subqueries using IN

IN checks whether a value matches any value in a set.

SELECT patient_id, hospital_department, payment_status
FROM hospital_records
WHERE hospital_department IN (
    SELECT hospital_department
    FROM hospital_records
    WHERE payment_status = 'Unpaid'
);

Returns all records from departments with at least one unpaid record, regardless of the individual record's payment status.

Note: NOT IN can behave unexpectedly when the subquery returns NULL values.

4.6 Subqueries using EXISTS

EXISTS checks whether a subquery returns at least one row.

SELECT h.patient_id, h.hospital_department, h.payment_status
FROM hospital_records h
WHERE EXISTS (
    SELECT 1
    FROM hospital_records u
    WHERE u.hospital_department = h.hospital_department
      AND u.payment_status = 'Unpaid'
);

Returns records from departments with at least one unpaid record. SELECT 1 is commonly used because only the existence of a row matters.

4.7 Subqueries using ANY

ANY returns true when a comparison holds for at least one value in the subquery.

SELECT patient_id, bill_amount_kes
FROM hospital_records
WHERE bill_amount_kes > ANY (
    SELECT bill_amount_kes
    FROM hospital_records
    WHERE hospital_department = 'Cardiology'
      AND bill_amount_kes IS NOT NULL
);

With a non-empty set, returns records whose bills exceed at least one bill recorded in Cardiology. This is equivalent to exceeding the minimum value in that set.

Replace 'Cardiology' with a department present in the dataset. If the subquery returns no rows, > ANY evaluates to false.

4.8 Subqueries using ALL

ALL returns true when a comparison holds for every value in the subquery.

SELECT patient_id, bill_amount_kes
FROM hospital_records
WHERE bill_amount_kes > ALL (
    SELECT bill_amount_kes
    FROM hospital_records
    WHERE hospital_department = 'Cardiology'
      AND bill_amount_kes IS NOT NULL
);

With a non-empty set, returns records whose bills exceed every bill recorded in Cardiology, meaning they exceed the maximum in that set.

ANY requires the comparison to hold for at least one value; ALL requires it to hold for every value. In PostgreSQL, ALL over an empty set evaluates to true.

4.9 Subqueries in the SELECT clause

SELECT h.patient_id, h.hospital_department, h.bill_amount_kes,
       (SELECT AVG(x.bill_amount_kes)
        FROM hospital_records x
        WHERE x.hospital_department = h.hospital_department
       ) AS department_average_bill
FROM hospital_records h;

Displays each record alongside the average bill for its department. The subquery is correlated with the outer query.

4.10 Subqueries in the FROM clause

SELECT department_summary.hospital_department,
       department_summary.average_bill
FROM (
    SELECT hospital_department,
           AVG(bill_amount_kes) AS average_bill
    FROM hospital_records
    GROUP BY hospital_department
) AS department_summary
WHERE department_summary.average_bill > (
    SELECT AVG(bill_amount_kes)
    FROM hospital_records
);

The derived table calculates departmental averages. The outer query returns departments whose average exceeds the overall average. A derived table requires an alias.

4.11 Subqueries in the WHERE clause

SELECT patient_id, diagnosis, bill_amount_kes
FROM hospital_records
WHERE bill_amount_kes > (
    SELECT AVG(bill_amount_kes)
    FROM hospital_records
);

Filters records to show bills above the overall average. Records with null bills do not pass the comparison.

Conclusion

Window functions, joins, CTEs and subqueries are essential SQL tools for analysing relational data. Window functions support rankings and comparisons while preserving individual rows. Joins combine related datasets. CTEs organise complex queries into readable stages, and recursive CTEs support iterative tasks. Subqueries allow one query to provide values, sets or conditions to another.

In a hospital environment, these concepts can support billing analysis, departmental comparisons, payment monitoring and operational reporting. Reliable analysis also requires validating data, understanding the business context and interpreting findings responsibly.

The goal is not simply to retrieve information, but to transform reliable hospital data into meaningful and actionable insights.