SQL Concepts Using Hospital Data

Introduction

Hospitals collect data about patients, visits, doctors, treatments and bills. This data can help answer practical questions: How many times has a patient visited? How does their latest bill compare with the previous one? Which patients returned on another date?

SQL provides several ways to answer these questions. In this article, we explore four important concepts: window functions, joins, common table expressions (CTEs) and subqueries using our hospital dataset.

Each concept is explained in simple language, followed by a hospital scenario, a SQL query and its expected result. The aim is to understand what each query does, why it is useful and when to use it.

About the dataset

The hospital dataset contains 30 visits and 25 patients. Each row in hospital_visits represents a visit. We use columns such as visit_id, patient_id, visit_date and total_bill_kes to answer the questions below. Source dates use day/month/year, and billed amounts are in Kenyan shillings (KES).

The examples use PostgreSQL syntax. Import the CSV into a table named hospital_visits, or use the companion SQL script, which includes the data and the queries. Query outputs were checked using DuckDB and are shown below as result tables.

Before we begin:

• AS gives a result column a readable name.
• WHERE chooses rows.
• ORDER BY sorts rows.
• NULL means no value is available; it does not mean zero.

Billed amounts do not necessarily represent payments received.

1. Window Functions

A window function calculates across related rows while keeping each visit visible. This is useful when we want the individual bill and a comparison beside it.

By comparison, GROUP BY normally produces one summary row per group.

Understanding OVER, PARTITION BY and ORDER BY

These clauses control how a window function works:

• OVER() tells SQL to use a window. Empty parentheses use all rows available to the query.
• PARTITION BY divides those rows into groups, such as patients.
• ORDER BY inside OVER() sets the calculation sequence.
• A window frame chooses which rows in the group to include.

AVG — Calculate the Average Bill

AVG() calculates the average.

Hospital scenario: Compare each of Amina’s bills with her average bill. PARTITION BY patient_id makes a separate calculation for each patient. [1, 3]

SQL query:

SELECT visit_id, total_bill_kes, AVG(total_bill_kes) OVER ( PARTITION BY patient_id ) AS average_bill FROM hospital_visits WHERE patient_id = 1 ORDER BY visit_id; 

Result:

Explanation: Both visits show an average of KES 3,850 because:

(3,500 + 4,200) / 2 = 3,850

Without PARTITION BY, OVER() would average all rows kept by WHERE. In this example, that is still Amina’s two visits.

ROW_NUMBER — Number Each Patient’s Visits

ROW_NUMBER() gives each row a different number.

Hospital scenario: Identify a patient’s first and second recorded visits. The numbering restarts for each patient.

SQL query:

SELECT patient_id, visit_id, ROW_NUMBER() OVER ( PARTITION BY patient_id ORDER BY visit_date, visit_id ) AS visit_number FROM hospital_visits WHERE patient_id IN (1, 2) ORDER BY patient_id, visit_number; 

Result:

Explanation: Patients 1 and 2 each have visit numbers 1 and 2.

Inside the window, visit_date sets the sequence and visit_id breaks a tie if two visits share a date. The final ORDER BY arranges the displayed result.

RANK and DENSE_RANK — Compare Bills

RANK() gives tied bills the same position and skips positions afterwards.

DENSE_RANK() also gives tied bills the same position, but leaves no gaps.

Hospital scenario: Rank bills in Orthopedics where two visits have the same billed amount.

SQL query:

SELECT visit_id, total_bill_kes, RANK() OVER ( ORDER BY total_bill_kes ) AS rank_no, DENSE_RANK() OVER ( ORDER BY total_bill_kes ) AS dense_no FROM hospital_visits WHERE department_id = 30 ORDER BY total_bill_kes, visit_id; 

Result:

Explanation: The two KES 6,500 bills both rank 1.

The KES 7,200 bill receives:

• RANK() = 3, because two rows occupy the first position.
• DENSE_RANK() = 2, because it continues with the next position.

Unlike these functions, ROW_NUMBER() would give the tied visits different numbers.

NTILE — Divide Bills into Groups

NTILE() divides ordered rows into groups of nearly equal size.

Hospital scenario: Divide the Orthopedics bills into two groups for review. NTILE(2) creates two groups.

SQL query:

SELECT visit_id, total_bill_kes, NTILE(2) OVER ( ORDER BY total_bill_kes, visit_id ) AS bill_group FROM hospital_visits WHERE department_id = 30 ORDER BY total_bill_kes, visit_id; 

