SQL Window Functions, Joins, CTEs and Subqueries: A Practical Guide Using a Hospital Dataset

Introduction

Most SQL tutorials stop at SELECT, WHERE and GROUP BY. Real analysis needs more: ranking patients, comparing a visit with the previous one, combining several tables, and breaking a hard question into steps. This article covers four tools that make that possible:

1. Window functions: calculations across related rows without collapsing them.
2. Joins: combining tables.
3. Common Table Expressions (CTEs): naming intermediate results.
4. Subqueries: queries nested inside other queries.

Every example uses one hospital database (PostgreSQL syntax), and every result shown comes from running the query on that data.

The hospital dataset

The database lives in a schema called morning and has a star-style design: one fact table (visits) and several dimension tables.

Before running anything:

set search_path to morning; 

Data quality notes. While working with the data I noticed a few things that affect grouping and joining: diagnosis names such as Back Pian and the category Respiratoey Disease contain typos; Infectious and Infectious Disease are two spellings of one category; one county is stored with a leading space (Kiambu); and diagnoses 513 to 518 were missing from the original list and were added with suggested names. In real work you would clean these first, because GROUP BY treats each spelling as a separate group.

Part 1: Window Functions

What is a window function?

An aggregate with GROUP BY collapses many rows into one. A window function performs a calculation across a set of related rows (the window) but keeps every original row. That lets you show a visit and, on the same row, its rank, the previous bill, or a running total.

The OVER() clause

Every window function is followed by OVER (...), which defines the window:

function_name(...) OVER ( PARTITION BY column -- split rows into groups (optional) ORDER BY column -- order rows inside each group (optional) frame_clause -- which rows around the current row to use (optional) ) 

• OVER () with nothing inside means the window is the whole result set.
• PARTITION BY restarts the calculation for each group, like GROUP BY but without collapsing rows. Partition by doctor_id and each doctor gets their own ranking.
• ORDER BY inside OVER decides the order the function sees rows in. It matters for ranking, LAG/LEAD, and running totals. It is separate from the ORDER BY at the end of the query, which only sorts the final output.
• Window frame says exactly which rows, relative to the current row, are included (covered in detail below).

1. ROW_NUMBER()

What it does: gives each row in a partition a unique number, 1, 2, 3, and so on, following the ORDER BY. Even tied rows get different numbers.

Why use it: numbering visits, picking the latest record per patient, removing duplicates.

Hospital scenario: number each patient's visits in date order, for the five patients who came back more than once.

select visit_id, patient_id, visit_date, total_bill_kes, row_number() over (partition by patient_id order by visit_date) as visit_number from visits where patient_id in (1, 2, 5, 7, 10) order by patient_id, visit_date; 

What it does: it splits the rows into one group per patient, sorts each group by date, and numbers them from 1.

A very common pattern: the latest visit per patient. You cannot filter on a window function directly in WHERE, so wrap it in a CTE (explained in Part 3):

with ranked as ( select visit_id, patient_id, visit_date, row_number() over (partition by patient_id order by visit_date desc, visit_id desc) as rn from visits ) select visit_id, patient_id, visit_date from ranked where rn = 1; 

This returns exactly one row per patient (25 rows), the most recent visit of each.

2. RANK() and 3. DENSE_RANK() (compared with ROW_NUMBER)

These three functions all rank rows, but they handle ties differently:

Hospital scenario: rank inpatient visits by length of stay, longest first. Several patients stayed the same number of days, so ties appear.

select visit_id, patient_id, length_of_stay_days as stay, row_number() over (order by length_of_stay_days desc, visit_id) as row_num, rank() over (order by length_of_stay_days desc) as rnk, dense_rank() over (order by length_of_stay_days desc) as dense_rnk from visits where admission_type = 'Inpatient' order by row_num; 

Reading the result: visits 1003 and 1016 both stayed 3 days, so RANK gives both rank 4, and the next group starts at rank 6, because positions 4 and 5 were used. DENSE_RANK gives the next group rank 5 with no gap. ROW_NUMBER simply counts 1 to 9.

When to use which: ROW_NUMBER when you need exactly one row per group (de-duplication). RANK for competition-style ranking where gaps are meaningful. DENSE_RANK for 'top N levels' questions, such as 'the three longest distinct stay lengths'.

4. NTILE(n)

What it does: divides the ordered rows into n roughly equal buckets and tells you which bucket each row falls in. If the rows do not divide evenly, the first buckets get one extra row.

Why use it: quartiles, percentile-style bands, segmenting customers or patients into tiers.

Hospital scenario: split all 30 visits into four billing quartiles and see the bill range of each.

