Window Functions, Joins, CTEs and Subqueries in SQL: A Hospital Dataset Walkthrough.

Every hospital runs on data: patients, bills, wards, diagnoses and doctors. Answering real questions from that data, like "Which doctor brings the most revenue?" or "Which patients came back for a second visit?", takes more than just the basic SELECT. In this article I use a small hospital database to explain four SQL topics

1. Window Functions

2. Joins

3. Common Table Expressions (CTEs)

4. Subqueries

The Dataset

The database lives in Postgres and has the following core tables:
doctors (doctor_id, doctor_name, doctor_gender, doctor_specialty, doctor_experience_years, email, city)
patients (patient_id, patient_name, patient_gender, patient_age, county, sub_county)
departments (department_id, department_name, manager_id)
wards (ward_id, ward_name)
diagnosis (diagnosis_id, diagnosis_name, diagnosis_category)
procedures (procedure_id, procedure_name, procedure_category)
fact_visits (visit_id, visit_date, patient_id, doctor_id, department_id, ward_id, diagnosis_id, procedure_id, admission_type, length_of_stay_days, triage_level, insurance_provider, total_bill_kes, payment_method, follow_up_required, patient_satisfaction_score)

fact_visits is the central table. It holds 30 visits from 1 to 15 June 2026, and the other tables describe who, where and why. The total billed across all visits is KES 488,000.

1. Window Functions

A window function performs a calculation across a set of rows related to the current row, without collapsing rows like GROUP BY does. Every window function needs a OVER() clause.

How OVER(), PARTITION BY, ORDER BY and Frames Work

• OVER() defines the "window" the function operates on.
• PARTITION BY splits rows into groups, like GROUP BY but each row keeps its identity.
• ORDER BY inside OVER() defines the order used for ranking or running calculations.
• A window frame (e.g., ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) limits which rows in the partition the function sees.

Ranking: ROW_NUMBER(), RANK(), DENSE_RANK(), NTILE()

• ROW_NUMBER() gives a unique number to every row, even when values tie.
• RANK() gives ties the same rank and then skips numbers (1, 2, 2, 4).
• DENSE_RANK() gives ties the same rank without skipping (1, 2, 2, 3).
• NTILE(n) splits rows into n roughly equal groups.

SELECT
    visit_id,
    total_bill_kes,
    ROW_NUMBER() OVER (ORDER BY total_bill_kes DESC) AS row_num,
    RANK()       OVER (ORDER BY total_bill_kes DESC) AS rnk,
    DENSE_RANK() OVER (ORDER BY total_bill_kes DESC) AS dense_rnk,
    NTILE(4)     OVER (ORDER BY total_bill_kes DESC) AS bill_quartile
FROM fact_visits;

This ranks every visit from the highest bill to the lowest. Visit 1017 (KES 82,000) comes first. Visits 1008 and 1020 both billed KES 6,500, so they show the difference between the functions: ROW_NUMBER gives them 15 and 16, RANK gives both 15, and the next bill (KES 5,500) gets 17, while DENSE_RANK gives both 15, and the next gets 16. NTILE(4) puts the 8 biggest bills in quartile 1, which helps identify the costliest visits.

WITH ranked AS (
    SELECT visit_id, department_id, total_bill_kes,
           ROW_NUMBER() OVER (PARTITION BY department_id ORDER BY total_bill_kes DESC) AS rn
    FROM fact_visits
)
SELECT * FROM ranked WHERE rn = 1;

Use case: adding PARTITION BY department_id to ROW_NUMBER() and filtering for rn = 1 in a CTE gives the highest billed visit per department. This is a common interview question.

Value Functions: LAG, LEAD, FIRST_VALUE, LAST_VALUE

LAG() reads a value from the previous row and LEAD() from the next row. FIRST_VALUE() and LAST_VALUE() return the first or last value in the frame.

SELECT
    patient_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_visit
FROM fact_visits
ORDER BY patient_id, visit_date;

For each patient, this shows the previous and next visit dates and the gap in days. A patient's first visit has NULL for previous_visit. This is useful for tracking follow-up compliance.

