# A Practical Guide to Advanced SQL: Window Functions, Joins, CTEs, and Subqueries Using a Hospital Database

1. Introduction

Structured Query Language (SQL) is used to retrieve, manipulate, and analyze data stored in relational databases. In a hospital environment, SQL helps administrators understand patient visits, monitor medical departments, evaluate hospital performance, track revenue, and support operational decisions.

The hospital database used in this article is built with PostgreSQL and contains the following main tables:

Table	Purpose
patients	Stores patient details, ages, genders, counties, and sub-counties.
doctors	Stores doctors' names, specialties, genders, and years of experience.
departments	Stores hospital departments.
wards	Stores hospital ward information.
diagnosis	Stores diagnoses and their categories.
procedures	Stores medical procedures and their categories.
visits	Stores hospital visit records, including dates, bills, admission types, and lengths of stay.

The visits table connects the other tables using identifiers such as patient_id, doctor_id, department_id, and diagnosis_id. This structure allows information from several tables to be combined during analysis.

The examples use the actual table and column names in the supplied hospital SQL script. They assume the database tables have been created and populated successfully and that the current schema is evening.

SET search_path TO evening;

The article covers four important SQL concepts: window functions, joins, common table expressions, and subqueries. Each concept is explained through practical hospital scenarios.

2. SQL Window Functions

2.1 What are window functions?

Window functions perform calculations across a group of related rows while preserving the individual rows in the query result. Unlike ordinary aggregate functions used with GROUP BY, window functions do not normally collapse multiple records into a single row.

For example, a hospital manager may want to display every patient visit alongside the total bill for the entire hospital. A window function can calculate that total while keeping every visit visible.

2.2 Understanding OVER()

The OVER() clause specifies the window of rows over which a function operates. It can be used with ranking functions, navigation functions, and aggregate functions.

Example:

SELECT
    visit_id,
    patient_id,
    total_bill_kes,
    SUM(total_bill_kes) OVER () AS hospital_total_bill
FROM visits
ORDER BY visit_id;

Explanation: The query displays every visit and its bill alongside the sum of all visit bills. Because OVER() contains no partition or ordering, the total covers the entire result set.

Expected result: The query returns 30 rows, one for each visit in the supplied dataset. Each row contains its individual bill and the same hospital-wide total.

2.3 Understanding PARTITION BY

PARTITION BY divides the rows into separate groups for calculation. The function then operates independently within each group.

Example:

SELECT
    visit_id,
    department_id,
    total_bill_kes,
    SUM(total_bill_kes) OVER (
        PARTITION BY department_id
    ) AS department_total_bill
FROM visits
ORDER BY department_id, visit_id;

Explanation: Every visit is displayed with the total bill for its department. The total is repeated for each visit belonging to that department.

Hospital application: Management can compare the revenue generated by individual departments without losing the details of the visits contributing to each total.

2.4 Understanding ORDER BY inside a window

Within OVER(), ORDER BY determines the order in which rows are ranked or processed. It is different from the final ORDER BY, which determines the presentation order of the query results.

SELECT
    visit_id,
    visit_date,
    total_bill_kes,
    SUM(total_bill_kes) OVER (
        ORDER BY visit_date, visit_id
    ) AS running_total
FROM visits
ORDER BY visit_date, visit_id;

Explanation: The query calculates a cumulative total in visit-date order. Adding visit_id makes the ordering deterministic when multiple visits share a date.

Expected result: The running total increases as the visits are processed. It includes the current visit and all preceding visits in the defined window.

2.5 Understanding window frames

A window frame specifies which rows within a partition are included in a calculation for the current row. Common frame boundaries include:

• UNBOUNDED PRECEDING: starts at the first row in the partition.
• CURRENT ROW: includes the current row as a boundary.
• n PRECEDING: refers to a specified number of preceding rows.
• UNBOUNDED FOLLOWING: extends to the final row in the partition.

Example: running total

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_total
FROM visits
ORDER BY visit_date, visit_id;

Explanation: The frame starts with the first visit and extends through the current visit. ROWS specifies a physical row-based frame.

Example: moving average over three visits

SELECT
    visit_id,
    visit_date,
    total_bill_kes,
    ROUND(
        AVG(total_bill_kes) OVER (
            ORDER BY visit_date, visit_id
            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW
        ), 2
    ) AS three_visit_average
