Mastering Advanced SQL with a Hospital Dataset: Window Functions, Joins, CTEs and Subqueries

Introduction

SQL (Structured Query Language) is used to retrieve, combine, summarize and analyse relational data. In a hospital information system, SQL can support workload reporting, billing analysis, patient follow-up, clinical service monitoring, and operational planning.
This article explains each concept, why it is used, and demonstrates it on the hospital dataset with a real query, a description of what the query does, and its expected result.

SQL Window Functions

SQL window functions allow performing calculations across a set of rows that are related to the current row, without collapsing the result into a single value. They are commonly used for tasks like aggregates, rankings and running totals. The OVER clause defines the “window” of rows for the calculation. It can:

PARTITION BY: It divides the data into groups using PARTITION BY.

ORDER BY: It specifies the order of rows within each group using ORDER BY.

OVER()

OVER()is what turns an ordinary function into a window function. It defines the "window" of rows the function can see. An empty OVER () means the window is the whole result set.

PARTITION BY

PARTITION BY divides the rows into independent groups, and the function restarts for each group. It is like GROUP BY, but rows are not collapsed. Example: PARTITION BY doctor_id computes a separate calculation for each doctor.

SELECT visit_id, department_id, total_bill,
       SUM(total_bill) OVER (PARTITION BY department_id) AS department_revenue
FROM visits
ORDER BY department_id, visit_date, visit_id;

Each visit is paired with the sum of bills for its department. Cardiology, General Medicine, Orthopedics and Pediatrics each have their own total; the department total repeats on every visit in that department.
Expected result: One row per visit. All rows with the same department_id show the same department_revenue

ORDER BY (inside OVER)
ORDER BY sets the sequence of rows inside each partition. It is essential for ranking functions, for LAG/LEAD, and for running totals. It is independent of the ORDER BY at the end of the query, which only sorts the final output.

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_revenue
FROM visits
ORDER BY visit_date, visit_id;

Types of Window Functions in SQL

SQL window functions are mainly of two types: aggregate window functions and ranking window functions.

1. Aggregate Window Functions

Aggregate window functions calculate aggregates over a window of rows while retaining individual rows. Common aggregate functions include:

SUM(): Sums values within a window.
AVG(): Calculates the average value within a window.
COUNT(): Counts the rows within a window.
MAX(): Returns the maximum value in the window.
MIN(): Returns the minimum value in the window.

Example: Using AVG(), I calculated average bill for each department while displaying every visit, just like the employee salary example.

SELECT visit_id, department_id, total_bill,
       AVG(total_bill) OVER (
           PARTITION BY department_id
       ) AS avg_bill
FROM visits;

2. Ranking Window Functions

These functions provide rankings of rows within a partition based on specific criteria. Common ranking functions include:

RANK(): Assigns ranks to rows, skipping ranks for duplicates.
DENSE_RANK(): Assigns ranks to rows without skipping rank numbers for duplicates.
ROW_NUMBER(): Assigns a unique number to each row in the result set.
PERCENT_RANK(): Shows the relative rank of a row as a percentage between 0 and 1.

2.1 RANK() Function

RANK() functions are used to assign ranks to rows within a group based on a specific order. Ranking functions are commonly used to organize and analyze data. Some common ranking functions include:

Example: Using RANK(), I ranked visits by their total bill within each department, from the highest bill to the lowest:

SELECT visit_id, department_id, total_bill,
RANK() OVER (
PARTITION BY department_id
ORDER BY total_bill DESC
) AS bill_rank
FROM visits;

2.2 DENSE RANK() Function

DENSE_RANK()gives the same rank to rows with equal values. It then continues with the next number without skipping, keeping the ranking sequence continuous.

SELECT visit_id, department_id, total_bill,
       DENSE_RANK() OVER (
           PARTITION BY department_id
           ORDER BY total_bill DESC
       ) AS bill_dense_rank
FROM visits;

2.3 ROW NUMBER() Function

ROW_NUMBER() gives a unique number to each row in the result set. It increments by 1 for every row, even if values are the same, so no two rows have the same number.

SELECT visit_id, department_id, total_bill,
ROW_NUMBER() OVER (
PARTITION BY department_id
ORDER BY total_bill DESC
) AS visit_row_no
FROM visits;

2.4 PERCENT RANK() Function

