SQL CONCEPTS

SQL Window Functions, Joins, CTEs, and Subqueries Using a Hospital Database

Introduction

SQL (Structured Query Language) is used to store, retrieve, and analyze information in relational databases. In healthcare, SQL can help hospitals monitor patient visits, evaluate department performance, analyze treatment costs, track admissions, and prepare operational reports.

This article uses a PostgreSQL hospital database, which contains the following tables:

• patients — patient demographics and locations
• doctors — doctors’ names, specialties, and experience.
• department — hospital departments.
• diagnosis — diagnoses and their categories.
• wards — hospital wards.
• procedures — procedures and their categories.
• visits — visit dates, patient and doctor references, diagnoses, procedures, admission details, billing, and satisfaction scores.

The visits table is the central transaction table. It connects the patient, doctor, department, ward, diagnosis, and procedure records through foreign keys.

1. SQL Window Functions

A window function performs a calculation over a group of related rows while keeping the individual rows in the result. For example, a hospital may want to calculate each patient’s total billing amount while still displaying every visit separately. A window function can show the patient’s total alongside each visit without collapsing all visits into one row. This is different from GROUP BY, which combines rows into summary groups.

1.2 OVER()

The OVER() clause defines the window of rows used by a window function. For example:

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

It calculates the sum of all visit bills and displays the same total on every visit row.

All 30 visits are returned, and each row contains the hospital’s total billing across those visits: KSh 488,000. This is useful when a hospital needs to display an overall billing figure alongside individual patient visits.

1.3 PARTITION BY

PARTITION BY divides rows into independent groups before the window function is calculated. For example:

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

It calculates the total billing for each patient and displays that amount on every visit belonging to the patient.

Patient 1 has two visits with bills of KSh 3,500 and KSh 4,200, so both rows display a patient total of KSh 7,700. In a hospital, this is useful for reviewing a patient’s accumulated bills across multiple visits.

1.4 ORDER BY inside a window

The ORDER BY inside OVER() specifies the order in which rows are ranked or processed. For example:

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

It calculates a running total of hospital billing, starting with the earliest visit.

Expected result: The first visit has a running total of KSh 3,500. The second has KSh 6,000. The sixth visit brings the total to KSh 83,000. The visit_id provides a consistent tie-breaker when multiple visits share the same date.

1.5 Window frames

A window frame specifies which rows around the current row should be included in the calculation. Common frame boundaries include:

• UNBOUNDED PRECEDING: Starts from the first row in the partition.
• CURRENT ROW: Includes the current row.
• n PRECEDING: Includes a specified number of preceding rows.
• n FOLLOWING: Includes a specified number of following rows.
• UNBOUNDED FOLLOWING: Extends to the last row in the partition.

For example:

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

This adds each visit’s bill to the bill from the immediately preceding row.

Visit 1001 shows KSh 3,500. Visit 1002 shows KSh 6,000, combining KSh 3,500 and KSh 2,500. Visit 1003 shows KSh 30,500, combining KSh 2,500 and KSh 28,000.

The ROWS frame is useful for calculations based on a specific number of records. An explicit frame and a deterministic ordering help avoid unexpected results when multiple visits share a date.

1.6 Ranking window functions

Ranking functions assign positions to rows based on an ordering. They are useful for identifying the most expensive visits, ranking doctors by patient satisfaction, and comparing department performance.

1.6.1 ROW_NUMBER()

This is used to assign a unique sequential number to each row and can identify the latest visit for each patient, or number visits chronologically.

SELECT
  patient_id,
  visit_id,
  visit_date,
ROW_NUMBER() OVER (
PARTITION BY patient_id
ORDER BY visit_date DESC, visit_id DESC
) AS visit_number
FROM visits
ORDER BY patient_id, visit_number;

Each patient’s most recent visit receives number 1. Patient 1’s visit 1011 is numbered 1 and visit 1001 is numbered 2. The numbering restarts for each patient because of PARTITION BY patient_id.

1.6.2 RANK()

This is used to assign the same rank to tied values and leaves gaps in subsequent ranks which is useful in ranking visits by bill amount or doctors by average patient satisfaction.

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

Visit 1017, with a bill of KSh 82,000, receives rank 1. Visit 1010, with KSh 76,000, receives rank 2. If two visits have the same bill, they receive the same rank, and the next rank is skipped accordingly.