FROM visits
ORDER BY visit_date, visit_id;

This query calculates the average bill for the current visit and up to the two preceding visits. At the beginning of the dataset, fewer than three rows may be available.

Important distinction: In PostgreSQL, an ordered window using the default frame commonly includes all rows from the beginning of the partition through the current row's peer group. For predictable running calculations, explicitly specifying ROWS is often clearer. This distinction is particularly important when several visits share the same date or ordering value.

2.6 ROW_NUMBER()

ROW_NUMBER() assigns a unique sequential number to each row in a window partition.

SELECT
    visit_id,
    patient_id,
    total_bill_kes,
    ROW_NUMBER() OVER (
        ORDER BY total_bill_kes DESC, visit_id
    ) AS bill_row_number
FROM visits;

Explanation: Visits are numbered from the highest bill to the lowest. The visit_id breaks ties so that the ordering is deterministic.

Expected result: The visit with the highest bill receives row number 1, the next visit receives 2, and so forth. Even if two visits have identical bills, they receive different row numbers.

Hospital application: This is useful when creating ranked lists or selecting the first visit per patient using a common table expression.

2.7 RANK()

RANK() assigns the same rank to rows with equal ordering values. It leaves gaps after tied ranks.

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

Explanation: Visits are ranked by bill amount. Equal bills receive equal ranks, and the next rank skips the number of tied positions.

For example, if three visits occupy positions 1, 2, and 3 but share the same bill, all three receive rank 1, and the next distinct bill receives rank 4.

Hospital application: A hospital can identify the highest-billed visits while preserving ties.

2.8 DENSE_RANK()

DENSE_RANK() also assigns the same rank to tied values, but it does not leave gaps.

SELECT
    visit_id,
    total_bill_kes,
    DENSE_RANK() OVER (
        ORDER BY total_bill_kes DESC
    ) AS dense_bill_rank
FROM visits;

Explanation: Visits with the same bill share a rank. The next distinct bill receives the next consecutive rank.

Comparison:

Function	Treatment of ties	Example ranks for tied top values
ROW_NUMBER()	Assigns a unique number to every row	1, 2, 3
RANK()	Same rank for ties; gaps follow	1, 1, 3
DENSE_RANK()	Same rank for ties; no gaps	1, 1, 2

These functions are useful when analyzing hospital bills, patient satisfaction scores, or department performance.

2.9 NTILE()

NTILE(n) divides an ordered result set into approximately equal groups called buckets.

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

Explanation: The visits are sorted by bill and divided into four buckets. Because the dataset contains 30 visits, the bucket sizes will be as balanced as possible: two buckets will contain eight visits and two will contain seven.

Bucket 1 contains the lowest bills, while bucket 4 contains the highest bills.

Hospital application: The hospital can segment visits into four bill-value groups for financial reporting.

2.10 LAG()

LAG() retrieves a value from a preceding row without requiring a self-join.

SELECT
    visit_id,
    visit_date,
    total_bill_kes,
    LAG(total_bill_kes) OVER (
        ORDER BY visit_date, visit_id
    ) AS previous_visit_bill
FROM visits
ORDER BY visit_date, visit_id;

Explanation: Each visit is displayed alongside the bill of the preceding visit in the ordered dataset. The first row has NULL because no preceding row exists.

Hospital application: Analysts can compare consecutive visits and identify changes in bill amounts.

2.11 LEAD()

LEAD() retrieves a value from a subsequent row.

SELECT
    visit_id,
    visit_date,
    total_bill_kes,
    LEAD(total_bill_kes) OVER (
        ORDER BY visit_date, visit_id
    ) AS next_visit_bill
FROM visits
ORDER BY visit_date, visit_id;

Explanation: The query displays the bill of the next visit alongside the current visit. The last row has NULL because no following row exists.

Comparison: LAG() looks backward, while LEAD() looks forward. Both are useful for comparisons between neighboring records.

2.12 FIRST_VALUE()

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

SELECT
    visit_id,
    department_id,
    total_bill_kes,
    FIRST_VALUE(total_bill_kes) OVER (
        PARTITION BY department_id
        ORDER BY total_bill_kes DESC, visit_id
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND UNBOUNDED FOLLOWING
    ) AS highest_department_bill
FROM visits;

Explanation: The query displays each visit alongside the highest bill in its department.

Hospital application: Department managers can see the highest-billed visit while retaining the rest of the department's visit records.