PERCENT_RANK() shows where a row stands compared to others in the same group. The formula is:

PERCENT_RANK() = (RANK - 1)/(Total Rows in Partition - 1)

SELECT visit_id, department_id, total_bill,
PERCENT_RANK() OVER (
PARTITION BY department_id
ORDER BY total_bill DESC
) AS bill_percent_rank
FROM visits;

3. NTILE(n)

What it does: splits the ordered rows into n roughly equal buckets and returns the bucket number. If the rows do not divide evenly, the earlier buckets receive one extra row.
Why it is used: quartiles, percentiles and tiering, for example banding patients into cost tiers.

Splits the ordered rows into four buckets with as equal a number of rows as possible. Because there are 30 visits, bucket sizes will differ by at most one row. Here, bucket 1 contains the lower bills and bucket 4 the higher bills.
Result: Four bucket labels (1–4) are assigned across 30 rows; the two extra rows are allocated to the first buckets under the standard distribution rule.

4. LAG() and LEAD()

LAG reads a value from an earlier row in the window order
LEAD reads a value from a later row.
They are useful for changes over time without joining a table to itself.

4.1 LAG() example: Comparing each bill with the previous visit's bill

LAG(total_bill) OVER (ORDER BY visit_date, visit_id) AS previous_bill,
       total_bill - LAG(total_bill) OVER (ORDER BY visit_date, visit_id) AS change_from_previous
FROM visits
ORDER BY visit_date, visit_id;

The first visit has no previous row, so previous_bill and change_from_previous are NULL. For later rows, the change is current bill minus previous bill; a positive number means the bill increased.
Result: The first row has NULL for previous_bill. The second row compares the 2 June visit with the preceding visit, and so on.

4.2 LEAD(): View the next visit's bill in the sequence

SELECT visit_id, visit_date, total_bill,
       LEAD(total_bill) OVER (ORDER BY visit_date, visit_id) AS next_bill
FROM visits
ORDER BY visit_date, visit_id;

Shows the next row's bill beside the current row. The final row has NULL because no later row exists.
Result: 30 rows; the last row's next_bill is NULL

5. FIRST_VALUE() and LAST_VALUE()

FIRST_VALUE: Returns the first value in the current window frame; LAST_VALUE: Returns the last value in that frame. With an ordered window, LAST_VALUE can return the current row's value under the default frame rather than the last value in the whole partition. To obtain the final value of the entire partition, explicitly extend the frame to UNBOUNDED FOLLOWING.

5.1 FIRST_VALUE()

Show the lowest bill for each department

SELECT visit_id, department_id, total_bill,
       FIRST_VALUE(total_bill) OVER (
           PARTITION BY department_id
           ORDER BY total_bill ASC, visit_id
       ) AS lowest_bill_in_department
FROM visits
ORDER BY department_id, total_bill;

Within each department, the first row is the lowest bill. That amount is displayed beside every visit in the department.
Result: One row per visit; lowest_bill_in_department is constant within each department.

5.2 LAST_VALUE()

Correctly show the highest bill for each department
The explicit frame includes the entire department partition, so the last value after sorting ascending is the highest bill in that department. Without the UNBOUNDED FOLLOWING boundary, LAST_VALUE may only see rows through the current row.
Expected result: One row per visit; highest_bill_in_department repeats for all visits in the same department.

SELECT visit_id, department_id, total_bill,
       LAST_VALUE(total_bill) OVER (
           PARTITION BY department_id
           ORDER BY total_bill ASC, visit_id
           ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
       ) AS highest_bill_in_department
FROM visits
ORDER BY department_id, total_bill;

6. SQL joins

The joins combine rows from two tables using a relationship or join condition. In this dataset, visits contains foreign keys that connect encounters to patient, doctor, department, ward, diagnosis and procedure descriptions. Joins are essential because normalized databases store each type of information separately.

Inner Join

Inner join returns only rows that satisfy the join condition in both tables. It is appropriate when a report should include only visits with a matching patient record.

Example: Visits with patient and doctor names
SELECT visit_id, visit_date, patient_name, doctor_name, total_bill

SELECT v.visit_id, v.visit_date, p.patient_name, d.doctor_name, v.total_bill
FROM visits AS v
INNER JOIN patients AS p ON p.patient_id = v.patient_id
INNER JOIN doctors AS d ON d.doctor_id = v.doctor_id
ORDER BY v.visit_date, v.visit_id;

