SQL Window Functions, Joins, CTEs, and Subqueries in Hospital Data Analysis.

1. Introduction

Structured Query Language (SQL) is used to retrieve, manipulate, and analyze data stored in relational databases. In a hospital environment, SQL can help management monitor patient admissions, analyze treatment costs, compare departmental performance, identify patients with repeated visits, and evaluate healthcare service delivery.
Four important SQL concepts make these analyses more effective:

• Window functions calculate values across related rows without combining those rows into a single result.
• Joins combine information from two or more tables using related columns.
• Common Table Expressions (CTEs) organize complex queries into named, reusable steps within a single statement.
• Subqueries place one SQL query inside another to support filtering, calculations, and data retrieval. This article explains how these concepts work, why they are useful, and how they can be applied to practical hospital scenarios.

2. Example hospital database

The following examples assume a hospital database with three tables. Replace these names and columns with those in your actual class dataset where necessary.

Patients
Stores patient details.
patient_id · patient_name · gender · date_of_birth

Departments
Stores hospital department information.
department_id · department_name

Admissions
Stores patient visits and admission information.
admission_id · patient_id · department_id · admission_date · discharge_date · total_bill · status

The relationships are:

• One patient can have multiple admissions.
• One department can handle multiple admissions.
• Each admission belongs to a patient and a department. The examples use PostgreSQL-compatible syntax. Some syntax, especially window-frame clauses and date functions, may differ in other database systems.

3. SQL Window Functions

3.1 What are window functions?
A window function performs a calculation across a set of rows related to the current row. Unlike GROUP BY, which combines rows into summary records, a window function preserves the individual rows while adding calculated values.
For example, a hospital manager may want to see each patient's admission bill alongside the average bill for that patient's department.
General syntax:

function_name(expression) OVER (
    PARTITION BY column_name
    ORDER BY column_name
    ROWS BETWEEN ...
);

Not every window function requires all these clauses. The OVER() clause defines the window of rows over which the calculation is performed.

3.2 Understanding OVER(), PARTITION BY, ORDER BY and window frames
A. OVER()
OVER() tells SQL to perform a calculation as a window function.

SELECT
    admission_id,
    total_bill,
    AVG(total_bill) OVER () AS hospital_average_bill
FROM Admissions;

What it does: Calculates the average bill across all admissions and displays it alongside every admission.
Expected result: If the average bill is KSh 15,000, every row displays KSh 15,000 in hospital_average_bill, while each admission retains its own bill.
Hospital application: Compare individual admission costs against the hospital-wide average.
B. PARTITION BY
PARTITION BY divides rows into groups for a window calculation without removing individual rows.

SELECT
    admission_id,
    department_id,
    total_bill,
    AVG(total_bill) OVER (
        PARTITION BY department_id
    ) AS department_average_bill
FROM Admissions;

What it does: Calculates a separate average for each department.
Expected result: Admissions belonging to the same department display the same department average, but different departments can have different averages.
Hospital application: Compare admission costs across departments such as maternity, outpatient and emergency services.
C. ORDER BY
Within a window, ORDER BY specifies the order in which rows are evaluated. It is essential for ranking functions and time-based comparisons.

SELECT
    admission_id,
    admission_date,
    total_bill,
    ROW_NUMBER() OVER (
        ORDER BY admission_date
    ) AS admission_sequence
FROM Admissions;

What it does: Numbers admissions in chronological order.
Expected result: The earliest admission receives sequence number 1, followed by subsequent admissions.
Hospital application: Establish the sequence of admissions or compare current and previous patient visits.
Note that the ORDER BY inside OVER() does not necessarily sort the final displayed result. Use an outer ORDER BY if you need to control the output order.
D. Window frames
A window frame defines which rows within a window contribute to the calculation for the current row. Frames are particularly useful for running totals, moving averages and cumulative counts.
For example:

SELECT
    admission_id,
    admission_date,
    total_bill,
    SUM(total_bill) OVER (
        ORDER BY admission_date, admission_id
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND CURRENT ROW
    ) AS running_total
FROM Admissions
ORDER BY admission_date, admission_id;

The frame means:

• UNBOUNDED PRECEDING: Start from the first row.
• CURRENT ROW: Stop at the current row.
• ROWS BETWEEN: Specify the exact rows included in the calculation. Expected result: The first admission contributes its bill, the second contributes its bill plus the first, and the cumulative total continues increasing.

Other useful frames include:
| Window Frame | Meaning | Example Use |
|---|---|---|
| ROWS BETWEEN 1 PRECEDING AND CURRENT ROW | Includes the current row and the previous row. | Two-admission moving total |
| ROWS BETWEEN 2 PRECEDING AND CURRENT ROW | Includes the current row and the two previous rows. | Three-row moving average |
| ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING | Includes all rows in the window. | Overall total for each department |

A critical distinction is that an ordered window's default frame can include all rows tied with the current row. Explicitly specifying ROWS makes row-by-row calculations more predictable.

3.3 Ranking window functions
Ranking functions assign positions to rows based on specified values.
A. ROW_NUMBER()
Assigns a unique sequential number to every row, even when rows have identical values.

SELECT
    admission_id,
    department_id,
    total_bill,
    ROW_NUMBER() OVER (
        PARTITION BY department_id
        ORDER BY total_bill DESC, admission_id
    ) AS row_num
FROM Admissions;

Explanation: Each department starts numbering at 1. The highest bill receives the first position. admission_id breaks ties consistently when it is unique.
Expected result: If bills in one department are KSh 30,000, KSh 20,000 and KSh 20,000, their row numbers are 1, 2 and 3.
Hospital use: Select one latest admission per patient or identify the highest-cost admissions in each department.

B. RANK()
Assigns the same rank to tied values, then skips subsequent rank numbers.

SELECT
    admission_id,
    total_bill,
    RANK() OVER (
        ORDER BY total_bill DESC
    ) AS bill_rank
FROM Admissions;

Explanation: Admissions with equal bills receive equal ranks.

Expected result:
| Total Bill (KSh) | Rank |
|---:|---:|
| 30,000 | 1 |
| 20,000 | 2 |
| 20,000 | 2 |
| 10,000 | 4 |
The next rank after two admissions ranked 2 is 4.
Hospital use: Rank departments or admissions by cost while recognizing tied positions.

C. DENSE_RANK()
Assigns the same rank to tied values without skipping rank numbers.

SELECT
    admission_id,
    total_bill,
    DENSE_RANK() OVER (
        ORDER BY total_bill DESC
    ) AS dense_bill_rank
FROM Admissions;

Expected result: For bills of KSh 30,000, KSh 20,000, KSh 20,000 and KSh 10,000, the ranks are 1, 2, 2 and 3.
Hospital use: Identify the three highest distinct bill levels without gaps in the ranking.

Comparison of ranking functions
| Function | Unique Row Number? | Ties Share a Rank? | Skips Ranks After Ties? |
|---|---|---|---|
| ROW_NUMBER() | Yes | No | No |
| RANK() | No | Yes | Yes |
| DENSE_RANK() | No | Yes | No |

D. NTILE()
Divides ordered rows into a specified number of approximately equal groups, called buckets.

SELECT
    admission_id,
    total_bill,
    NTILE(4) OVER (
        ORDER BY total_bill
    ) AS cost_quartile
FROM Admissions;

Explanation: Divides admissions into four groups based on their bills. If there are 100 admissions, each bucket will contain 25 rows.
Expected result: Bucket 1 contains the lowest bills, while bucket 4 contains the highest bills, assuming ascending order.
Hospital use: Categorize admissions into low-, moderate- and high-cost groups. The buckets represent relative positions in the dataset, not predefined medical cost thresholds.

3.4 Navigation window functions
Navigation functions retrieve values from other rows in the same window.
A. LAG()
Retrieves a value from a preceding row without requiring a self-join.

SELECT
    patient_id,
    admission_date,
    total_bill,
    LAG(total_bill) OVER (
        PARTITION BY patient_id
        ORDER BY admission_date, admission_id
    ) AS previous_bill
FROM Admissions;