2.13 LAST_VALUE()

LAST_VALUE() returns the value from the last row in the current window frame. It is important to define the frame carefully.

SELECT
    visit_id,
    department_id,
    total_bill_kes,
    LAST_VALUE(total_bill_kes) OVER (
        PARTITION BY department_id
        ORDER BY total_bill_kes, visit_id
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND UNBOUNDED FOLLOWING
    ) AS highest_department_bill
FROM visits;

Explanation: Because the rows are ordered from the lowest bill to the highest, and the frame extends to the final row, the function returns the highest bill in each department.

Without the explicit frame ending in UNBOUNDED FOLLOWING, LAST_VALUE() may return the current row's value or the last value in a smaller frame instead of the final value in the whole department.

2.14 Aggregate window functions: SUM(), AVG(), COUNT(), MIN(), and MAX()

Aggregate functions summarize values. When combined with OVER(), they calculate summaries across a window while keeping individual visits visible.

A. SUM() — total bill by department

SELECT
    visit_id,
    department_id,
    total_bill_kes,
    SUM(total_bill_kes) OVER (
        PARTITION BY department_id
    ) AS department_total
FROM visits;

The query calculates each department's total bill and repeats that value for every visit in the department.

B. AVG() — average bill by admission type

SELECT
    visit_id,
    admission_type,
    total_bill_kes,
    ROUND(
        AVG(total_bill_kes) OVER (
            PARTITION BY admission_type
        ), 2
    ) AS average_bill_for_admission_type
FROM visits;

The query calculates the average bill separately for outpatient, inpatient, and emergency visits. Each visit remains visible.

C. COUNT() — number of visits per patient

SELECT
    visit_id,
    patient_id,
    COUNT(*) OVER (
        PARTITION BY patient_id
    ) AS patient_visit_count
FROM visits
ORDER BY patient_id, visit_id;

The query counts the visits associated with each patient. A patient with multiple visits receives the same visit count on each of their rows.

D. MIN() — lowest bill by department

SELECT
    visit_id,
    department_id,
    total_bill_kes,
    MIN(total_bill_kes) OVER (
        PARTITION BY department_id
    ) AS lowest_department_bill
FROM visits;

The query identifies the minimum bill within each department.

E. MAX() — highest bill by department

SELECT
    visit_id,
    department_id,
    total_bill_kes,
    MAX(total_bill_kes) OVER (
        PARTITION BY department_id
    ) AS highest_department_bill
FROM visits;

The query identifies the maximum bill within each department.

Why use aggregate window functions? They are especially useful for dashboards and detailed reports where analysts need both individual visit information and group-level performance measures in the same result set.

3. SQL Joins

3.1 What are joins?

A join combines rows from two or more tables based on a related column or condition. In the hospital database, visits contains identifiers that link to patients, doctors, departments, diagnoses, and procedures.

Joins allow analysts to replace numerical identifiers with meaningful information such as patient names, department names, and diagnosis descriptions.

3.2 INNER JOIN

An INNER JOIN returns only rows with matching records in both tables.

SELECT
    v.visit_id,
    p.patient_name,
    v.visit_date,
    v.total_bill_kes
FROM visits v
INNER JOIN patients p
    ON v.patient_id = p.patient_id
ORDER BY v.visit_id;

Explanation: The query combines each visit with the corresponding patient record. Only visits whose patient IDs match a patient in patients appear.

Expected result: The supplied dataset has 30 visits and all referenced patient IDs exist, so this query returns 30 rows.

Hospital scenario: Retrieving a patient's name and bill for each hospital visit.

3.3 LEFT JOIN

A LEFT JOIN returns every row from the left table and matching rows from the right table. If there is no match, columns from the right table contain NULL.

SELECT
    p.patient_id,
    p.patient_name,
    v.visit_id,
    v.visit_date
FROM patients p
LEFT JOIN visits v
    ON p.patient_id = v.patient_id
ORDER BY p.patient_id, v.visit_date;

Explanation: Every patient is displayed, including patients without visits. If a patient has multiple visits, that patient appears once for each matching visit.

Hospital scenario: Identifying patients who have never visited the hospital.

SELECT
    p.patient_id,
    p.patient_name
FROM patients p
LEFT JOIN visits v
    ON p.patient_id = v.patient_id
WHERE v.visit_id IS NULL;