1.6.3 DENSE_RANK()

This is used to assign ranks without gaps after ties. It is useful for identifying distinct billing levels or grouping doctors by performance tiers.

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

Visits with identical bill amounts receive the same rank. Unlike RANK(), the next distinct bill amount receives the next consecutive rank. For example, if the ordered bills were 7,600, 7,200, 7,000, 6,500, 6,500 and 5,500, RANK() would assign 12,13, 14, 15, 15 and 17, while DENSE_RANK() would assign 12,13, 14, 15, 15 and 16.

1.6.4 NTILE(n)

It is used to divide ordered rows into approximately equal-sized buckets. It can divide visits into four billing groups or classify doctors into performance quartiles.

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

The 30 visits are divided into four buckets of 8, 8, 7, and 7 rows. Bucket 1 contains the highest bills, while bucket 4 contains the lowest bills. NTILE() creates groups of approximately equal row counts, not groups with equal monetary ranges.

1.6.5 LAG()

This retrieves a value from a preceding row without requiring a self-join and can compare a patient’s current bill with their previous bill.

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

Patient 1’s first visit has a NULL previous bill. Their second visit shows KSh 3,500 as the previous bill, allowing it to be compared with the current bill of KSh 4,200. LAG() defaults to an offset of one row. An optional default value can be supplied, such as LAG(total_bill_kes, 1, 0).

1.6.6 LEAD()

This is used to retrieve a value from a subsequent row. It can help identify a patient’s next recorded visit or compare a current bill with the next bill.

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

Patient 1’s first visit shows June 6, 2026, as the next recorded visit date. Their second visit has NULL because no later visit for that patient appears in the dataset. This identifies the next recorded visit in the data, not a future appointment.

1.6.7 FIRST_VALUE()

This returns the first value in the current window frame and can identify the earliest visit date for each patient.

SELECT
  patient_id,
  visit_id,
  visit_date,