Explanation: Retrieves the previous admission's bill for each patient.
Expected result: The first admission for each patient has NULL for previous_bill. Later admissions show the previous bill.
Hospital use: Compare a patient's current admission cost with the cost of their previous visit.

B. LEAD()
Retrieves a value from a subsequent row.

SELECT
    patient_id,
    admission_date,
    total_bill,
    LEAD(total_bill) OVER (
        PARTITION BY patient_id
        ORDER BY admission_date, admission_id
    ) AS next_bill
FROM Admissions;

Explanation: Looks forward to the next admission for the same patient.
Expected result: The last admission for each patient has NULL for next_bill unless a default value is specified.
Hospital use: Analyze changes between successive visits or examine the cost pattern of patients who return for further treatment.

C. FIRST_VALUE()
Returns the value from the first row in the window frame.

SELECT
    admission_id,
    department_id,
    admission_date,
    total_bill,
    FIRST_VALUE(total_bill) OVER (
        PARTITION BY department_id
        ORDER BY admission_date, admission_id
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND UNBOUNDED FOLLOWING
    ) AS first_admission_bill
FROM Admissions;

Explanation: Returns the bill associated with the earliest admission in each department.
Expected result: Every admission in a department displays the same first-admission bill.
Hospital use: Compare subsequent admission costs against the cost of the department's first recorded admission.
D. LAST_VALUE()
Returns the value from the last row in the current window frame.

SELECT
    admission_id,
    department_id,
    admission_date,
    total_bill,
    LAST_VALUE(total_bill) OVER (
        PARTITION BY department_id
        ORDER BY admission_date, admission_id
        ROWS BETWEEN UNBOUNDED PRECEDING
                 AND UNBOUNDED FOLLOWING
    ) AS last_admission_bill
FROM Admissions;

Explanation: Returns the bill associated with the latest admission in each department.
Expected result: Every admission in a department displays the same latest-admission bill.
Why specify the frame? Without UNBOUNDED FOLLOWING, LAST_VALUE() often returns the current row's value or the last value within the default frame, rather than the final value in the whole department.
Hospital use: Compare the earliest and latest recorded admission costs.

3.5 Aggregate window functions
Aggregate functions calculate summary values over a window while preserving individual rows.
A. SUM()
Calculates the total of numeric values.

SELECT
    admission_id,
    department_id,
    total_bill,
    SUM(total_bill) OVER (
        PARTITION BY department_id
    ) AS department_total_bills
FROM Admissions;

Expected result: Every admission displays its bill and the total bills for its department.
Hospital use: Compare individual admission costs with total departmental billing.

B. AVG()
Calculates the arithmetic mean.

SELECT
    admission_id,
    department_id,
    total_bill,
    AVG(total_bill) OVER (
        PARTITION BY department_id
    ) AS department_average_bill
FROM Admissions;

Expected result: Every row displays the average admission bill for its department.
Hospital use: Identify departments whose admission costs are above or below their own departmental average.

C. COUNT()
Counts rows or non-null values, depending on the expression.

SELECT
    admission_id,
    department_id,
    COUNT(*) OVER (
        PARTITION BY department_id
    ) AS department_admission_count
FROM Admissions;

Expected result: Each admission displays the total number of admissions in its department.
Hospital use: Measure admission volume per department while retaining individual admission records.
COUNT(*) counts rows, including rows containing nulls in other columns. COUNT(column_name) counts only rows where that column is not null.

D. MIN()
Returns the minimum value in a window.

SELECT
    admission_id,
    department_id,
    total_bill,
    MIN(total_bill) OVER (
        PARTITION BY department_id
    ) AS lowest_department_bill
FROM Admissions;

Expected result: Each admission displays the lowest non-null bill in its department.
Hospital use: Identify the minimum recorded admission cost for each department.
E. MAX()
Returns the maximum value in a window.

SELECT
    admission_id,
    department_id,
    total_bill,
    MAX(total_bill) OVER (
        PARTITION BY department_id
    ) AS highest_department_bill
FROM Admissions;

Expected result: Each admission displays the highest non-null bill in its department.
Hospital use: Identify departments with unusually high individual admission costs.

3.6 Comparing aggregate windows with GROUP BY
A window function preserves rows, while GROUP BY produces a summary for each group.
Using GROUP BY:

SELECT
    department_id,
    AVG(total_bill) AS average_bill
FROM Admissions
GROUP BY department_id;

Using a window function:

SELECT
    admission_id,
    department_id,
    total_bill,
    AVG(total_bill) OVER (
        PARTITION BY department_id
    ) AS average_bill
FROM Admissions;

The first query returns one row per department. The second returns one row per admission, including the departmental average.

4. SQL Joins

4.1 What is a join?
A join combines rows from two or more tables based on a related column or join condition.
For example, the Admissions table contains patient_id, while Patients contains the corresponding patient names. A join makes it possible to retrieve both the admission details and patient names in one query.

4.2 INNER JOIN
Returns only rows with matching values in both tables.

SELECT
    a.admission_id,
    p.patient_name,
    a.admission_date,
    a.total_bill
FROM Admissions a
INNER JOIN Patients p
    ON a.patient_id = p.patient_id;

Explanation: The query returns admissions whose patient IDs match records in the Patients table.
Expected result: Admissions with no matching patient record are excluded.
Hospital application: Generate a report of admissions linked to valid patient records.

4.3 LEFT JOIN
Returns every row from the left table and matching rows from the right table. If no match exists, the right-side columns contain NULL.

SELECT
    p.patient_id,
    p.patient_name,
    a.admission_id,
    a.admission_date
FROM Patients p
LEFT JOIN Admissions a
    ON p.patient_id = a.patient_id;

Explanation: Every patient is included, even if they have never been admitted.
Expected result: Patients with admissions appear alongside their admission details. Patients without admissions have NULL in the admission columns.
Hospital application: Identify patients who have never been admitted.
To return only patients without admissions:

SELECT
    p.patient_id,
    p.patient_name
FROM Patients p
LEFT JOIN Admissions a
    ON p.patient_id = a.patient_id
WHERE a.admission_id IS NULL;

**4.4 RIGHT JOIN
**Returns every row from the right table and matching rows from the left table.

SELECT
    p.patient_name,
    a.admission_id,
    a.patient_id,
    a.total_bill
FROM Patients p
RIGHT JOIN Admissions a
    ON p.patient_id = a.patient_id;

Explanation: Every admission is retained, including admissions whose patient ID does not match a patient record.
Expected result: A matching patient name appears where available; otherwise, patient_name is NULL.
Hospital application: Audit admission records to identify those that may reference missing patient information.
A RIGHT JOIN can usually be rewritten as a LEFT JOIN by reversing the order of the tables.

4.5 FULL OUTER JOIN
Returns all matching and non-matching rows from both tables.

SELECT
    p.patient_id AS registered_patient_id,
    p.patient_name,
    a.admission_id,
    a.patient_id AS admission_patient_id
FROM Patients p
FULL OUTER JOIN Admissions a
    ON p.patient_id = a.patient_id;

Explanation: The result includes all patients and all admissions. Matching records are combined, while unmatched records remain with NULL values for the missing side.
Hospital application: Reconcile patient registration records with admission records to identify missing links and unused registrations.

4.6 CROSS JOIN
Returns the Cartesian product of two tables: every row from the first table is paired with every row from the second.

SELECT
    p.patient_name,
    d.department_name
FROM Patients p
CROSS JOIN Departments d;

Explanation: Each patient is paired with every department.
Expected result: If there are 5 patients and 4 departments, the result contains 20 rows.
Hospital application: Generate all possible patient-department combinations for planning or reporting templates. This does not mean that a patient actually visited every department.
Use caution with large tables because a cross join can produce a very large result.

4.7 SELF JOIN
A self join joins a table to itself using different aliases. It is useful when records in one table have relationships with other records in that same table.
Assume the Staff table contains staff_id, staff_name and manager_id.

SELECT
    staff.staff_name AS staff_member,
    manager.staff_name AS manager_name
FROM Staff staff
LEFT JOIN Staff manager
    ON staff.manager_id = manager.staff_id;