This second query returns patients without a matching visit. In the supplied dataset, every patient has at least one recorded visit, so it returns no rows.

3.4 RIGHT JOIN

A RIGHT JOIN returns every row from the right table and any matching rows from the left table.

SELECT
    v.visit_id,
    d.department_name
FROM visits v
RIGHT JOIN departments d
    ON v.department_id = d.department_id
ORDER BY d.department_id, v.visit_id;

Explanation: Every department appears, even if it has no visits. A department without visits would have NULL in the visit columns.

Hospital scenario: Reporting all hospital departments, including departments that have not recorded any visits.

Comparison with LEFT JOIN: The result can generally be expressed using a LEFT JOIN by reversing the table order. For example, departments LEFT JOIN visits serves the same purpose as the preceding right join.

3.5 FULL OUTER JOIN

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

SELECT
    v.visit_id,
    v.department_id AS visit_department_id,
    d.department_id AS registered_department_id,
    d.department_name
FROM visits v
FULL OUTER JOIN departments d
    ON v.department_id = d.department_id
ORDER BY d.department_id, v.visit_id;

Explanation: The result includes visits with matching departments, departments without visits, and visits whose department ID does not match a department record.

In the supplied dataset, every visit references an existing department and all four departments have visits. Therefore, this query produces matched visit-department rows only.

Hospital scenario: Auditing records across operational tables to detect unmatched or incomplete relationships.

3.6 CROSS JOIN

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

SELECT
    d.department_name,
    w.ward_name
FROM departments d
CROSS JOIN wards w
ORDER BY d.department_name, w.ward_name;

Explanation: There are four departments and four wards, producing (4 \times 4 = 16) combinations.

Hospital scenario: Preparing a planning matrix showing all department-ward combinations for resource allocation. These combinations are hypothetical unless a separate assignment table confirms that a department operates in a particular ward.

3.7 SELF JOIN

A self join joins a table to itself. It is useful when records within the same table must be compared.

The doctors table does not contain a manager or supervisor ID, so a practical self-join example can compare doctors within the same specialty.

SELECT
    d1.doctor_name AS doctor_one,
    d2.doctor_name AS doctor_two,
    d1.doctor_specialty
FROM doctors d1
JOIN doctors d2
    ON d1.doctor_specialty = d2.doctor_specialty
   AND d1.doctor_id < d2.doctor_id;

Explanation: The query finds pairs of doctors who share a specialty. The condition d1.doctor_id < d2.doctor_id prevents a doctor from being matched with themselves and avoids listing the same pair twice.

Expected result: The supplied data contains four doctors with four different specialties, so the query returns no rows.

Hospital scenario: In a larger hospital database, this approach can identify doctors sharing specialties or compare staff records. A true supervisor hierarchy would require an appropriate relationship column, such as manager_id.

3.8 Comparing SQL joins

Join type	What it returns	Typical hospital use
INNER JOIN	Matching records only	Visits with valid patient records
LEFT JOIN	All left-side records and matches	All patients, including those without visits
RIGHT JOIN	All right-side records and matches	All departments, including unused departments
FULL OUTER JOIN	All records from both sides	Auditing unmatched records
CROSS JOIN	Every possible combination	Planning matrices
SELF JOIN	Comparisons within the same table	Finding doctors with the same specialty

Choosing the correct join is important because an inappropriate join can exclude relevant records or create unintended combinations.

4. Common Table Expressions (CTEs)

4.1 What is a CTE?

A Common Table Expression is a named temporary result defined using the WITH clause. It exists for the duration of the statement and can make complex SQL easier to read and maintain.

A CTE does not automatically create a permanent table.

4.2 Basic CTE

Scenario: Finding visits with bills greater than KES 20,000.

WITH expensive_visits AS (
    SELECT
        visit_id,
        patient_id,
        visit_date,
        total_bill_kes
    FROM visits
    WHERE total_bill_kes > 20000
)
SELECT *
FROM expensive_visits
ORDER BY total_bill_kes DESC;

Explanation: The CTE first selects visits whose bills exceed KES 20,000. The outer query retrieves and sorts those records.

Hospital application: Reviewing high-value visits for financial monitoring or administrative follow-up.

4.3 Multiple CTEs

Multiple CTEs allow a query to be divided into logical stages. A later CTE can reference an earlier one.

