Exploring Hospital Data: Window Functions, Joins, CTEs and Subqueries

Introduction

While working on my hospital dataset, I realised that SQL is not just about retrieving data. It can also help me compare hospital visits, bring information from different tables together and answer questions that may support reporting and decision making.

In this article, I cover four SQL concepts I have been practising: window functions, joins, common table expressions (CTEs) and subqueries. I use examples around hospital visits, departments, patients and bills.

1. Window functions

Window functions calculate something across a set of related rows while keeping each row in the result. Unlike group by, they do not usually collapse the rows into one summary row.

over(), partition by, order by and window frames

over() defines the rows a function works with. partition by separates rows into groups, while order by sets the order for functions such as ranking and comparing previous rows. A window frame specifies which rows are included in a calculation, for example, all rows from the start up to the current row.

select visit_id, total_bill,
       avg(total_bill) over (partition by department_id) as department_avg
from visits;

This shows each visit alongside the average bill for its department. To calculate a running total, I can define a frame:

select visit_id, visit_date, total_bill,
       sum(total_bill) over (order by visit_date, visit_id rows between unbounded preceding and current row) as running_total
from visits;

This adds each bill to the bills recorded before it. I included visit_id to help keep the order consistent when visits share a date.

Ranking functions

• row_number() assigns a different sequential number to each row.
• rank() gives tied values the same rank and skips the next rank.
• dense_rank() gives tied values the same rank without skipping numbers.
• ntile(4) divides the ordered rows into four roughly equal groups.

select visit_id, total_bill,
       row_number() over (order by total_bill desc, visit_id) as row_num,
       rank() over (order by total_bill desc) as bill_rank,
       dense_rank() over (order by total_bill desc) as dense_bill_rank
from visits;

I could use this to compare high-billed visits. If two visits have the same bill, rank() and dense_rank() treat the tie equally, while row_number() still gives each visit a separate number.

select visit_id, total_bill,
       ntile(4) over (order by total_bill) as bill_group
from visits;

This puts visits into four groups based on bill amount. These are relative groups in the dataset, not labels for whether a bill is medically appropriate.

Navigation functions

lag() looks at a previous row, while lead() looks at a later row. first_value() returns the first value in the window frame and last_value() returns the last value in that frame.

select patient_id, visit_date, total_bill,
       lag(total_bill) over (partition by patient_id order by visit_date, visit_id) as previous_bill,
       lead(total_bill) over (partition by patient_id order by visit_date, visit_id) as next_bill
from visits;

This compares each patient's bill with their previous and next recorded visits. The first previous bill and last next bill will normally be null.

Aggregate window functions

sum(), avg(), count(), min(), and max() can also work as window functions. They calculate a total, average, count, minimum or maximum without removing individual visit rows.

select visit_id, department_id, total_bill,
       sum(total_bill) over (partition by department_id) as department_total,
       avg(total_bill) over (partition by department_id) as department_avg,
       count(*) over (partition by department_id) as visit_count
from visits;

This gives me the department total, average bill and number of visits alongside each visit. I can use min(total_bill) over (...) and max(total_bill) over (...) in the same way to show the lowest and highest bills in each department.

I would use group by if I only needed one row per department. I would use window functions if I wanted the department summary and the individual visits together.

2. Joins in SQL

Joins combine information stored in different tables. For example, visits may contain a department key while departments contains the department name.

inner join

select v.visit_id, v.total_bill, d.department_name
from visits v
inner join departments d on v.department_id = d.department_id;

This returns visits that have a matching department record. I would use it when I only need records with matching information.

left join

select v.visit_id, v.total_bill, d.department_name
from visits v
left join departments d on v.department_id = d.department_id;

This keeps every visit, even if its department is missing. I can use it to find unmatched records by filtering for where d.department_id is null.

right join

A right join keeps every row from the table on the right, even when there is no matching visit.

select v.visit_id, d.department_name
from visits v
right join departments d on v.department_id = d.department_id;

This can show departments that have no recorded visits. I can rewrite it as a left join by swapping the table order.

full outer join

A full outer join keeps matched rows and unmatched rows from both tables. It can help show visits against the department list. Not all databases support it directly, so it is important to check the syntax depending on the database engine.

select v.visit_id, d.department_name
from visits v
full outer join departments d on v.department_id = d.department_id;

cross join

A cross join returns every possible combination of rows from two tables. For example, it can help create a department-by-gender grid for a report. However, the result can become very large.

select d.department_name, p.patient_gender
from departments d
cross join (select distinct patient_gender from patients) p;

self join

A self join joins a table to itself. Here, I compare different visits belonging to the same patient.

