Introduction
SQL allows us to store, retrieve, organize and analyze data in relational databases. In a hospital, SQL can help management understand patient visits, identify doctors handling the most cases, compare hospital bills, track admissions and investigate patterns in diagnoses and this article uses the PostgreSQL hospital database, which contains tables such as patients, doctors, department, diagnosis, procedures, wards and visits. The visits table is especially important because it records hospital visits and connects other tables through IDs. For example, visits.patient_id references patients.patient_id, and visits.doctor_id references doctors.doctor_id.
Before running the examples:
SQL Window Functions
What are window functions?
A window function performs a calculation across a set of related rows while keeping the individual rows in the result. For example, a hospital manager can see every visit alongside the total bill for that visit’s admission type. A GROUP BY query would combine visits into groups, but a window function can calculate a group total while still displaying each visit.
Window functions are useful for ranking doctors or visits, comparing bills across rows, calculating running totals, and finding group averages, counts, minimums and maximums.
OVER(), PARTITION BY, ORDER BY and window frames
Over() Defines the windows over which a function operates
Partition by Divides rows into independent groups without collapsing them.
Order by Defines the order in which rows are ranked.
Window frame Defines the particular rows in a partition used for a calculation relative to the current row.
Example Calculate a total for each group while retaining individual rows.
Compare billing across outpatient, inpatient and emergency visit
Select
Visit_id,
Visit_date,
Admission_type,
Total_bill_kes,
Sum(total_bill_kes) over |(partition by admission_type) as total_bill_by_admission_type
From visits
Order by visit_id;
Each visit remains in the output. All outpatient visits show the same outpatient total; inpatient and emergency visits show their own group totals. PARTITION BY separates the groups, while the final ORDER BY sorts the output.
A window frame determines exactly which rows inside the ordered window are included. For a running total, use a frame from the first row through the current row.
Example Calculate a cumulative sum in a deterministic order.
Hospital scenario: Track how recorded billing accumulates from one visit to the next.
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_kes
FROM visits;
The total begins at KES 3,500 for visit 1001, reaches KES 6,000 after visit 1002, and KES 34,000 after visit 1003. The frame includes all rows from the beginning through the current row.
Ranking window functions
ROW_NUMBER()
A unique sequential number to every row in the window.
Hospital scenario: List each doctor’s visits from the highest bill to the lowest.
SELECT
visit_id,
doctor_id,
total_bill_kes,
ROW_NUMBER() OVER (
PARTITION BY doctor_id
ORDER BY total_bill_kes DESC, visit_id
) AS bill_position
FROM visits;
Each doctor has a sequence beginning at 1. Doctor 101’s visit 1017, costing KES 82,000, ranks first among that doctor’s visits. The visit_id tie-breaker makes ordering predictable.
RANK()
Assign the same rank to tied values and leave gaps after ties.
Rank visits by total bill.
SELECT
visit_id,
total_bill_kes,
RANK() OVER (
ORDER BY total_bill_kes DESC
) AS bill_rank
FROM visits;
Visit 1017, costing KES 82,000, receives rank 1; visit 1010, costing KES 76,000, receives rank 2. If two visits tie for first, both get rank 1 and the next rank is 3.
DENSE_RANK()
Assign the same rank to ties without leaving gaps.
Rank distinct bill amounts without skipping rank numbers after ties.
SELECT
visit_id,
total_bill_kes,
DENSE_RANK() OVER (
ORDER BY total_bill_kes DESC
) AS bill_rank
FROM visits;
The highest bill receives rank 1. The next distinct bill receives rank 2, even if several visits share rank 1.
NTILE()
Divide ordered rows into a specified number of approximately equal-sized groups.
Divide the 30 visits into four groups by bill amount.
SELECT
visit_id,
total_bill_kes,
NTILE(4) OVER (
ORDER BY total_bill_kes DESC, visit_id
) AS bill_quartile
FROM visits;
Every visit is assigned to group 1, 2, 3 or 4. Group 1 contains the highest-billed visits and group 4 the lowest. Because 30 is not divisible by four, the groups will not all have the same size. NTILE creates groups, not statistical quartile boundaries.
Navigation window functions
LAG()
Retrieve a value from a preceding row without joining the table to itself.
Compare each visit’s bill with the previous visit’s bill in the ordered data.
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;
Visit 1001 has NULL because it is first. Visit 1002 shows KES 3,500, the bill for visit 1001. LAG returns NULL when no preceding row exists unless a default is supplied.
LEAD()
Retrieve a value from a subsequent row in the ordered result.
Display the next visit’s bill beside the current visit.
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;
Visit 1001 shows KES 2,500 as the next bill because visit 1002 follows it. The final row has NULL because there is no subsequent row. LAG looks backward; LEAD looks forward.
FIRST_VALUE()
Return the first value in the specified window frame.
Show each visit alongside the highest bill recorded for that visit’s doctor.
SELECT
visit_id,
doctor_id,
total_bill_kes,
FIRST_VALUE(total_bill_kes) OVER (
PARTITION BY doctor_id
ORDER BY total_bill_kes DESC, visit_id
) AS highest_bill_for_doctor
FROM visits;
Every visit handled by doctor 101 displays KES 82,000, the highest bill for that doctor. Other doctors display their own highest-bill values.
LAST_VALUE()
Return the last value in the specified window frame. With ORDER BY, the default frame commonly ends at the current row’s last peer rather than the whole partition.
Show each doctor’s lowest bill alongside every visit handled by that doctor.
SELECT
visit_id,
doctor_id,
total_bill_kes,
LAST_VALUE(total_bill_kes) OVER (
PARTITION BY doctor_id
ORDER BY total_bill_kes, visit_id
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS lowest_bill_for_doctor
FROM visits;
All visits handled by doctor 101 show KES 3,500 as that doctor’s lowest bill. The explicit frame makes PostgreSQL examine the whole partition.
Aggregate window functions
SUM()
Add values across a window.
Display each visit alongside total recorded hospital billing.
SELECT
visit_id,
total_bill_kes,
SUM(total_bill_kes) OVER () AS total_hospital_billing
FROM visits;
Every visit shows its own bill and the same overall total of KES 577,000 across all 30 visits. The window calculation retains individual rows.
AVG()
Calculate the average value across a window.
Compare each bill with the average bill for the same admission type.
SELECT
visit_id,
admission_type,
total_bill_kes,
AVG(total_bill_kes) OVER (
PARTITION BY admission_type
) AS average_bill_by_admission
FROM visits;
Each visit displays its bill and the average for its admission type. All inpatient visits share one inpatient average, for example.
COUNT()
Count rows or non-NULL values within a window.
Show how many visit records each doctor handled.
SELECT
visit_id,
doctor_id,
COUNT(*) OVER (
PARTITION BY doctor_id
) AS visits_handled_by_doctor
FROM visits;
Each visit for doctors 101 and 102 displays 10; visits for doctors 103 and 104 display 5. This counts visit records, not distinct patients.
MIN()
Find the minimum value within a window.
Show the smallest bill recorded for each admission type.
SELECT
visit_id,
admission_type,
total_bill_kes,
MIN(total_bill_kes) OVER (
PARTITION BY admission_type
) AS minimum_bill_for_admission
FROM visits;
Every visit displays the minimum bill for its admission type. The minimum outpatient bill is KES 1,800, for visit 1014.
MAX()
Find the maximum value within a window.
Show the largest bill recorded for each admission type.
SELECT
visit_id,
admission_type,
total_bill_kes,
MAX(total_bill_kes) OVER (
PARTITION BY admission_type
) AS maximum_bill_for_admission
FROM visits;
Every visit displays the maximum bill for its admission type. The maximum inpatient bill is KES 82,000 for visit 1017; the emergency visit is KES 22,000.
SQL Joins
*What are SQL joins?
*
A join combines data from two or more tables using related columns or a join condition. In the hospital database, visits stores IDs rather than repeating every patient, doctor and department name. Joins retrieve those details together.
INNER JOIN
Return only rows that have matching records in both tables.
Hospital scenario: Create a detailed report of each visit, its patient and the doctor responsible.
SELECT
v.visit_id,
p.patient_name,
d.doctor_name,
v.total_bill_kes
FROM visits v
INNER JOIN patients p
ON v.patient_id = p.patient_id
INNER JOIN doctors d
ON v.doctor_id = d.doctor_id;
The report contains 30 rows because every visit references an existing patient and doctor. A patient with repeat visits appears multiple times. Use INNER JOIN when matching records are required.
LEFT JOIN
Preserve every row from the left table and add matching right-table values; unmatched right-side values are NULL.
Hospital scenario: List every doctor, including any who have no visits.
SELECT
d.doctor_id,
d.doctor_name,
v.visit_id
FROM doctors d
LEFT JOIN visits v
ON d.doctor_id = v.doctor_id
ORDER BY d.doctor_id, v.visit_id;
All four doctors appear. In the current dataset each doctor has visits; a doctor without visits would still appear with a NULL visit_id.
RIGHT JOIN
Purpose: Preserve every row from the right table and add matching left-table values; unmatched left-side values are NULL.
Hospital scenario: List every registered patient, including any who have never visited.
SELECT
v.visit_id,
v.total_bill_kes,
p.patient_id,
p.patient_name
FROM visits v
RIGHT JOIN patients p
ON v.patient_id = p.patient_id
ORDER BY p.patient_id, v.visit_id;
All 25 patients appear. Patients with several visits may appear on several rows; patients without visits would have NULL visit details. All supplied patients have at least one visit.
FULL OUTER JOIN
Purpose: Return matches plus unmatched rows from both tables.
Hospital scenario: Reconcile doctor records against visit records.
SELECT
d.doctor_id,
d.doctor_name,
v.visit_id
FROM doctors d
FULL OUTER JOIN visits v
ON d.doctor_id = v.doctor_id;
Matching doctor-visit combinations appear. Unmatched doctors would have NULL visit details, and visits without matching doctors would have NULL doctor details. The supplied foreign-key data has no such unmatched records.
CROSS JOIN
Produce every possible combination of rows from two tables.
Create a planning matrix of all doctors and all departments.
SELECT
d.doctor_name,
dep.department_name
FROM doctors d
CROSS JOIN department dep;
Four doctors multiplied by four departments produces 16 rows. Every doctor is paired with every department, whether or not that doctor actually works there. Use CROSS JOIN only when all combinations are intended.
SELF JOIN
Join a table to itself using aliases to compare different rows in that same table.
Identify pairs of patients registered in the same county.
SELECT
p1.patient_name AS patient_one,
p2.patient_name AS patient_two,
p1.county
FROM patients p1
JOIN patients p2
ON p1.county = p2.county
AND p1.patient_id < p2.patient_id
ORDER BY p1.county;
Patient pairs from the same county are listed. For example, Amina Otieno and Isaac Kamau are both registered in Nairobi. The ID condition avoids pairing a patient with themselves and avoids reversed duplicate pairs.
Join comparison
Join What it preserves Common use
INNER JOIN -Matching rows only- Visits with patient and doctor details
LEFT JOIN -Every left-side row All doctors, including those without visits
RIGHT JOIN -Every right-side row -All patients, including those without visits
FULL OUTER JOIN -All rows from both sides -Reconciliation and mismatch detection
CROSS JOIN -Every possible combination -Planning matrices and combinations
SELF JOIN -Related rows within the same table- Comparing patients in the same county
A join does not automatically aggregate data. If a patient has five visits, joining that patient to visits produces five matching rows. Use aggregation when you need one summary row per patient or doctor.
Common Table Expressions (CTEs)
** What is a CTE?**
A Common Table Expression (CTE) is a named result set defined using WITH. It can be referenced by the main SQL statement that follows it. CTEs break complicated SQL into smaller, more understandable steps.
Basic CTE
Define one temporary named result set for use within a single SQL statement.
Compare total recorded billing associated with each doctor’s visits.
WITH doctor_billing AS (
SELECT
doctor_id,
SUM(total_bill_kes) AS total_billing_kes
FROM visits
GROUP BY doctor_id
)
SELECT
d.doctor_name,
db.total_billing_kes
FROM doctor_billing db
JOIN doctors d
ON db.doctor_id = d.doctor_id
ORDER BY db.total_billing_kes DESC;
One row per doctor shows the doctor’s name and the sum of bills for their visits, ordered from highest to lowest. The CTE aggregates visits; the main query joins in doctor names. The CTE does not create a permanent table.
Multiple CTEs
Define two or more named result sets that can feed into the main query.
Compare doctors by total billing and number of visits.
_
WITH doctor_billing AS (
SELECT
doctor_id,
SUM(total_bill_kes) AS total_billing_kes
FROM visits
GROUP BY doctor_id
),
doctor_visit_counts AS (
SELECT
doctor_id,
COUNT(*) AS total_visits
FROM visits
GROUP BY doctor_id
)
SELECT
d.doctor_name,
db.total_billing_kes,
vc.total_visits
FROM doctors d
JOIN doctor_billing db
ON d.doctor_id = db.doctor_id
JOIN doctor_visit_counts vc
ON d.doctor_id = vc.doctor_id
ORDER BY db.total_billing_kes DESC;_
One row per doctor displays name, total billing and visit count. One CTE calculates billing, another calculates visit counts, and the main query combines them.
Recursive CTE
Repeatedly reference a CTE’s own result to process sequential or hierarchical data.
Generate all dates in a five-day hospital reporting period, including dates without visits.
WITH RECURSIVE reporting_dates AS (
SELECT DATE '2026-06-01' AS report_date
UNION ALL
SELECT report_date + 1
FROM reporting_dates
WHERE report_date < DATE '2026-06-05'
)
SELECT report_date
FROM reporting_dates
ORDER BY report_date;
Five dates are returned: 1–5 June 2026. The anchor query starts at 1 June; the recursive query adds one day each time; the WHERE condition stops after 5 June. Recursive CTEs are also useful for parent-child hierarchies.
CTE with joins and aggregations
Aggregate data in a CTE, then join it to a descriptive table.
Compare departments by visit volume, total billing and average bill.
WITH department_summary AS (
SELECT
department_id,
COUNT(*) AS total_visits,
SUM(total_bill_kes) AS total_billing_kes,
AVG(total_bill_kes) AS average_bill_kes
FROM visits
GROUP BY department_id
)
SELECT
dep.department_name,
ds.total_visits,
ds.total_billing_kes,
ROUND(ds.average_bill_kes, 2) AS average_bill_kes
FROM department_summary ds
JOIN department dep
ON ds.department_id = dep.department_id
ORDER BY ds.total_billing_kes DESC;
Each department appears with visit count, total billing and average bill. The CTE aggregates the visits, and the join adds department names.
CTEs versus subqueries
Feature CTE Subquery
Definition Uses WITH Written inside another query
Readability Often clearer for multi-step logic Convenient for short calculations
Reuse in one statement Can be referenced multiple times, depending on structure May need to be repeated
Recursion Supports WITH RECURSIVE Does not directly provide the same recursive CTE structure
Performance Depends on query and PostgreSQL plan Depends on query and PostgreSQL plan
A CTE is not automatically faster than a subquery. PostgreSQL may inline or materialize a CTE depending on the query, version and optimization choices. Use CTEs primarily for clarity and inspect the execution plan when performance matters.
SQL Subqueries
What is a subquery?
A subquery is a SQL query placed inside another query. It can return a single value, a set of values, a derived table, or a Boolean result used to filter data.
Scalar subquery
Return a single value (one row and one column) for use in an expression or comparison.
Find visits with bills above the average bill across all visits.
SELECT
visit_id,
total_bill_kes
FROM visits
WHERE total_bill_kes > (
SELECT AVG(total_bill_kes)
FROM visits
);
Only visits with bills greater than the overall average are returned. The inner query calculates the average; the outer query compares every visit against it. A scalar subquery must return no more than one row.
Non-correlated subquery
Run independently of the current outer row because it does not reference outer-query columns.
Hospital scenario: Find visits involving patients registered in Kisumu County.
SELECT
visit_id,
patient_id,
total_bill_kes
FROM visits
WHERE patient_id IN (
SELECT patient_id
FROM patients
WHERE county = 'Kisumu'
);
Visits for patients whose county is exactly Kisumu are returned, including visits for Esther Achieng and Brian Omondi. The inner query identifies patient IDs and does not depend on the outer row.
Correlated subquery
Reference a column from the outer query, making the subquery depend logically on each outer row.
Identify doctors who have handled more than five visits.
SELECT
d.doctor_id,
d.doctor_name
FROM doctors d
WHERE (
SELECT COUNT(*)
FROM visits v
WHERE v.doctor_id = d.doctor_id
) > 5;
Doctors 101 and 102 qualify because each handled 10 visits. Doctors 103 and 104 handled five each and do not qualify. The inner query refers to d.doctor_id from the outer query. PostgreSQL may optimize execution rather than literally rerunning it for every row.
Nested subqueries
Place a subquery inside another subquery to answer a question in multiple stages.
Find patients who have at least one visit with a bill above the overall average.
SELECT
patient_id,
patient_name,
county
FROM patients
WHERE patient_id IN (
SELECT patient_id
FROM visits
WHERE total_bill_kes > (
SELECT AVG(total_bill_kes)
FROM visits
)
);
The innermost query calculates the average bill; the middle query identifies patient IDs with an above-average visit; the outer query retrieves those patients. Each patient is returned once even if they have multiple qualifying visits.
IN operator
Check whether a value matches any value in a list or subquery result.
Retrieve visits associated with diagnoses categorized as chronic diseases.
SELECT
visit_id,
patient_id,
diagnosis_id,
total_bill_kes
FROM visits
WHERE diagnosis_id IN (
SELECT diagnosis_id
FROM diagnosis
WHERE diagnosis_category = 'Chronic Disease'
);
The result includes visits linked to diagnoses such as Hypertension, Diabetes and Heart Failure. The inner query returns diagnosis IDs in the exact category Chronic Disease.
EXISTS operator
Test whether a subquery returns at least one row.
Find patients who have had an emergency visit.
SELECT
p.patient_id,
p.patient_name,
p.county
FROM patients p
WHERE EXISTS (
SELECT 1
FROM visits v
WHERE v.patient_id = p.patient_id
AND v.admission_type = 'Emergency'
);
The query returns patients with at least one emergency visit. In the supplied data, visit 1030 is an emergency visit involving patient 10, Peter Ouma. SELECT 1 is used because only existence matters.
ANY operator
Return true when a comparison succeeds for at least one value returned by a subquery.
Find visits whose bills exceed at least one emergency-visit bill.
SELECT
visit_id,
total_bill_kes
FROM visits
WHERE total_bill_kes > ANY (
SELECT total_bill_kes
FROM visits
WHERE admission_type = 'Emergency'
);
There is one emergency visit costing KES 22,000, so the query returns visits costing more than KES 22,000. With multiple non-NULL emergency bills, > ANY means greater than at least one of them, effectively greater than the minimum.
ALL operator
Return true only when a comparison succeeds for every value returned by a subquery.
Find visits whose bills exceed every emergency-visit bill.
SELECT
visit_id,
total_bill_kes
FROM visits
WHERE total_bill_kes > ALL (
SELECT total_bill_kes
FROM visits
WHERE admission_type = 'Emergency'
);
With one emergency visit costing KES 22,000, the query returns visits costing more than KES 22,000. With multiple non-NULL emergency bills, > ALL means greater than the maximum.
ANY versus ALL: > ANY means greater than at least one value; > ALL means greater than every value. Empty subqueries and NULL values have special SQL comparison semantics. In particular, NULL values can affect comparisons, and NOT IN requires care when the subquery can return NULL.
Subqueries in SELECT, FROM and WHERE
Subquery in SELECT
Add a calculated scalar value to every output row.
Hospital scenario: Display every visit’s bill alongside the hospital-wide average.
SELECT
visit_id,
total_bill_kes,
(
SELECT AVG(total_bill_kes)
FROM visits
) AS hospital_average_bill
FROM visits;
Every visit appears with its own bill and the same hospital-wide average. The subquery calculates the average independently of each visit.
Subquery in FROM
Create a derived table that the outer query can treat like a table.
Find doctors whose total recorded billing exceeds KES 100,000.
SELECT
doctor_id,
total_billing_kes
FROM (
SELECT
doctor_id,
SUM(total_bill_kes) AS total_billing_kes
FROM visits
GROUP BY doctor_id
) AS doctor_summary
WHERE total_billing_kes > 100000
ORDER BY total_billing_kes DESC;
The inner query calculates each doctor’s total bills. The outer query filters totals above KES 100,000 and sorts them from highest to lowest. The derived table needs an alias in PostgreSQL.
Subquery in WHERE
Filter records according to a calculated value or set of values.
Find visits whose bills exceed the overall average.
SELECT
visit_id,
patient_id,
total_bill_kes
FROM visits
WHERE total_bill_kes > (
SELECT AVG(total_bill_kes)
FROM visits
);
Only visits with bills above the average are returned. Use this form when the records you need depend on a separate calculation or list of values.
Clause Purpose Hospital application
SELECT Adds a calculated value to the output Show each bill beside the hospital average
FROM Creates a derived table for further analysis Calculate and filter total billing by doctor
WHERE Filters rows using a subquery result Find visits above average billing.
Conclusion
Window functions, joins, CTEs and subqueries are fundamental SQL techniques for analyzing relational data.
- Window functions calculate rankings, comparisons and summaries while preserving individual rows.
- Joins combine related information stored across tables.
- CTEs organize complex queries into understandable steps, while recursive CTEs support sequential and hierarchical analysis.
- Subqueries allow one query to depend on another query’s results.
Top comments (0)