WITH department_revenue AS (
    SELECT
        department_id,
        SUM(total_bill_kes) AS total_revenue
    FROM visits
    GROUP BY department_id
),
department_averages AS (
    SELECT
        AVG(total_revenue) AS average_department_revenue
    FROM department_revenue
)
SELECT
    d.department_name,
    dr.total_revenue,
    da.average_department_revenue
FROM department_revenue dr
JOIN departments d
    ON d.department_id = dr.department_id
CROSS JOIN department_averages da
ORDER BY dr.total_revenue DESC;

Explanation:

1. department_revenue calculates the total bill for each department.
2. department_averages calculates the average of those department totals.
3. The final query displays each department's revenue alongside the overall average department revenue.

Hospital application: Management can identify departments performing above or below the average revenue level. This is a financial comparison, not a measure of clinical quality.

4.4 Recursive CTEs

A recursive CTE refers to its own result and repeatedly generates rows until its stopping condition is met. It normally contains an initial query, a recursive query, and a termination condition.

Example: generating a sequence of numbers

WITH RECURSIVE visit_day_sequence AS (
    SELECT 1 AS day_number

    UNION ALL

    SELECT day_number + 1
    FROM visit_day_sequence
    WHERE day_number < 7
)
SELECT day_number
FROM visit_day_sequence
ORDER BY day_number;

Explanation: The initial query creates day 1. The recursive part adds one to the previous number until day 7 is reached.

Expected result: Seven rows containing the numbers 1 through 7.

Hospital application: The generated sequence can support reporting templates for a seven-day monitoring period or be joined to a calendar table to show dates with no visits.

A recursive CTE can also traverse a genuine hierarchy, such as a hospital organizational chart, provided the database contains a parent-child relationship. The current doctor table does not contain such a relationship, so the sequence example demonstrates recursion without inventing one.

4.5 CTEs with joins and aggregations

CTEs can organize joins and aggregate calculations into separate stages.

WITH visit_details AS (
    SELECT
        v.visit_id,
        v.total_bill_kes,
        d.department_name,
        dg.diagnosis_name
    FROM visits v
    JOIN departments d
        ON d.department_id = v.department_id
    JOIN diagnosis dg
        ON dg.diagnosis_id = v.diagnosis_id
),
diagnosis_summary AS (
    SELECT
        department_name,
        diagnosis_name,
        COUNT(*) AS visit_count,
        SUM(total_bill_kes) AS total_billed
    FROM visit_details
    GROUP BY department_name, diagnosis_name
)
SELECT
    department_name,
    diagnosis_name,
    visit_count,
    total_billed
FROM diagnosis_summary
ORDER BY total_billed DESC;

Explanation: The first CTE combines visit, department, and diagnosis information. The second groups the records by department and diagnosis, calculating the number of visits and total bills. The final query sorts the summaries by total billed.

Hospital application: Identifying which diagnoses contribute most to each department's recorded visit volume and revenue.

4.6 CTEs compared with subqueries

Both CTEs and subqueries can break complex SQL into smaller logical operations. Their differences mainly concern organization, reuse, and readability.

Feature	CTE	Subquery
Syntax	Defined using WITH	Written inside another query
Readability	Often clearer for multi-stage analysis	Convenient for short, focused logic
Reuse	Can be referenced multiple times in a statement	Often repeated if needed in multiple places
Recursion	Supports recursive queries with WITH RECURSIVE	Does not independently provide recursive CTE functionality
Performance	Depends on the query and PostgreSQL's optimization	Depends on the query and PostgreSQL's optimization

A CTE is not automatically faster than a subquery. PostgreSQL can inline many CTEs, while materialization behavior depends on the query and explicit options. The best choice should be guided by clarity and, when necessary, execution-plan analysis.

5. SQL Subqueries

5.1 What is a subquery?

A subquery is a query placed inside another SQL statement. It can return a single value, a list of values, a set of rows, or a table-like result.

Subqueries help solve problems where one query depends on the result of another.

5.2 Scalar subqueries

A scalar subquery returns exactly one column and at most one row. It can be used wherever a single value is expected.

Scenario: Finding visits with bills above the hospital's average bill.

SELECT
    visit_id,
    patient_id,
    total_bill_kes
FROM visits
WHERE total_bill_kes > (
    SELECT AVG(total_bill_kes)
    FROM visits
)
ORDER BY total_bill_kes DESC;