Result:

Explanation: The three lower bills are in group 1 and the three higher bills are in group 2.

The groups contain equal numbers of visits, not equal billing totals. NTILE() can split tied values between groups.

LAG and LEAD — Compare Nearby Visits

LAG() looks backward to the previous row.

LEAD() looks forward to the next row.

Hospital scenario: Compare Amina’s current bill with the bills from her previous and next recorded visits.

SQL query:

SELECT visit_id, total_bill_kes, LAG(total_bill_kes) OVER ( ORDER BY visit_date, visit_id ) AS previous_bill, LEAD(total_bill_kes) OVER ( ORDER BY visit_date, visit_id ) AS next_bill FROM hospital_visits WHERE patient_id = 1 ORDER BY visit_date, visit_id; 

Result:

Explanation:

• Visit 1001 has next_bill of KES 4,200.
• Visit 1011 has previous_bill of KES 3,500.
• NULL appears where no neighbouring visit exists.

Only patient 1 is selected here. When analysing several patients, add PARTITION BY patient_id to both windows so that each patient is considered separately.

FIRST_VALUE and LAST_VALUE — Compare First and Latest Bills

FIRST_VALUE() reads the first value in the window frame.

LAST_VALUE() reads the last value in the window frame.

Hospital scenario: Display Amina’s earliest and latest recorded bills beside both of her visits.

SQL query:

SELECT visit_id, 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 ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS last_bill FROM hospital_visits WHERE patient_id = 1 ORDER BY visit_id; 

Result:

Explanation: Both rows show:

• First bill: KES 3,500.
• Latest bill: KES 4,200.

UNBOUNDED FOLLOWING extends the frame to the final row. Without it, LAST_VALUE() often returns the current row’s value rather than the latest bill.

SUM — Calculate a Patient’s Total Bill

SUM() adds values.

Hospital scenario: Display Amina’s total billed amount beside each visit while keeping both individual visits visible.

SQL query:

SELECT visit_id, total_bill_kes, SUM(total_bill_kes) OVER () AS total_bill FROM hospital_visits WHERE patient_id = 1 ORDER BY visit_id; 

Result:

Explanation: Both rows show KES 7,700 because:

3,500 + 4,200 = 7,700

OVER() uses both rows selected by WHERE.

COUNT — Count a Patient’s Visits

COUNT(*) counts rows.

Hospital scenario: Show how many recorded visits Amina has beside each visit.

SQL query:

SELECT visit_id, COUNT(*) OVER () AS number_of_visits FROM hospital_visits WHERE patient_id = 1 ORDER BY visit_id; 

Result:

Explanation: The count is 2 beside each of Amina’s visits.

COUNT(*) counts rows, while COUNT(column) counts only non-NULL values in that column.

MIN and MAX — Find the Lowest and Highest Bills

MIN() returns the smallest value.

MAX() returns the largest value.

Hospital scenario: Identify the lowest and highest bills recorded for Amina.

SQL query:

SELECT visit_id, MIN(total_bill_kes) OVER () AS lowest_bill, MAX(total_bill_kes) OVER () AS highest_bill FROM hospital_visits WHERE patient_id = 1 ORDER BY visit_id; 

Result:

Explanation: Both rows show a lowest bill of KES 3,500 and a highest bill of KES 4,200.

SUM(), AVG(), MIN() and MAX() ignore NULL inputs.

Window Frames — Calculate a Running Total

A window frame tells SQL which rows to include for the current calculation.

Hospital scenario: Follow how Amina’s total billed amount grows with each visit. [1, 4]

SQL query:

SELECT visit_id, total_bill_kes, SUM(total_bill_kes) OVER ( ORDER BY visit_date, visit_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_total FROM hospital_visits WHERE patient_id = 1 ORDER BY visit_date, visit_id; 

Result:

Explanation: The total grows from KES 3,500 to KES 7,700.

The frame means:

• ROWS: use row positions.
• UNBOUNDED PRECEDING: start at the first row.
• CURRENT ROW: stop at the current row.

Unlike the earlier SUM() example, the running total changes as SQL moves forward.

Other useful frame options include:

• ROWS BETWEEN 1 PRECEDING AND CURRENT ROW: include the previous and current rows, useful for a moving average.
• UNBOUNDED FOLLOWING: extend to the final row.
• RANGE: treat equal ORDER BY values as peers.
• GROUPS: count peer groups.

With ORDER BY, the default frame runs from the beginning through the current row’s peers. Without ORDER BY, it covers the whole partition. [1, 4]

2. SQL Joins

A join combines related rows.

We will compare patients seen on 1 June with those seen on 11 June:

• june_1 contains patient IDs 1 and 2.
• june_11 contains patient IDs 2 and 20.

These groups come directly from the hospital dataset.

Prepare the Helper Views

A view is a saved query that we can read like a table.

Run this setup once after importing the CSV into hospital_visits. If you already ran the companion script, these views already exist.

The first two views give us distinct patient and department records. The last two select visits on the dates we want to compare.

SQL query:

CREATE VIEW patients AS SELECT DISTINCT patient_id, patient_name FROM hospital_visits; CREATE VIEW departments AS SELECT DISTINCT department_id, department_name FROM hospital_visits; CREATE VIEW june_1 AS SELECT patient_id FROM hospital_visits WHERE visit_date = DATE '2026-06-01'; CREATE VIEW june_11 AS SELECT patient_id FROM hospital_visits WHERE visit_date = DATE '2026-06-11'; 

DISTINCT removes repeated ID and name pairs.

The two date groups:

In the queries below:

• a and b are short names for the two views.
• ON tells SQL how to match their rows.

INNER JOIN — Keep Matches Only

An INNER JOIN keeps rows that match on both sides.

Hospital scenario: Identify patients who attended on both dates. [5]

SQL query:

SELECT a.patient_id AS june_1_id, b.patient_id AS june_11_id FROM june_1 AS a INNER JOIN june_11 AS b ON a.patient_id = b.patient_id ORDER BY a.patient_id; 

Result:

Explanation: Only patient 2 appears because this ID exists in both groups.

LEFT JOIN — Keep Every Patient in the First Group

A LEFT JOIN keeps every row from the left group and adds matching rows from the right group.

Hospital scenario: Check whether each patient seen on 1 June returned on 11 June.

SQL query:

SELECT a.patient_id AS june_1_id, b.patient_id AS june_11_id FROM june_1 AS a LEFT JOIN june_11 AS b ON a.patient_id = b.patient_id ORDER BY a.patient_id; 

Result:

Explanation: Patients 1 and 2 remain.

Patient 1 has NULL on the right because no matching 11 June visit exists in that group.

RIGHT JOIN — Keep Every Patient in the Second Group

A RIGHT JOIN keeps every row from the right group.

Hospital scenario: List all patients seen on 11 June, including those absent from the 1 June group.

SQL query:

SELECT a.patient_id AS june_1_id, b.patient_id AS june_11_id FROM june_1 AS a RIGHT JOIN june_11 AS b ON a.patient_id = b.patient_id ORDER BY b.patient_id; 

Result:

Explanation: Patient 2 matches both groups. Patient 20 has NULL on the left because no 1 June match exists.

A RIGHT JOIN can be rewritten as a LEFT JOIN by reversing the table order.

FULL OUTER JOIN — Keep Both Groups

A FULL OUTER JOIN keeps matching and unmatched rows from both sides.

Hospital scenario: Compare attendance across both dates without dropping patients from either group.

SQL query:

SELECT a.patient_id AS june_1_id, b.patient_id AS june_11_id FROM june_1 AS a FULL OUTER JOIN june_11 AS b ON a.patient_id = b.patient_id ORDER BY COALESCE(a.patient_id, b.patient_id); 

Result:

Explanation:

• Patient 1 appears only in the first group.
• Patient 2 appears in both groups.
• Patient 20 appears only in the second group.

COALESCE() selects the available non-NULL ID to sort the rows.

CROSS JOIN — Create Every Possible Pair

A CROSS JOIN pairs every row from one group with every row from another.

Hospital scenario: Create a grid of possible comparisons between the two attendance groups.

SQL query:

SELECT a.patient_id AS june_1_id, b.patient_id AS june_11_id FROM june_1 AS a CROSS JOIN june_11 AS b ORDER BY a.patient_id, b.patient_id; 

Result:

Explanation: Two rows multiplied by two rows gives four combinations.

These pairs are possible combinations, not evidence of a relationship between patients.

SELF JOIN — Connect Repeat Visits

A SELF JOIN uses the same table twice.

Hospital scenario: Place two visits by the same patient side by side. Here we use patient 1.

SQL query:

SELECT a.patient_id, a.visit_id AS first_visit, b.visit_id AS later_visit FROM hospital_visits AS a JOIN hospital_visits AS b ON a.patient_id = b.patient_id AND a.visit_date < b.visit_date WHERE a.patient_id = 1; 

Result:

Explanation: Patient 1’s visit 1001 is paired with visit 1011.

a and b represent two uses of the same table. The earlier-date condition avoids pairing a visit with itself or returning the reversed pair.

Comparing the Joins

A join can produce several rows for one patient when several records match. Always check the matching key and the number of rows returned.

3. Common Table Expressions

A common table expression, or CTE, names a query result using WITH.

The name lasts for one SQL statement. It helps us break a question into smaller steps without creating a permanent table.

A Basic CTE — Find High Bills

A basic CTE gives one query result a temporary name.

Hospital scenario: Prepare a list of visits with bills above KES 40,000.

SQL query:

WITH high_bills AS ( SELECT visit_id, total_bill_kes FROM hospital_visits WHERE total_bill_kes > 40000 ) SELECT * FROM high_bills ORDER BY total_bill_kes DESC; 

Result:

Explanation: The CTE creates a list named high_bills. The final query reads that list.

Three visits qualify. SELECT * displays all columns from the CTE.

Multiple CTEs — Build a Two-Step Report

We can define several CTEs by separating them with commas.

Hospital scenario: Calculate each patient’s total bill, then select patients whose total exceeds KES 90,000.

SQL query:

WITH patient_totals AS ( SELECT patient_id, SUM(total_bill_kes) AS total_bill FROM hospital_visits GROUP BY patient_id ), high_totals AS ( SELECT * FROM patient_totals WHERE total_bill > 90000 ) SELECT * FROM high_totals; 

Result:

Explanation:

1. patient_totals calculates one total per patient.
2. high_totals selects totals above KES 90,000.
3. The final query displays the qualifying patient.

Only patient 10 qualifies, with KES 98,000 across two visits.

A CTE with a Join and Aggregation

CTEs can combine calculations such as COUNT() with joins.

Hospital scenario: Count each patient’s visits then add patient names to create a repeat-patient list.

SQL query:

WITH visit_counts AS ( SELECT patient_id, COUNT(*) AS visits FROM hospital_visits GROUP BY patient_id ) SELECT p.patient_name, c.visits FROM patients AS p JOIN visit_counts AS c ON p.patient_id = c.patient_id WHERE c.visits > 1 ORDER BY p.patient_id; 

Result:

Explanation: The CTE counts visits for each patient. The join adds the patient’s name from the patients view.

Five patients have two recorded visits each.

A Recursive CTE — Generate Reporting Dates

A recursive CTE uses its own previous result to continue a sequence.

Hospital scenario: Build a three-day reporting calendar starting from the date of visit 1001.

SQL query:

WITH RECURSIVE calendar(day) AS ( SELECT visit_date FROM hospital_visits WHERE visit_id = 1001 UNION ALL SELECT day + 1 FROM calendar WHERE day < DATE '2026-06-03' ) SELECT day FROM calendar ORDER BY day; 

Result:

Explanation:

1. The first SELECT starts with 1 June.
2. The second SELECT adds one day to the previous result.
3. WHERE stops the sequence at 3 June.
4. UNION ALL appends the generated rows.

The stopping condition is essential so that the query does not continue indefinitely.

CTEs Compared with Subqueries

A CTE places a named step before the main query. A subquery places a SELECT inside another query.

Choose a CTE for several readable steps or reuse within one statement. Choose a subquery for a short calculation.

Neither approach is automatically faster.

4. SQL Subqueries

A subquery is a query inside another query.

A subquery can:

• Supply a value in SELECT.
• Provide a small table in FROM.
• Filter rows in WHERE.

The categories can overlap. For example, a scalar subquery may also be correlated.

A Scalar Subquery in SELECT

A scalar subquery supplies a single value.

Hospital scenario: Display the highest hospital bill next to Amina’s individual bills.

SQL query:

SELECT visit_id, total_bill_kes, ( SELECT MAX(total_bill_kes) FROM hospital_visits ) AS highest_hospital_bill FROM hospital_visits WHERE patient_id = 1 ORDER BY visit_id; 

Result:

Explanation: The inner query finds the highest bill across the hospital: KES 82,000. The outer query displays it beside both of Amina’s bills.

A scalar subquery must return one column and at most one row:

...