SELECT
    patient_id,
    visit_date,
    total_bill_kes,
    FIRST_VALUE(total_bill_kes) OVER (
        PARTITION BY patient_id ORDER BY visit_date
    ) AS first_bill,
    LAST_VALUE(total_bill_kes) OVER (
        PARTITION BY patient_id ORDER BY visit_date
        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
    ) AS latest_bill
FROM fact_visits;

This compares each patient's first bill with their latest one. Common mistake: when a window has ORDER BY, the default frame ends at the current row, so LAST_VALUE() would just return the current row's value. Extending the frame to UNBOUNDED FOLLOWING fixes that.

Aggregate Window Functions: SUM(), AVG(), COUNT(), MIN(), MAX()

Used with OVER(), aggregates compute running totals and comparisons while keeping every row.

SELECT
    d.doctor_name,
    f.visit_id,
    f.total_bill_kes,
    SUM(f.total_bill_kes) OVER (PARTITION BY f.doctor_id) AS doctor_total,
    ROUND(AVG(f.total_bill_kes) OVER (PARTITION BY f.doctor_id), 2) AS doctor_avg,
    COUNT(*) OVER (PARTITION BY f.doctor_id) AS doctor_visits,
    MIN(f.total_bill_kes) OVER (PARTITION BY f.doctor_id) AS doctor_min,
    MAX(f.total_bill_kes) OVER (PARTITION BY f.doctor_id) AS doctor_max,
    SUM(f.total_bill_kes) OVER (
        PARTITION BY f.doctor_id ORDER BY f.visit_date, f.visit_id
    ) AS running_total
FROM fact_visits f
JOIN doctors d ON d.doctor_id = f.doctor_id;

Every visit row keeps its own details and also shows its doctor's totals. The running_total column grows visit by visit because ORDER BY inside OVER() creates a cumulative window.

Window Frames

A frame decides exactly which rows are included. For example, a moving average of the current and two previous bills:

AVG(total_bill_kes) OVER (ORDER BY visit_date, visit_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)

Frame boundaries include UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING and UNBOUNDED FOLLOWING.

2. SQL Joins

Joins combine rows from two or more tables based on a related column, like fact_visits.doctor_id = doctors.doctor_id.

INNER JOIN

Returns only rows where the match exists in both tables.

SELECT f.visit_id, p.patient_name, d.doctor_name, f.total_bill_kes
FROM fact_visits f
INNER JOIN patients p ON p.patient_id = f.patient_id
INNER JOIN doctors  d ON d.doctor_id  = f.doctor_id;

This returns 30 rows, one per visit, with the patient and doctor names attached.

LEFT JOIN

Returns all rows from the left table plus matching rows from the right. Unmatched rows get NULL on the right side.

SELECT p.patient_name, COUNT(f.visit_id) AS visits
FROM patients p
LEFT JOIN fact_visits f ON f.patient_id = p.patient_id
GROUP BY p.patient_name
ORDER BY visits DESC;

This returns all 25 patients. Five patients have 2 visits and the rest have 1.

Use case: finding patients who registered but never visited. Adding WHERE f.visit_id IS NULL would check for patients without visits, which in my case returns no rows since every patient has a visit.

RIGHT JOIN

Keeps every row from the right table. It is a LEFT JOIN with the tables swapped.

SELECT d.doctor_name, f.visit_id
FROM fact_visits f
RIGHT JOIN doctors d ON d.doctor_id = f.doctor_id;

This lists every doctor with their visits, 30 rows here. A doctor with no visits would appear once with NULL in visit_id.

FULL OUTER JOIN

Keeps all rows from both tables, filling NULLs where no match exists; it is useful for data quality checks.

SELECT d.doctor_id, f.visit_id
FROM doctors d
FULL OUTER JOIN fact_visits f ON f.doctor_id = d.doctor_id
WHERE d.doctor_id IS NULL OR f.visit_id IS NULL;

This finds doctors with no visits and visits with no doctor. Here it returns no rows, which confirms the data is consistent.

CROSS JOIN

Produces the Cartesian product: every row from the first table paired with every row from the second. No ON clause.