Explanation: The inner query calculates the average bill across all visits. The outer query returns visits with bills above that average.

The aggregate query returns one row, making it suitable as a scalar subquery.

Hospital application: Identifying unusually expensive visits relative to the overall average. Such visits may merit review, but a high bill alone does not establish an error.

5.3 Non-correlated subqueries

A non-correlated subquery does not reference columns from the outer query. Its result is independent of the current outer row.

SELECT
    visit_id,
    patient_id,
    total_bill_kes
FROM visits
WHERE department_id IN (
    SELECT department_id
    FROM departments
    WHERE department_name IN ('Cardiology', 'Orthopedics')
)
ORDER BY visit_id;

Explanation: The inner query retrieves the IDs of Cardiology and Orthopedics. The outer query returns visits whose department IDs are in that set.

Hospital application: Retrieving visits from selected departments without hard-coding their numerical IDs.

5.4 Correlated subqueries

A correlated subquery references a value from the outer query. Conceptually, its result depends on the current outer row.

Scenario: Finding visits whose bill exceeds the average bill for their own department.

SELECT
    v.visit_id,
    v.department_id,
    v.total_bill_kes
FROM visits v
WHERE v.total_bill_kes > (
    SELECT AVG(v2.total_bill_kes)
    FROM visits v2
    WHERE v2.department_id = v.department_id
)
ORDER BY v.department_id, v.total_bill_kes DESC;

Explanation: For each outer visit, the inner query calculates the average bill for the same department. The outer query retains the visit if its bill exceeds that department-specific average.

Hospital application: Comparing financial activity fairly within departments instead of comparing every visit against a single hospital-wide average.

5.5 Nested subqueries

A nested subquery contains one subquery inside another. This can be useful when a condition depends on several stages of analysis.

Scenario: Finding visits associated with the department that has the highest total bill.

SELECT
    visit_id,
    department_id,
    total_bill_kes
FROM visits
WHERE department_id = (
    SELECT department_id
    FROM (
        SELECT
            department_id,
            SUM(total_bill_kes) AS department_total
        FROM visits
        GROUP BY department_id
        ORDER BY department_total DESC, department_id
        LIMIT 1
    ) AS highest_revenue_department
)
ORDER BY visit_id;

Explanation: The innermost query calculates total revenue for each department and selects the highest-revenue department. The enclosing query extracts that department's ID. The outer query then returns its individual visits.

The LIMIT 1 ensures the scalar comparison receives no more than one row. The department ID acts as a deterministic tie-breaker if totals are equal.

Hospital application: Retrieving detailed visit records for the department with the greatest total billed amount.

5.6 Subqueries using IN

IN checks whether a value matches any value in a specified list or result set.

SELECT
    visit_id,
    patient_id,
    diagnosis_id
FROM visits
WHERE diagnosis_id IN (
    SELECT diagnosis_id
    FROM diagnosis
    WHERE diagnosis_category = 'Infectious Disease'
)
ORDER BY visit_id;

Explanation: The inner query finds diagnoses classified as infectious diseases. The outer query returns visits linked to those diagnoses.

Hospital application: Retrieving visits associated with infectious disease diagnoses, such as malaria, typhoid, and other diagnoses categorized as infectious in the supplied dataset.

5.7 Subqueries using EXISTS

EXISTS tests whether a subquery returns at least one row. It is particularly useful for checking whether a related record exists.

Scenario: Finding patients who have visited the hospital.

SELECT
    p.patient_id,
    p.patient_name
FROM patients p
WHERE EXISTS (
    SELECT 1
    FROM visits v
    WHERE v.patient_id = p.patient_id
)
ORDER BY p.patient_id;

Explanation: For each patient, the subquery checks for at least one visit linked to that patient. The query returns patients for whom a match exists.

Hospital application: Producing a list of patients with recorded hospital visits.

The opposite condition, NOT EXISTS, can identify patients without any recorded visits:

SELECT
    p.patient_id,
    p.patient_name
FROM patients p
WHERE NOT EXISTS (
    SELECT 1
    FROM visits v
    WHERE v.patient_id = p.patient_id
);

In the supplied data, this second query returns no rows because every listed patient has a visit.

5.8 Subqueries using ANY

ANY compares a value with the results of a subquery and returns true when the comparison succeeds for at least one returned value.

Scenario: Finding visits whose bill exceeds at least one bill from an outpatient visit.

