Introduction to Databases and SQL with PostgreSQL: Window Functions, Joins, CTEs and Subqueries on a simple Hospital Database.

Introduction

Up to now in our SQL class, I could use SELECT, WHERE, GROUP BY and ORDER BY to get simple answers out of a table. This assignment pushed me further. We had to research four topics and show how each one works on our hospital database:

1. Part 1: Window functions
2. Part 2: Joins
3. Part 3: CTEs (Common Table Expressions)
4. Part 4: SQL Subqueries

In this article I explain each one the way I came to understand it. For every idea, I'll say what it does, why you would use it, show a query I ran on the hospital data, and explain what the result told me. I've also written down the mistakes I made along the way, because those taught me the most.

I ran every query on my local PostgreSQL 13 on Windows, and the results shown are exactly what I got back.

The hospital database I used

The tables live in a schema (a folder for tables) called morning. I ran this first, so I didn't have to type morning. in front of every table name:

SET search_path TO morning; 

There are seven tables. The main one is visits, where each row is one visit to the hospital. The other six tables hold the details about each visit.

The first thing I noticed is that visits doesn't store any names. It only stores ID numbers like patient_id and doctor_id, which point to the other tables. It also stores facts about each visit: the date, the type of visit (Outpatient, Inpatient or Emergency), how many days the patient stayed, the bill in Kenya shillings (total_bill_kes), the satisfaction score, and a few more.

Before writing any queries, I looked through the data and wrote down a few facts that helped me read my results:

• There are four doctors, and each one works in one department:
  • Dr. Brian Mwangi (101): Cardiology (10)
  • Dr. Sarah Wanjiku (102): General Medicine (20)
  • Dr. Peter Otieno (103): Orthopedics (30)
  • Dr. Faith Chebet (104): Pediatrics (40)
• All the visits happened between 1 and 15 June 2026, two visits a day.
• Five patients came twice: patients 1, 2, 5, 7 and 10.
• All 30 visits together were billed KES 488,000.

Assumptions I made

• I used the data exactly as it is in the script. I didn't add, remove or fix anything. Names are shown as they're saved, so one doctor appears as DR.Brian Mwangi.
• "Week 1" means 1 to 7 June. "Week 2" means 8 to 15 June.
• In this data, every patient, doctor and department has at least one visit. I found out this makes some joins look the same, so in a few places I asked a narrower question (like "Emergency visits only") to show the difference.
• The data has no empty (NULL) IDs.

Part 1: Window Functions

1.1 The problem window functions solve

I already knew GROUP BY from class. It combines many rows into one row per group:

SELECT doctor_id, SUM(total_bill_kes) AS doctor_total FROM visits WHERE doctor_id IN (103, 104) GROUP BY doctor_id; 

 doctor_id | doctor_total -----------+-------------- 103 | 105200.00 104 | 74900.00 (2 rows) 

That gave me the totals, but I lost the individual visits. I wanted to see each visit with the doctor's total beside it, and with GROUP BY alone I couldn't do that.

That's exactly what a window function does. It does the maths for a group, but it doesn't combine the rows. Every row remains.

SELECT visit_id, doctor_id, total_bill_kes, SUM(total_bill_kes) OVER (PARTITION BY doctor_id) AS doctor_total FROM visits WHERE doctor_id IN (103, 104) ORDER BY doctor_id, visit_id; 

 visit_id | doctor_id | total_bill_kes | doctor_total ----------+-----------+----------------+-------------- 1003 | 103 | 28000.00 | 105200.00 1008 | 103 | 6500.00 | 105200.00 1015 | 103 | 24000.00 | 105200.00 1020 | 103 | 6500.00 | 105200.00 1023 | 103 | 7200.00 | 105200.00 1027 | 103 | 33000.00 | 105200.00 1004 | 104 | 42000.00 | 74900.00 1007 | 104 | 5500.00 | 74900.00 1014 | 104 | 1800.00 | 74900.00 1019 | 104 | 5200.00 | 74900.00 1024 | 104 | 18500.00 | 74900.00 1028 | 104 | 1900.00 | 74900.00 (12 rows) 

The totals are the same as before, but all 12 visits are still there.

We learn that GROUP BY gives one row per group. A window function gives every row, plus something about its group.

1.2 How a window function is written: OVER, PARTITION BY, ORDER BY and window frames

I learnt that a window function always has the word OVER after it. The brackets describe the "window", which is just the set of rows the function looks at.

function_name() OVER ( PARTITION BY column -- split rows into groups (optional) ORDER BY column -- sort rows inside each group (optional) ROWS BETWEEN ... -- the frame: which nearby rows to use (optional) ) 

