SQL Window Functions, Joins, CTEs and Subqueries: A Hospital Data Guide

Window functions, joins, CTEs and subqueries are the four tools that turn raw hospital tables into answers: who is busiest, who was missed, what each department earns, how patients return. This article explains each one, shows why it exists and runs it on one small hospital database. Every query was run in PostgreSQL and the results shown are the real output.

Introduction and the hospital dataset

The dataset has five tables. Doctors belong to departments, and a doctor can report to another doctor. Patients book appointments with doctors and are sometimes admitted to a department.

Table	What a row is	Key columns
departments	One hospital department	department_id, department_name
doctors	One doctor	doctor_id, department_id, supervisor_id (another doctor)
patients	One patient	patient_id, gender, date_of_birth, city
appointments	One booking	patient_id, doctor_id, appointment_date, status, fee
admissions	One hospital stay	patient_id, department_id, admit_date, discharge_date, bill_amount

The data is built to expose the differences between concepts. Irene Kamau (patient 9) has never booked an appointment. Dr. Mutua (doctor 8) has no appointments. Oncology (department 5) has no doctors. Dr. Karanja, the chief medical officer, has no department and no supervisor.

Run this script once to follow along.

CREATE TABLE departments (
    department_id   INT PRIMARY KEY,
    department_name VARCHAR(50) NOT NULL
);

CREATE TABLE doctors (
    doctor_id     INT PRIMARY KEY,
    doctor_name   VARCHAR(50) NOT NULL,
    department_id INT REFERENCES departments (department_id),
    supervisor_id INT REFERENCES doctors (doctor_id)
);

CREATE TABLE patients (
    patient_id    INT PRIMARY KEY,
    patient_name  VARCHAR(50) NOT NULL,
    gender        CHAR(1),
    date_of_birth DATE,
    city          VARCHAR(50)
);

CREATE TABLE appointments (
    appointment_id   INT PRIMARY KEY,
    patient_id       INT REFERENCES patients (patient_id),
    doctor_id        INT REFERENCES doctors (doctor_id),
    appointment_date DATE,
    status           VARCHAR(20),
    fee              DECIMAL(10,2)
);

CREATE TABLE admissions (
    admission_id   INT PRIMARY KEY,
    patient_id     INT REFERENCES patients (patient_id),
    department_id  INT REFERENCES departments (department_id),
    admit_date     DATE,
    discharge_date DATE,
    bill_amount    DECIMAL(12,2)
);

INSERT INTO departments VALUES
(1,'Cardiology'),(2,'Pediatrics'),(3,'Orthopedics'),(4,'Emergency'),(5,'Oncology');

INSERT INTO doctors VALUES
(1,'Dr. Karanja',NULL,NULL),
(2,'Dr. Wanjiku',1,1),
(3,'Dr. Otieno',2,1),
(4,'Dr. Njeri',3,1),
(5,'Dr. Barasa',1,2),
(6,'Dr. Chebet',2,3),
(7,'Dr. Hassan',4,1),
(8,'Dr. Mutua',1,5);

INSERT INTO patients VALUES
(1,'Amina Yusuf','F','1985-03-12','Nairobi'),
(2,'Brian Kiptoo','M','1990-07-25','Nakuru'),
(3,'Cynthia Atieno','F','2015-01-09','Kisumu'),
(4,'Daniel Mwangi','M','1958-11-30','Nairobi'),
(5,'Esther Wambui','F','1972-05-18','Nairobi'),
(6,'Felix Omondi','M','2010-09-02','Kisumu'),
(7,'Grace Chebet','F','1995-12-21','Eldoret'),
(8,'Hassan Ali','M','1980-04-04','Mombasa'),
(9,'Irene Kamau','F','2002-06-15','Nairobi'),
(10,'James Otieno','M','1966-08-08','Nakuru');

INSERT INTO appointments VALUES
(1,1,2,'2026-09-01','Completed',3000),
(2,1,5,'2026-09-15','Completed',2500),
(3,2,4,'2026-09-02','Completed',3500),
(4,3,3,'2026-09-03','Completed',2000),
(5,3,6,'2026-09-20','No-show',2000),
(6,4,2,'2026-09-04','Completed',3000),
(7,4,5,'2026-09-18','Completed',2500),
(8,4,2,'2026-10-02','Completed',3000),
(9,5,5,'2026-09-05','Cancelled',2500),
(10,6,3,'2026-09-06','Completed',2000),
(11,7,7,'2026-09-07','Completed',1500),
(12,8,4,'2026-09-08','Completed',3500),
(13,8,2,'2026-09-25','Completed',3000),
(14,10,7,'2026-09-10','Completed',1500),
(15,2,4,'2026-10-05','Completed',3500),
(16,5,5,'2026-10-06','Completed',2500);

INSERT INTO admissions VALUES
(1,4,1,'2026-09-04','2026-09-09',85000),
(2,2,3,'2026-09-02','2026-09-12',120000),
(3,7,4,'2026-09-07','2026-09-08',15000),
(4,10,4,'2026-09-10','2026-09-11',18000),
(5,8,3,'2026-09-08','2026-09-20',150000),
(6,4,1,'2026-10-02','2026-10-07',90000),
(7,3,2,'2026-09-03','2026-09-06',30000),
(8,6,2,'2026-09-06','2026-09-10',35000);

1. SQL window functions