Each visit is enriched with the patient's and attending doctor's names. Since the foreign keys in visits reference patients and doctors, all valid visit rows should match both lookup tables.
Result: 30 rows of the patient's and attending doctor's names.

Left Join

Left Join returns every row from the left table and matching rows from the right table. If no match exists, right-table columns are NULL. It is useful when the hospital wants to list all patients, including those with no visit.

Example: All patients and any visits

SELECT p.patient_id, p.patient_name, v.visit_id, v.visit_date
FROM patients AS p
LEFT JOIN visits AS v ON v.patient_id = p.patient_id
ORDER BY p.patient_id, v.visit_date;

Patients with visits appear once per matching visit. Patients without visits still appear once, with NULL visit fields. The sample includes repeated visits for some patients, such as patient_id 1.
Result: At least 25 rows, more rows appear where a patient has multiple visits. Patients with no visits, have NULL visit_id and visit_date.

Right Join

Right join preserves all rows from the right-hand table and matching rows from the left. It can be rewritten as a LEFT JOIN by swapping table order, and many teams prefer LEFT JOIN for readability.

SELECT d.doctor_id, d.doctor_name, v.visit_id
FROM visits AS v
RIGHT JOIN doctors AS d ON d.doctor_id = v.doctor_id
ORDER BY d.doctor_id, v.visit_id;

Full Outer Join

Full Outer Join returns matched rows plus unmatched rows from both sides. Missing-side columns are NULL. It is useful for reconciliation, such as identifying patient records with no visits and visit records whose patient key does not match a patient.

SELECT p.patient_id, p.patient_name, v.visit_id, v.patient_id AS visit_patient_id
FROM patients AS p
FULL OUTER JOIN visits AS v ON v.patient_id = p.patient_id
ORDER BY COALESCE(p.patient_id, v.patient_id), v.visit_id;

Matched patient/visit pairs are returned, patients without visits are retained, and visits with an unknown patient_id are also retained.

Cross Join

Cross Join returns every possible combination of rows from two tables, with no matching condition. It is useful for creating a planning grid, but can produce a large result.

Example: Every department paired with every triage level in use

SELECT dep.department_name, triage.triage_level
FROM department AS dep
CROSS JOIN (
    SELECT DISTINCT triage_level
    FROM visits
) AS triage
ORDER BY dep.department_name, triage.triage_level;

Creates a table of every department and every distinct triage level found in visits. The result can help reveal department and triage combinations that have no actual visits when compared with observed data.

Self Join

Joins a table to itself using aliases. It is useful for comparing records within the same table, such as finding visits by the same patient on different dates.

Example: find pairs of visits belonging to the same patient

SELECT v1.patient_id,
       v1.visit_id AS earlier_visit,
       v1.visit_date AS earlier_date,
       v2.visit_id AS later_visit,
       v2.visit_date AS later_date
FROM visits AS v1
JOIN visits AS v2
  ON v1.patient_id = v2.patient_id
 AND (v1.visit_date, v1.visit_id) < (v2.visit_date, v2.visit_id)
ORDER BY v1.patient_id, earlier_date, later_date;

Common Table Expressions (CTEs)

A Common Table Expression is a named temporary result defined with WITH and used by the main SQL statement. CTEs make complex queries easier to read by breaking them into logical steps. In PostgreSQL, a CTE exists for the duration of one statement.

Basic CTE

Example: I identified high-value visits. The CTE first filters visits to bills of KES 30,000 or more. The outer query then lists those qualifying visits from highest bill to lowest. This is useful for reviewing expensive admissions or investigating unusually large bills.

WITH high_value_visits AS (
    SELECT visit_id, visit_date, patient_id, total_bill
    FROM visits
    WHERE total_bill >= 30000
)
SELECT *
FROM high_value_visits
ORDER BY total_bill DESC;

Multiple CTEs

Multiple CTEs are separated by commas and can build on earlier CTEs. This is useful when a report needs several distinct calculation steps.

WITH department_totals AS (
    SELECT department_id,
           COUNT(*) AS visit_count,
           SUM(total_bill) AS revenue
    FROM visits
    GROUP BY department_id
),
department_details AS (
    SELECT d.department_name, t.visit_count, t.revenue
    FROM department_totals AS t
    JOIN department AS d ON d.department_id = t.department_id
)
SELECT department_name, visit_count, revenue,
       ROUND(revenue / NULLIF(visit_count, 0), 2) AS average_bill_per_visit