Here's what I learnt about each part.

OVER()

Empty brackets mean "look at all the rows". I used this to put the hospital's grand total next to every visit and work out each visit's share of the money:

SELECT visit_id, total_bill_kes, SUM(total_bill_kes) OVER () AS hospital_total, ROUND(100.0 * total_bill_kes / SUM(total_bill_kes) OVER (), 1) AS percent_of_total FROM visits ORDER BY total_bill_kes DESC LIMIT 5; 

 visit_id | total_bill_kes | hospital_total | percent_of_total ----------+----------------+----------------+------------------ 1017 | 82000.00 | 488000.00 | 16.8 1010 | 76000.00 | 488000.00 | 15.6 1004 | 42000.00 | 488000.00 | 8.6 1016 | 36000.00 | 488000.00 | 7.4 1027 | 33000.00 | 488000.00 | 6.8 (5 rows) 

The output told me that one visit (1017, a heart failure patient) brought in almost 17% of all the money in these two weeks. I wouldn't have spotted that from the raw table.

PARTITION BY

PARTITION BY splits the rows into groups, and the function starts again for each group. It's like GROUP BY, except the rows aren't combined. I used PARTITION BY doctor_id above to get each doctor's total.

ORDER BY (inside OVER)

This sorts the rows before the function does its work. It matters whenever order matters: numbering rows 1, 2, 3, looking at the previous row, or keeping a running total.

ORDER BY inside OVER (...) is not the same as the ORDER BY at the end of the query. The one inside changes how the answer is worked out. The one at the end only changes how the rows are shown.

Window frames

A frame says which rows near the current row to include. These are the ones I used:

Something I noticed is that when I wrote a running total with ORDER BY but no frame, the numbers looked wrong. I found out that without a frame, PostgreSQL quietly uses RANGE instead of ROWS, and RANGE treats rows with the same value as one block. Our hospital has two visits every day, so this is what happened:

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

 visit_id | visit_date | total_bill_kes | running_default | running_rows ----------+------------+----------------+-----------------+-------------- 1001 | 2026-06-01 | 3500.00 | 6000.00 | 3500.00 1002 | 2026-06-01 | 2500.00 | 6000.00 | 6000.00 1003 | 2026-06-02 | 28000.00 | 76000.00 | 34000.00 1004 | 2026-06-02 | 42000.00 | 76000.00 | 76000.00 1005 | 2026-06-03 | 4000.00 | 83000.00 | 80000.00 1006 | 2026-06-03 | 3000.00 | 83000.00 | 83000.00 (6 rows) 

• running_default adds both visits from the same day together. That's why both 1 June rows say 6,000.
• running_rows adds one visit at a time: 3,500, then 6,000, then 34,000, and so on.

In the end I understood that neither one is wrong. The first answers "how much by the end of this day?" The second answers "how much up to this visit?"

1.3 Ranking functions: ROW_NUMBER, RANK, DENSE_RANK and NTILE

ROW_NUMBER()

This function numbers the rows 1, 2, 3... Every row gets its own number, even if two rows look the same.

It can be used to count things in order, like a patient's 1st visit, 2nd visit, and so on.

In my practice, I numbered each returning patient's visits by date.

SELECT patient_id, visit_id, visit_date, 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_number; 

 patient_id | visit_id | visit_date | visit_number ------------+----------+------------+-------------- 1 | 1001 | 2026-06-01 | 1 1 | 1011 | 2026-06-06 | 2 2 | 1002 | 2026-06-01 | 1 2 | 1021 | 2026-06-11 | 2 5 | 1005 | 2026-06-03 | 1 5 | 1025 | 2026-06-13 | 2 7 | 1007 | 2026-06-04 | 1 7 | 1028 | 2026-06-14 | 2 10 | 1010 | 2026-06-05 | 1 10 | 1030 | 2026-06-15 | 2 (10 rows) 

The output showed that because of PARTITION BY patient_id, the count starts again at 1 for every patient. Any row marked 2 is a return visit.

RANK() and DENSE_RANK()

These functions give each row a position, like runners in a race. If two rows tie, they get the same position. The difference is what comes next:

• RANK() skips a number after a tie: 1, 1, 3
• DENSE_RANK() doesn't skip: 1, 1, 2

In my practice, I ranked the visits by patient satisfaction score, highest first. Some visits have the same score, so I put all three functions side by side to see how they differ.