A window function calculates a value for each row using a set of related rows, without collapsing those rows into one. GROUP BY answers "what is the total per department?" and returns one row per department. A window function answers the same question and keeps every admission on its own row, so each row can show its own bill next to its department total. Hospitals use this to rank doctors, track a patient's visits in order, build running revenue and compare each bill with its department average.

How OVER, PARTITION BY, ORDER BY and frames work

Every window function is followed by an OVER clause. The clause is built from the pieces below, all optional except the brackets.

• OVER ( ) marks the call as a window function. Empty brackets mean the window is the whole result set.
• PARTITION BY splits the rows into independent groups. The function restarts in each group, for example once per patient or per department.
• ORDER BY (inside OVER) sorts the rows within each partition. Ranking, LAG, LEAD and running totals need it because they depend on order.
• Window frame picks which rows around the current row the function can see. It is written as ROWS or RANGE BETWEEN <start> AND <end>.

A frame boundary is one of: UNBOUNDED PRECEDING (first row of the partition), n PRECEDING, CURRENT ROW, n FOLLOWING, UNBOUNDED FOLLOWING (last row of the partition). Two defaults matter:

• With no ORDER BY, the frame is the whole partition.
• With ORDER BY and no frame, the frame is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW: from the first row up to the current row and any rows that tie with it.

ROWS counts physical rows. RANGE groups rows that have the same ORDER BY value (peers). The difference appears in the frames section below.

Ranking functions

ROW_NUMBER()

ROW_NUMBER gives every row in a partition a unique number, 1, 2, 3 and so on, even when values tie. It is used to number things in order and to keep one row per group, such as the latest visit per patient.

The query numbers each visit for three patients in date order.

SELECT patient_id, appointment_id, appointment_date,
       ROW_NUMBER() OVER (PARTITION BY patient_id ORDER BY appointment_date) AS visit_number
FROM appointments
WHERE patient_id IN (1, 4, 8)
ORDER BY patient_id, visit_number;

The numbering restarts at 1 for each patient. Daniel Mwangi (patient 4) is on his third visit on 2 October.

patient_id	appointment_id	appointment_date	visit_number
1	1	2026-09-01	1
1	2	2026-09-15	2
4	6	2026-09-04	1
4	7	2026-09-18	2
4	8	2026-10-02	3
8	12	2026-09-08	1
8	13	2026-09-25	2

RANK() and DENSE_RANK() compared with ROW_NUMBER()

All three number rows by an ORDER BY. They differ only when values tie.

• ROW_NUMBER never repeats a number. Tied rows get different numbers in an arbitrary order unless you add a tie-breaker column.
• RANK gives tied rows the same rank and then skips numbers, so after three rows tied at 1 the next rank is 4.
• DENSE_RANK gives tied rows the same rank and does not skip, so the next rank is 2.

The query ranks completed appointments of Dr. Wanjiku (2) and Dr. Njeri (4) by fee. Three appointments cost 3500 and four cost 3000, so ties are guaranteed.

SELECT appointment_id, doctor_id, fee,
       ROW_NUMBER() OVER (ORDER BY fee DESC, appointment_id) AS row_num,
       RANK()       OVER (ORDER BY fee DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY fee DESC) AS dense_rnk
FROM appointments
WHERE status = 'Completed' AND doctor_id IN (2, 4)
ORDER BY fee DESC, appointment_id;

appointment_id	doctor_id	fee	row_num	rnk	dense_rnk
3	4	3500.00	1	1	1
12	4	3500.00	2	1	1
15	4	3500.00	3	1	1
1	2	3000.00	4	4	2
6	2	3000.00	5	4	2
8	2	3000.00	6	4	2
13	2	3000.00	7	4	2

Use ROW_NUMBER to pick exactly one row, RANK when a gap after ties is meaningful ("three tied for first, so the next is fourth"), and DENSE_RANK for "the top two price levels" regardless of how many rows share them.

NTILE(n)

NTILE splits the ordered rows into n buckets of nearly equal size and returns the bucket number. If the rows do not divide evenly, the first buckets get one extra row. Hospitals use it to group bills into quartiles or to split patients into risk bands.

SELECT admission_id, bill_amount,
       NTILE(4) OVER (ORDER BY bill_amount) AS bill_quartile
FROM admissions
ORDER BY bill_amount;

Eight admissions split into four buckets of two. Bucket 1 holds the cheapest stays and bucket 4 the most expensive.

admission_id	bill_amount	bill_quartile
3	15000.00	1
4	18000.00	1
7	30000.00	2
8	35000.00	2
1	85000.00	3
6	90000.00	3
2	120000.00	4
5	150000.00	4

Value functions

LAG() and LEAD()

LAG returns a value from an earlier row in the partition and LEAD from a later row. The call is LAG(column, offset, default): offset defaults to 1 and default to NULL, which is what you get when no such row exists. They are used to compare a row with its neighbour, for example the gap between a patient's visits, which hospitals watch to spot missed follow-ups.

SELECT appointment_id, appointment_date, fee,
       LAG(appointment_date)  OVER (ORDER BY appointment_date) AS previous_visit,
       appointment_date - LAG(appointment_date) OVER (ORDER BY appointment_date) AS days_since_previous,
       LEAD(appointment_date) OVER (ORDER BY appointment_date) AS next_visit
FROM appointments
WHERE patient_id = 4
ORDER BY appointment_date;