SELECT d.doctor_name, dep.department_name
FROM doctors d
CROSS JOIN departments dep;

4 doctors x 4 departments = 16 rows. Use case: generating all possible combinations, like a roster of every doctor against every department to plan rotations.

SELF JOIN

A table joined to itself, useful when rows in one table reference other rows in the same table. Here it finds pairs of patients from the same county.

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 a.patient_id < b.patient_id condition stops a patient from being paired with themselves and avoids duplicate pairs. This results in patients from the same county being listed together.

Quick Comparison

Join	Rows kept	Best for
INNER	Matches in both tables	Standard reports
LEFT	All left table rows	Finding missing matches
RIGHT	All right table rows	Same as LEFT, flipped
FULL OUTER	All rows from both	Reconciliation
CROSS	All combinations	Rosters, testing scenarios
SELF	Table joined to itself	Hierarchies and comparisons

3. Common Table Expressions (CTEs)

A CTE is a named temporary result set defined with WITH, readable only for that query. It makes complex logic clearer than nested subqueries.

Basic CTE

WITH doctor_revenue AS (
    SELECT doctor_id, SUM(total_bill_kes) AS revenue
    FROM fact_visits
    GROUP BY doctor_id
)
SELECT d.doctor_name, dr.revenue
FROM doctor_revenue dr
JOIN doctors d ON d.doctor_id = dr.doctor_id
ORDER BY dr.revenue DESC;

The CTE totals revenue per doctor first, then the main query joins it to doctor names. This also shows a CTE working with a join and an aggregation.

WITH high_value_visits AS (
    SELECT *
    FROM fact_visits
    WHERE total_bill_kes > 20000
)
SELECT department_id, COUNT(*) AS expensive_visits
FROM high_value_visits
GROUP BY department_id;

The CTE filters bills above KES 20,000 first, then the main query counts them per department. The counts are department 10:3, 20:2, 30:3 and 40:1.

Multiple CTEs

Separate CTEs with commas. Later ones can use earlier ones.

WITH dept_revenue AS (
    SELECT department_id, SUM(total_bill_kes) AS revenue
    FROM fact_visits
    GROUP BY department_id
),
avg_revenue AS (
    SELECT AVG(revenue) AS avg_rev FROM dept_revenue
)
SELECT d.department_name, dr.revenue
FROM dept_revenue dr
JOIN departments d ON d.department_id = dr.department_id
CROSS JOIN avg_revenue a
WHERE dr.revenue > a.avg_rev;

The second CTE consumes the first, showing how CTEs chain. This query lists departments earning above the average departmental revenue. The average is KES 122,000, so only Cardiology is returned.

Recursive CTE

A recursive CTE references itself using UNION ALL to process hierarchical or sequential data. Here in this example it builds a calendar so days with no visits still appear.

WITH RECURSIVE calendar AS (
    SELECT DATE '2026-06-01' AS day
    UNION ALL
    SELECT day + 1 FROM calendar WHERE day < DATE '2026-06-17'
)
SELECT c.day, COUNT(f.visit_id) AS visits
FROM calendar c
LEFT JOIN fact_visits f ON f.visit_date = c.day
GROUP BY c.day
ORDER BY c.day;

Use case: recursive CTEs shine on organization charts (a doctor supervising other doctors but it does not apply on my dataset) or generating date series, like listing all days of June to find dates with zero visits.

CTEs vs Subqueries

• Readability: CTEs name each step and read top to bottom. Deeply nested subqueries read inside out.

• Reuse: a CTE defined once can be referenced multiple times in the main query. A subquery must be rewritten each time.

• Recursion: only CTEs support WITH RECURSIVE.

• Scope: both exist only for one query. Neither stores data permanently. For reusable storage, use a table or view
  

WITH avg_bill AS (
    SELECT AVG(total_bill_kes) AS avg_kes FROM fact_visits
)
SELECT f.visit_id, f.total_bill_kes
FROM fact_visits f
CROSS JOIN avg_bill a
WHERE f.total_bill_kes > a.avg_kes;

4. SQL Subqueries