SELECT visit_id, patient_satisfaction_score AS score, ROW_NUMBER() OVER (ORDER BY patient_satisfaction_score DESC, visit_id) AS row_num, RANK() OVER (ORDER BY patient_satisfaction_score DESC) AS rank, DENSE_RANK() OVER (ORDER BY patient_satisfaction_score DESC) AS dense_rank FROM visits ORDER BY score DESC, visit_id LIMIT 9; 

 visit_id | score | row_num | rank | dense_rank ----------+-------+---------+------+------------ 1011 | 4.8 | 1 | 1 | 1 1028 | 4.8 | 2 | 1 | 1 1005 | 4.7 | 3 | 3 | 2 1019 | 4.7 | 4 | 3 | 2 1007 | 4.6 | 5 | 5 | 3 1015 | 4.6 | 6 | 5 | 3 1025 | 4.6 | 7 | 5 | 3 1001 | 4.5 | 8 | 8 | 4 1013 | 4.5 | 9 | 8 | 4 (9 rows) 

In the output, visits 1011 and 1028 both scored 4.8, so RANK and DENSE_RANK both put them in 1st place.

• The next score (4.7) is 3rd with RANK, because two visits already took 1st and 2nd. With DENSE_RANK it's 2nd.
• ROW_NUMBER ignores ties completely and just counts 1 to 9.

When I first ran this, tied visits came out of ROW_NUMBER in a different order each time. I learnt that if rows tie, PostgreSQL can number them in any order. Adding visit_id to its sort made the result the same every time.

NTILE(n)

NTILE(n) splits the rows into n groups of about the same size and says which group each row is in.

It is used to sort things into bands, like cheap, middle and expensive.

In practice, I split the 9 inpatient visits into 3 cost groups.

SELECT visit_id, total_bill_kes, NTILE(3) OVER (ORDER BY total_bill_kes) AS cost_group FROM visits WHERE admission_type = 'Inpatient' ORDER BY total_bill_kes; 

 visit_id | total_bill_kes | cost_group ----------+----------------+------------ 1024 | 18500.00 | 1 1015 | 24000.00 | 1 1003 | 28000.00 | 1 1026 | 29000.00 | 2 1027 | 33000.00 | 2 1016 | 36000.00 | 2 1004 | 42000.00 | 3 1010 | 76000.00 | 3 1017 | 82000.00 | 3 (9 rows) 

As per the output, group 1 holds the three cheapest stays and group 3 the three most expensive. I also read that if the rows don't split evenly, the first groups just get one extra row each.

1.4 Looking at other rows: LAG, LEAD, FIRST_VALUE and LAST_VALUE

LAG() and LEAD()

LAG looks back at the previous row. LEAD looks ahead at the next row.

They are used to compare a row with the one before or after it. For example, "How many days since this patient was last here?"

In my practice, for each returning patient, I showed their previous visit, their next visit, and the number of days in between.

SELECT patient_id, visit_id, visit_date, LAG(visit_date) OVER (PARTITION BY patient_id ORDER BY visit_date) AS previous_visit, LEAD(visit_date) OVER (PARTITION BY patient_id ORDER BY visit_date) AS next_visit, visit_date - LAG(visit_date) OVER (PARTITION BY patient_id ORDER BY visit_date) AS days_since_last FROM visits WHERE patient_id IN (1, 2, 5, 7, 10) ORDER BY patient_id, visit_date; 

 patient_id | visit_id | visit_date | previous_visit | next_visit | days_since_last ------------+----------+------------+----------------+------------+----------------- 1 | 1001 | 2026-06-01 | NULL | 2026-06-06 | NULL 1 | 1011 | 2026-06-06 | 2026-06-01 | NULL | 5 2 | 1002 | 2026-06-01 | NULL | 2026-06-11 | NULL 2 | 1021 | 2026-06-11 | 2026-06-01 | NULL | 10 5 | 1005 | 2026-06-03 | NULL | 2026-06-13 | NULL 5 | 1025 | 2026-06-13 | 2026-06-03 | NULL | 10 7 | 1007 | 2026-06-04 | NULL | 2026-06-14 | NULL 7 | 1028 | 2026-06-14 | 2026-06-04 | NULL | 10 10 | 1010 | 2026-06-05 | NULL | 2026-06-15 | NULL 10 | 1030 | 2026-06-15 | 2026-06-05 | NULL | 10 (10 rows) 

The output showed that patient 1 came back after 5 days, and everyone else came back after 10. A first visit has nothing before it, so LAG shows NULL. A last visit has nothing after it, so LEAD shows NULL.

FIRST_VALUE() and LAST_VALUE()

FIRST_VALUE takes the value from the first row in the window. LAST_VALUE takes it from the last row.

Using our hospital data, for each of Dr. Faith Chebet's visits (doctor 104), I showed the bill from her first visit and from her last visit.

