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

Introduction

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

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

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

The hospital dataset

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

Before running anything:

set search_path to morning;

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

Part 1: Window Functions

What is a window function?

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

The OVER() clause

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

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

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

1. ROW_NUMBER()

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

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

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

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

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

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

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

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

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

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

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

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

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

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

4. NTILE(n)

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

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

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

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

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

5. LAG() and 6. LEAD()

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

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

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

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

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

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

7. FIRST_VALUE() and 8. LAST_VALUE()

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

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

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

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

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

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

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

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

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

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

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

First five of the 15 rows:

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

Window frames in depth: ROWS vs RANGE

The frame clause has this shape:

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

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

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

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

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

Part 2: SQL Joins

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

INNER JOIN

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

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

select v.visit_id, p.patient_name, d.doctor_name, v.total_bill_kes
from visits v
inner join patients p on v.patient_id = p.patient_id
inner join doctors  d on v.doctor_id  = d.doctor_id
where v.admission_type = 'Inpatient'
order by v.visit_id;

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

LEFT JOIN

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

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

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

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

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

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

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

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

RIGHT JOIN

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

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

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

First rows of the 18 returned:

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

FULL OUTER JOIN

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

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

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

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

CROSS JOIN

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

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

select d.doctor_name, t.admission_type, count(v.visit_id) as visits
from doctors d
cross join (select 'Outpatient' as admission_type
            union all select 'Inpatient'
            union all select 'Emergency') t
left join visits v
       on v.doctor_id = d.doctor_id
      and v.admission_type = t.admission_type
group by d.doctor_id, d.doctor_name, t.admission_type
order by d.doctor_id, t.admission_type;

First six of the 12 rows:

The zero rows are the point: a normal GROUP BY would silently drop 'Dr. Sarah Wanjiku, Emergency' because no such visit exists. Caution: a cross join of two big tables explodes (1,000 x 1,000 = 1,000,000 rows), so use it deliberately.

SELF JOIN

A self join joins a table to itself, using two aliases so SQL can tell the copies apart. It suits comparisons between rows of the same table.

Scenario A: find pairs of patients from the same county (to plan a shared outreach clinic).

select a.patient_name as patient_1, b.patient_name as patient_2, a.county
from patients a
join patients b
  on a.county = b.county
 and a.patient_id < b.patient_id;

The condition a.patient_id < b.patient_id stops a patient being paired with themselves and prevents the same pair appearing twice in reverse order.

Scenario B: measure how many days passed between a patient's visits (re-visit analysis).

select v1.patient_id, v1.visit_id as first_visit, v2.visit_id as return_visit,
       v2.visit_date - v1.visit_date as days_between
from visits v1
join visits v2
  on v1.patient_id = v2.patient_id
 and v2.visit_date > v1.visit_date
order by v1.patient_id;

The same answer is possible with LAG(), which is usually cleaner and faster. A self join is still the right tool when you need to compare every pair of rows, not just neighbours.

Choosing the right join

• Need only matched records? INNER.
• Need all of the main table, matched or not? LEFT (and swap the tables instead of using RIGHT).
• Need to find rows with no match? LEFT + IS NULL.
• Reconciling two sources? FULL OUTER.
• Need every combination or a complete reporting grid? CROSS.
• Comparing rows inside one table? SELF.

Part 3: Common Table Expressions (CTEs)

A CTE is a named temporary result defined with WITH, which exists only for the duration of one query. Think of it as giving a sub-step a name so the main query reads top to bottom like a story.

with name as (
    select ...
)
select ... from name;

Basic CTE

Scenario: which departments earn more than the average department?

with dept_revenue as (
    select department_id, sum(total_bill_kes) as total_revenue
    from visits
    group by department_id
)
select d.department_name, r.total_revenue
from dept_revenue r
join department d on d.department_id = r.department_id
where r.total_revenue > (select avg(total_revenue) from dept_revenue)
order by r.total_revenue desc;

The four departments bring in KES 488,000 in total, an average of KES 122,000 each, and only Cardiology is above that. The CTE is used twice: once to join names and once inside the average. That is the first advantage of CTEs: define once, reuse many times.

Multiple CTEs

Separate several CTEs with commas; later ones can use earlier ones.

Scenario: what percentage of total hospital revenue does each department bring in?

with dept_revenue as (
    select department_id, sum(total_bill_kes) as total_revenue
    from visits
    group by department_id
),
overall as (
    select sum(total_revenue) as grand_total
    from dept_revenue
)
select d.department_name, r.total_revenue,
       round(100.0 * r.total_revenue / o.grand_total, 1) as pct_of_total
from dept_revenue r
cross join overall o
join department d on d.department_id = r.department_id
order by r.total_revenue desc;

The second CTE (overall) is built from the first (dept_revenue). Cardiology alone brings in 43.1% of revenue. Each step is named and simple, where one giant query would be hard to read.

CTEs with joins and aggregations

Scenario: a doctor scorecard with visit count, revenue and average patient satisfaction. Aggregate first in a CTE, then join to the descriptive table.