Explanation: The table is used twice: once to identify each staff member and once to retrieve their manager's name.
Expected result: Each staff member appears with their manager, where one is assigned. Senior managers without a manager remain in the output with a NULL manager name.
Hospital application: Display reporting relationships among hospital staff, nurses, doctors and department managers.

4.8 Comparison of SQL joins
| Join Type | Records Retained | Hospital Use |
|---|---|---|
| INNER JOIN | Matching records only | Admissions with valid patient records |
| LEFT JOIN | All left records and matching right records | All registered patients, including those without admissions |
| RIGHT JOIN | All right records and matching left records | All admissions, including unmatched ones |
| FULL OUTER JOIN | All records from both sides | Reconciliation of registrations and admissions |
| CROSS JOIN | Every possible combination | Planning combinations of patients and departments |
| SELF JOIN | Matching records within one table | Linking staff members to managers |

Important: Joins can produce multiple rows for one entity when relationships are one-to-many. For example, a patient with five admissions can appear five times in a patient-admission join. This is expected and should be considered when calculating totals.

5. Common Table Expressions (CTEs)

5.1 What is a CTE?
A Common Table Expression is a named result set defined using the WITH keyword. It exists for the duration of a single SQL statement and can make complicated queries easier to read.
General syntax:

WITH cte_name AS (
    SELECT ...
)
SELECT *
FROM cte_name;

5.2 Basic CTE
A basic CTE defines one intermediate result that the main query can use.

WITH high_cost_admissions AS (
    SELECT
        admission_id,
        patient_id,
        department_id,
        total_bill
    FROM Admissions
    WHERE total_bill > 50000
)
SELECT *
FROM high_cost_admissions;

Explanation: The CTE selects admissions with bills greater than KSh 50,000. The main query retrieves those records.
Expected result: Only admissions whose total_bill exceeds KSh 50,000 are returned.
Hospital application: Identify high-cost admissions for financial review.
A CTE improves readability, but it does not automatically make a query faster. PostgreSQL may inline or materialize a CTE depending on the query and its options.

5.3 Multiple CTEs
A query can define multiple CTEs, with later CTEs referencing earlier ones.

WITH department_totals AS (
    SELECT
        department_id,
        SUM(total_bill) AS total_revenue
    FROM Admissions
    GROUP BY department_id
),
department_averages AS (
    SELECT
        AVG(total_revenue) AS average_department_revenue
    FROM department_totals
)
SELECT
    dt.department_id,
    dt.total_revenue,
    da.average_department_revenue
FROM department_totals dt
CROSS JOIN department_averages da;

Explanation:

1. department_totals calculates the total recorded bills for each department.
2. department_averages calculates the average of those department totals.
3. The final query compares every department's total with the average across departments. Expected result: Each department appears with its total revenue and the average revenue across all departments. Hospital application: Identify departments performing above or below the average in recorded billing. The query measures recorded bills, not necessarily cash collected. Payment data would be needed to calculate actual collections.

5.4 Recursive CTEs
A recursive CTE references its own result. It is useful for traversing hierarchical relationships.
Assume a Staff table contains staff_id, staff_name and manager_id.

WITH RECURSIVE staff_hierarchy AS (
    SELECT
        staff_id,
        staff_name,
        manager_id,
        1 AS hierarchy_level
    FROM Staff
    WHERE manager_id IS NULL

    UNION ALL

    SELECT
        s.staff_id,
        s.staff_name,
        s.manager_id,
        sh.hierarchy_level + 1
    FROM Staff s
    JOIN staff_hierarchy sh
        ON s.manager_id = sh.staff_id
)
SELECT *
FROM staff_hierarchy
ORDER BY hierarchy_level, staff_id;

Explanation:

• The anchor query selects staff members who have no manager.
• The recursive query finds staff whose manager appears in the previous result.
• UNION ALL combines the initial rows and subsequent levels.
• Recursion stops when no more matching staff are found.
• Expected result: _Staff are listed by their level in the hospital's reporting hierarchy. _Hospital application: Map the reporting structure from senior management to department managers and other staff. Important: The example assumes a valid hierarchy. Cycles or malformed reporting relationships can cause problems and should be prevented or handled explicitly. 5.5 CTEs with joins CTEs can prepare data before joining it with another table.

