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.

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.

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; 

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.

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.

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; 

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.

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.

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.

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; 

...Source：https://dev.to/kbonymark/sql-window-functions-joins-ctes-and-subqueries-a-hospital-data-guide-27l5

My favorites

PreviousDiving into Databases: Indexes, Locks, and the Problem of Selling the Same Ticket TwiceNextOn-Premise Database Inventory: Bringing MySQL and PostgreSQL Into One Multi-Cloud View

Related posts

Postgres LISTEN/NOTIFY vs Redis Pub/Sub: Latency, Write Cost and Failure Cases, Tested

Cross-posted from my blog. The original, with any updates, lives on fabwaseem.com . If you already run Postgres, you can

0dev.to Node

LlamaIndex Workflows vs LangGraph: Event-Driven Architecture and Async State Overhead Showdown

LlamaIndex Workflows and LangGraph represent the two dominant paradigms for orchestrating complex agentic swarms: pure e

0dev.to Programming

Rider and Databases: Lesser-Known IDE Features That Save Time

Most developers work with databases through dedicated tools: some prefer SQL Server Management Studio (SSMS), others go

0dev.to Database

KivaDB v2: What's Coming and How You Can Contribute

We're excited to announce that development for KivaDB v2 is officially underway! In this post, we'll share what we're pl

0dev.to Database

Your LLM cost log says $0.00. The invoice says $1,320.

You log what each LLM call costs, right next to the request, in a DECIMAL(10,2) column like every other amount of money

0dev.to Database

SQLite in Production: A Practical Setup with Litestream Backups

SQLite often gets dismissed as a toy database, which surprises the many useful applications quietly running on it. For a

0dev.to Database

department_name	admission_id	bill_amount	rnk