SELECT
    visit_id,
    admission_type,
    total_bill_kes
FROM visits
WHERE total_bill_kes > ANY (
    SELECT total_bill_kes
    FROM visits
    WHERE admission_type = 'Outpatient'
)
ORDER BY total_bill_kes DESC;

Explanation: A visit is returned if its bill is greater than at least one outpatient bill. This does not mean its bill must exceed every outpatient bill.

For a nonempty set of outpatient bills, > ANY effectively means that the bill exceeds the minimum value in that set, subject to SQL's NULL comparison rules.

Hospital application: Finding visits that are more expensive than at least one outpatient visit.

5.9 Subqueries using ALL

ALL requires a comparison to be true for every value returned by the subquery.

Scenario: Finding visits whose bills exceed every outpatient bill.

SELECT
    visit_id,
    admission_type,
    total_bill_kes
FROM visits
WHERE total_bill_kes > ALL (
    SELECT total_bill_kes
    FROM visits
    WHERE admission_type = 'Outpatient'
)
ORDER BY total_bill_kes DESC;

Explanation: A visit is returned only when its bill exceeds every outpatient bill in the subquery result.

Hospital application: Identifying visits that are more expensive than all outpatient visits, which can help administrators investigate high-cost admissions or emergencies.

Comparison: > ANY requires the bill to exceed at least one value; > ALL requires it to exceed every value. The two conditions can therefore produce very different results.

5.10 Using subqueries in the SELECT clause

A scalar subquery can calculate a value to display alongside each row.

SELECT
    v.visit_id,
    v.total_bill_kes,
    (
        SELECT AVG(total_bill_kes)
        FROM visits
    ) AS hospital_average_bill
FROM visits v
ORDER BY v.visit_id;

Explanation: Every visit is displayed alongside the same hospital-wide average bill.

Hospital application: Providing context for individual bills in a detailed financial report.

5.11 Using subqueries in the FROM clause

A subquery in the FROM clause acts as a derived table. PostgreSQL requires it to have an alias.

SELECT
    department_summary.department_id,
    department_summary.total_revenue
FROM (
    SELECT
        department_id,
        SUM(total_bill_kes) AS total_revenue
    FROM visits
    GROUP BY department_id
) AS department_summary
WHERE department_summary.total_revenue > 0
ORDER BY department_summary.total_revenue DESC;

Explanation: The inner query calculates total revenue per department. The outer query filters the resulting department summaries.

Hospital application: Creating a department-level financial report using an intermediate aggregated result.

5.12 Using subqueries in the WHERE clause

Subqueries in WHERE filter records based on values calculated or selected by another query.

SELECT
    visit_id,
    patient_id,
    total_bill_kes
FROM visits
WHERE total_bill_kes > (
    SELECT AVG(total_bill_kes)
    FROM visits
)
AND length_of_stay_days > 0
ORDER BY total_bill_kes DESC;

Explanation: The query selects visits whose bills exceed the hospital-wide average and whose recorded length of stay is greater than zero.

Hospital application: Reviewing higher-billed visits that involved an overnight or other recorded inpatient stay. The dataset's length_of_stay_days field should be interpreted according to the hospital's recording practices.

5.13 Comparing subquery types

Type	Main characteristic	Example use
Scalar	Returns one value	Compare a bill with the overall average
Non-correlated	Does not reference the outer query	Select visits from chosen departments
Correlated	References the outer query	Compare a visit with its department average
Nested	Contains another subquery	Find visits from the highest-revenue department
IN	Tests membership in a result set	Select infectious disease visits
EXISTS	Checks whether a matching row exists	Find patients with visits
ANY	Requires at least one comparison to succeed	Exceed at least one outpatient bill
ALL	Requires every comparison to succeed	Exceed every outpatient bill

Subqueries can make SQL expressive and powerful. However, equivalent joins, CTEs, or window functions may sometimes be easier to understand or more suitable for a particular performance requirement.

6. Applying the Concepts to Real Hospital Analysis

The four topics can be combined to solve more complex operational questions.

6.1 Ranking the highest-billed visit in each department

