Functions, Joins and Common Table Expressions Through Hospital Data
Introduction
Working with a relational database involves more than retrieving records from individual tables. In practice, analysts need to combine information from different sources, compare records within groups, identify patterns over time and build queries that remain understandable as the analysis becomes more complex. SQL joins, window functions and Common Table Expressions (CTEs) provide different ways of solving these problems.
The hospital database used in this article models patients, doctors, departments, diagnoses, procedures, wards and hospital visits. The visits table is particularly important because it connects patient activity to clinical and administrative information. Each visit records details such as the visit date, attending doctor, department, diagnosis, procedure, admission type, length of stay, insurance provider, total bill and patient satisfaction score.
This structure makes it possible to move from simple questions, such as how much a patient was billed, to more meaningful analytical questions. Which doctor generates the highest billing? How does a patient's current bill compare with their previous visit? Which departments contribute the most revenue? How does a visit compare with other visits within the same department?
Answering these questions requires an understanding of how SQL processes relationships, groups and analytical calculations.
The examples below use PostgreSQL and the tables defined in the evenig schema.
Understanding SQL joins through the relationships between hospital tables
A relational database avoids storing all information in one enormous table. Instead, information is separated into related tables, with primary and foreign keys establishing the relationships between them. This reduces duplication and makes the data easier to maintain, but it also means that meaningful analysis often requires combining multiple tables.
Consider the visits table. A visit contains a patient_id, but this identifier does not tell us the patient's name, county or age. Those attributes reside in the patients table. Similarly, the visit contains a doctor_id, while the doctor's name and specialty are stored in doctors.
The join operation allows these records to be brought together through their relationships.
SELECT
v.visit_id,
v.visit_date,
p.patient_name,
p.county,
d.doctor_name,
v.admission_type,
v.total_bill_kes
FROM evenig.visits AS v
INNER JOIN evenig.patients AS p
ON v.patient_id = p.patient_id
INNER JOIN evenig.doctors AS d
ON v.doctor_id = d.doctor_id
ORDER BY v.visit_date, v.visit_id;
The query connects three tables to produce a single view of hospital activity. The visits table acts as the starting point, while patients and doctors provide descriptive information.
The ON conditions are what make these relationships meaningful. Matching v.patient_id to p.patient_id ensures that a visit is associated with the correct patient. Matching v.doctor_id to d.doctor_id then identifies the doctor responsible for that visit.
An important detail is that a patient can appear more than once in the output. Amina Otieno, for example, appears in visits 1001 and 1011. This is not a duplication error. It represents two separate visits by the same patient. The unit of analysis remains the visit, not the patient.
This distinction becomes critical when calculating totals. Counting rows in the joined result measures visits, whereas counting distinct patient identifiers measures unique patients.
How different join strategies change the result
An INNER JOIN returns records for which the join condition matches on both sides. In this dataset, the foreign key constraints on visits.patient_id and visits.doctor_id ensure that each valid visit references an existing patient and doctor. Consequently, joining those tables through their keys should preserve the visit records.
A LEFT JOIN, however, is useful when the analysis must retain all records from a chosen starting table, even if related information is missing.
For example, a hospital administrator might want to identify doctors who have not attended any recorded visits.
SELECT
d.doctor_id,
d.doctor_name,
COUNT(v.visit_id) AS total_visits
FROM evenig.doctors AS d
LEFT JOIN evenig.visits AS v
ON d.doctor_id = v.doctor_id
GROUP BY
d.doctor_id,
d.doctor_name
ORDER BY total_visits DESC;
Starting from doctors ensures that every doctor is represented, including a doctor with no matching visit. For such a doctor, columns from visits would contain NULL.
The choice of COUNT(v.visit_id) is deliberate. Since visit_id is non-null for actual visits, the expression counts matched visits but ignores the null placeholder produced by the left join. Using COUNT(*) instead would count the preserved doctor row even when no visit matched, incorrectly producing one visit for that doctor.
The same principle applies to other hospital tables. A department can be joined to visits through department_id, a diagnosis through diagnosis_id, and a procedure through procedure_id.
The difference between an inner join and a left join is therefore not merely syntactical. It determines which records remain visible in the result and, consequently, which entities are included in the analysis.
Joining multiple tables for a hospital performance report
A more realistic analytical query combines several relationships to create a report that clinicians and administrators can interpret.
SELECT
v.visit_id,
v.visit_date,
p.patient_name,
d.doctor_name,
dep.department_name,
dg.diagnosis_name,
pr.procedure_name,
v.admission_type,
v.length_of_stay_days,
v.total_bill_kes,
v.patient_satisfaction_score
FROM evenig.visits AS v
JOIN evenig.patients AS p
ON v.patient_id = p.patient_id
JOIN evenig.doctors AS d
ON v.doctor_id = d.doctor_id
JOIN evenig.department AS dep
ON v.department_id = dep.department_id
LEFT JOIN evenig.diagnosis AS dg
ON v.diagnosis_id = dg.diagnosis_id
LEFT JOIN evenig.procedures AS pr
ON v.procedure_id = pr.procedure_id
ORDER BY v.visit_date, v.visit_id;
The result is a visit-level dataset that can support reporting, dashboard development and further analysis. The inner joins are appropriate for the required patient, doctor and department relationships. The left joins allow a visit to remain visible if diagnosis or procedure information is absent.
The distinction matters because a missing diagnosis should not automatically cause a valid financial record to disappear from a hospital billing report.
There is another subtle issue with joins: they can change the number of rows. Joining a visit to a table with several matching records can multiply that visit in the result. This commonly occurs when analysts join a transaction table to a one-to-many relationship without considering its cardinality.
If a visit has two associated procedures in a separate procedure-details table, for example, the visit may appear twice. Summing the original visit bill after that join would overstate revenue. Analysts must therefore understand the relationship between tables and verify the grain of the result before aggregating financial measures.
SQL window functions: analysing records without losing their detail
Aggregation is useful when a report requires one result per group. A query using GROUP BY doctor_id, for instance, can calculate total revenue for each doctor. However, the individual visit records disappear from the final output because they have been reduced to grouped results.
Window functions solve a different problem. They perform calculations across related rows while retaining the individual rows in the result.
This makes them particularly useful for rankings, running totals, comparisons with previous records and identifying the relative position of each visit within a group.
Ranking doctors' visits by billing value
Suppose the hospital wants to identify the highest-billed visits handled by each doctor. A simple descending sort would show the largest bills across the entire hospital, but it would not establish each doctor's position relative to their own visits.
SELECT
v.visit_id,
d.doctor_name,
v.total_bill_kes,
RANK() OVER (
PARTITION BY v.doctor_id
ORDER BY v.total_bill_kes DESC
) AS billing_rank
FROM evenig.visits AS v
JOIN evenig.doctors AS d
ON v.doctor_id = d.doctor_id
ORDER BY
d.doctor_name,
billing_rank;
The window definition has two important parts. PARTITION BY v.doctor_id separates the visits into independent groups, one for each doctor. ORDER BY v.total_bill_kes DESC ranks the visits within each group from the largest bill to the smallest.
The rank resets for every doctor. A bill of KSh 42,000 could rank highly among one doctor's visits without necessarily being one of the highest bills across the entire hospital.
The choice of ranking function also matters. RANK() assigns equal ranks to tied values and leaves gaps in subsequent ranks. DENSE_RANK() also assigns equal ranks to ties but does not leave gaps. ROW_NUMBER() assigns a unique sequential number to every row, even when bills are equal. If a stable ordering is needed, a secondary column such as visit_id can be added to the window's ORDER BY.
These functions answer different questions. Ranking identifies relative position, while row numbering identifies a particular sequence of records.
Comparing each visit with the previous visit
Window functions become even more valuable when the order of records matters. Suppose a hospital analyst wants to compare each patient's current bill with their previous recorded bill.
SELECT
v.patient_id,
p.patient_name,
v.visit_id,
v.visit_date,
v.total_bill_kes,
LAG(v.total_bill_kes) OVER (
PARTITION BY v.patient_id
ORDER BY v.visit_date, v.visit_id
) AS previous_bill,
v.total_bill_kes
- LAG(v.total_bill_kes) OVER (
PARTITION BY v.patient_id
ORDER BY v.visit_date, v.visit_id
) AS bill_difference
FROM evenig.visits AS v
JOIN evenig.patients AS p
ON v.patient_id = p.patient_id
ORDER BY
p.patient_name,
v.visit_date,
v.visit_id;
LAG() accesses a value from a preceding row in the same window partition. Here, the partition is the patient, so a patient's bill is compared with that patient's previous visit rather than with another patient's bill.
For Amina Otieno, visit 1001 has a bill of KSh 3,500 and visit 1011 has a bill of KSh 4,200. The second visit therefore has a bill difference of KSh 700.
The first visit for each patient has no preceding visit within the available data, so previous_bill and bill_difference return NULL. This is a meaningful result, not an error.
The ordering also needs to be deterministic. Ordering by date alone may be insufficient if several visits occur on the same date. Including visit_id provides a tie-breaker for this dataset. In a production system, the analyst should use the actual event timestamp or another reliable sequence field where necessary.
The comparison reveals a change in the recorded bill, but it does not establish why the bill changed. A higher bill could result from a different diagnosis, procedure, admission type or treatment plan. SQL produces the evidence; further analysis is required to explain the cause.
Calculating cumulative hospital revenue
A running total provides another example of a window function preserving the detail of the underlying records.
SELECT
visit_id,
visit_date,
total_bill_kes,
SUM(total_bill_kes) OVER (
ORDER BY visit_date, visit_id
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
) AS cumulative_billing
FROM evenig.visits
ORDER BY visit_date, visit_id;
The cumulative total starts with the first visit and increases as each subsequent visit is processed. It allows an analyst to examine how recorded billing accumulates over the observation period.
The explicit ROWS frame is significant. It specifies that the calculation should include every row from the beginning of the ordered result through the current row. Without an explicit frame, some window aggregates use a default frame that can treat rows with equal ordering values as peers. Stating the intended frame makes the calculation easier to understand and reduces ambiguity.
The result is not a daily revenue summary. It is a visit-level sequence of cumulative billing. If the objective is to report one total per day, a grouped aggregation would be more appropriate.
Window functions are most effective when the question requires context across multiple records but the output must retain the individual observations.
Common Table Expressions: building complex queries in understandable stages
As analytical questions become more demanding, a single SQL statement can become difficult to read. The problem is particularly noticeable when a calculation must first be performed at one level, such as per doctor, before another calculation is applied at a different level, such as ranking doctors.
A Common Table Expression, or CTE, addresses this by allowing a query to define a named intermediate result using the WITH clause. The main query can then reference that result as though it were a temporary relation available within the statement.
Consider the question of which doctors generate the highest total billing.
WITH doctor_revenue AS (
SELECT
d.doctor_id,
d.doctor_name,
SUM(v.total_bill_kes) AS total_revenue,
COUNT(v.visit_id) AS total_visits
FROM evenig.doctors AS d
JOIN evenig.visits AS v
ON d.doctor_id = v.doctor_id
GROUP BY
d.doctor_id,
d.doctor_name
)
SELECT
doctor_name,
total_revenue,
total_visits,
RANK() OVER (
ORDER BY total_revenue DESC
) AS revenue_rank
FROM doctor_revenue
ORDER BY revenue_rank, doctor_name;
The CTE first establishes a doctor-level result. Each doctor has one row containing total billing and visit count. The outer query then ranks those doctor-level totals.
The two stages operate at different grains. The inner query reduces visit-level data to one record per doctor, while the outer query compares those resulting doctor records. This separation is essential: ranking individual visits would answer a different question from ranking doctors by their total revenue.
The CTE makes the analytical logic visible. A reader can inspect the revenue calculation independently from the ranking calculation, and changes to one stage are easier to reason about.
A CTE is not, however, automatically a permanent table or a guaranteed performance optimisation. It exists for the duration of the SQL statement. PostgreSQL may inline a CTE into the surrounding query or materialise its result depending on the query, the PostgreSQL version and the planner's decisions. Performance should be assessed using the actual execution plan rather than assumed from the presence of WITH.
Chaining CTEs for a more complete analysis
CTEs can also be chained so that one stage builds on another. For example, an analyst may calculate department revenue first and then compare each department with the hospital-wide average.
WITH department_revenue AS (
SELECT
dep.department_id,
dep.department_name,
SUM(v.total_bill_kes) AS total_revenue
FROM evenig.department AS dep
JOIN evenig.visits AS v
ON dep.department_id = v.department_id
GROUP BY
dep.department_id,
dep.department_name
),
hospital_average AS (
SELECT
AVG(total_revenue) AS average_department_revenue
FROM department_revenue
)
SELECT
dr.department_name,
dr.total_revenue,
ha.average_department_revenue,
dr.total_revenue
- ha.average_department_revenue AS difference_from_average
FROM department_revenue AS dr
CROSS JOIN hospital_average AS ha
ORDER BY dr.total_revenue DESC;
The first CTE calculates total revenue per department. The second derives the average of those department totals. The final query compares each department with that average.
The CROSS JOIN is appropriate because hospital_average returns a single row, which is attached to each department record. The comparison is between department totals, not individual visit bills.
That distinction illustrates why analytical stages should be designed deliberately. The average of department revenues is not the same as the average visit bill. One gives each department equal weight; the other gives each visit equal weight.
CTEs versus subqueries: two approaches to structuring the same logic
A subquery is a query nested inside another SQL statement. It can appear in a FROM clause, a WHERE condition, a SELECT expression or other permitted positions. CTEs and subqueries can often solve the same analytical problem, but their structure affects how easily a query can be understood and maintained.
Take the task of identifying departments whose total billing exceeds the average department revenue.
Using a CTE, the calculation can be separated into named stages:
WITH department_revenue AS (
SELECT
department_id,
SUM(total_bill_kes) AS total_revenue
FROM evenig.visits
GROUP BY department_id
)
SELECT
dep.department_name,
dr.total_revenue
FROM department_revenue AS dr
JOIN evenig.department AS dep
ON dep.department_id = dr.department_id
WHERE dr.total_revenue > (
SELECT AVG(total_revenue)
FROM department_revenue
)
ORDER BY dr.total_revenue DESC;
The department_revenue CTE defines the department-level totals once. Both the outer query and the scalar subquery refer to those totals, making the intended level of comparison explicit.
The same logic can be expressed with nested subqueries:
SELECT
dep.department_name,
dr.total_revenue
FROM evenig.department AS dep
JOIN (
SELECT
department_id,
SUM(total_bill_kes) AS total_revenue
FROM evenig.visits
GROUP BY department_id
) AS dr
ON dr.department_id = dep.department_id
WHERE dr.total_revenue > (
SELECT AVG(x.total_revenue)
FROM (
SELECT
department_id,
SUM(total_bill_kes) AS total_revenue
FROM evenig.visits
GROUP BY department_id
) AS x
)
ORDER BY dr.total_revenue DESC;
Both statements express the same fundamental calculation: aggregate visits to the department level, calculate the average of those totals, and retain departments above that average. The subquery version repeats the aggregation logic, whereas the CTE version gives that logic a reusable name.
This difference becomes more important as a query grows. Repeated subqueries can make it difficult to verify whether two calculations use identical filters and grouping rules. A CTE can make each analytical stage easier to inspect and modify.
Nevertheless, it would be incorrect to conclude that CTEs are always faster or that subqueries are inherently inferior. PostgreSQL's query planner can optimise many equivalent expressions similarly. A CTE may be materialised in some circumstances, potentially affecting performance, while a subquery may be flattened into a larger query plan.
The more useful distinction is one of query structure and semantics. CTEs are especially valuable when the query has several logical stages, an intermediate result needs to be referenced more than once, or the analysis benefits from clear named steps. Subqueries are often concise when the nested calculation is small and used only once.
Neither technique should be selected purely for style without considering the required result, the complexity of the logic and, where performance matters, the execution plan.
Bringing joins, window functions and CTEs together
The real analytical value of these concepts emerges when they are used together. Consider a hospital report that ranks departments by total billing while also calculating each department's percentage contribution to all recorded billing.
WITH department_summary AS (
SELECT
dep.department_id,
dep.department_name,
COUNT(v.visit_id) AS total_visits,
SUM(v.total_bill_kes) AS total_revenue,
AVG(v.patient_satisfaction_score)
AS average_satisfaction
FROM evenig.department AS dep
JOIN evenig.visits AS v
ON dep.department_id = v.department_id
GROUP BY
dep.department_id,
dep.department_name
)
SELECT
department_name,
total_visits,
total_revenue,
ROUND(average_satisfaction, 2) AS average_satisfaction,
RANK() OVER (
ORDER BY total_revenue DESC
) AS revenue_rank,
ROUND(
100.0 * total_revenue
/ NULLIF(SUM(total_revenue) OVER (), 0),
2
) AS revenue_percentage
FROM department_summary
ORDER BY revenue_rank, department_name;
The CTE combines the department and visit tables and aggregates their records into a department-level summary. The window functions then rank the departments and calculate each department's percentage of the combined revenue total.
SUM(total_revenue) OVER () calculates the total of the department revenue values across the entire result without collapsing the department rows. NULLIF(..., 0) prevents division by zero if the total is zero, and multiplying by 100.0 ensures a decimal percentage calculation.
The result supports two distinct interpretations. The ranking identifies the relative order of departments by billing, while the percentage indicates how much each department contributes to total recorded billing. A department can rank first without necessarily accounting for a majority of the hospital's billing.
The average satisfaction score also requires careful interpretation. It is the average of the scores recorded for visits in each department, not necessarily an average of all patients' lifetime satisfaction. If the same patient has multiple visits, that patient can contribute multiple observations.
This is why understanding the grain of the data is just as important as understanding SQL syntax. Every metric has a unit of analysis, and changing the joins, grouping level or filtering conditions can change what that metric represents.
Top comments (0)