WITH patient_admission_counts AS (


   SELECT
        patient_id,
        COUNT(*) AS admission_count
    FROM Admissions
    GROUP BY patient_id
)
SELECT
    p.patient_name,
    COALESCE(pac.admission_count, 0) AS admission_count
FROM Patients p
LEFT JOIN patient_admission_counts pac
    ON p.patient_id = pac.patient_id;

Explanation: The CTE counts admissions for each patient. The main query joins those counts to the patient register.
Expected result: Every patient is listed with their number of admissions. Patients with no admissions receive a count of zero because of COALESCE().
Hospital application: Identify patients with frequent hospital visits.

5.6 CTEs with aggregations
CTEs can separate detailed filtering from summary calculations.
WITH monthly_admissions AS (

    SELECT
        DATE_TRUNC('month', admission_date)::date AS admission_month,
        COUNT(*) AS total_admissions,
        SUM(total_bill) AS total_billed
    FROM Admissions
    GROUP BY DATE_TRUNC('month', admission_date)::date
)
SELECT
    admission_month,
    total_admissions,
    total_billed
FROM monthly_admissions
ORDER BY admission_month;

Explanation: The CTE groups admissions by month and calculates admission counts and total bills.
Expected result: One row per month with the number of admissions and the total recorded bills.
Hospital application: Monitor monthly admission trends and billing activity.

5.7 CTEs versus subqueries
| Feature | CTE | Subquery |
|---|---|---|
| Syntax | Uses WITH | Written inside another query |
| Readability | Often clearer for multi-step logic | Suitable for simple, localized logic |
| Reuse within the statement | Can be referenced multiple times | Often must be repeated if needed in multiple places |
| Recursion | Supports recursive CTEs | Ordinary subqueries are not recursive |
| Performance | Depends on the query and database optimizer | Depends on the query and database optimizer |
| Best use | Complex, multi-stage queries | Focused filtering or calculation |

A CTE is not inherently faster than a subquery. The best choice depends on clarity, reuse, and execution characteristics.

6. SQL Subqueries

6.1 What is a subquery?
A subquery is a SQL query nested inside another SQL statement. It can return a single value, one column of values, a table of results or a Boolean condition, depending on its structure and use.
Subqueries can appear in SELECT, FROM, WHERE and other contexts supported by the database.

6.2 Scalar subqueries
A scalar subquery returns a single value: one row and one column.

SELECT
    admission_id,
    total_bill,
    (
        SELECT AVG(total_bill)
        FROM Admissions
    ) AS hospital_average_bill
FROM Admissions;

Explanation: The inner query calculates the hospital-wide average bill. The outer query displays that average alongside every admission.
Expected result: Every admission includes its individual bill and the same average bill.
Hospital application: Compare individual bills with the hospital-wide average.
If a scalar subquery returns more than one row, PostgreSQL raises an error. If it returns no rows, its scalar result is NULL.

6.3 Non-correlated subqueries
A non-correlated subquery does not reference columns from the outer query. It can be evaluated independently of the current outer row.

SELECT
    admission_id,
    patient_id,
    total_bill
FROM Admissions
WHERE total_bill > (
    SELECT AVG(total_bill)
    FROM Admissions
);

Explanation: The inner query calculates the average bill independently. The outer query selects admissions above that average.
Expected result: Only admissions with bills greater than the overall average are returned.
Hospital application: Identify unusually expensive admissions compared with the hospital-wide average.

6.4 Correlated subqueries
A correlated subquery references one or more columns from the outer query. Its result depends on the current outer row.

SELECT
    a.admission_id,
    a.patient_id,
    a.total_bill
FROM Admissions a
WHERE a.total_bill > (
    SELECT AVG(a2.total_bill)
    FROM Admissions a2
    WHERE a2.patient_id = a.patient_id
);

Explanation: For each admission, the inner query calculates that patient's average bill across all admissions. The outer query returns admissions above the patient's own average.
Expected result: Admissions that cost more than the corresponding patient's average bill.
Hospital application: Identify visits that are unusually expensive for individual patients.
Although correlated subqueries are often explained as running once per outer row, the database optimizer may transform them into a more efficient execution plan.