The first visit has no previous visit and the last has no next visit, so those cells are NULL. Daniel returned every 14 days.

appointment_id	appointment_date	fee	previous_visit	days_since_previous	next_visit
6	2026-09-04	3000.00	NULL	NULL	2026-09-18
7	2026-09-18	2500.00	2026-09-04	14	2026-10-02
8	2026-10-02	3000.00	2026-09-18	14	NULL

FIRST_VALUE() and LAST_VALUE()

FIRST_VALUE returns the first row's value in the frame and LAST_VALUE the last row's. They are used to compare every row with a starting or ending point, such as a patient's first and latest fee.

LAST_VALUE has a common trap. With ORDER BY and no explicit frame, the frame stops at the current row, so the "last" row is the current row itself. The query below shows the trap next to the fix.

SELECT appointment_id, appointment_date, fee,
       FIRST_VALUE(fee) OVER (ORDER BY appointment_date) AS first_fee,
       LAST_VALUE(fee)  OVER (ORDER BY appointment_date) AS last_fee_default,
       LAST_VALUE(fee)  OVER (ORDER BY appointment_date
                              ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_fee_full
FROM appointments
WHERE patient_id = 4
ORDER BY appointment_date;

appointment_id	appointment_date	fee	first_fee	last_fee_default	last_fee_full
6	2026-09-04	3000.00	3000.00	3000.00	3000.00
7	2026-09-18	2500.00	3000.00	2500.00	3000.00
8	2026-10-02	3000.00	3000.00	3000.00	3000.00

In row 7, last_fee_default is 2500, the row's own fee, which is wrong for "latest fee". Extending the frame to UNBOUNDED FOLLOWING gives 3000 on every row.

Aggregate window functions: SUM, AVG, COUNT, MIN, MAX

The aggregates you already know from GROUP BY become window functions when followed by OVER. They compute across the partition or frame but keep every row. This lets one row show a detail value and a group value side by side, for example a bill and its department average.

SELECT d.department_name, a.admission_id, a.bill_amount,
       SUM(a.bill_amount) OVER (PARTITION BY a.department_id) AS dept_total,
       ROUND(AVG(a.bill_amount) OVER (PARTITION BY a.department_id), 2) AS dept_avg,
       COUNT(*) OVER (PARTITION BY a.department_id) AS dept_admissions,
       MIN(a.bill_amount) OVER (PARTITION BY a.department_id) AS dept_min,
       MAX(a.bill_amount) OVER (PARTITION BY a.department_id) AS dept_max
FROM admissions a
JOIN departments d ON d.department_id = a.department_id
ORDER BY d.department_name, a.admission_id;

There are eight rows, one per admission, and each carries its department's totals. A GROUP BY version would return only four rows and lose the individual bills. Oncology is absent because it has no admissions.

department_name	admission_id	bill_amount	dept_total	dept_avg	dept_admissions	dept_min	dept_max
Cardiology	1	85000.00	175000.00	87500.00	2	85000.00	90000.00
Cardiology	6	90000.00	175000.00	87500.00	2	85000.00	90000.00
Emergency	3	15000.00	33000.00	16500.00	2	15000.00	18000.00
Emergency	4	18000.00	33000.00	16500.00	2	15000.00	18000.00
Orthopedics	2	120000.00	270000.00	135000.00	2	120000.00	150000.00
Orthopedics	5	150000.00	270000.00	135000.00	2	120000.00	150000.00
Pediatrics	7	30000.00	65000.00	32500.00	2	30000.00	35000.00
Pediatrics	8	35000.00	65000.00	32500.00	2	30000.00	35000.00

Running totals and moving averages

Add ORDER BY inside OVER and the aggregate becomes cumulative. A frame turns it into a moving window. The first query below is running revenue from completed appointments. The second is a moving average of the current and previous two fees, which smooths daily noise.

SELECT appointment_date, fee,
       SUM(fee) OVER (ORDER BY appointment_date, appointment_id) AS running_revenue,
       ROUND(AVG(fee) OVER (ORDER BY appointment_date, appointment_id
                            ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3
FROM appointments
WHERE status = 'Completed'
ORDER BY appointment_date, appointment_id;

The first six of fourteen rows are shown. Revenue ends at 37000 on 6 October. The moving average on 2 September is (3000 + 3500) / 2 because only two rows exist so far.

appointment_date	fee	running_revenue	moving_avg_3
2026-09-01	3000.00	3000.00	3000.00
2026-09-02	3500.00	6500.00	3250.00
2026-09-03	2000.00	8500.00	2833.33
2026-09-04	3000.00	11500.00	2833.33
2026-09-06	2000.00	13500.00	2333.33
2026-09-07	1500.00	15000.00	2166.67

ROWS compared with RANGE

Both frames run from the first row to the current row. ROWS stops at the current physical row. RANGE also includes every row tied with the current row on the ORDER BY value. This is why the default frame, which is RANGE, can surprise you when values repeat.

SELECT appointment_id, fee,
       SUM(fee) OVER (ORDER BY fee, appointment_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS rows_total,
       SUM(fee) OVER (ORDER BY fee RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS range_total
FROM appointments
WHERE status = 'Completed' AND doctor_id IN (2, 4)
ORDER BY fee, appointment_id;

Four appointments cost 3000. ROWS builds the total one row at a time (3000, 6000, 9000, 12000). RANGE treats all four as peers, so each shows 12000.

appointment_id	fee	rows_total	range_total
1	3000.00	3000.00	12000.00
6	3000.00	6000.00	12000.00
8	3000.00	9000.00	12000.00
13	3000.00	12000.00	12000.00
3	3500.00	15500.00	22500.00
12	3500.00	19000.00	22500.00
15	3500.00	22500.00	22500.00

Using a window result in a filter

Window functions run after WHERE, so they cannot appear in WHERE. To keep only the top row per group, compute the rank in a subquery and filter outside it. This query returns the most expensive admission in each department.

SELECT *
FROM (
    SELECT d.department_name, a.admission_id, a.bill_amount,
           RANK() OVER (PARTITION BY a.department_id ORDER BY a.bill_amount DESC) AS rnk
    FROM admissions a
    JOIN departments d ON d.department_id = a.department_id
) t
WHERE rnk = 1
ORDER BY department_name;

department_name	admission_id	bill_amount	rnk
Cardiology	6	90000.00	1
Emergency	4	18000.00	1
Orthopedics	5	150000.00	1
Pediatrics	8	35000.00	1

2. SQL joins

A join combines rows from two tables by matching a column in one to a column in the other, usually a foreign key to a primary key. The join type decides what happens to rows that find no match. Hospital data is split across tables on purpose, so almost every real question, such as which patient saw which doctor, needs a join.

INNER JOIN

INNER JOIN returns only the rows that match on both sides. Rows with no partner are dropped. Use it when you only care about records that exist in both tables, such as completed appointments with their patient and doctor names.

SELECT p.patient_name, d.doctor_name, a.appointment_date, a.fee
FROM appointments a
INNER JOIN patients p ON p.patient_id = a.patient_id
INNER JOIN doctors  d ON d.doctor_id  = a.doctor_id
WHERE a.status = 'Completed' AND a.appointment_date < '2026-09-08'
ORDER BY a.appointment_date, p.patient_name;

The query chains two joins, replaces IDs with names and keeps completed appointments before 8 September. Irene Kamau cannot appear because she has no appointment to match.

patient_name	doctor_name	appointment_date	fee
Amina Yusuf	Dr. Wanjiku	2026-09-01	3000.00
Brian Kiptoo	Dr. Njeri	2026-09-02	3500.00
Cynthia Atieno	Dr. Otieno	2026-09-03	2000.00
Daniel Mwangi	Dr. Wanjiku	2026-09-04	3000.00
Felix Omondi	Dr. Otieno	2026-09-06	2000.00
Grace Chebet	Dr. Hassan	2026-09-07	1500.00

LEFT JOIN

LEFT JOIN keeps every row from the left table and fills the right table's columns with NULL when there is no match. Use it when the left table is the list you must not lose: all patients, including those who never booked.

SELECT p.patient_name, COUNT(a.appointment_id) AS appointments
FROM patients p
LEFT JOIN appointments a ON a.patient_id = p.patient_id
GROUP BY p.patient_name
ORDER BY appointments, p.patient_name;

All ten patients appear. Irene Kamau shows 0 because COUNT ignores the NULL from the missing match. An INNER JOIN here would have returned nine patients and hidden her.

patient_name	appointments
Irene Kamau	0
Felix Omondi	1
Grace Chebet	1
James Otieno	1
Amina Yusuf	2
Brian Kiptoo	2
Cynthia Atieno	2
Esther Wambui	2
Hassan Ali	2
Daniel Mwangi	3

The same join answers "who has no match at all?" when you keep only the NULL rows. This is called an anti-join.

SELECT p.patient_name
FROM patients p
LEFT JOIN appointments a ON a.patient_id = p.patient_id
WHERE a.appointment_id IS NULL;

The result is one row: Irene Kamau.

Where you put a filter on the right table matters. A condition in ON filters before the join and keeps unmatched left rows. The same condition in WHERE filters after the join and removes them, which quietly turns the LEFT JOIN into an INNER JOIN.

-- Condition in ON: Irene is kept, with NULL
SELECT p.patient_name, a.appointment_id
FROM patients p
LEFT JOIN appointments a ON a.patient_id = p.patient_id AND a.status = 'Completed'
WHERE p.patient_id IN (3, 5, 9)
ORDER BY p.patient_id, a.appointment_id;

-- Condition in WHERE: Irene disappears
SELECT p.patient_name, a.appointment_id
FROM patients p
LEFT JOIN appointments a ON a.patient_id = p.patient_id
WHERE a.status = 'Completed' AND p.patient_id IN (3, 5, 9)
ORDER BY p.patient_id, a.appointment_id;

Version	patient_name	appointment_id
ON	Cynthia Atieno	4
ON	Esther Wambui	16
ON	Irene Kamau	NULL
WHERE	Cynthia Atieno	4
WHERE	Esther Wambui	16

RIGHT JOIN

RIGHT JOIN is the mirror image: it keeps every row from the right table. Any RIGHT JOIN can be rewritten as a LEFT JOIN by swapping the tables, so many teams use only LEFT JOIN for readability. It is useful when the table you must keep is already written last.

The query lists every doctor with their appointment count, including doctors nobody has booked.

SELECT d.doctor_name, COUNT(a.appointment_id) AS appointments
FROM appointments a
RIGHT JOIN doctors d ON d.doctor_id = a.doctor_id
GROUP BY d.doctor_name
ORDER BY appointments, d.doctor_name;

Dr. Karanja and Dr. Mutua have no appointments, so they show 0. The counts add up to the 16 appointments in the table.

doctor_name	appointments
Dr. Karanja	0
Dr. Mutua	0
Dr. Chebet	1
Dr. Hassan	2
Dr. Otieno	2
Dr. Njeri	3
Dr. Barasa	4
Dr. Wanjiku	4

FULL OUTER JOIN

FULL OUTER JOIN keeps every row from both tables. Matched rows are combined, and unmatched rows from either side appear with NULLs on the other side. Use it for reconciliation, where you need to see what is missing on both sides. The query compares doctors with departments and keeps only the rows that failed to match.

SELECT d.doctor_name, dep.department_name
FROM doctors d
FULL OUTER JOIN departments dep ON dep.department_id = d.department_id
WHERE d.doctor_id IS NULL OR dep.department_id IS NULL
ORDER BY d.doctor_name;

It finds two data gaps in one pass: a doctor with no department and a department with no doctor.

doctor_name	department_name
Dr. Karanja	NULL
NULL	Oncology

CROSS JOIN

CROSS JOIN pairs every row of one table with every row of the other and has no ON clause. The result has rows(A) × rows(B) rows. It is used to generate combinations, such as a roster skeleton of every department in every shift. Be careful with large tables, because the output grows very fast.

SELECT dep.department_name, s.shift
FROM departments dep
CROSS JOIN (VALUES ('Morning'), ('Afternoon'), ('Night')) AS s(shift)
WHERE dep.department_name IN ('Cardiology', 'Emergency')
ORDER BY dep.department_name, s.shift;

Five departments and three shifts give 15 combinations. The filter above shows the six for Cardiology and Emergency.

department_name	shift
Cardiology	Afternoon
Cardiology	Morning
Cardiology	Night
Emergency	Afternoon
Emergency	Morning
Emergency	Night

SELF JOIN

A self join joins a table to itself, using two aliases so SQL can tell the two copies apart. It fits rows that point to other rows in the same table. The doctors table has supervisor_id, which refers to another doctor. A LEFT JOIN is used so the chief medical officer, who has no supervisor, is not lost.

SELECT d.doctor_name AS doctor, s.doctor_name AS supervisor
FROM doctors d
LEFT JOIN doctors s ON s.doctor_id = d.supervisor_id
ORDER BY d.doctor_id;

doctor	supervisor
Dr. Karanja	NULL
Dr. Wanjiku	Dr. Karanja
Dr. Otieno	Dr. Karanja
Dr. Njeri	Dr. Karanja
Dr. Barasa	Dr. Wanjiku
Dr. Chebet	Dr. Otieno
Dr. Hassan	Dr. Karanja
Dr. Mutua	Dr. Barasa

Choosing a join

Join	Rows returned	Unmatched rows	Hospital scenario
INNER	Matches only	Dropped from both tables	Completed appointments with patient and doctor names
LEFT	All left rows plus matches	Left kept, right is NULL	Every patient with their visit count, including zero
RIGHT	All right rows plus matches	Right kept, left is NULL	Every doctor with their appointment count
FULL OUTER	All rows from both	Both kept, NULL on the missing side	Doctors without departments and departments without doctors
CROSS	Every pairing	Not applicable	Department by shift roster skeleton
SELF	Table joined to itself	Depends on INNER or LEFT	Doctor and their supervisor

3. Common Table Expressions (CTEs)

A CTE is a named, temporary result set defined with WITH at the top of a query and used like a table in the query below it. It exists only for that one statement. CTEs break a long query into named steps, so each step can be read, tested and reused on its own, which matters for hospital reports that combine visits, stays and billing.

Basic CTE

The syntax is WITH name AS ( query ) main_query. The query below first builds a CTE of revenue per doctor, then joins it to doctors to show names.

WITH doctor_revenue AS (
    SELECT doctor_id, COUNT(*) AS visits, SUM(fee) AS revenue
    FROM appointments
    WHERE status = 'Completed'
    GROUP BY doctor_id
)
SELECT d.doctor_name, dr.visits, dr.revenue
FROM doctor_revenue dr
JOIN doctors d ON d.doctor_id = dr.doctor_id
ORDER BY dr.revenue DESC, d.doctor_name;

The CTE does the aggregation and the main query only attaches names and sorts. Doctors with no completed appointments, Dr. Karanja and Dr. Mutua, are not listed because the join is an INNER JOIN.

doctor_name	visits	revenue
Dr. Wanjiku	4	12000.00
Dr. Njeri	3	10500.00
Dr. Barasa	3	7500.00
Dr. Otieno	2	4000.00
Dr. Hassan	2	3000.00

Multiple CTEs with joins and aggregations

Separate several CTEs with commas and write WITH only once. Each CTE can be joined in the main query. The query below builds one summary per department from two different tables: consultation fees from appointments and bills from admissions.

WITH dept_visits AS (
    SELECT d.department_id, SUM(a.fee) AS consultation_revenue
    FROM appointments a
    JOIN doctors d ON d.doctor_id = a.doctor_id
    WHERE a.status = 'Completed'
    GROUP BY d.department_id
),
dept_stays AS (
    SELECT department_id, COUNT(*) AS admissions, SUM(bill_amount) AS admission_revenue
    FROM admissions
    GROUP BY department_id
)
SELECT dep.department_name,
       COALESCE(v.consultation_revenue, 0) AS consultation_revenue,
       COALESCE(s.admissions, 0)           AS admissions,
       COALESCE(s.admission_revenue, 0)    AS admission_revenue
FROM departments dep
LEFT JOIN dept_visits v ON v.department_id = dep.department_id
LEFT JOIN dept_stays  s ON s.department_id = dep.department_id
ORDER BY dep.department_id;

Each CTE aggregates its own table first, and the main query joins the two small results. Oncology has no data in either, so LEFT JOIN with COALESCE turns its NULLs into 0.

department_name	consultation_revenue	admissions	admission_revenue
Cardiology	19500.00	2	175000.00
Pediatrics	4000.00	2	65000.00
Orthopedics	10500.00	2	270000.00
Emergency	3000.00	2	33000.00
Oncology	0	0	0

Aggregating before joining matters. If you join appointments and admissions directly and then sum, every appointment repeats once per admission in the same department, and Cardiology consultation revenue is reported as 39000 instead of 19500.

CTEs that build on each other

A later CTE can read an earlier one. The query below finds patients who spent more than the average patient. The first CTE totals each patient's fees, the second averages those totals, and the main query compares them.

WITH patient_spend AS (
    SELECT patient_id, SUM(fee) AS total_fee
    FROM appointments
    WHERE status = 'Completed'
    GROUP BY patient_id
),
average_spend AS (
    SELECT AVG(total_fee) AS avg_fee FROM patient_spend
)
SELECT p.patient_name, ps.total_fee, ROUND(av.avg_fee, 2) AS average_fee
FROM patient_spend ps
JOIN patients p ON p.patient_id = ps.patient_id
CROSS JOIN average_spend av
WHERE ps.total_fee > av.avg_fee
ORDER BY ps.total_fee DESC;

The average across the nine patients with completed visits is 4111.11, and four patients are above it.

patient_name	total_fee	average_fee
Daniel Mwangi	8500.00	4111.11
Brian Kiptoo	7000.00	4111.11
Hassan Ali	6500.00	4111.11
Amina Yusuf	5500.00	4111.11

Recursive CTEs

A recursive CTE refers to itself, which lets SQL walk through hierarchies or generate sequences. It has three parts: an anchor query that returns the starting rows, a recursive query that uses the CTE's own previous output to find the next rows, and UNION ALL to stack them. It stops when the recursive part returns no new rows. In PostgreSQL the keyword is WITH RECURSIVE.

The query below walks the reporting line from the chief medical officer down, tracking the level and the full chain.

WITH RECURSIVE chain AS (
    SELECT doctor_id, doctor_name, 1 AS level, doctor_name::text AS path
    FROM doctors
    WHERE supervisor_id IS NULL
    UNION ALL
    SELECT d.doctor_id, d.doctor_name, c.level + 1, c.path || ' > ' || d.doctor_name
    FROM doctors d
    JOIN chain c ON d.supervisor_id = c.doctor_id
)
SELECT level, doctor_name, path
FROM chain
ORDER BY path;

The anchor is Dr. Karanja, the only doctor with no supervisor. Each pass adds the people who report to the doctors found in the previous pass. Dr. Mutua is four levels down, which a plain self join could not show without knowing the depth in advance.

level	doctor_name	path
1	Dr. Karanja	Dr. Karanja
2	Dr. Hassan	Dr. Karanja > Dr. Hassan
2	Dr. Njeri	Dr. Karanja > Dr. Njeri
2	Dr. Otieno	Dr. Karanja > Dr. Otieno
3	Dr. Chebet	Dr. Karanja > Dr. Otieno > Dr. Chebet
2	Dr. Wanjiku	Dr. Karanja > Dr. Wanjiku
3	Dr. Barasa	Dr. Karanja > Dr. Wanjiku > Dr. Barasa
4	Dr. Mutua	Dr. Karanja > Dr. Wanjiku > Dr. Barasa > Dr. Mutua

Recursion also generates sequences. This query builds every date from 6 to 12 September and left joins appointments, so days with no appointments still show as 0, which a plain GROUP BY on appointments would omit.

WITH RECURSIVE days AS (
    SELECT DATE '2026-09-06' AS day
    UNION ALL
    SELECT day + 1 FROM days WHERE day < DATE '2026-09-12'
)
SELECT days.day, COUNT(a.appointment_id) AS appointments
FROM days
LEFT JOIN appointments a ON a.appointment_date = days.day
GROUP BY days.day
ORDER BY days.day;

day	appointments
2026-09-06	1
2026-09-07	1
2026-09-08	1
2026-09-09	0
2026-09-10	1
2026-09-11	0
2026-09-12	0

The WHERE day < ... condition is the stop rule. Without one, a recursive CTE runs forever.

CTEs compared with subqueries

A CTE and a subquery can often return the same result. The choice is mostly about readability and reuse, and the next section shows the same question written as a subquery.

Aspect	CTE	Subquery
Where it is written	At the top, with WITH	Inline in SELECT, FROM or WHERE
Reuse in one statement	Name it once, reference it many times	Must be repeated each time it is needed
Readability	Reads top to bottom as named steps	Nested, read inside out
Recursion	Supported	Not supported
Scope	Visible to the whole statement that follows	Visible only where it is written
Performance	In PostgreSQL 12 and later, a non-recursive CTE used once is planned like a subquery	Planned by the optimiser in place

Use a CTE when a query has several steps, when the same result is needed twice, or when the data is hierarchical. Use a short subquery for a one-off filter, such as a value to compare against.

4. SQL subqueries

A subquery is a SELECT written inside another query, in brackets. The inner query runs to produce a value, a list of values or a small table, and the outer query uses it. Subqueries answer questions that depend on another question, such as "which bills are above the hospital average?", without creating a separate table first.

Subqueries are grouped in two ways: by what they return (scalar, list or table) and by how they relate to the outer query (non-correlated or correlated).

Scalar subqueries

A scalar subquery returns exactly one row and one column, so it can be used wherever a single value fits. It is most often used in WHERE to compare against a computed value.

SELECT appointment_id, patient_id, fee
FROM appointments
WHERE fee > (SELECT AVG(fee) FROM appointments)
ORDER BY fee DESC, appointment_id;

The inner query returns one number, the average fee of 2593.75, and the outer query keeps appointments above it. If the inner query returned more than one row, PostgreSQL would raise an error.

appointment_id	patient_id	fee
3	2	3500.00
12	8	3500.00
15	2	3500.00
1	1	3000.00
6	4	3000.00
8	4	3000.00
13	8	3000.00

The same question written as a CTE returns identical rows. The CTE form names the average and is easier to extend when the comparison value is needed in several places.

WITH avg_fee AS (SELECT AVG(fee) AS value FROM appointments)
SELECT a.appointment_id, a.patient_id, a.fee
FROM appointments a
CROSS JOIN avg_fee
WHERE a.fee > avg_fee.value
ORDER BY a.fee DESC, a.appointment_id;

Subqueries in the SELECT, FROM and WHERE clauses

A subquery can sit in three places, and what it must return changes with the place.

Clause	What the subquery returns	Typical use
WHERE	One value, or a list for IN, ANY, ALL, EXISTS	Filter rows by a computed value or by a related table
SELECT	One value per outer row (scalar)	Add a computed column, such as a count or an average
FROM	A table, called a derived table, which needs an alias	Aggregate first, then query or join the result

In SELECT

The query adds the hospital's average bill and each stay's difference from it as columns. Because the inner query is not tied to the outer row, it returns the same value on every row.

SELECT admission_id, bill_amount,
       (SELECT ROUND(AVG(bill_amount), 2) FROM admissions) AS hospital_avg,
       bill_amount - (SELECT ROUND(AVG(bill_amount), 2) FROM admissions) AS difference
FROM admissions
ORDER BY admission_id
LIMIT 4;

admission_id	bill_amount	hospital_avg	difference
1	85000.00	67875.00	17125.00
2	120000.00	67875.00	52125.00
3	15000.00	67875.00	-52875.00
4	18000.00	67875.00	-49875.00

In FROM

The inner query summarises admissions by department. The outer query treats that summary as a table and joins it to department names. The alias t is required.

SELECT dep.department_name, t.admissions, t.total_billed
FROM (
    SELECT department_id, COUNT(*) AS admissions, SUM(bill_amount) AS total_billed
    FROM admissions
    GROUP BY department_id
) AS t
JOIN departments dep ON dep.department_id = t.department_id
ORDER BY t.total_billed DESC;

department_name	admissions	total_billed
Orthopedics	2	270000.00
Cardiology	2	175000.00
Pediatrics	2	65000.00
Emergency	2	33000.00

Non-correlated subqueries and IN

A non-correlated subquery does not refer to the outer query, so it runs once and its result is handed to the outer query. IN tests whether a value appears in the list the subquery returns. The query below lists patients who have been admitted.

SELECT patient_name
FROM patients
WHERE patient_id IN (SELECT patient_id FROM admissions)
ORDER BY patient_name;

Seven patients have at least one admission. The list from the inner query contains duplicates, such as patient 4 twice, which IN handles without repeating the patient.

patient_name
Brian Kiptoo
Cynthia Atieno
Daniel Mwangi
Felix Omondi
Grace Chebet
Hassan Ali
James Otieno

Correlated subqueries

A correlated subquery refers to a column of the outer query, so it is evaluated again for each outer row. Read it as "for this row, look up something about related rows." The query below finds admissions that cost more than the average for their own department.

SELECT a.admission_id, a.department_id, a.bill_amount
FROM admissions a
WHERE a.bill_amount > (SELECT AVG(x.bill_amount)
                       FROM admissions x
                       WHERE x.department_id = a.department_id)
ORDER BY a.admission_id;

The inner WHERE uses a.department_id from the outer row, so each admission is compared with its own department's average and not the hospital average. Each department has two admissions, so the more expensive of each pair qualifies.

admission_id	department_id	bill_amount
4	4	18000.00
5	3	150000.00
6	1	90000.00
8	2	35000.00

Correlated subqueries are also used inside SELECT to add a per-row lookup. This one counts appointments per patient and shows 0 for Irene Kamau.

SELECT p.patient_name,
       (SELECT COUNT(*) FROM appointments a WHERE a.patient_id = p.patient_id) AS appointments
FROM patients p
WHERE p.patient_id IN (1, 4, 9)
ORDER BY p.patient_id;

patient_name	appointments
Amina Yusuf	2
Daniel Mwangi	3
Irene Kamau	0

Aspect	Non-correlated	Correlated
Refers to the outer query	No	Yes
Runs	Once	Logically once per outer row
Can be run alone for testing	Yes	No, it needs the outer row
Example above	Admitted patients (IN)	Bills above the department average

EXISTS and NOT EXISTS

EXISTS returns true if the subquery returns at least one row, whatever the columns are, so SELECT 1 is the convention. It stops at the first match, which makes it efficient for "is there any related row?" questions. The query finds patients who have at least one no-show or cancelled appointment, a list a clinic would follow up.

SELECT p.patient_name
FROM patients p
WHERE EXISTS (SELECT 1 FROM appointments a
              WHERE a.patient_id = p.patient_id
                AND a.status IN ('No-show', 'Cancelled'))
ORDER BY p.patient_name;

The result is Cynthia Atieno (no-show) and Esther Wambui (cancelled). NOT EXISTS is the opposite: it keeps outer rows with no match, as in the next query, which finds patients who have never booked.

SELECT p.patient_name
FROM patients p
WHERE NOT EXISTS (SELECT 1 FROM appointments a WHERE a.patient_id = p.patient_id);

The result is one row: Irene Kamau.

IN and EXISTS often give the same rows, but they differ with NULLs. NOT IN returns no rows at all if the subquery list contains even one NULL, because the comparison becomes unknown. NOT EXISTS does not have this problem. In the test below, adding a single NULL to the list makes NOT IN return zero rows, although Irene should still appear.

-- Returns Irene Kamau
SELECT patient_name FROM patients
WHERE patient_id NOT IN (SELECT patient_id FROM appointments);

-- Same query with one NULL added to the list: returns no rows
SELECT patient_name FROM patients
WHERE patient_id NOT IN (SELECT NULL::int UNION SELECT patient_id FROM appointments);

ANY and ALL

ANY and ALL compare a value with every value a subquery returns. > ANY means greater than at least one value, which is the same as greater than the smallest. > ALL means greater than every value, which is the same as greater than the largest. = ANY behaves like IN.

Pediatrics bills are 30000 and 35000. The first query finds stays more expensive than every Pediatrics bill. The second finds stays more expensive than at least one of them.

-- > ALL: above 35000
SELECT admission_id, bill_amount
FROM admissions
WHERE bill_amount > ALL (SELECT bill_amount FROM admissions WHERE department_id = 2)
ORDER BY admission_id;

-- > ANY: above 30000
SELECT admission_id, bill_amount
FROM admissions
WHERE bill_amount > ANY (SELECT bill_amount FROM admissions WHERE department_id = 2)
ORDER BY admission_id;

The > ANY result adds admission 8 (35000), which beats 30000 but not 35000 itself, so it fails > ALL.

Query	admission_id	bill_amount
> ALL	1	85000.00
> ALL	2	120000.00
> ALL	5	150000.00
> ALL	6	90000.00
> ANY	1	85000.00
> ANY	2	120000.00
> ANY	5	150000.00
> ANY	6	90000.00
> ANY	8	35000.00

Nested subqueries

A subquery can contain another subquery. Each level feeds the one above it. The query below finds patients who have seen a Cardiology doctor, working from the department name outward in three steps: the department, its doctors, then their appointments.

SELECT patient_name
FROM patients
WHERE patient_id IN (
    SELECT patient_id
    FROM appointments
    WHERE doctor_id IN (
        SELECT doctor_id
        FROM doctors
        WHERE department_id = (SELECT department_id FROM departments WHERE department_name = 'Cardiology')
    )
)
ORDER BY patient_name;

The innermost query returns department 1. The next returns doctors 2, 5 and 8. The next returns the patients who booked them. Four patients qualify.

patient_name
Amina Yusuf
Daniel Mwangi
Esther Wambui
Hassan Ali

Deep nesting is hard to read. The same result can be written with joins, or with CTEs that name each step, as the CTE section showed.

5. Putting the four together

Each tool answers a different kind of question, and real hospital reports usually combine them. Pick the tool from the shape of the question.

If you need to	Use	Hospital example
Keep every row and add a group total, rank, neighbour or running figure	Window function	Each bill beside its department average; days between visits
Combine columns from two or more tables	Join	Appointments with patient and doctor names
Break a long query into named steps, reuse a result or walk a hierarchy	CTE	Department summary from visits and stays; supervisor chain
Filter by a computed value or test whether related rows exist	Subquery	Bills above average; patients with no appointment

The query below uses three of them at once. A CTE with a window function ranks admissions inside each department, and joins attach the names. It returns the most expensive admission per department, with the patient.

WITH ranked AS (
    SELECT admission_id, patient_id, department_id, bill_amount,
           RANK() OVER (PARTITION BY department_id ORDER BY bill_amount DESC) AS rnk
    FROM admissions
)
SELECT dep.department_name, p.patient_name, r.bill_amount
FROM ranked r
JOIN departments dep ON dep.department_id = r.department_id
JOIN patients p      ON p.patient_id      = r.patient_id
WHERE r.rnk = 1
ORDER BY dep.department_name;

department_name	patient_name	bill_amount
Cardiology	Daniel Mwangi	90000.00
Emergency	James Otieno	18000.00
Orthopedics	Hassan Ali	150000.00
Pediatrics	Felix Omondi	35000.00

The habits that prevent most mistakes are small. Check which rows a join keeps before trusting a count. Aggregate before joining two fact tables. Add a frame to LAST_VALUE. Prefer NOT EXISTS to NOT IN when the list can contain NULLs. Test each CTE on its own before chaining them.