with t as ( select visit_id, total_bill_kes, ntile(4) over (order by total_bill_kes) as quartile from visits ) select quartile, count(*) as visits, min(total_bill_kes) as lowest, max(total_bill_kes) as highest from t group by quartile order by quartile; 

Thirty rows cannot split into four equal groups, so the first two quartiles hold 8 rows and the last two hold 7. The top quartile (bills of KES 28,000 and above) is where most of the hospital's revenue sits.

5. LAG() and 6. LEAD()

What they do: LAG(col) returns the value of col from the previous row in the window; LEAD(col) returns it from the next row. The first row of a partition has no previous row, so LAG returns NULL there; the last row gets NULL from LEAD. An optional second argument sets the offset and a third a default (LAG(col, 2, 0)).

Why use them: comparing a record with its neighbours, such as change since last visit, time between events, month-over-month growth, without a self join.

Hospital scenario: for returning patients, show how the bill changed compared with their previous visit, and the date of the next visit.

select patient_id, visit_id, visit_date, total_bill_kes, lag(total_bill_kes) over w as previous_bill, total_bill_kes - lag(total_bill_kes) over w as bill_change, lead(visit_date) over w as next_visit from visits where patient_id in (1, 2, 5, 7, 10) window w as (partition by patient_id order by visit_date) order by patient_id, visit_date; 

The WINDOW w AS (...) clause names a window definition once so several functions can reuse it.

(Patient 2 follows the same pattern: 2500 then 3800, a change of 1300.) Patient 10's bill dropped by KES 54,000: the first visit was a five-day inpatient stay for heart failure, the second a one-day emergency visit for chest pain. The change per visit tells a story that a plain SUM would hide.

7. FIRST_VALUE() and 8. LAST_VALUE()

What they do: return the first or last value of the window frame. This is where window frames matter most, so read the warning below.

Hospital scenario: for Dr. Faith Chebet (doctor 104), compare each visit's bill with her first and last bill.

select visit_id, visit_date, total_bill_kes, first_value(total_bill_kes) over w as first_bill, last_value(total_bill_kes) over w as last_bill_default, last_value(total_bill_kes) over ( partition by doctor_id order by visit_date, visit_id rows between unbounded preceding and unbounded following ) as last_bill_correct from visits where doctor_id = 104 window w as (partition by doctor_id order by visit_date, visit_id) order by visit_date, visit_id; 

The trap: when a window has an ORDER BY and no explicit frame, the default frame runs from the start of the partition to the current row. So LAST_VALUE with the default frame just returns the current row's own value (the last_bill_default column), which is almost never what you want. Extending the frame to UNBOUNDED FOLLOWING makes it see the real last row (last_bill_correct). FIRST_VALUE is unaffected because the start of the frame is always the partition start.

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

The familiar aggregates become window functions when you add OVER. Without ORDER BY they cover the whole partition on every row; with ORDER BY they become running calculations.

Hospital scenario: show each department's totals while keeping the context of every visit. Here SELECT DISTINCT is used to display one line per department.

select distinct department_id, sum(total_bill_kes) over (partition by department_id) as dept_revenue, round(avg(total_bill_kes) over (partition by department_id), 0) as dept_avg_bill, count(*) over (partition by department_id) as visit_count, min(total_bill_kes) over (partition by department_id) as lowest_bill, max(total_bill_kes) over (partition by department_id) as highest_bill from visits order by department_id; 

Without DISTINCT, every one of the 30 visit rows would carry its department's figures next to its own bill, which is perfect for questions like 'how does this bill compare with its department average?'

Running totals and moving averages use ORDER BY and a frame. Here we total each day's revenue first, then accumulate:

with daily as ( select visit_date, sum(total_bill_kes) as revenue from visits group by visit_date ) select visit_date, revenue, sum(revenue) over (order by visit_date) as running_total, round(avg(revenue) over (order by visit_date rows between 2 preceding and current row), 0) as moving_avg_3day from daily order by visit_date; 

First five of the 15 rows:

The moving average on day 2 is (6000 + 70000) / 2 because only two rows exist so far; from day 3 on it averages the current day and the two before.

Window frames in depth: ROWS vs RANGE

The frame clause has this shape:

{ ROWS | RANGE } BETWEEN <start> AND <end> -- start/end can be: UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING, UNBOUNDED FOLLOWING 

• ROWS counts physical rows.
• RANGE works on values: all rows with the same ORDER BY value (peers) are treated as one unit.
• The default, when ORDER BY is present, is RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW.

The hospital data has two visits on most days, which shows the difference clearly:

select visit_id, visit_date, total_bill_kes, sum(total_bill_kes) over (order by visit_date) as range_default, sum(total_bill_kes) over (order by visit_date, visit_id rows between unbounded preceding and current row) as rows_running from visits where visit_date <= '2026-06-02' order by visit_date, visit_id; 

With RANGE, both visits on 1 June are peers, so each row already includes the other and shows 6000. With ROWS, the total grows one row at a time. Use ROWS for true row-by-row running totals; use RANGE when rows with the same value should be treated together.

Part 2: SQL Joins

A join combines rows from two tables using a condition, usually a foreign key matching a primary key. In our database visits.patient_id points to patients.patient_id, visits.doctor_id to doctors.doctor_id, and so on.

INNER JOIN

Use when you only want records that exist on both sides, such as visits together with their patient and doctor details.

Scenario: list every inpatient visit with the patient's name and the doctor who handled it.

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 where v.admission_type = 'Inpatient' order by v.visit_id; 

Nine inpatient visits, each now readable instead of just IDs. Notice that joins can be chained: two joins here, three tables combined.

LEFT JOIN

Use when you need every row from the main table even when there is no match: 'all patients, and their inpatient stay if they had one.'

In this dataset every patient has at least one visit, so a plain left join from patients to visits would show no NULLs. To demonstrate the behaviour realistically, we put the inpatient condition inside the ON clause:

select count(*) as total_rows, count(v.visit_id) as matched from patients p left join visits v on p.patient_id = v.patient_id and v.admission_type = 'Inpatient'; 

All 25 patients survive; only 9 have a matching inpatient visit, and the other 16 get NULL in the visit columns. (COUNT(v.visit_id) ignores NULLs, which is why it counts only real matches.)

The anti-join pattern: add WHERE v.visit_id IS NULL to keep only the patients with no match, meaning patients who have never been admitted:

select p.patient_id, p.patient_name from patients p left join visits v on p.patient_id = v.patient_id and v.admission_type = 'Inpatient' where v.visit_id is null; 

This returns 16 patients, for example Amina Otieno (1), John Kariuki (2) and Esther Achieng (5).

Important: putting the filter in WHERE instead of ON would remove the NULL rows and quietly turn the left join into an inner join. Filters on the right table belong in ON.

RIGHT JOIN

A right join is a left join with the tables swapped: all rows of the right table are kept. Most people just write a left join and reorder the tables, but it is useful to know.

Scenario: list every diagnosis in the hospital's catalogue with the number of emergency visits it had, including diagnoses with none.

select d.diagnosis_name, count(v.visit_id) as emergency_visits from visits v right join diagnosis d on v.diagnosis_id = d.diagnosis_id and v.admission_type = 'Emergency' group by d.diagnosis_id, d.diagnosis_name order by emergency_visits desc, d.diagnosis_id; 

First rows of the 18 returned:

Only one visit in the data was an emergency (visit 1030, chest pain), but the right join keeps all 18 diagnoses, with a count of zero where nothing matched. Writing from diagnosis d left join visits v ... would give the identical result.

FULL OUTER JOIN

Use when you need everything from both sides and want to see which rows match and which do not: reconciling two lists.

Scenario: compare patients who paid cash at least once with patients who have been admitted as inpatients.

with cash as ( select distinct patient_id from visits where payment_method = 'Cash' ), inpt as ( select distinct patient_id from visits where admission_type = 'Inpatient' ) select coalesce(c.patient_id, i.patient_id) as patient_id, case when c.patient_id is not null and i.patient_id is not null then 'both' when c.patient_id is not null then 'cash only' else 'inpatient only' end as segment from cash c full outer join inpt i on c.patient_id = i.patient_id order by patient_id; 

The query returns 17 patients: 8 are cash only, 7 are inpatient only and 2 are in both lists (patients 4, David Mutua, and 22, Nadia Hussein). A full join is the only join that can show 'in A but not B', 'in B but not A' and 'in both' in one pass. COALESCE picks whichever side has the ID.

CROSS JOIN

A cross join pairs every row of one table with every row of the other: 4 doctors x 3 admission types = 12 rows. It has no ON clause.

Use when you need to generate all combinations, typically to build a complete grid so that combinations with zero activity still appear in a report.

select d.doctor_name, t.admission_type, count(v.visit_id) as visits from doctors d cross join (select 'Outpatient' as admission_typeSource：https://dev.to/adrian_mageto/sql-window-functions-joins-ctes-and-subqueries-a-practical-guide-using-a-hospital-dataset-3ahe
My favorites

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