with doctor_stats as (
    select doctor_id,
           count(*)                                   as visits,
           sum(total_bill_kes)                        as revenue,
           round(avg(patient_satisfaction_score), 2)  as avg_satisfaction
    from visits
    group by doctor_id
)
select d.doctor_name, d.doctor_specialty, s.visits, s.revenue, s.avg_satisfaction
from doctor_stats s
join doctors d on d.doctor_id = s.doctor_id
order by s.revenue desc;

Aggregating before joining keeps the join small and avoids the classic mistake of aggregating after a join that multiplied rows.

Recursive CTEs

A recursive CTE refers to itself. It has two parts joined by UNION ALL: an anchor (the starting row) and a recursive member that builds on the previous output, repeating until a stopping condition is met. Typical uses are org charts, category trees and, as here, generating a series of dates.

Scenario: the visits data covers 1 to 15 June. Which days between 1 and 20 June have no recorded visits? A normal query cannot list days that do not exist in the table, so we generate the calendar first.

with recursive days as (
    select date '2026-06-01' as d            -- anchor
    union all
    select d + 1 from days                   -- recursive member
    where d < date '2026-06-20'              -- stop condition
)
select days.d, count(v.visit_id) as visits
from days
left join visits v on v.visit_date = days.d
group by days.d
having count(v.visit_id) = 0
order by days.d;

The CTE produces 20 consecutive dates, the LEFT JOIN attaches visits, and HAVING keeps the empty days. Always include a stop condition, otherwise the recursion never ends. (If the hospital stored staff reporting lines, say staff_id and manager_id, the same pattern would walk a whole chain of command.)

CTEs vs subqueries

Any CTE can be rewritten as a subquery. The same question both ways, 'visits with a bill above the average':

-- Subquery
select visit_id, total_bill_kes
from visits
where total_bill_kes > (select avg(total_bill_kes) from visits);

-- CTE
with avg_bill as (select avg(total_bill_kes) as avg_amount from visits)
select v.visit_id, v.total_bill_kes
from visits v, avg_bill a
where v.total_bill_kes > a.avg_amount;

In PostgreSQL 12 and later, a CTE that is referenced once is normally inlined, so performance is usually the same as the equivalent subquery. You can force the old behaviour with AS MATERIALIZED. Rule of thumb: use a subquery for a small one-off filter, and a CTE once the logic has more than one step or you need the result twice.

Part 4: SQL Subqueries

A subquery is a SELECT inside another query, wrapped in parentheses. The inner query supplies a value, a list or a table to the outer query.

Where a subquery can sit

Scalar subquery

Returns exactly one row and one column, so it can be used like a value.

Scenario: find visits that cost more than the average visit.

select visit_id, total_bill_kes
from visits
where total_bill_kes > (select avg(total_bill_kes) from visits)
order by total_bill_kes desc;

The inner query returns one number, KES 16,266.67. Ten visits are above it:

If a scalar subquery returns more than one row, PostgreSQL raises an error.

Non-correlated subquery

The inner query is independent of the outer query: it runs once, and its result is handed to the outer query. The scalar example above is non-correlated, and so is the IN example below.

Correlated subquery

The inner query refers to a column of the outer query, so it is re-evaluated for every outer row.

Scenario: find visits that cost more than the average for their own department, a fairer comparison than the global average.

select v.visit_id, v.department_id, v.total_bill_kes
from visits v
where v.total_bill_kes > (
    select avg(v2.total_bill_kes)
    from visits v2
    where v2.department_id = v.department_id   -- link to the outer row
)
order by v.department_id, v.visit_id;

Visit 1009 (KES 15,000) is below the hospital average of 16,267, so it was missing from the previous list, but it is well above its department's average of about 10,867, so it appears here. That is the value of correlation. The same result can be achieved with AVG() OVER (PARTITION BY department_id), which is often faster on large tables.

IN

IN tests whether a value appears in the list the subquery returns.

Scenario: names of patients who have been admitted as inpatients.

select patient_name
from patients
where patient_id in (
    select patient_id from visits where admission_type = 'Inpatient'
)
order by patient_id;

Returns 9 patients: Grace Naliaka, David Mutua, Peter Ouma, Eunice Moraa, Francis Njuguna, Hellen Jepchirchir, Nadia Hussein, Patrick Muli and Ruth Nekesa.

NOT IN finds the opposite, but beware: if the subquery returns even one NULL, NOT IN returns no rows at all. Prefer NOT EXISTS when the column can be NULL. In visits, columns such as ward_id, diagnosis_id and procedure_id are nullable.

EXISTS

EXISTS returns true if the subquery returns at least one row. It is normally written as a correlated subquery, and it stops searching at the first match.

Scenario: patients who have ever been triaged as Red (the most urgent level).

select p.patient_name
from patients p
where exists (
    select 1
    from visits v
    where v.patient_id = p.patient_id
      and v.triage_level = 'Red'
)
order by p.patient_id;

Returns 6 patients: Grace Naliaka, David Mutua, Peter Ouma, Eunice Moraa, Hellen Jepchirchir and Ruth Nekesa. The select 1 is a convention: EXISTS only cares whether a row exists, not what it contains.

