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:
- Part 1: Window functions
- Part 2: Joins
- Part 3: CTEs (Common Table Expressions)
- 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.
| Table | One row is... | Rows |
|---|---|---|
visits |
one visit | 30 |
patients |
one patient | 25 |
doctors |
one doctor | 4 |
department |
one department | 4 |
wards |
one ward | 4 |
diagnosis |
one diagnosis | 18 |
procedures |
one procedure | 15 |
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:
| Frame | In plain words |
|---|---|
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW |
From the very first row up to this one (a running total) |
ROWS BETWEEN 2 PRECEDING AND CURRENT ROW |
This row and the two before it (a 3-row average) |
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING |
Every row in the group |
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_defaultadds both visits from the same day together. That's why both 1 June rows say 6,000. -
running_rowsadds 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. WithDENSE_RANKit's 2nd. -
ROW_NUMBERignores 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.
| Function | Do ties share a number? | Does it skip after a tie? | I'd use it when I want... |
|---|---|---|---|
ROW_NUMBER() |
No | – | exactly one row per number |
RANK() |
Yes | Yes | race-style positions |
DENSE_RANK() |
Yes | No | the "top 3 scores", even if more than 3 rows have them |
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_billis 42,000 on every row. That's her first visit (1004), so that part worked straight away. -
last_bill_defaultjust 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_fulltells 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
FROM doctors AS d
LEFT JOIN visits AS v
ON d.doctor_id = v.doctor_id
AND v.admission_type = 'Emergency'
ORDER BY d.doctor_id;
doctor_name | visit_id | admission_type
-------------------+----------+----------------
DR.Brian Mwangi | 1030 | Emergency
Dr. Sarah Wanjiku | NULL | NULL
Dr. Peter Otieno | NULL | NULL
Dr. Faith Chebet | NULL | NULL
(4 rows)
The result shows all four doctors are listed, but only Dr. Brian Mwangi had an emergency. The NULLs for the other three just mean "no emergency visit found".
A mistake I learnt from: filtering a LEFT JOIN in WHERE
Before I got the query above right, I had put the Emergency check in WHERE instead of in ON, like this:
SELECT d.doctor_name, v.visit_id, v.admission_type
FROM doctors AS d
LEFT JOIN visits AS v ON d.doctor_id = v.doctor_id
WHERE v.admission_type = 'Emergency'
ORDER BY d.doctor_id;
doctor_name | visit_id | admission_type
-----------------+----------+----------------
DR.Brian Mwangi | 1030 | Emergency
(1 row)
Three of the doctors disappeared. It took me a while to see why, and again the answer was the order SQL runs in. WHERE runs after the join, so it looks at every row and throws out anything that isn't 'Emergency'. That includes the empty rows the LEFT JOIN was keeping for the other three doctors. So without meaning to, I had turned my LEFT JOIN into an INNER JOIN.
What I took away from this: with a LEFT JOIN, if I'm filtering the right-hand table and still want to keep the rows with no match, the condition goes in ON, not WHERE.
2.3 RIGHT JOIN
The RIGHT JOIN mirrors the LEFT JOIN. It keeps every row from the right table (the one after JOIN), and adds matching rows from the left table. It is used when we want to keep every row from one table, even rows with no match. The only difference is which side that table sits on.
To really see this, I asked the exact same question as in my LEFT JOIN example: which doctors handled an Emergency visit?
This time I started FROM visits and put doctors on the right, after RIGHT JOIN. Because doctors is now the right-hand table, RIGHT JOIN keeps every doctor:
SELECT d.doctor_name, v.visit_id, v.admission_type
FROM visits AS v
RIGHT JOIN doctors AS d
ON v.doctor_id = d.doctor_id
AND v.admission_type = 'Emergency'
ORDER BY d.doctor_id;
doctor_name | visit_id | admission_type
-------------------+----------+----------------
DR.Brian Mwangi | 1030 | Emergency
Dr. Sarah Wanjiku | NULL | NULL
Dr. Peter Otieno | NULL | NULL
Dr. Faith Chebet | NULL | NULL
(4 rows)
The output is exactly the same as the LEFT JOIN. A LEFT JOIN B is the same as B RIGHT JOIN A. From my reading, most people stick to LEFT JOIN because "start with this table, then add that one" is easier to follow. RIGHT JOIN is useful when adding one more table to the end of a long query and you want to keep all of its rows.
2.4 FULL OUTER JOIN
FULL OUTER JOIN keeps every row from both tables. Rows that match are joined. Rows that don't match get NULL on the side that's missing.
We use it to compare two lists and see what's in one, what's in the other, and what's in both.
I compared the patients who came in Week 1 (1–7 June) with those who came in Week 2 (8–15 June). Each list is a small query in brackets, called a subquery, which I explain in Part 4.
SELECT w1.patient_id AS week1_patient,
w2.patient_id AS week2_patient
FROM (SELECT DISTINCT patient_id FROM visits
WHERE visit_date BETWEEN '2026-06-01' AND '2026-06-07') AS w1
FULL OUTER JOIN
(SELECT DISTINCT patient_id FROM visits
WHERE visit_date BETWEEN '2026-06-08' AND '2026-06-15') AS w2
ON w1.patient_id = w2.patient_id
ORDER BY COALESCE(w1.patient_id, w2.patient_id);
week1_patient | week2_patient
---------------+---------------
1 | NULL
2 | 2
3 | NULL
4 | NULL
5 | 5
6 | NULL
7 | 7
8 | NULL
9 | NULL
10 | 10
11 | NULL
12 | NULL
13 | NULL
NULL | 14
NULL | 15
NULL | 16
NULL | 17
NULL | 18
NULL | 19
NULL | 20
NULL | 21
NULL | 22
NULL | 23
NULL | 24
NULL | 25
(25 rows)
From the output:
- 4 patients came in both weeks (2, 5, 7 and 10), so both columns are filled.
- 9 patients came only in Week 1. Patient 1 came twice, but both visits were in Week 1.
- 12 patients came only in Week 2.
An INNER JOIN would only show the 4 who came both weeks. A LEFT JOIN would only show the 13 from Week 1. The FULL OUTER JOIN shows all 25.
(COALESCE(a, b) means "use a, but if it's empty, use b". I used it from Chapter 19 of our notes to keep the list sorted by patient number.)
2.5 CROSS JOIN
CROSS JOIN pairs every row from one table with every row from the other. There's no ON. 4 rows × 4 rows = 16 rows.
We use it when we want every possible pair, like a blank duty rota listing every doctor against every ward.
SELECT d.doctor_name, w.ward_name
FROM doctors AS d
CROSS JOIN wards AS w
ORDER BY d.doctor_id, w.ward_id
LIMIT 6;
doctor_name | ward_name
-------------------+-------------------
DR.Brian Mwangi | Cardiac Care Unit
DR.Brian Mwangi | General Ward
DR.Brian Mwangi | Surgical Ward
DR.Brian Mwangi | Childrens Ward
Dr. Sarah Wanjiku | Cardiac Care Unit
Dr. Sarah Wanjiku | General Ward
(6 rows)
Without the LIMIT there are 16 rows: 4 doctors × 4 wards.
2.6 SELF JOIN
A SELF JOIN joins a table to itself. I was surprised there's no special keyword for it. You just use the same table twice with two different nicknames. It's used to compare rows in a table with other rows in the same table.
We try to answer the question: which patients come from the same county?
SELECT p1.patient_name AS patient_1,
p2.patient_name AS patient_2,
p1.county
FROM patients AS p1
INNER JOIN patients AS p2
ON p1.county = p2.county
AND p1.patient_id < p2.patient_id
ORDER BY p1.county;
patient_1 | patient_2 | county
----------------+--------------+---------
Esther Achieng | Brian Omondi | Kisumu
Amina Otieno | Isaac Kamau | Nairobi
(2 rows)
The p1.patient_id < p2.patient_id part took me a moment to understand. Without it, every patient gets matched with themselves, and every pair shows up twice (Esther–Brian and Brian–Esther).
I also tried to match each returning patient's first visit with their later one.
SELECT a.patient_id, a.visit_id AS first_visit, a.visit_date AS first_date,
b.visit_id AS later_visit, b.visit_date AS later_date
FROM visits AS a
INNER JOIN visits AS b
ON a.patient_id = b.patient_id
AND a.visit_date < b.visit_date
ORDER BY a.patient_id;
patient_id | first_visit | first_date | later_visit | later_date
------------+-------------+------------+-------------+------------
1 | 1001 | 2026-06-01 | 1011 | 2026-06-06
2 | 1002 | 2026-06-01 | 1021 | 2026-06-11
5 | 1005 | 2026-06-03 | 1025 | 2026-06-13
7 | 1007 | 2026-06-04 | 1028 | 2026-06-14
10 | 1010 | 2026-06-05 | 1030 | 2026-06-15
(5 rows)
From my research, self joins are also how you'd show "who is whose boss" if a table had a column like supervisor_id. Our doctors table doesn't have one, so I couldn't show that here.
2.7 How the joins compare
After trying all six, it helped me to put them next to each other. The main difference between them is simply which rows they keep when there's no match:
| Join | Rows kept from the left table | Rows kept from the right table | When I'd use it | My example |
|---|---|---|---|---|
INNER JOIN |
only matches | only matches | I only want rows that exist on both sides | Visits with patient and doctor names |
LEFT JOIN |
all | only matches | I want everything from the first table, matched or not | All doctors, with any emergency visits |
RIGHT JOIN |
only matches | all | Same as LEFT, but the table I want to keep is on the right | Same question, tables swapped |
FULL OUTER JOIN |
all | all | I want to compare two lists and see what's missing on either side | Week 1 patients vs Week 2 patients |
CROSS JOIN |
every pair | every pair | I want every possible combination | Every doctor with every ward |
SELF JOIN |
the table joined to itself | – | I want to compare rows within one table | Patients from the same county |
Part 3: CTEs (Common Table Expressions)
Our notes (Chapter 14) introduced CTEs briefly. A CTE lets you give a name to a query and use that name like a table inside a bigger query. It starts with the word WITH, and it only lasts while that one query runs.
WITH name_of_cte AS (
SELECT ...
)
SELECT ... FROM name_of_cte;
Why it helps: my queries were getting long and hard to follow. A CTE breaks one big query into small, named steps that read from top to bottom.
3.1 A basic CTE
A basic CTE using the question: which departments billed more than KES 100,000?
WITH department_totals AS (
SELECT department_id, SUM(total_bill_kes) AS total_billed
FROM visits
GROUP BY department_id
)
SELECT department_id, total_billed
FROM department_totals
WHERE total_billed > 100000
ORDER BY total_billed DESC;
department_id | total_billed
---------------+--------------
10 | 210100.00
30 | 105200.00
(2 rows)
The result shows that Cardiology (10) and Orthopedics (30) each billed more than KES 100,000.
- The CTE
department_totalsadds up the bills for each department. That gives four rows. - The main query reads from
department_totalslike any other table and keeps only the totals over 100,000.
3.2 Multiple CTEs
I learnt you can write several CTEs in one WITH, with commas between them, and a later CTE can use an earlier one. For example: which doctors billed more than the average doctor?
WITH doctor_totals AS (
SELECT doctor_id, SUM(total_bill_kes) AS total_billed
FROM visits
GROUP BY doctor_id
),
average_total AS (
SELECT AVG(total_billed) AS avg_billed
FROM doctor_totals
)
SELECT dt.doctor_id, dt.total_billed, ROUND(a.avg_billed, 2) AS avg_billed
FROM doctor_totals AS dt
CROSS JOIN average_total AS a
WHERE dt.total_billed > a.avg_billed;
doctor_id | total_billed | avg_billed
-----------+--------------+------------
101 | 210100.00 | 122000.00
(1 row)
- We see that
doctor_totalsworks out each doctor's total, giving 4 rows. -
average_totaluses those 4 totals to get the average, KES 122,000. That's just 1 row. - The main query puts them together. A
CROSS JOINworks here becauseaverage_totalhas only one row, so it simply adds the average to every doctor's row. Then it keeps only the doctors above it.
The output shows that only Dr. Brian Mwangi (101) billed more than the average doctor.
3.3 CTEs with joins and aggregations
We use the example: who are the top five patients by money spent, and where are they from?
WITH patient_spend AS (
SELECT patient_id,
COUNT(*) AS visits,
SUM(total_bill_kes) AS total_spent
FROM visits
GROUP BY patient_id
)
SELECT p.patient_name, p.county, ps.visits, ps.total_spent
FROM patient_spend AS ps
INNER JOIN patients AS p ON ps.patient_id = p.patient_id
ORDER BY ps.total_spent DESC
LIMIT 5;
patient_name | county | visits | total_spent
--------------------+-------------+--------+-------------
Peter Ouma | Siaya | 2 | 98000.00
Hellen Jepchirchir | Bomet | 1 | 82000.00
David Mutua | Machakos | 1 | 42000.00
Francis Njuguna | Laikipia | 1 | 36000.00
Ruth Nekesa | Trans Nzoia | 1 | 33000.00
(5 rows)
We see that the CTE does the adding up (the aggregation), and the main query adds the names (the join). Peter Ouma spent the most, KES 98,000 over two visits: a heart failure stay, and later an emergency visit for chest pain.
3.4 Recursive CTEs
A recursive CTE is one that uses itself. It builds a list one step at a time, a bit like counting on your fingers. It has two parts, joined by UNION ALL: a starting point, and a next step that takes the last row and makes the next one. It keeps going until a condition tells it to stop.
I started with the simplest example I could think of, counting from 1 to 5:
WITH RECURSIVE counter AS (
SELECT 1 AS n -- start at 1
UNION ALL
SELECT n + 1 FROM counter WHERE n < 5 -- add 1 until we reach 5
)
SELECT n FROM counter;
n
---
1
2
3
4
5
(5 rows)
Using the hospital data, I built a calendar for Week 1 and counted the visits on each day. The reason for a calendar is that a normal GROUP BY visit_date can only show days that had visits. A calendar lists every day, so a quiet day would show 0 instead of just being missing.
WITH RECURSIVE calendar AS (
SELECT DATE '2026-06-01' AS day
UNION ALL
SELECT (day + 1)::DATE FROM calendar WHERE day < DATE '2026-06-07'
)
SELECT c.day,
COUNT(v.visit_id) AS visits,
COALESCE(SUM(v.total_bill_kes), 0) AS billed
FROM calendar AS c
LEFT JOIN visits AS v ON v.visit_date = c.day
GROUP BY c.day
ORDER BY c.day;
day | visits | billed
------------+--------+----------
2026-06-01 | 2 | 6000.00
2026-06-02 | 2 | 70000.00
2026-06-03 | 2 | 7000.00
2026-06-04 | 2 | 12000.00
2026-06-05 | 2 | 91000.00
2026-06-06 | 2 | 6500.00
2026-06-07 | 2 | 8800.00
(7 rows)
The output shows that the hospital had exactly 2 visits every day, and 5 June brought in the most money (KES 91,000), mostly from one heart failure patient. I was pleased that this one query uses three things I learnt in this assignment: a recursive CTE, a LEFT JOIN and a total.
From my research, recursive CTEs are also the usual way to follow a chain, like "this doctor reports to that doctor, who reports to..." Our data doesn't have that kind of column, so I used a calendar instead.
3.5 CTEs vs subqueries
A subquery (Part 4) is a query written inside another query. While I was learning about CTEs, I kept wondering how they were different from subqueries. So I took my first CTE from 3.1 and rewrote it as a subquery to compare them side by side:
SELECT department_id, total_billed
FROM (SELECT department_id, SUM(total_bill_kes) AS total_billed
FROM visits
GROUP BY department_id) AS department_totals
WHERE total_billed > 100000
ORDER BY total_billed DESC;
department_id | total_billed
---------------+--------------
10 | 210100.00
30 | 105200.00
(2 rows)
It gave exactly the same answer. The difference isn't in the result. It's in how the query is written and how easy it is to read. With the CTE, I name the step first and then use it, so it reads from top to bottom. With the subquery, the step is tucked inside the main query, so I have to read from the inside out.
This is how I'd sum up the differences I found:
| CTE | Subquery | |
|---|---|---|
| Where you write it | At the top, before the main query | Inside the main query |
| Does it have a name? | Yes | Only when it's in FROM
|
| Can you use it twice in the same query? | Yes, just use the name again | No, you'd have to type it out again |
| Can it repeat itself (recursive)? | Yes | No |
| Easy to read with many steps? | Yes, top to bottom | Harder, with brackets inside brackets |
| Best for | Step-by-step logic and reusing results | Short, one-off checks |
I also read that in PostgreSQL 12 and newer, a simple CTE and the same subquery run about as fast as each other. So my takeaway is to pick whichever one is easier to read.
Part 4: SQL Subqueries
A subquery is a SELECT inside another query, wrapped in brackets. PostgreSQL answers the small question first, then uses that answer in the big one. You can put a subquery in WHERE, SELECT or FROM.
4.1 Scalar (single-value) subqueries
This is a subquery that gives back just one value: one row, one column. "Scalar" turned out to be just the fancy name for that. It can be used to compare against a number I'd otherwise have to look up first, like an average.
Scalar subquery in WHERE
For example, to answer the question: which visits cost more than the average visit?
First I checked the average on its own:
SELECT AVG(total_bill_kes) FROM visits;
avg
--------------------
16266.666666666667
(1 row)
Instead of copying that number into another query by hand, I let a subquery work it out:
SELECT visit_id, patient_id, total_bill_kes
FROM visits
WHERE total_bill_kes > (SELECT AVG(total_bill_kes) FROM visits)
ORDER BY total_bill_kes DESC;
visit_id | patient_id | total_bill_kes
----------+------------+----------------
1017 | 16 | 82000.00
1010 | 10 | 76000.00
1004 | 4 | 42000.00
1016 | 15 | 36000.00
1027 | 24 | 33000.00
1026 | 23 | 29000.00
1003 | 3 | 28000.00
1015 | 14 | 24000.00
1030 | 10 | 22000.00
1024 | 22 | 18500.00
(10 rows)
The output shows that 10 of the 30 visits cost more than the average (KES 16,266.67). I also realised that if new visits are added, the query still works, because it works out the average fresh every time.
Scalar subquery in SELECT
To answer the question: what share of the hospital's money did each department bring in?
SELECT department_id,
SUM(total_bill_kes) AS department_total,
ROUND(100.0 * SUM(total_bill_kes) / (SELECT SUM(total_bill_kes) FROM visits), 1) AS percent_of_hospital
FROM visits
GROUP BY department_id
ORDER BY department_id;
department_id | department_total | percent_of_hospital
---------------+------------------+---------------------
10 | 210100.00 | 43.1
20 | 97800.00 | 20.0
30 | 105200.00 | 21.6
40 | 74900.00 | 15.3
(4 rows)
The output shows that Cardiology (10) brought in 43% of all the money. The subquery gives the hospital total (488,000), and each department's total is divided by it.
4.2 Non-correlated vs correlated subqueries
This was the difference that took me longest to get:
- A non-correlated subquery stands alone. You could copy it out and run it by itself, and PostgreSQL only runs it once. Both examples above are like this.
- A correlated subquery borrows a value from the outer query, so it can't run by itself. It's worked out again for each row.
For example (correlated), to answer the question: which visits cost more than the average for their own department? I thought it was fairer to compare a Cardiology visit with other Cardiology visits, not with the whole hospital.
SELECT v.visit_id, v.department_id, v.total_bill_kes
FROM visits AS v
WHERE v.total_bill_kes > (SELECT AVG(v2.total_bill_kes)
FROM visits AS v2
WHERE v2.department_id = v.department_id)
ORDER BY v.department_id, v.total_bill_kes DESC;
visit_id | department_id | total_bill_kes
----------+---------------+----------------
1017 | 10 | 82000.00
1010 | 10 | 76000.00
1016 | 20 | 36000.00
1026 | 20 | 29000.00
1009 | 20 | 15000.00
1027 | 30 | 33000.00
1003 | 30 | 28000.00
1015 | 30 | 24000.00
1004 | 40 | 42000.00
1024 | 40 | 18500.00
(10 rows)
For reference, the department averages are: Cardiology 23,344.44, General Medicine 10,866.67, Orthopedics 17,533.33 and Pediatrics 12,483.33.
The output shows that visit 1009 (General Medicine, KES 15,000) is below the hospital average, so my earlier query skipped it. But it's above the General Medicine average, so this query includes it. The part that makes it correlated is WHERE v2.department_id = v.department_id: the inside query borrows v from the outside query.
| Non-correlated | Correlated | |
|---|---|---|
| Borrows from the outer query? | No | Yes |
| Can you run it by itself? | Yes | No |
| How often does it run? | Once | Once per outer row |
| My example | Above the hospital average | Above their department's average |
4.3 Nested subqueries
A nested subquery is a subquery inside another subquery. PostgreSQL starts with the innermost one and works its way out.
My example: which patients saw the doctor who billed the most?
SELECT patient_name
FROM patients
WHERE patient_id IN (
SELECT patient_id
FROM visits
WHERE doctor_id = (
SELECT doctor_id
FROM visits
GROUP BY doctor_id
ORDER BY SUM(total_bill_kes) DESC
LIMIT 1))
ORDER BY patient_name;
patient_name
--------------------
Amina Otieno
Catherine Musyoka
Esther Achieng
Hellen Jepchirchir
John Kariuki
Peter Ouma
(6 rows)
How I read it, from the inside out:
- The innermost query finds the doctor who billed the most: 101, Dr. Brian Mwangi (KES 210,100).
- The middle query finds the patients he saw: 1, 2, 5, 10, 12 and 16.
- The outer query swaps those numbers for names.
4.4 Subqueries with IN
IN (subquery) keeps a row if its value is on the list the subquery gives back.
My example: which visits were for a chronic (long-term) disease?
SELECT visit_id, patient_id, diagnosis_id
FROM visits
WHERE diagnosis_id IN (SELECT diagnosis_id
FROM diagnosis
WHERE diagnosis_category = 'Chronic Disease')
ORDER BY visit_id;
visit_id | patient_id | diagnosis_id
----------+------------+--------------
1001 | 1 | 501
1005 | 5 | 505
1010 | 10 | 510
1011 | 1 | 505
1013 | 12 | 501
1017 | 16 | 510
1021 | 2 | 501
1025 | 5 | 501
(8 rows)
Findings: the subquery gives back three chronic diseases (501 Hypertension, 505 Diabetes and 510 Heart Failure), and 8 visits were for one of them.
A trap I read about: NOT IN with an empty value
NOT IN keeps rows that are not on the list. But I learnt that if the list has even one NULL in it, NOT IN gives back nothing at all. Our data has no NULL IDs, so I tested it with a tiny list typed by hand:
SELECT COUNT(*) AS patients_found FROM patients WHERE patient_id NOT IN (1, 2);
patients_found
----------------
23
(1 row)
SELECT COUNT(*) AS patients_found FROM patients WHERE patient_id NOT IN (1, 2, NULL);
patients_found
----------------
0
(1 row)
Our notes taught us that NULL means "we don't know". PostgreSQL can't be sure patient 3 isn't the unknown value, so it plays it safe and keeps nothing. If a list might contain a NULL, NOT EXISTS (next) is the safer choice.
4.5 Subqueries with EXISTS
EXISTS asks a yes-or-no question: "Is there at least one matching row?" It doesn't care what's in the row, only whether there is one. That's why people usually write SELECT 1 inside it.
For example, which doctors had at least one emergency visit, and which had none?
SELECT d.doctor_name
FROM doctors AS d
WHERE EXISTS (SELECT 1
FROM visits AS v
WHERE v.doctor_id = d.doctor_id
AND v.admission_type = 'Emergency');
doctor_name
-----------------
DR.Brian Mwangi
(1 row)
SELECT d.doctor_name
FROM doctors AS d
WHERE NOT EXISTS (SELECT 1
FROM visits AS v
WHERE v.doctor_id = d.doctor_id
AND v.admission_type = 'Emergency');
doctor_name
-------------------
Dr. Sarah Wanjiku
Dr. Peter Otieno
Dr. Faith Chebet
(3 rows)
The output gives the same answer as my LEFT JOIN example in Part 2, just asked as a yes-or-no question for each doctor. I also noticed the inside query borrows d.doctor_id from the outside, so EXISTS subqueries are nearly always correlated.
IN |
EXISTS |
|
|---|---|---|
| The question it asks | "Is this value on the list?" | "Is there at least one matching row?" |
| What the subquery gives back | A list of values | Rows (only whether there are any matters) |
Safe with NULL when you use NOT? |
No, NOT IN breaks |
Yes, NOT EXISTS is safe |
4.6 Subqueries with ANY and ALL
ANY and ALL compare one value with a whole list, using >, <, = and so on. This is how I made sense of them:
-
> ANY (list)means "bigger than at least one of them", so bigger than the smallest. -
> ALL (list)means "bigger than every one of them", so bigger than the biggest.
My example: I compared Cardiology visits (department 10) with Orthopedics bills (department 30). The six Orthopedics bills are 6,500, 6,500, 7,200, 24,000, 28,000 and 33,000.
ANY: which Cardiology visits cost more than at least one Orthopedics visit?
SELECT visit_id, total_bill_kes
FROM visits
WHERE department_id = 10
AND total_bill_kes > ANY (SELECT total_bill_kes
FROM visits
WHERE department_id = 30)
ORDER BY total_bill_kes;
visit_id | total_bill_kes
----------+----------------
1013 | 7000.00
1025 | 7600.00
1030 | 22000.00
1010 | 76000.00
1017 | 82000.00
(5 rows)
ALL: which Cardiology visits cost more than every Orthopedics visit?
SELECT visit_id, total_bill_kes
FROM visits
WHERE department_id = 10
AND total_bill_kes > ALL (SELECT total_bill_kes
FROM visits
WHERE department_id = 30)
ORDER BY total_bill_kes;
visit_id | total_bill_kes
----------+----------------
1010 | 76000.00
1017 | 82000.00
(2 rows)
-
> ANYkept the 5 Cardiology visits over 6,500, the cheapest Orthopedics bill. -
> ALLkept only the 2 over 33,000, the most expensive Orthopedics bill. - I also found out that
= ANY (...)means exactly the same asIN (...).
4.7 Subqueries in FROM
A subquery in FROM acts like a temporary table. I learnt that it must have a nickname. Here I called it totals.
For example, what's the average amount billed per doctor? That takes two steps: first each doctor's total, then the average of those totals.
SELECT ROUND(AVG(doctor_total), 2) AS avg_per_doctor
FROM (SELECT doctor_id, SUM(total_bill_kes) AS doctor_total
FROM visits
GROUP BY doctor_id) AS totals;
avg_per_doctor
----------------
122000.00
(1 row)
The output shows that the four doctor totals are 210,100, 97,800, 105,200 and 74,900, and their average is KES 122,000. That's the same number I got with two CTEs in Part 3, which was a nice way to check my work. You can't write AVG(SUM(...)) in one go, so the subquery handles the first step.
4.8 Where a subquery can go: SELECT, FROM and WHERE
| Where | What it needs to give back | My example |
|---|---|---|
WHERE |
One value (for >, =...), a list (for IN, ANY, ALL), or rows (for EXISTS) |
Visits above the average bill |
SELECT |
One value for each row | Each department's share of the total |
FROM |
A table of rows, with a nickname | The average of the doctor totals |
What I learnt overall
| Topic | In one line | A question it helped me answer |
|---|---|---|
| Window functions | Do maths across a group without losing any rows | "Rank visits by satisfaction", "days since the last visit" |
| Joins | Combine tables using matching IDs | "Show each visit with the patient's and doctor's names" |
| CTEs | Name the steps of a query so it reads top to bottom | "Which doctors billed more than the average doctor?" |
| Subqueries | A small question inside a bigger one | "Which visits cost more than their department's average?" |
Top comments (0)