select a.patient_id, a.visit_id as earlier_visit, b.visit_id as later_visit
from visits a
join visits b on a.patient_id = b.patient_id and a.visit_date < b.visit_date;

This returns pairs of visits where one happened before the other. A patient with many visits can appear in multiple pairs.

In short, inner join returns matches; left join preserves the left table; right join preserves the right table; full outer join preserves both; cross join creates combinations; and self join compares rows within the same table.

3. Common table expressions (ctes)

A CTE is a named result set created with with. I find it useful for splitting a longer query into steps that are easier to read.

basic cte, joins and aggregation

with department_bills as (
    select department_id, sum(total_bill) as total_bills
    from visits
    group by department_id
)
select d.department_name, db.total_bills
from department_bills db
join dim_departments d on db.department_id = d.department_id
order by db.total_bills desc;

The CTE totals bills by department. The final query adds department names and sorts the results from highest to lowest total.

Multiple ctes

I can use more than one CTE when I need separate calculations.

with visit_counts as (
    select department_id, count(*) as visits_count from visits group by department_id
),
department_bills as (
    select department_id, sum(total_bill) as total_bills from visits group by department_id
)
select d.department_name, vc.visits_count, db.total_bills
from departments d
left join visit_counts vc on d.department_id = vc.department_id
left join department_bills db on d.department_id = db.department_id;

This combines visit counts and total bills for each department. A left join keeps departments even if they do not have visits.

Recursive ctes

A recursive CTE refers back to itself usually to explore a hierarchy. For example, it could list departments under larger hospital divisions if the database contained a parent-department key. My current report does not show that such a hierarchy exists, so I would only use this pattern if those columns were available. The syntax also varies between database systems.

CTEs versus subqueries

Both CTEs and subqueries can organise or filter data. I prefer a CTE when a query has several steps because it gives each step a name. A subquery can be shorter for a simple calculation. Neither is automatically faster as performance depends on the database engine and query plan.

4. SQL subqueries

A subquery is a query inside another query. It can return a single value, a list of values or a table-like result.

Scalar and non-correlated subqueries

A scalar subquery returns one value. This example compares each visit's bill with the overall average.

select visit_id, total_bill,
       (select avg(total_bill) from visits) as overall_average
from visits;

A non-correlated subquery can run independently of the outer query:

select visit_id, total_bill
from visits
where total_bill > (select avg(total_bill) from visits);

This returns visits with bills above the overall average.

Correlated subqueries

A correlated subquery refers to a column from the outer query. Here, each bill is compared with the average for its department.

select v.visit_id, v.department_id, v.total_bill
from visits v
where v.total_bill > (select avg(v2.total_bill) from visits v2 where v2.department_id = v.department_id);

I would use this when comparing visits within their own departments rather than against one hospital-wide average.

Nested subqueries and in

A nested subquery is a query inside another subquery. in checks whether a value appears in a list returned by a query.

select patient_id, county
from patients
where patient_id in (select patient_id from visits);

This returns patients whose IDs appear in the visits table.

exists

exists checks whether a subquery returns at least one matching row.

select p.patient_id, p.county
from patients p
where exists (select 1 from visits v where v.patient_id = p.patient_id);

This returns patients with at least one recorded visit. not exists can be used to find patients without a matching visit. I find exists useful when I only need to check whether a related record exists.

any and all

any means a comparison must be true for at least one value returned by a subquery. all means it must be true for every value returned.

select visit_id, total_bill
from visits
where total_bill > any (select total_bill from visits where department_id = 2);

This returns visits whose bill is greater than at least one bill in department 2.

select visit_id, total_bill
from visits
where total_bill > all (select total_bill from visits where department_id = 2);

This returns visits whose bill is greater than every bill in department 2. I used 2 as an example department ID; I would replace it with a real ID from my data. any and all support and null behaviour can vary, so I would check the database documentation and handle missing bills carefully.

Subqueries in select, from and where

• select: a scalar subquery can add a calculated value, such as the overall average bill, to each row.
• from: a subquery can create a derived table, such as totals grouped by department, which can then be joined to departments.
• where: a subquery can filter visits based on a threshold or whether a related record exists.

Conclusion

These concepts help me ask more useful questions of a dataset. Window functions let me compare visits while keeping the details. Joins bring related tables together. CTEs break complex queries into manageable steps and subqueries let one query use the result of another.

Before trusting a result, I would confirm the column names and table relationships, check for duplicate rows caused by joins, decide how to handle missing values and compare key totals with my original report. A query can run successfully and still give a misleading result if I do not understand the data behind it.