IN vs EXISTS: both answer 'is there a match?'. IN compares against a list; EXISTS checks for existence and is safer with NULLs (especially as NOT EXISTS). On modern PostgreSQL the planner often treats them the same for simple cases.

ANY and ALL

These compare one value against every value the subquery returns.

• > ANY (subquery): greater than at least one returned value (so, greater than the minimum).
• > ALL (subquery): greater than every returned value (so, greater than the maximum).
• = ANY is equivalent to IN.

Scenario: compare Pediatrics visits (department 40) with General Medicine visits (department 20).

-- Pediatrics bills higher than AT LEAST ONE General Medicine bill
select visit_id, total_bill_kes
from visits
where department_id = 40
  and total_bill_kes > any (select total_bill_kes from visits where department_id = 20);

-- Pediatrics bills higher than EVERY General Medicine bill
select visit_id, total_bill_kes
from visits
where department_id = 40
  and total_bill_kes > all (select total_bill_kes from visits where department_id = 20);

General Medicine bills range from KES 2,000 to 36,000. So:

• ANY keeps Pediatrics bills above 2,000: visits 1004 (42,000), 1007 (5,500), 1019 (5,200) and 1024 (18,500).
• ALL keeps only bills above 36,000: just visit 1004 (42,000).

Nested subqueries

Subqueries can sit inside other subqueries. Read them from the inside out.

Scenario: which patients were treated by the more experienced doctors, meaning those with more years of experience than the average doctor?

select p.patient_name
from patients p
where p.patient_id in (
    select patient_id
    from visits
    where doctor_id in (
        select doctor_id
        from doctors
        where doctor_experience_years > (select avg(doctor_experience_years) from doctors)
    )
)
order by p.patient_id;

Step by step: the innermost query returns the average experience (11 years); the next level returns the doctors above it (Dr. Brian Mwangi with 12 and Dr. Peter Otieno with 15); the next returns the patients who visited either of them; and the outer query turns those IDs into 12 names (Amina Otieno, John Kariuki, Grace Naliaka, Esther Achieng, Samuel Kiptoo, Peter Ouma, Catherine Musyoka, Eunice Moraa, Hellen Jepchirchir, Kevin Barasa, Moses Cheruiyot and Ruth Nekesa). Three levels works, but the same logic written as CTEs or joins would be easier to read, which is why deep nesting is usually a sign to switch.

Subquery in SELECT

A scalar subquery in the SELECT list adds a computed column to every row.

select d.department_name,
       (select count(*) from visits v where v.department_id = d.department_id) as visit_count,
       (select max(total_bill_kes) from visits v where v.department_id = d.department_id) as highest_bill
from department d
order by d.department_id;

Each subquery is correlated and runs once per department, which is fine for four rows but would be slow for large outer tables; a join with GROUP BY scales better.

Subquery in FROM (derived table)

A subquery in FROM acts as a temporary table. It must have an alias.

Scenario: average length of stay per doctor, joined back to the doctor names.

select d.doctor_name, t.avg_stay_days
from (
    select doctor_id, round(avg(length_of_stay_days), 2) as avg_stay_days
    from visits
    group by doctor_id
) t
join doctors d on d.doctor_id = t.doctor_id
order by t.avg_stay_days desc;

The average includes outpatient visits (which have a stay of 0 days), so the numbers reflect the doctor's whole workload, not just admissions. Dr. Mwangi's cardiac cases keep patients longest.

Putting it all together

The four tools are not competitors; they are used together. A single report can use a CTE to aggregate, joins to attach names, a window function to rank and a subquery to filter. For example: 'for each department, show the top-billing visit with its patient and doctor':

with ranked as (
    select v.visit_id, v.department_id, v.patient_id, v.doctor_id, v.total_bill_kes,
           row_number() over (partition by v.department_id order by v.total_bill_kes desc) as rn
    from visits v
)
select dep.department_name, p.patient_name, d.doctor_name, r.total_bill_kes
from ranked r
join department dep on dep.department_id = r.department_id
join patients   p   on p.patient_id      = r.patient_id
join doctors    d   on d.doctor_id       = r.doctor_id
where r.rn = 1
order by r.total_bill_kes desc;

This gives one row per department: Cardiology's top bill is KES 82,000 (Hellen Jepchirchir, Dr. Brian Mwangi), Pediatrics' is 42,000 (David Mutua, Dr. Faith Chebet), General Medicine's is 36,000 (Francis Njuguna, Dr. Sarah Wanjiku) and Orthopedics' is 33,000 (Ruth Nekesa, Dr. Peter Otieno).

Quick reference

Conclusion

Window functions let you analyse rows in context without losing detail; joins connect the dimension tables to the visits fact table; CTEs make multi-step logic readable; and subqueries give you precise, flexible filters. On the hospital dataset these tools answered real questions: which patients stayed longest, which departments carry the revenue (Cardiology, with 43.1%), which returning patients' bills rose or fell, and who has never been admitted. The best way to build the skill is to write each query yourself, change a condition and watch how the result changes. The goal is not to memorise syntax but to recognise the shape of a question and pick the right tool.