6.5 Nested subqueries
A nested subquery contains another subquery within it.

SELECT
    admission_id,
    department_id,
    total_bill
FROM Admissions
WHERE department_id IN (
    SELECT department_id
    FROM Admissions
    GROUP BY department_id
    HAVING AVG(total_bill) > (
        SELECT AVG(total_bill)
        FROM Admissions
    )
);

Explanation:

1. The innermost query calculates the overall average bill.
2. The middle query identifies departments whose average bill exceeds the overall average.
3. The outer query retrieves admissions belonging to those departments. Expected result: Admissions from departments whose average recorded bill is above the hospital-wide average. Hospital application: Analyze billing patterns in relatively high-cost departments.

6.6 Subqueries using IN
IN checks whether a value matches any value in a list or the result of a subquery.

SELECT
    patient_id,
    patient_name
FROM Patients
WHERE patient_id IN (
    SELECT patient_id
    FROM Admissions
    WHERE total_bill > 50000
);

Explanation: The subquery returns patient IDs associated with admissions costing more than KSh 50,000.
Expected result: Patients with at least one admission exceeding that amount.
Hospital application: Find patients who have experienced a high-cost admission.
IN is useful when membership in a set of values is what matters. Be careful when dealing with NULL values, especially when using NOT IN.

6.7 Subqueries using EXISTS
EXISTS checks whether a subquery returns at least one row.

SELECT
    p.patient_id,
    p.patient_name
FROM Patients p
WHERE EXISTS (
    SELECT 1
    FROM Admissions a
    WHERE a.patient_id = p.patient_id
);

Explanation: A patient is selected if at least one matching admission exists.
Expected result: Patients who have at least one admission.
Hospital application: Retrieve registered patients who have used hospital admission services.
The related NOT EXISTS condition can identify patients without admissions:

SELECT
    p.patient_id,
    p.patient_name
FROM Patients p
WHERE NOT EXISTS (
    SELECT 1
    FROM Admissions a
    WHERE a.patient_id = p.patient_id
);

Difference between IN and EXISTS: IN tests membership in a set of values, while EXISTS tests whether matching rows exist. Both can solve similar problems; performance depends on the query and database optimizer.

6.8 Subqueries using ANY
ANY compares a value with at least one value returned by a subquery.

SELECT
    admission_id,
    department_id,
    total_bill
FROM Admissions
WHERE total_bill > ANY (
    SELECT total_bill
    FROM Admissions
    WHERE department_id = 2
);

Explanation: An admission is selected if its bill is greater than at least one bill recorded in department 2.
Expected result: Bills greater than the minimum qualifying comparison value, assuming the subquery returns non-null values.
Hospital application: Find admissions that cost more than at least one admission in a specified department.
ANY does not mean greater than all values. If the subquery returns no rows, the comparison is false.

6.9 Subqueries using ALL
ALL compares a value with every value returned by a subquery.

SELECT
    admission_id,
    department_id,
    total_bill
FROM Admissions
WHERE total_bill > ALL (
    SELECT total_bill
    FROM Admissions
    WHERE department_id = 2
);

Explanation: An admission qualifies only if its bill exceeds every non-null bill returned for department 2.
Expected result: Admissions whose bills are higher than the highest bill in department 2, assuming the subquery returns at least one non-null value.
Hospital application: Identify admissions whose costs exceed all recorded admission bills in a comparison department.

ANY versus ALL
Operator Meaning of total_bill > ... Hospital interpretation

ANY Greater than at least one value More expensive than at least one admission in department 2
ALL Greater than every value More expensive than every admission in department 2
NULL values require care in comparisons with ANY and ALL, because SQL uses three-valued logic: true, false and unknown.

6.10 Subqueries in SELECT, FROM and WHERE
A. Subquery in SELECT
A scalar subquery can add a calculated value to each row.

SELECT
    admission_id,
    total_bill,
    (
        SELECT MAX(total_bill)
        FROM Admissions
    ) AS highest_hospital_bill
FROM Admissions;

This shows each admission alongside the highest bill recorded in the table.
B. Subquery in FROM
A subquery in the FROM clause acts as a derived table. In PostgreSQL, it must have an alias.