FIRST_VALUE(visit_date) OVER (
PARTITION BY patient_id
ORDER BY visit_date, visit_id
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS first_visit_date
FROM visits
ORDER BY patient_id, visit_date;

Both of patient 1’s records display June 1, 2026, as the first visit date. All patients with multiple visits display their own earliest recorded date.

1.6.8 LAST_VALUE()

This returns the last value in the current window frame. It can identify each patient’s most recent recorded visit date.

SELECT
  patient_id,
  visit_id,
  visit_date,
LAST_VALUE(visit_date) OVER (
PARTITION BY patient_id
ORDER BY visit_date, visit_id
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS last_visit_date
FROM visits
ORDER BY patient_id, visit_date;

All visits belonging to patient 1 display June 6, 2026, as their last recorded visit date. The explicit frame is essential here. Without it, LAST_VALUE() may return the current row’s date instead of the final date in the patient’s complete history.

1.7 Aggregate window functions

Aggregate window functions calculate totals, averages, counts, minimums, and maximums without removing individual visit rows.

1.7.1 SUM()

It adds values across a window. For example: Calculate the total billing for each department while retaining each visit.

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

Every visit in the same department displays the same department total. For example, the Cardiology department (ID 10) has total billing of KSh 210,100. This can help administrators examine individual visits alongside department-wide revenue.

1.7.2 AVG()

It calculates the average of values in a window. For example: Calculate the average bill for each department.

SELECT
  visit_id,
  department_id,
  total_bill_kes,
AVG(total_bill_kes) OVER (
PARTITION BY department_id
) AS department_average_bill
FROM visits
ORDER BY department_id, visit_id;

Each visit displays the average bill for its department. For Cardiology, the average is KSh 23,344.44 because its 9 visits total KSh 210,100. The result is repeated for every visit in the same department.

1.7.3 COUNT()

It counts rows or non-null values. For exapmle: Count the number of visits recorded for each patient.

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

Patient 1 has a count of 2 on both of their visit records. Patients with one visit have a count of 1. COUNT(*) counts all rows in the window. COUNT(column_name) counts only rows where that column is not NULL.

1.7.4 MIN()

It returns the smallest value in the window. For example: Identify the lowest bill recorded in each department.

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

Each visit shows the minimum bill for its department. In Cardiology, the minimum is KSh 3,500.

1.7.5 MAX()

It returns the largest value in the window. For example: Identify the highest bill recorded in each department.

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

Each visit shows the maximum bill for its department. In Cardiology, the maximum is KSh 82,000.

2. SQL Joins

A join combines related rows from two or more tables. In the hospital database, the visits table contains IDs linking each visit to a patient, doctor, department, ward, diagnosis, and procedure. Joins allow us to transform those IDs into useful information, such as the patient’s name, doctor’s specialty, diagnosis, and bill amount.

2.1 INNER JOIN

It returns only rows with matching values in both tables. It retrieve visits with the corresponding patient names.

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;

All 30 visits are returned because every visit references an existing patient. In a hospital, an INNER JOIN is useful for preparing reports of recorded visits with valid patient details.

2.2 LEFT JOIN

It returns every row from the left table and matching rows from the right table with unmatched right-side columns contain NULL. For example: Find all registered patients, including those who have never visited the hospital.

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;

All 25 patients are represented. Since every supplied patient has at least one visit, this dataset produces 30 rows, rather than 25, because patients with multiple visits appear more than once. If a registered patient had no visit, that patient would still appear with NULL visit columns.

2.3 RIGHT JOIN

This preserves every row from the right-hand table and returns matching rows from the left-hand table. For example: Display all recorded visits, retaining the visit records even if patient details are missing.

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

All 30 visits are returned with their matching patient names. None of the visit rows has a missing patient because the foreign key enforces this relationship. A RIGHT JOIN can be rewritten as a LEFT JOIN by reversing the table order.

2.4 FULL OUTER JOIN

It returns matching rows and unmatched rows from both tables. For example: Compare registered patients with the patients appearing in the visit register.

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

The supplied data produces 30 rows because all 25 patients have at least one visit and every visit has a matching patient. If either table contained unmatched records, those records would also appear with NULL values for the columns from the other table. This join can help reconcile separate hospital registers.

2.5 CROSS JOIN

It produces every possible combination of rows from two tables. For example: Generate every possible doctor-department combination for planning purposes.

SELECT
  d.doctor_name,
  dep.department_name
FROM doctors d
CROSS JOIN department dep
ORDER BY d.doctor_id, dep.department_id;

Four doctors multiplied by four departments produces 16 rows. The query does not imply that each doctor actually works in every department. It simply generates all possible combinations.

2.6 SELF JOIN

It joins a table to itself using aliases. For example: Compare patients of the same gender who are registered in the same county.

SELECT
  p1.patient_name AS patient_one,
  p2.patient_name AS patient_two,
  p1.county,
  p1.patient_gender
FROM patients p1
JOIN patients p2
ON TRIM(p1.county) = TRIM(p2.county)
AND p1.patient_gender = p2.patient_gender
AND p1.patient_id < p2.patient_id
WHERE p1.county IS NOT NULL
ORDER BY p1.county, p1.patient_id, p2.patient_id;

This matches different patients with the same normalized county and gender. Zero rows are returned as it identifies pairs of patients sharing both attributes. The query includes only unique pairs because p1.patient_id < p2.patient_id prevents matching a patient with themselves or reversing the same pair.

Get Hilda Chepkirui’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

This is useful for data-quality analysis, demographic reporting, or checking whether records share selected characteristics. It should not be used by itself to infer a clinical relationship between patients.

3. Common Table Expressions (CTEs)

A Common Table Expression (CTE) is a named query result defined using the WITH clause. It helps organize complex SQL statements into smaller, more readable steps.

CTEs are useful for hospital reporting because calculations such as patient billing, department averages, and doctor performance often require several stages of processing.

3.1 Basic CTE

For example: Calculate the total billing for each patient and display patients whose total exceeds KSh 10,000.

WITH patient_billing AS (
  SELECT
    patient_id,
    SUM(total_bill_kes) AS total_billing
FROM visits
GROUP BY patient_id
)
SELECT
   p.patient_id,
   p.patient_name,
  pb.total_billing
FROM patient_billing pb
JOIN patients p
ON pb.patient_id = p.patient_id
WHERE pb.total_billing > 10000
ORDER BY pb.total_billing DESC;

What it does:

• The CTE groups visits by patient and calculates each patient’s total bill.
• The main query joins those totals to the patient names.
• The WHERE clause keeps only patients whose total billing exceeds KSh 10,000.

The report includes patients such as David Mutua, with KSh 42,000, and Peter Ouma with 98,000. Patients with multiple expensive visits, such as patient 10, also appear with their combined total.

This approach separates the calculation from the final report, making the SQL easier to understand and maintain.

3.2 Multiple CTEs

Multiple CTEs allow a query to perform several stages of processing, with later CTEs referencing earlier ones. For example: Calculate total billing per department, then identify departments whose total exceeds KSh 100,000.

WITH visit_details AS (
 SELECT
   v.department_id,
   v.total_bill_kes,
  v.visit_id
 FROM visits v
),
department_totals AS (
SELECT
  department_id,
  COUNT(*) AS total_visits,
  SUM(total_bill_kes) AS total_billing,
  AVG(total_bill_kes) AS average_bill
FROM visit_details
GROUP BY department_id
)
SELECT
  dep.department_name,
  dt.total_visits,
  dt.total_billing,
  ROUND(dt.average_bill, 2) AS average_bill
FROM department_totals dt
JOIN department dep
ON dt.department_id = dep.department_id
WHERE dt.total_billing > 100000
ORDER BY dt.total_billing DESC;

What it does:

• visit_details selects the visit data needed for analysis.
• department_totals calculates the number of visits, total billing, and average bill for each department.
• The final query adds department names and filters the results.

Cardiology and Orthopedics are included because their total billings each exceed KSh 100,000 in the supplied data. This structure is useful when hospital administrators need several performance indicators in one report.

3.3 Recursive CTEs

A recursive CTE references its own result to process hierarchical data or generate a sequence. It generally contains:

• An anchor query that establishes the starting rows.
• UNION ALL to combine the starting rows with subsequent results.
• A recursive query that refers to the CTE.
• A termination condition.

The supplied hospital schema does not contain a parent-child hierarchy, such as departments with parent departments. To demonstrate the syntax using the existing visit data, we can generate a sequence of dates covering the visit period.

WITH RECURSIVE calendar AS (
  SELECT DATE '2026-06-01' AS calendar_date
  UNION ALL
  SELECT calendar_date + 1
  FROM calendar
  WHERE calendar_date < DATE '2026-06-15'
)
SELECT calendar_date
FROM calendar
ORDER BY calendar_date;

What it does: Starts on June 1, 2026, and generates one additional date at each recursive step until June 15.

Fifteen dates are returned. In a hospital, a calendar CTE could support daily reporting, including dates with zero visits. A true hierarchy example would require additional parent-child data, which the supplied tables do not currently contain.

3.4 CTEs with joins

For example: Show the total number of visits handled by each doctor, including doctors with no visits.

WITH doctor_visits AS (
SELECT
  doctor_id,
  COUNT(*) AS visit_count
FROM visits
GROUP BY doctor_id
)
SELECT
  d.doctor_id,
  d.doctor_name,
  COALESCE(dv.visit_count, 0) AS visit_count
FROM doctors d
LEFT JOIN doctor_visits dv
ON d.doctor_id = dv.doctor_id
ORDER BY visit_count DESC, d.doctor_id;

The CTE counts visits per doctor. The LEFT JOIN preserves all doctors, while COALESCE changes missing visit counts to zero. All four doctors are listed. Dr. Brian Mwangi has the highest visit count, with 9 visits, followed by Dr. Sarah Wanjiku with 9, Dr. Peter Otieno with 6, and Dr. Faith Chebet with 6.

3.5 CTEs with aggregations

For example: Calculate the total number of visits, total billing, and average patient satisfaction for each admission type.

WITH admission_summary AS (
SELECT
  admission_type,
  COUNT(*) AS visit_count,
  SUM(total_bill_kes) AS total_billing,
  AVG(patient_satisfaction_score) AS average_satisfaction
FROM visits
GROUP BY admission_type
)
SELECT
  admission_type,
  visit_count,
  total_billing,
  ROUND(average_satisfaction, 2) AS average_satisfaction
FROM admission_summary
ORDER BY total_billing DESC;

It groups visits by admission_type and calculates three measures for each group.

The output contains three rows: Outpatient, Inpatient, and Emergency. Each row reports its number of visits, total billing, and average satisfaction score. This is useful for comparing outpatient, inpatient, and emergency service activity.

A CTE is often easier to read when several steps are involved. It does not improve the performance of a query because that is dependent on the query plan, data size, indexes, and database optimization.

4. SQL Subqueries

A subquery is a query placed inside another SQL statement. Its result may be a single value, multiple values, or a table-like result.

In hospital databases, subqueries can identify expensive visits, patients with repeat visits, doctors with above-average workloads, or diagnoses associated with specific procedures.

4.1 Scalar subqueries

A scalar subquery returns one column and at most one row. It can be used wherever SQL expects a single value. For example: Find visits whose bills exceed the average bill across all visits.

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;

The subquery calculates the average bill. The outer query returns visits with bills above that amount.

The query returns visits with bills greater than that average, including visit 1017 (KSh 82,000), visit 1010 (KSh 76,000), visit 1004 (KSh 42,000), and other above-average visits. The query returns individual visits rather than one summary row.

4.2 Non-correlated subqueries

A non-correlated subquery does not refer to columns from the outer query. It can be understood independently. For example: Find visits handled by doctors whose specialty is Cardiology.

SELECT
  visit_id,
  doctor_id,
  visit_date,
  total_bill_kes
FROM visits
WHERE doctor_id IN (
  SELECT doctor_id
  FROM doctors
  WHERE doctor_specialty = 'Cardiologist'
  )
ORDER BY visit_date;

The subquery retrieves the ID of the cardiologist, and the outer query returns visits assigned to that doctor.

Nine visits are returned because Dr. Brian Mwangi is the only doctor in the supplied data whose specialty is Cardiologist. The subquery does not depend on the current visit row.

4.3 Correlated subqueries

A correlated subquery refers to one or more columns from the outer query. For example: Find patients who have more than one recorded visit.

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

For each patient, the subquery counts visits belonging to that patient. The outer query retains patients with a count greater than one.

Expected result: Patients 1, 2, 5, 7, and 10 are returned. Each of these patients has two recorded visits. The reference v.patient_id = p.patient_id makes the subquery correlated.

4.4 Nested subqueries

A nested subquery contains another subquery within it. For example: Find visits whose bills exceed the average bill for visits handled by cardiologists.

SELECT
  visit_id,
  doctor_id,
  total_bill_kes
FROM visits
WHERE total_bill_kes > (
  SELECT AVG(total_bill_kes)
  FROM visits
  WHERE doctor_id IN (
    SELECT doctor_id
    FROM doctors
    WHERE doctor_specialty = 'Cardiologist'
    )
  )
ORDER BY total_bill_kes DESC;

What it does:

• The innermost query identifies cardiologists.
• The middle query calculates the average bill for visits handled by those doctors.
• The outer query returns visits with bills above that average.

Visits to the cardiologist with bills above that threshold are returned, regardless of which doctor handled them. This demonstrates how nested queries can express multi-stage conditions without requiring a separate CTE.

4.5 Subqueries using IN

IN checks whether a value matches any value in a set returned by a subquery. For example: Find patients who have at least one emergency visit.

SELECT
  patient_id,
  patient_name
FROM patients
WHERE patient_id IN (
  SELECT patient_id
  FROM visits
  WHERE admission_type = 'Emergency'
)
ORDER BY patient_id;

The subquery retrieves the IDs of patients who have an emergency visit. The outer query returns the corresponding patient names.

Patient 10, Peter Ouma, is returned because visit 1030 is classified as an emergency. IN is useful when the question is whether a record’s value appears in a list produced by another query.

4.6 Subqueries using EXISTS

EXISTS checks whether a subquery returns at least one row. For example: Find patients who have a visit requiring follow-up.

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

For each patient, the subquery checks whether at least one associated visit has follow_up_required = ‘Yes’.

The query returns patients who have at least one visit requiring follow-up. A patient is included only once, even if several visits require follow-up. SELECT 1 is commonly used inside EXISTS because the actual values returned by the subquery do not matter; only the existence of a row matters.

4.7 Subqueries using ANY

ANY makes a comparison against a set of values and returns true if the comparison succeeds for at least one value. For example: Find visits whose bills exceed at least one bill recorded for emergency visits.

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

The subquery returns the emergency bill, KSh 22,000. The outer query returns visits with bills greater than that amount.

Visits with bills above KSh 22,000 are returned, including the visits with bills of KSh 24,000, KSh 28,000, KSh 33,000, KSh 42,000, KSh 76,000, and KSh 82,000. Because the sample has one emergency visit, this example produces the same result as comparing against that single bill. With multiple emergency visits, > ANY means the bill must exceed at least one of the emergency bills, not necessarily all of them.

4.8 Subqueries using ALL

ALL makes a comparison against every value returned by a subquery. For example: Find visits whose bills exceed every emergency bill.

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

The subquery retrieves the bill amounts for emergency visits. The outer query selects visits whose bills exceed all values in that set. Since the supplied data contains one emergency visit with a bill of KSh 22,000, the result is the same as the ANY example.

If several emergency visits existed, > ALL would require a bill greater than the largest emergency bill, whereas > ANY would require it to exceed at least one emergency bill.

4.9 Subqueries in the SELECT clause

A scalar subquery can be placed in the SELECT list to calculate an additional value for every output row. For example: Display each patient with their total number of visits.

SELECT
  p.patient_id,
  p.patient_name,
(
  SELECT COUNT(*)
  FROM visits v
  WHERE v.patient_id = p.patient_id
) AS total_visits
FROM patients p
ORDER BY p.patient_id;

For each patient, the scalar subquery counts the number of matching visits.

All 25 patients are returned. Patients with two visits display 2, while patients with one visit display 1. This is a correlated scalar subquery because it references p.patient_id from the outer query.

4.10 Subqueries in the FROM clause

A subquery in the FROM clause creates a derived table that can be joined or filtered by the outer query. For example: Calculate average billing for each department, then display the department names.

SELECT
  d.department_name,
  billing.department_average
FROM (
  SELECT
  department_id,
  AVG(total_bill_kes) AS department_average
  FROM visits
  GROUP BY department_id
  ) AS billing
JOIN department d
ON billing.department_id = d.department_id
ORDER BY billing.department_average DESC;

The derived table calculates average billing per department. The outer query joins the results to the department names.

Four rows are returned, one per department, ordered by average bill from highest to lowest. In PostgreSQL, a derived table in the FROM clause must have an alias, such as billing.

4.11 Subqueries in the WHERE clause

Subqueries in WHERE filter rows based on a value or set produced by another query. For example: Find visits whose bills are greater than the average bill for all visits.

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;

The inner query calculates the average bill. The outer query returns visits whose bills are above that average.

The individual visit rows remain visible, making it easy to identify the highest-cost encounters. This is the same scalar-subquery technique discussed earlier, illustrating how subqueries in WHERE support data filtering.

A subquery may be simpler when the task involves a single calculation or filtering condition. However, it does not improve the performance of a query because that is dependent on the query plan, data size, indexes, and database optimization.

Practical comparison: Window functions, CTEs, and subqueries

These concepts can solve similar problems, but they approach them differently. For example: Find the latest recorded visit for every patient.

Method 1: Using a window function

WITH ranked_visits AS (
  SELECT
  patient_id,
  visit_id,
  visit_date,
  ROW_NUMBER() OVER (
  PARTITION BY patient_id
  ORDER BY visit_date DESC, visit_id DESC
) AS rn
FROM visits
)
SELECT
patient_id,
visit_id,
visit_date
FROM ranked_visits
WHERE rn = 1;

The window function ranks visits within each patient’s history. The CTE allows the outer query to filter on the calculated row number.

Method 2: Using a correlated subquery

SELECT
  v.patient_id,
  v.visit_id,
  v.visit_date
FROM visits v
WHERE v.visit_date = (
SELECT MAX(v2.visit_date)
FROM visits v2
WHERE v2.patient_id = v.patient_id
)
ORDER BY v.patient_id, v.visit_id;

The subquery calculates each patient’s latest visit date, and the outer query retrieves visits matching that date.

The window-function method returns exactly one row per patient because ROW_NUMBER() assigns a unique sequence. The correlated subquery can return multiple rows if a patient has multiple visits on the same latest date. This distinction matters when reporting the most recent visit.

Conclusion

SQL window functions, joins, Common Table Expressions, and subqueries are essential techniques for extracting useful information from a relational hospital database.

Window functions support patient-history analysis, ranking, running totals, and comparisons between visits. Joins connect patient, doctor, department, diagnosis, procedure, and ward records. CTEs organize multi-step reports into readable stages, while subqueries allow one query to filter or calculate results based on another.

In practice, the best technique depends on the question being answered. A window function is useful when individual visits must remain visible alongside patient-level calculations. A join is appropriate when combining information from related tables. A CTE is useful for structuring complex reporting logic, while a subquery is often suitable for comparisons and existence checks.