DEV Community: Zena Muhoro

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

Zena Muhoro — Sun, 18 Oct 2026 12:26:26 +0000

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_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.

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_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
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_totals adds up the bills for each department. That gives four rows.
The main query reads from department_totals like 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_totals works out each doctor's total, giving 4 rows.
average_total uses those 4 totals to get the average, KES 122,000. That's just 1 row.
The main query puts them together. A CROSS JOIN works here because average_total has 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)

> ANY kept the 5 Cardiology visits over 6,500, the cheapest Orthopedics bill.
> ALL kept only the 2 over 33,000, the most expensive Orthopedics bill.
I also found out that = ANY (...) means exactly the same as IN (...).

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?"

Excel Dashboards: Product Analysis for Jumia Kenya

Zena Muhoro — Sat, 05 Sep 2026 18:09:55 +0000

Introduction

E-commerce platforms like Jumia collect large amounts of data daily on prices, discounts, reviews, and ratings. However, this data is only useful when it is broken down and analyzed to identify trends and insights that can help improve pricing, promotions, and customer engagement.

For this project, I analyzed a dataset of 112 products listed on Jumia, to understand how pricing and discounting relate to customer reviews and ratings. The goal was to move from a raw dataset to a clean, interactive Excel dashboard that surfaces real business insights to be used by the company's managers and directors to make desions.

This article walks you through my process: cleaning the data, enriching it with calculated fields, running the analysis, building the dashboard, and the insights that came out of it.

Dataset Description

The dataset contained six fields per product:

Product — the product name
Current Price — the current selling price, in KSh
Old Price — the price before any discount
Discount — the discount percentage applied
Review — the number of customer reviews
Rating — the average customer rating, out of 5

At a glance, the raw data had a few problems typical of scraped or exported e-commerce data: prices stored as text with currency symbols and commas, ratings embedded in strings like "4.5 out of 5" instead of numbers, and some review counts stored as negative values. None of this was usable for analysis until it was cleaned.

Data Cleaning and Preparation

Before any analysis could happen, I worked through a standard data-cleaning checklist:

Numeric conversion — stripped KSh, commas, and other non-numeric characters from Current Price and Old Price, converting both to clean numeric columns.
Rating extraction — parsed the numeric rating out of the "X out of 5" text format.
Fixing sign errors — corrected review counts that had been stored as negative numbers.
Missing values — checked every column for gaps. Notably, 55 of the 112 products (about 49%) had no rating at all. Rather than dropping these rows (which would have thrown away real pricing and discount data), I labeled them "Not Provided" in a rating category column so they could be handled explicitly instead of silently distorting averages.
Duplicates and inconsistencies — scanned for duplicate or near-duplicate product entries and inconsistent formatting.

Enrichment: New Calculated Columns

Once the base data was clean, I added several derived columns to make deeper analysis possible:

Discount Amount = Old Price − Current Price
Rating Category: Poor (below 3), Average (3–4), Excellent (above 4.5)

Formula used:
=IF(ISBLANK(I13),"",IF(I13<3,"Poor",IF(I13>=4.5,"Excellent","Average")))

Discount Category: Low (below 20%), Medium (20–40%), High (above 40%)
Formula used:
=IF(F2<20%,"Low Discount",IF(F2<=40%,"Medium Discount", "High Discount"))
Price Category: Low, Medium, High — based on price quartiles (roughly KSh 493 and KSh 1,670 as breakpoints)
Formula used:
=IF([@[Current Price]]="","Missing",IF([@[Current Price]]<Data_Analysis!$B$21,"Low Price",IF([@[Current Price]]<Data_Analysis!$B$22,"Medium Price","High Price")))

Excel Techniques and Analysis

With clean, enriched data in place, I moved to analysis using core Excel tools: formulas (AVERAGE, MAX, MIN, CORREL, COUNTIFS), PivotTables, and Pivot Charts.

Descriptive statistics across the 112 products:

Metric	Value
Total products	112
Total reviews	723
Average current price	KSh 1,186.89
Average old price	KSh 1,811.11
Average discount	36.8%
Average rating (where available)	3.89 / 5
Most expensive product	32PCS Portable Cordless Drill Set — KSh 3,750
Least expensive product	3PCS Single Head Knitting Crochet Sweater Needle Set — KSh 38