A subquery is a query nested inside another query. It can appear in SELECT, FROM or WHERE.

Scalar and Non-Correlated Subqueries

A scalar subquery returns a single value. A non-correlated subquery runs once, independently of the outer query. This one is both:

SELECT visit_id, total_bill_kes
FROM fact_visits
WHERE total_bill_kes > (SELECT AVG(total_bill_kes) FROM fact_visits);

The average bill is about KES 16,267, so this returns 10 visits.

Correlated Subquery

A correlated subquery uses a value from the outer row, so it runs once per row. This finds visits where the bill is above that same doctor's average:

SELECT f.visit_id, f.doctor_id, f.total_bill_kes
FROM fact_visits f
WHERE f.total_bill_kes > (
    SELECT AVG(f2.total_bill_kes)
    FROM fact_visits f2
    WHERE f2.doctor_id = f.doctor_id
);

It compares each bill with the average for its own doctor, giving a fairer view than one hospital-wide average.

Nested Subqueries

A subquery inside a subquery. This finds patients treated in Cardiology:

SELECT patient_name
FROM patients
WHERE patient_id IN (
    SELECT patient_id
    FROM fact_visits
    WHERE department_id = (
        SELECT department_id
        FROM departments
        WHERE department_name = 'Cardiology'
    )
);

The innermost query finds the Cardiology ID, the middle one finds its visits, and the outer one returns the patients.

IN and EXISTS

IN checks whether a value is in a list. EXISTS checks whether the subquery returns any row and stops at the first match, so it can be faster on large tables.

-- IN
SELECT patient_name FROM patients
WHERE patient_id IN (SELECT patient_id FROM fact_visits WHERE admission_type = 'Inpatient');

-- EXISTS
SELECT p.patient_name FROM patients p
WHERE EXISTS (
    SELECT 1 FROM fact_visits f
    WHERE f.patient_id = p.patient_id AND f.admission_type = 'Inpatient'
);

Both return the same 9 inpatients. Prefer NOT EXISTS over NOT IN when the subquery could contain NULL, because NOT IN then returns no rows at all.

ANY and ALL

> ANY means greater than at least one value in the list. > ALL means greater than every value.

-- Bigger than at least one Mobile Money bill
SELECT visit_id, total_bill_kes FROM fact_visits
WHERE total_bill_kes > ANY (SELECT total_bill_kes FROM fact_visits WHERE payment_method = 'Mobile Money');

-- Bigger than every Mobile Money bill
SELECT visit_id, total_bill_kes FROM fact_visits
WHERE total_bill_kes > ALL (SELECT total_bill_kes FROM fact_visits WHERE payment_method = 'Mobile Money');

Mobile Money bills range from KES 1,900 to 24,000. ANY compares against the smallest, so it returns 28 visits. ALL compares against the largest, so it returns only the 7 visits above KES 24,000.

Subqueries in SELECT, FROM and WHERE

-- In SELECT: a calculated value per row
SELECT p.patient_name,
       (SELECT COUNT(*) FROM fact_visits f WHERE f.patient_id = p.patient_id) AS visit_count
FROM patients p;

-- In FROM: a derived table
SELECT department_id, avg_bill
FROM (
    SELECT department_id, AVG(total_bill_kes) AS avg_bill
    FROM fact_visits
    GROUP BY department_id
) dept_avg
WHERE avg_bill > 15000;
--for the WHERE subqueries check the examples above

The first returns 25 rows, one visit count per patient.The second returns departments 10 and 30, with averages af about KES 23,344 and 17,533.

Wrapping Up

• Window functions add ranking, offsets and running aggregates without collapsing rows.

• Joins combine tables; pick INNER for matches, LEFT or RIGHT to keep one side, FULL OUTER to keep both, CROSS for combinations and SELF for intra-table relationships.

• CTEs break complex logic into named, reusable, even recursive steps.

• Subqueries answer inner questions first; scalar for single values, correlated for row-by-row logic, and IN, EXISTS, ANY and ALL for set membership and comparison.

Mastering these four areas turns basic SELECT statements into the analytical queries interviewers and organizations actually need.