FROM department_details
ORDER BY revenue DESC;

The first CTE calculates visit count and revenue per department. The second adds department names. The final query calculates average bill per visit

Recursive CTE

A recursive CTE refers to itself and is commonly used for hierarchies or sequences. It has an anchor query that starts the recursion and a recursive query that produces the next level. The recursion must have a termination condition, otherwise it may continue until the database stops it or the query fails.

CTEs with joins and aggregations

The CTE filters inpatient visits and aggregates them by ward. The outer LEFT JOIN retains every ward, even one with no inpatient records. LOWER handles the inconsistent capitalization of 'Inpatient' and 'inpatient' in the supplied data.
Result: Four ward rows are returned. Counts and totals are zero for wards without inpatient visits, average_length_of_stay remains NULL where there are no matching inpatient rows.

SQL subqueries

A subquery is a query nested inside another SQL statement. It can return a single value, a list of values, or a table-like result. Subqueries can be placed in SELECT, FROM or WHERE clauses. Their behaviour depends on whether they are correlated with the outer query and how many rows and columns they return.

Scalar subquery

A scalar subquery returns exactly one column and at most one row. It can be used where a single value is expected. If it returns more than one row, PostgreSQL raises an error.
Each scalar subquery calculates one overall average value. The outer query shows that value for each visit and calculates how far the visit bill is above or below it. This can also be written with AVG(total_bill) OVER() for a window-function approach.

SELECT visit_id, total_bill,
       (SELECT AVG(total_bill) FROM visits) AS overall_average_bill,
       total_bill - (SELECT AVG(total_bill) FROM visits) AS difference_from_average
FROM visits
ORDER BY visit_id;

Non-correlated subquery

A non-correlated subquery does not refer to a column from the outer query. It can be evaluated independently and its result is then used by the outer query.

Correlated subquery

A correlated subquery refers to a value from the current row of the outer query. Conceptually, its result is determined for each outer row. The optimizer may transform it for efficiency.

Nested subqueries

A nested subquery contains another subquery inside it. Nesting is useful when one condition depends on the result of another, but excessive nesting can reduce readability; a CTE may be clearer.

Subquery with IN

IN tests whether a value matches any value in a returned list. It is useful when filtering one table based on a set of IDs selected from another.
The inner query returns patient IDs with an inpatient visit. IN selects those patients from patients. Duplicate IDs in the inner result do not duplicate the outer patient row

Subquery with EXISTS

EXISTS is true when the subquery returns at least one row. It is commonly used for existence checks and correlated queries.

ANY and ALL

ANY also referred to as SOME, makes a comparison true if it is true for at least one value returned by the subquery. ALL makes a comparison true only if it is true for every returned value. If the subquery returns no rows, ANY is false and ALL is true under SQL's quantified-comparison rules. NULL values can make comparisons UNKNOWN

Subquery in the SELECT clause

The correlated scalar subquery counts visits for each patient. Because COUNT(*) returns one row and a numeric value even when there are no matches, patients with no visits receive 0

Subquery in the FROM clause

A subquery in FROM acts as a derived table. It must have an alias in PostgreSQL. It is useful for aggregating data first and then joining or filtering the summary.

Subquery in the WHERE clause

SELECT visit_id, department_id, total_bill
FROM visits
WHERE department_id IN (
    SELECT department_id
    FROM visits
    GROUP BY department_id
    HAVING COUNT(*) > 5
)
ORDER BY department_id, visit_id;

Conclusion

Window functions, joins, CTEs, and subqueries solve different but complementary problems. Window functions add rankings, comparisons, and analytical summaries without removing detail rows. Joins bring related information together across normalized tables. CTEs divide complex logic into readable stages and support recursive queries. Subqueries let one query depend on a value or set produced by another query. In the hospital dataset, these techniques can answer practical questions about repeat attendance, department workload, high bills, doctor activity, admission patterns, and follow-up requirements.
The most important habit is to understand the shape of the result before writing the query, whether it should return one row per visit, one row per patient, one row per department, or every possible combination. Then choose the join, window, CTE or subquery pattern that produces that shape, and verify the result against the underlying records.