SELECT visit_id, visit_date, total_bill_kes, FIRST_VALUE(total_bill_kes) OVER (ORDER BY visit_date, visit_id) AS first_bill, LAST_VALUE(total_bill_kes) OVER (ORDER BY visit_date, visit_id) AS last_bill_default, LAST_VALUE(total_bill_kes) OVER (ORDER BY visit_date, visit_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_bill_full FROM visits WHERE doctor_id = 104 ORDER BY visit_date, visit_id; 

 visit_id | visit_date | total_bill_kes | first_bill | last_bill_default | last_bill_full ----------+------------+----------------+------------+-------------------+---------------- 1004 | 2026-06-02 | 42000.00 | 42000.00 | 42000.00 | 1900.00 1007 | 2026-06-04 | 5500.00 | 42000.00 | 5500.00 | 1900.00 1014 | 2026-06-07 | 1800.00 | 42000.00 | 1800.00 | 1900.00 1019 | 2026-06-10 | 5200.00 | 42000.00 | 5200.00 | 1900.00 1024 | 2026-06-12 | 18500.00 | 42000.00 | 18500.00 | 1900.00 1028 | 2026-06-14 | 1900.00 | 42000.00 | 1900.00 | 1900.00 (6 rows) 

• The output showed that first_bill is 42,000 on every row. That's her first visit (1004), so that part worked straight away.
• last_bill_default just copied each row's own bill. The reason is the same hidden default frame from earlier. It stops at the current row, so from each row's point of view, the "last" row is itself.
• last_bill_full tells it to look at all the rows, and then it correctly shows 1,900, her last visit (1028).

Lesson learnt: whenever I use LAST_VALUE, I add ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING.

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

I knew these five from the GROUP BY chapter of our notes. What I learnt is that putting OVER (...) after them turns them into window functions with the same answers, but every row stays.

I put each Orthopedics visit (department 30) next to its department's count, total, average, lowest and highest bill.

SELECT visit_id, total_bill_kes, COUNT(*) OVER (PARTITION BY department_id) AS dept_visits, SUM(total_bill_kes) OVER (PARTITION BY department_id) AS dept_total, ROUND(AVG(total_bill_kes) OVER (PARTITION BY department_id), 2) AS dept_avg, MIN(total_bill_kes) OVER (PARTITION BY department_id) AS dept_min, MAX(total_bill_kes) OVER (PARTITION BY department_id) AS dept_max FROM visits WHERE department_id = 30 ORDER BY visit_id; 

 visit_id | total_bill_kes | dept_visits | dept_total | dept_avg | dept_min | dept_max ----------+----------------+-------------+------------+----------+----------+---------- 1003 | 28000.00 | 6 | 105200.00 | 17533.33 | 6500.00 | 33000.00 1008 | 6500.00 | 6 | 105200.00 | 17533.33 | 6500.00 | 33000.00 1015 | 24000.00 | 6 | 105200.00 | 17533.33 | 6500.00 | 33000.00 1020 | 6500.00 | 6 | 105200.00 | 17533.33 | 6500.00 | 33000.00 1023 | 7200.00 | 6 | 105200.00 | 17533.33 | 6500.00 | 33000.00 1027 | 33000.00 | 6 | 105200.00 | 17533.33 | 6500.00 | 33000.00 (6 rows) 

The results show the average Orthopedics bill is KES 17,533.33. With that number on every row, we see that visits 1003, 1015 and 1027 are above average and the other three are below.

A moving average

Adding ORDER BY and a frame turns AVG into a moving average, which is an average that slides along the rows. This one averages each visit with the two before it:

SELECT visit_id, visit_date, total_bill_kes, ROUND(AVG(total_bill_kes) OVER (ORDER BY visit_date, visit_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 2) AS moving_avg_3 FROM visits WHERE doctor_id = 104 ORDER BY visit_date, visit_id; 

 visit_id | visit_date | total_bill_kes | moving_avg_3 ----------+------------+----------------+-------------- 1004 | 2026-06-02 | 42000.00 | 42000.00 1007 | 2026-06-04 | 5500.00 | 23750.00 1014 | 2026-06-07 | 1800.00 | 16433.33 1019 | 2026-06-10 | 5200.00 | 4166.67 1024 | 2026-06-12 | 18500.00 | 8500.00 1028 | 2026-06-14 | 1900.00 | 8533.33 (6 rows) 

The output shows that the first row has nothing before it, so its average is just itself. The second row averages two visits: (42,000 + 5,500) ÷ 2 = 23,750. From the third row on, it always uses three. I could see how a moving average evens out a one-off big bill like that 42,000.

1.6 A mistake I made: putting a window function in WHERE

I wanted the most expensive visit in each department, so my first try was this:

SELECT visit_id, department_id, total_bill_kes, RANK() OVER (PARTITION BY department_id ORDER BY total_bill_kes DESC) AS bill_rank FROM visits WHERE RANK() OVER (PARTITION BY department_id ORDER BY total_bill_kes DESC) = 1; 

PostgreSQL gave me an error:

ERROR: window functions are not allowed in WHERE 

Why it failed: this is where the "order of execution" chapter in our notes finally clicked for me. PostgreSQL runs a query in this order: FROM → WHERE → GROUP BY → HAVING → SELECT → ORDER BY → LIMIT. Window functions are worked out in the SELECT step, which comes after WHERE. So when WHERE runs, the rank doesn't exist yet.

How I fixed it: the trick is to do it in two steps. First I worked out the rank inside a CTE (I explain CTEs in Part 3), and then I filtered on that rank in the main query. By the time the main query's WHERE runs, the rank already exists, so it works:

WITH ranked AS ( SELECT visit_id, department_id, total_bill_kes, RANK() OVER (PARTITION BY department_id ORDER BY total_bill_kes DESC) AS bill_rank FROM visits ) SELECT visit_id, department_id, total_bill_kes FROM ranked WHERE bill_rank = 1 ORDER BY department_id; 

 visit_id | department_id | total_bill_kes ----------+---------------+---------------- 1017 | 10 | 82000.00 1016 | 20 | 36000.00 1027 | 30 | 33000.00 1004 | 40 | 42000.00 (4 rows) 

Now I get the most expensive visit in each of the four departments, which is what I wanted in the first place.

Part 2: Joins

As I mentioned earlier, the visits table only has numbers like patient_id = 3 and doctor_id = 103. The names live in the patients and doctors tables. I learnt that storing each thing only once is good, but it means that to make a report people can read, I have to stitch the tables back together. That's what a join does.

Every join needs an ON part that says how the rows match up. Usually it's "this ID equals that ID".

2.1 INNER JOIN

INNER JOIN keeps only the rows that have matching values in both tables. We use it when we only care about rows that exist on both sides.

I showed each visit with the patient's name and the doctor's name.

SELECT v.visit_id, p.patient_name, d.doctor_name, v.total_bill_kes FROM visits AS v INNER JOIN patients AS p ON v.patient_id = p.patient_id INNER JOIN doctors AS d ON v.doctor_id = d.doctor_id ORDER BY v.visit_id LIMIT 5; 

 visit_id | patient_name | doctor_name | total_bill_kes ----------+----------------+-------------------+---------------- 1001 | Amina Otieno | DR.Brian Mwangi | 3500.00 1002 | John Kariuki | Dr. Sarah Wanjiku | 2500.00 1003 | Grace Naliaka | Dr. Peter Otieno | 28000.00 1004 | David Mutua | Dr. Faith Chebet | 42000.00 1005 | Esther Achieng | DR.Brian Mwangi | 4000.00 (5 rows) 

v, p and d are nicknames for the tables, so I don't have to type the full names every time. v.patient_id just means "the patient_id column from visits". I also learnt you can join as many tables as you need, one JOIN at a time.

Joins work with GROUP BY too. Here's how much each department billed, using the real department names:

SELECT dep.department_name, COUNT(*) AS visits, SUM(v.total_bill_kes) AS total_billed FROM visits AS v INNER JOIN department AS dep ON v.department_id = dep.department_id GROUP BY dep.department_name ORDER BY total_billed DESC; 

 department_name | visits | total_billed ------------------+--------+-------------- Cardiology | 9 | 210100.00 Orthopedics | 6 | 105200.00 General Medicine | 9 | 97800.00 Pediatrics | 6 | 74900.00 (4 rows) 

The output shows that Cardiology and General Medicine each saw 9 visits, but Cardiology made more than twice as much money.

2.2 LEFT JOIN

The LEFT JOIN keeps every row from the left table (the one after FROM), and adds matching rows from the right table. Where there's no match, the right side is NULL (empty).

We use it when we want to keep everything from one table, even rows with no match. For example, "all doctors, even the ones who had no emergency cases".

At first I tried a plain LEFT JOIN from doctors to visits, and I got the same 30 rows as an INNER JOIN. That confused me until I realised every doctor in our data has visits, so there was nothing unmatched to show. The difference only appears when something has no match. So I decided to ask: which doctors handled an Emergency visit?

SELECT d.doctor_name, v.visit_id, v.admission_type F...