SELECT
    department_id,
    average_bill
FROM (
    SELECT
        department_id,
        AVG(total_bill) AS average_bill
    FROM Admissions
    GROUP BY department_id
) AS department_summary
WHERE average_bill > 20000;

This calculates average bills by department and then returns departments with averages above KSh 20,000.
C. Subquery in WHERE
A subquery in WHERE
filters records according to a calculated value or a set of values.

SELECT
    admission_id,
    patient_id,
    total_bill
FROM Admissions
WHERE total_bill > (
    SELECT AVG(total_bill)
    FROM Admissions
);

This returns admissions whose bills exceed the hospital-wide average.

7. Applying the concepts to a hospital management scenario

Consider a hospital manager who wants to identify departments with high billing, patients with repeated admissions, and admissions that are unusually expensive.
The following query combines a CTE, a window function and a join.

WITH admission_analysis AS (
    SELECT
        admission_id,
        patient_id,
        department_id,
        admission_date,
        total_bill,
        AVG(total_bill) OVER (
            PARTITION BY department_id
        ) AS department_average,
        ROW_NUMBER() OVER (
            PARTITION BY patient_id
            ORDER BY admission_date, admission_id
        ) AS patient_admission_number,
        LAG(total_bill) OVER (
            PARTITION BY patient_id
            ORDER BY admission_date, admission_id
        ) AS previous_bill
    FROM Admissions
)
SELECT
    aa.admission_id,
    p.patient_name,
    d.department_name,
    aa.total_bill,
    aa.department_average,
    aa.patient_admission_number,
    aa.previous_bill
FROM admission_analysis aa
JOIN Patients p
    ON aa.patient_id = p.patient_id
JOIN Departments d
    ON aa.department_id = d.department_id
WHERE aa.total_bill > aa.department_average
ORDER BY aa.total_bill DESC;

What the query achieves

1. CTE: Organizes the admission analysis into a named intermediate result.
2. AVG(): Calculates the average bill for each department.
3. ROW_NUMBER(): Numbers each patient's admissions chronologically.
4. LAG(): Retrieves the previous admission's bill for the same patient.
5. INNER JOIN: Adds patient names and department names.
6. WHERE: Filters for admissions whose bills exceed their department's average.
7. ORDER BY: Displays the highest bills first. Expected result: A list of above-average-cost admissions, showing each patient's name, department, admission sequence and previous bill where available. Real-world value: The report helps hospital management identify costly admissions for further investigation. A high bill alone does not establish poor-quality care or inappropriate treatment; clinical context, service complexity and patient needs must also be considered.

8. Best practices when writing SQL for hospital data

• Use meaningful table aliases, such as p for patients and a for admissions.
• Qualify column names when multiple tables contain columns with the same name.
• Use PARTITION BY when calculations should restart for each patient or department.
• Use explicit window frames for cumulative calculations and LAST_VALUE().
• Select the correct join type based on whether unmatched records must be retained.
• Check for duplicate rows after joins, especially when tables contain one-to-many relationships.
• Use CTEs to separate complex analysis into understandable stages.
• Use EXISTS for existence checks and scalar subqueries for single-value comparisons.
• Handle NULL values deliberately rather than assuming they are equivalent to zero.
• Use stable ordering columns when sequential results must be reproducible.
• Validate totals against source records before presenting financial or operational findings.
• Protect patient confidentiality by limiting identifiable information to authorized purposes.

9. Conclusion

SQL window functions, joins, CTEs and subqueries provide complementary methods for analyzing relational data. Window functions support rankings, comparisons and cumulative calculations while preserving individual records. Joins combine information across related tables. CTEs make complex queries easier to organize, while subqueries support conditional filtering and calculations within other SQL statements.
In hospital management, these techniques can be used to analyze admission trends, compare departmental bills, identify patients with repeated visits, audit incomplete records and monitor operational performance.
The most important skill is not memorizing SQL syntax, but selecting the appropriate technique for the question being investigated. A well-designed query should produce accurate results, preserve the intended level of detail, and provide information that supports responsible decision-making.