Correlation analysis using CORREL() to test three relationships:

Relationship	Correlation	Reading
Discount % vs. Reviews	−0.14	No relationship — discounts don't drive more reviews
Rating vs. Reviews	+0.06	Essentially no relationship
Price vs. Rating	+0.11	Weak — expensive products aren't rated meaningfully higher

These weak-to-negligible correlations were one of the more interesting findings of the project — the intuitive assumption that "bigger discount = more engagement" simply didn't hold up in this dataset.

Category breakdowns (via PivotTables):

Rating: 23 Excellent, 22 Average, 12 Poor, 55 Not Provided
Discount: 62 High, 32 Medium, 18 Low
Price: 88 High, 7 Medium, 17 Low

Seller-performance flags — I built rule-based flags with COUNTIFS to segment products by behavior pattern:

Flag	Count	Rule
Strong customer engagement	27	Reviews ≥ 8
High discount + low engagement	50	Discount > 40% AND Reviews < 8
Many reviews + average rating	14	Reviews ≥ 8 AND Rating 3.0–4.5
High discount + low rating	10	Discount > 40% AND Rating < 3

Dashboard Creation Process

The final step was pulling everything into a single interactive dashboard sheet. I used:

KPI cards for total products, average price, average discount, average rating, and total reviews
PivotCharts for top 10 products by rating, by reviews, and by discount
Scatter/column charts to visualize the discount-vs-review, rating-vs-review, and price-vs-rating relationships
Pie and column charts for the rating, discount, and price category breakdowns
Conditional formatting to visually flag high-discount/low-rating and other risk products directly in the tables
Slicers connected to all PivotTables/charts for Rating Category, Discount Category, and Price Category, so the whole dashboard can be filtered interactively

(Screenshot: final dashboard with slicers)

Key Insights and Business Recommendations

A few findings stood out as genuinely useful for decision-making, not just descriptive trivia:

Discounting alone doesn't drive engagement. Nearly half the catalog (50 of 112 products) pairs a discount above 40% with fewer than 8 reviews. Sellers relying on discount depth to generate interest aren't seeing it pay off.
Popularity ≠ quality. The single most-reviewed product (a cordless vacuum cleaner, 69 reviews) has a "Poor" rating of 2.8/5 — visibility without customer satisfaction is a churn risk.
Price doesn't buy trust. The weak price–rating correlation shows customers aren't rating expensive items more favorably just because they cost more.
Rating data has a real gap. With 49% of products unrated, both buyers and analysts are working with an incomplete picture of product quality.
High discount + low rating (10 products) is a quality issue, not a pricing issue — these listings are already discounted heavily and still underperforming, so deeper discounts are unlikely to fix the underlying problem.

Recommendations for Jumia sellers:

Don't lean on discount depth as an engagement strategy — invest in better listings (photos, descriptions, search visibility) instead.
Encourage post-purchase reviews, especially for high-selling but unrated products, to close the 49% rating gap.
Treat high-review/low-rating products as a quality-control priority, not a marketing one.
Be skeptical of "perfect" 5.0 ratings built on only 1–2 reviews — they're not statistically meaningful yet.
Escalate the 10 "high discount + low rating" products for product-level review rather than further discounting.

Closing Thoughts

This project reinforced something that comes up in almost every real-world dataset: the obvious assumption (discounts drive engagement, price signals quality) often doesn't survive contact with the actual numbers. Cleaning the data properly — especially handling the missing ratings honestly instead of hiding them — was what made the correlation analysis trustworthy enough to act on.

The full workbook, including the raw data, cleaned data, analysis sheets, and interactive dashboard, is available in the GitHub repository linked below.

GitHub repo: https://github.com/zenamuhoro-dot/jumia-product-performance/tree/main

# My First Week Learning Git

Zena Muhoro — Sun, 23 Aug 2026 07:35:00 +0000

This week, I was introduced to Git and GitHub as part of my Data Science learning journey.

At first, I found Git a little confusing. There were several commands to learn, and I wasn't sure how they all fitted together.

After practising, I started to understand that Git is mainly about keeping track of changes to my work.

The basic process I learned was:

Working Directory → Staging → Commit → Push

Working Directory

The working directory is basically where I do my work on my computer.

When I create or edit a file, the changes are made here first.

One of the first commands I learned was:


bash
git status