WITH ranked_visits AS (
    SELECT
        v.visit_id,
        v.visit_date,
        p.patient_name,
        d.department_name,
        v.total_bill_kes,
        ROW_NUMBER() OVER (
            PARTITION BY v.department_id
            ORDER BY v.total_bill_kes DESC, v.visit_id
        ) AS department_rank
    FROM visits v
    JOIN patients p
        ON p.patient_id = v.patient_id
    JOIN departments d
        ON d.department_id = v.department_id
)
SELECT *
FROM ranked_visits
WHERE department_rank = 1
ORDER BY department_name;

This query combines joins, a CTE, and ROW_NUMBER(). It first enriches each visit with the patient and department names, then ranks visits by bill within each department. Finally, it returns one highest-billed visit per department.

If multiple visits share the highest bill, ROW_NUMBER() selects one of them according to the visit_id tie-breaker. To return every visit tied for the highest bill, a ranking function such as RANK() can be used instead.

6.2 Calculating revenue and running revenue over time

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

This query combines the ordering and window-frame concepts to track cumulative billed amounts over the recorded period. It can support financial trend analysis and help administrators see how recorded revenue accumulates over time.

6.3 Finding patients with repeated visits

SELECT
    p.patient_id,
    p.patient_name,
    COUNT(v.visit_id) AS number_of_visits,
    SUM(v.total_bill_kes) AS total_billed
FROM patients p
JOIN visits v
    ON v.patient_id = p.patient_id
GROUP BY p.patient_id, p.patient_name
HAVING COUNT(v.visit_id) > 1
ORDER BY number_of_visits DESC, total_billed DESC;

This query uses an inner join and aggregation to identify patients with more than one recorded visit. It also calculates the total amount billed across their visits.

The result can help the hospital understand repeat service utilization. It should not be interpreted as proof of a medical condition or an avoidable readmission.

6.4 Comparing each visit with its department's average

SELECT
    v.visit_id,
    d.department_name,
    v.total_bill_kes,
    ROUND(
        AVG(v.total_bill_kes) OVER (
            PARTITION BY v.department_id
        ), 2
    ) AS department_average_bill,
    v.total_bill_kes
      - AVG(v.total_bill_kes) OVER (
            PARTITION BY v.department_id
        ) AS difference_from_average
FROM visits v
JOIN departments d
    ON d.department_id = v.department_id
ORDER BY d.department_name, v.visit_id;

This query uses a join and aggregate window functions to display each visit's bill, its department's average bill, and the difference between the two. A positive difference indicates a bill above the department average, while a negative difference indicates a bill below it.

7. Best Practices and Common Mistakes

1. Use meaningful aliases. Short aliases such as v, p, and d make joins easier to read when used consistently.
2. Check join conditions carefully. Missing or incorrect join conditions can create duplicate combinations and inflate revenue calculations.
3. Distinguish grouped aggregates from window aggregates. GROUP BY normally reduces the result to groups, whereas a window aggregate preserves individual rows.
4. Specify deterministic ordering when ranking matters. Add a unique tie-breaker such as visit_id when using ROW_NUMBER().
5. Define window frames explicitly when necessary. This is especially important for running totals, moving averages, and LAST_VALUE().
6. Understand NULL values. Missing values can affect comparisons, aggregates, and subquery conditions. COUNT(column) ignores NULL, whereas COUNT(*) counts rows.
7. Use EXISTS when checking for related records. It expresses existence checks clearly and avoids multiplying outer rows as a join can do.
8. Do not assume CTEs are always faster. Performance depends on the SQL statement, data distribution, indexes, and PostgreSQL's query plan.
9. Validate results before reporting. Check row counts, aggregate totals, and unexpected duplicates before using SQL outputs in dashboards.
10. Protect patient information. Use only the patient details needed for a report and follow applicable access-control and privacy requirements.

8. Conclusion

Window functions, joins, CTEs, and subqueries are essential tools for advanced SQL analysis.

Window functions provide rankings, comparisons, running totals, moving averages, and group-level calculations while retaining individual visit records. Joins combine related hospital data, enabling analysts to connect visits with patients, doctors, departments, and diagnoses. CTEs organize complex SQL into readable stages and support recursive operations. Subqueries allow one query to depend on values or records returned by another.

When applied to the hospital dataset, these techniques support practical tasks such as identifying high-billed visits, comparing department revenue, examining repeat visits, analyzing trends over time, and detecting incomplete relationships.

The main objective is not merely to write SQL that executes. It is to write SQL that answers a meaningful operational question, produces accurate results, and communicates those results in a form that supports responsible hospital decision-making.