1. Introduction
Structured Query Language (SQL) is used to retrieve, manipulate, and analyze data stored in relational databases. In a hospital environment, SQL helps administrators understand patient visits, monitor medical departments, evaluate hospital performance, track revenue, and support operational decisions.
The hospital database used in this article is built with PostgreSQL and contains the following main tables:
| Table | Purpose |
|---|---|
patients | Stores patient details, ages, genders, counties, and sub-counties. |
doctors | Stores doctors' names, specialties, genders, and years of experience. |
departments | Stores hospital departments. |
wards | Stores hospital ward information. |
diagnosis | Stores diagnoses and their categories. |
procedures | Stores medical procedures and their categories. |
visits | Stores hospital visit records, including dates, bills, admission types, and lengths of stay. |
The visits table connects the other tables using identifiers such as patient_id, doctor_id, department_id, and diagnosis_id. This structure allows information from several tables to be combined during analysis.
The examples use the actual table and column names in the supplied hospital SQL script. They assume the database tables have been created and populated successfully and that the current schema is evening.
SET search_path TO evening; The article covers four important SQL concepts: window functions, joins, common table expressions, and subqueries. Each concept is explained through practical hospital scenarios.
2. SQL Window Functions
2.1 What are window functions?
Window functions perform calculations across a group of related rows while preserving the individual rows in the query result. Unlike ordinary aggregate functions used with GROUP BY, window functions do not normally collapse multiple records into a single row.
For example, a hospital manager may want to display every patient visit alongside the total bill for the entire hospital. A window function can calculate that total while keeping every visit visible.
2.2 Understanding OVER()
The OVER() clause specifies the window of rows over which a function operates. It can be used with ranking functions, navigation functions, and aggregate functions.
Example:
SELECT visit_id, patient_id, total_bill_kes, SUM(total_bill_kes) OVER () AS hospital_total_bill FROM visits ORDER BY visit_id; Explanation: The query displays every visit and its bill alongside the sum of all visit bills. Because OVER() contains no partition or ordering, the total covers the entire result set.
Expected result: The query returns 30 rows, one for each visit in the supplied dataset. Each row contains its individual bill and the same hospital-wide total.
2.3 Understanding PARTITION BY
PARTITION BY divides the rows into separate groups for calculation. The function then operates independently within each group.
Example:
SELECT visit_id, department_id, total_bill_kes, SUM(total_bill_kes) OVER ( PARTITION BY department_id ) AS department_total_bill FROM visits ORDER BY department_id, visit_id; Explanation: Every visit is displayed with the total bill for its department. The total is repeated for each visit belonging to that department.
Hospital application: Management can compare the revenue generated by individual departments without losing the details of the visits contributing to each total.
2.4 Understanding ORDER BY inside a window
Within OVER(), ORDER BY determines the order in which rows are ranked or processed. It is different from the final ORDER BY, which determines the presentation order of the query results.
SELECT visit_id, visit_date, total_bill_kes, SUM(total_bill_kes) OVER ( ORDER BY visit_date, visit_id ) AS running_total FROM visits ORDER BY visit_date, visit_id; Explanation: The query calculates a cumulative total in visit-date order. Adding visit_id makes the ordering deterministic when multiple visits share a date.
Expected result: The running total increases as the visits are processed. It includes the current visit and all preceding visits in the defined window.
2.5 Understanding window frames
A window frame specifies which rows within a partition are included in a calculation for the current row. Common frame boundaries include:
-
UNBOUNDED PRECEDING: starts at the first row in the partition. -
CURRENT ROW: includes the current row as a boundary. -
n PRECEDING: refers to a specified number of preceding rows. -
UNBOUNDED FOLLOWING: extends to the final row in the partition.
Example: running total
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 running_total FROM visits ORDER BY visit_date, visit_id; Explanation: The frame starts with the first visit and extends through the current visit. ROWS specifies a physical row-based frame.
Example: moving average over three visits
SELECT visit_id, visit_date, total_bill_kes, ROUND( AVG(total_bill_kes) OVER ( ORDER BY visit_date, visit_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW ), 2 ) AS three_visit_average FROM visits ORDER BY visit_date, visit_id; This query calculates the average bill for the current visit and up to the two preceding visits. At the beginning of the dataset, fewer than three rows may be available.
Important distinction: In PostgreSQL, an ordered window using the default frame commonly includes all rows from the beginning of the partition through the current row's peer group. For predictable running calculations, explicitly specifying ROWS is often clearer. This distinction is particularly important when several visits share the same date or ordering value.
2.6 ROW_NUMBER()
ROW_NUMBER() assigns a unique sequential number to each row in a window partition.
SELECT visit_id, patient_id, total_bill_kes, ROW_NUMBER() OVER ( ORDER BY total_bill_kes DESC, visit_id ) AS bill_row_number FROM visits; Explanation: Visits are numbered from the highest bill to the lowest. The visit_id breaks ties so that the ordering is deterministic.
Expected result: The visit with the highest bill receives row number 1, the next visit receives 2, and so forth. Even if two visits have identical bills, they receive different row numbers.
Hospital application: This is useful when creating ranked lists or selecting the first visit per patient using a common table expression.
2.7 RANK()
RANK() assigns the same rank to rows with equal ordering values. It leaves gaps after tied ranks.
SELECT visit_id, total_bill_kes, RANK() OVER ( ORDER BY total_bill_kes DESC ) AS bill_rank FROM visits; Explanation: Visits are ranked by bill amount. Equal bills receive equal ranks, and the next rank skips the number of tied positions.
For example, if three visits occupy positions 1, 2, and 3 but share the same bill, all three receive rank 1, and the next distinct bill receives rank 4.
Hospital application: A hospital can identify the highest-billed visits while preserving ties.
2.8 DENSE_RANK()
DENSE_RANK() also assigns the same rank to tied values, but it does not leave gaps.
SELECT visit_id, total_bill_kes, DENSE_RANK() OVER ( ORDER BY total_bill_kes DESC ) AS dense_bill_rank FROM visits; Explanation: Visits with the same bill share a rank. The next distinct bill receives the next consecutive rank.
Comparison:
| Function | Treatment of ties | Example ranks for tied top values |
|---|---|---|
ROW_NUMBER() | Assigns a unique number to every row | 1, 2, 3 |
RANK() | Same rank for ties; gaps follow | 1, 1, 3 |
DENSE_RANK() | Same rank for ties; no gaps | 1, 1, 2 |
These functions are useful when analyzing hospital bills, patient satisfaction scores, or department performance.
2.9 NTILE()
NTILE(n) divides an ordered result set into approximately equal groups called buckets.
SELECT visit_id, total_bill_kes, NTILE(4) OVER ( ORDER BY total_bill_kes ) AS bill_quartile FROM visits; Explanation: The visits are sorted by bill and divided into four buckets. Because the dataset contains 30 visits, the bucket sizes will be as balanced as possible: two buckets will contain eight visits and two will contain seven.
Bucket 1 contains the lowest bills, while bucket 4 contains the highest bills.
Hospital application: The hospital can segment visits into four bill-value groups for financial reporting.
2.10 LAG()
LAG() retrieves a value from a preceding row without requiring a self-join.
SELECT visit_id, visit_date, total_bill_kes, LAG(total_bill_kes) OVER ( ORDER BY visit_date, visit_id ) AS previous_visit_bill FROM visits ORDER BY visit_date, visit_id; Explanation: Each visit is displayed alongside the bill of the preceding visit in the ordered dataset. The first row has NULL because no preceding row exists.
Hospital application: Analysts can compare consecutive visits and identify changes in bill amounts.
2.11 LEAD()
LEAD() retrieves a value from a subsequent row.
SELECT visit_id, visit_date, total_bill_kes, LEAD(total_bill_kes) OVER ( ORDER BY visit_date, visit_id ) AS next_visit_bill FROM visits ORDER BY visit_date, visit_id; Explanation: The query displays the bill of the next visit alongside the current visit. The last row has NULL because no following row exists.
Comparison: LAG() looks backward, while LEAD() looks forward. Both are useful for comparisons between neighboring records.
2.12 FIRST_VALUE()
FIRST_VALUE() returns the value from the first row in the current window frame.
SELECT visit_id, department_id, total_bill_kes, FIRST_VALUE(total_bill_kes) OVER ( PARTITION BY department_id ORDER BY total_bill_kes DESC, visit_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS highest_department_bill FROM visits; Explanation: The query displays each visit alongside the highest bill in its department.
Hospital application: Department managers can see the highest-billed visit while retaining the rest of the department's visit records.
2.13 LAST_VALUE()
LAST_VALUE() returns the value from the last row in the current window frame. It is important to define the frame carefully.
SELECT visit_id, department_id, total_bill_kes, LAST_VALUE(total_bill_kes) OVER ( PARTITION BY department_id ORDER BY total_bill_kes, visit_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS highest_department_bill FROM visits; Explanation: Because the rows are ordered from the lowest bill to the highest, and the frame extends to the final row, the function returns the highest bill in each department.
Without the explicit frame ending in UNBOUNDED FOLLOWING, LAST_VALUE() may return the current row's value or the last value in a smaller frame instead of the final value in the whole department.
2.14 Aggregate window functions: SUM(), AVG(), COUNT(), MIN(), and MAX()
Aggregate functions summarize values. When combined with OVER(), they calculate summaries across a window while keeping individual visits visible.
A. SUM() — total bill by department
SELECT visit_id, department_id, total_bill_kes, SUM(total_bill_kes) OVER ( PARTITION BY department_id ) AS department_total FROM visits; The query calculates each department's total bill and repeats that value for every visit in the department.
B. AVG() — average bill by admission type
SELECT visit_id, admission_type, total_bill_kes, ROUND( AVG(total_bill_kes) OVER ( PARTITION BY admission_type ), 2 ) AS average_bill_for_admission_type FROM visits; The query calculates the average bill separately for outpatient, inpatient, and emergency visits. Each visit remains visible.
C. COUNT() — number of visits per patient
SELECT visit_id, patient_id, COUNT(*) OVER ( PARTITION BY patient_id ) AS patient_visit_count FROM visits ORDER BY patient_id, visit_id; The query counts the visits associated with each patient. A patient with multiple visits receives the same visit count on each of their rows.
D. MIN() — lowest bill by department
SELECT visit_id, department_id, total_bill_kes, MIN(total_bill_kes) OVER ( PARTITION BY department_id ) AS lowest_department_bill FROM visits; The query identifies the minimum bill within each department.
E. MAX() — highest bill by department
SELECT visit_id, department_id, total_bill_kes, MAX(total_bill_kes) OVER ( PARTITION BY department_id ) AS highest_department_bill FROM visits; The query identifies the maximum bill within each department.
Why use aggregate window functions? They are especially useful for dashboards and detailed reports where analysts need both individual visit information and group-level performance measures in the same result set.
3. SQL Joins
3.1 What are joins?
A join combines rows from two or more tables based on a related column or condition. In the hospital database, visits contains identifiers that link to patients, doctors, departments, diagnoses, and procedures.
Joins allow analysts to replace numerical identifiers with meaningful information such as patient names, department names, and diagnosis descriptions.
3.2 INNER JOIN
An INNER JOIN returns only rows with matching records in both tables.
SELECT v.visit_id, p.patient_name, v.visit_date, v.total_bill_kes FROM visits v INNER JOIN patients p ON v.patient_id = p.patient_id ORDER BY v.visit_id; Explanation: The query combines each visit with the corresponding patient record. Only visits whose patient IDs match a patient in patients appear.
Expected result: The supplied dataset has 30 visits and all referenced patient IDs exist, so this query returns 30 rows.
Hospital scenario: Retrieving a patient's name and bill for each hospital visit.
3.3 LEFT JOIN
A LEFT JOIN returns every row from the left table and matching rows from the right table. If there is no match, columns from the right table contain NULL.
SELECT p.patient_id, p.patient_name, v.visit_id, v.visit_date FROM patients p LEFT JOIN visits v ON p.patient_id = v.patient_id ORDER BY p.patient_id, v.visit_date; Explanation: Every patient is displayed, including patients without visits. If a patient has multiple visits, that patient appears once for each matching visit.
Hospital scenario: Identifying patients who have never visited the hospital.
SELECT p.patient_id, p.patient_name FROM patients p LEFT JOIN visits v ON p.patient_id = v.patient_id WHERE v.visit_id IS NULL; This second query returns patients without a matching visit. In the supplied dataset, every patient has at least one recorded visit, so it returns no rows.
3.4 RIGHT JOIN
A RIGHT JOIN returns every row from the right table and any matching rows from the left table.
SELECT v.visit_id, d.department_name FROM visits v RIGHT JOIN departments d ON v.department_id = d.department_id ORDER BY d.department_id, v.visit_id; Explanation: Every department appears, even if it has no visits. A department without visits would have NULL in the visit columns.
Hospital scenario: Reporting all hospital departments, including departments that have not recorded any visits.
Comparison with LEFT JOIN: The result can generally be expressed using a LEFT JOIN by reversing the table order. For example, departments LEFT JOIN visits serves the same purpose as the preceding right join.
3.5 FULL OUTER JOIN
A FULL OUTER JOIN returns all matching rows and all unmatched rows from both tables.
SELECT v.visit_id, v.department_id AS visit_department_id, d.department_id AS registered_department_id, d.department_name FROM visits v FULL OUTER JOIN departments d ON v.department_id = d.department_id ORDER BY d.department_id, v.visit_id; Explanation: The result includes visits with matching departments, departments without visits, and visits whose department ID does not match a department record.
In the supplied dataset, every visit references an existing department and all four departments have visits. Therefore, this query produces matched visit-department rows only.
Hospital scenario: Auditing records across operational tables to detect unmatched or incomplete relationships.
3.6 CROSS JOIN
A CROSS JOIN returns every possible combination of rows from two tables.
SELECT d.department_name, w.ward_name FROM departments d CROSS JOIN wards w ORDER BY d.department_name, w.ward_name; Explanation: There are four departments and four wards, producing (4 \times 4 = 16) combinations.
Hospital scenario: Preparing a planning matrix showing all department-ward combinations for resource allocation. These combinations are hypothetical unless a separate assignment table confirms that a department operates in a particular ward.
3.7 SELF JOIN
A self join joins a table to itself. It is useful when records within the same table must be compared.
The doctors table does not contain a manager or supervisor ID, so a practical self-join example can compare doctors within the same specialty.
SELECT d1.doctor_name AS doctor_one, d2.doctor_name AS doctor_two, d1.doctor_specialty FROM doctors d1 JOIN doctors d2 ON d1.doctor_specialty = d2.doctor_specialty AND d1.doctor_id < d2.doctor_id; Explanation: The query finds pairs of doctors who share a specialty. The condition d1.doctor_id < d2.doctor_id prevents a doctor from being matched with themselves and avoids listing the same pair twice.
Expected result: The supplied data contains four doctors with four different specialties, so the query returns no rows.
Hospital scenario: In a larger hospital database, this approach can identify doctors sharing specialties or compare staff records. A true supervisor hierarchy would require an appropriate relationship column, such as manager_id.
3.8 Comparing SQL joins
| Join type | What it returns | Typical hospital use |
|---|---|---|
INNER JOIN | Matching records only | Visits with valid patient records |
LEFT JOIN | All left-side records and matches | All patients, including those without visits |
RIGHT JOIN | All right-side records and matches | All departments, including unused departments |
FULL OUTER JOIN | All records from both sides | Auditing unmatched records |
CROSS JOIN | Every possible combination | Planning matrices |
SELF JOIN | Comparisons within the same table | Finding doctors with the same specialty |
Choosing the correct join is important because an inappropriate join can exclude relevant records or create unintended combinations.
4. Common Table Expressions (CTEs)
4.1 What is a CTE?
A Common Table Expression is a named temporary result defined using the WITH clause. It exists for the duration of the statement and can make complex SQL easier to read and maintain.
A CTE does not automatically create a permanent table.
4.2 Basic CTE
Scenario: Finding visits with bills greater than KES 20,000.
WITH expensive_visits AS ( SELECT visit_id, patient_id, visit_date, total_bill_kes FROM visits WHERE total_bill_kes > 20000 ) SELECT * FROM expensive_visits ORDER BY total_bill_kes DESC; Explanation: The CTE first selects visits whose bills exceed KES 20,000. The outer query retrieves and sorts those records.
Hospital application: Reviewing high-value visits for financial monitoring or administrative follow-up.
4.3 Multiple CTEs
Multiple CTEs allow a query to be divided into logical stages. A later CTE can reference an earlier one.
WITH department_revenue AS ( SELECT department_id, SUM(total_bill_kes) AS total_revenue FROM visits GROUP BY department_id ), department_averages AS ( SELECT AVG(total_revenue) AS average_department_revenue FROM department_revenue ) SELECT d.department_name, dr.total_revenue, da.average_department_revenue FROM department_revenue dr JOIN departments d ON d.department_id = dr.department_id CROSS JOIN department_averages da ORDER BY dr.total_revenue DESC; Explanation:
-
department_revenuecalculates the total bill for each department. -
department_averagescalculates the average of those department totals. - The final query displays each department's revenue alongside the overall average department revenue.
Hospital application: Management can identify departments performing above or below the average revenue level. This is a financial comparison, not a measure of clinical quality.
4.4 Recursive CTEs
A recursive CTE refers to its own result and repeatedly generates rows until its stopping condition is met. It normally contains an initial query, a recursive query, and a termination condition.
Example: generating a sequence of numbers
WITH RECURSIVE visit_day_sequence AS ( SELECT 1 AS day_number UNION ALL SELECT day_number + 1 FROM visit_day_sequence WHERE day_number < 7 ) SELECT day_number FROM visit_day_sequence ORDER BY day_number; Explanation: The initial query creates day 1. The recursive part adds one to the previous number until day 7 is reached.
Expected result: Seven rows containing the numbers 1 through 7.
Hospital application: The generated sequence can support reporting templates for a seven-day monitoring period or be joined to a calendar table to show dates with no visits.
A recursive CTE can also traverse a genuine hierarchy, such as a hospital organizational chart, provided the database contains a parent-child relationship. The current doctor table does not contain such a relationship, so the sequence example demonstrates recursion without inventing one.
4.5 CTEs with joins and aggregations
CTEs can organize joins and aggregate calculations into separate stages.
WITH visit_details AS ( SELECT v.visit_id, v.total_bill_kes, d.department_name, dg.diagnosis_name FROM visits v JOIN departments d ON d.department_id = v.department_id JOIN diagnosis dg ON dg.diagnosis_id = v.diagnosis_id ), diagnosis_summary AS ( SELECT department_name, diagnosis_name, COUNT(*) AS visit_count, SUM(total_bill_kes) Source：https://dev.to/frank_hahanyu_254/-a-practical-guide-to-advanced-sql-window-functions-joins-ctes-and-subqueries-using-a-hospital-1lno Related posts
Building an AI Chatbot with Spring Boot and the OpenAI API (2026 Guide)
Automate Telegram Webhook Deployments and Log Purges via Yii2 Console Commands
Parsing WhatsApp chat exports: what real files taught me
A Brighter Flashlight Makes the Release Look Late
Building a Minimal Model Context Protocol (MCP) Server in 30 Lines of TypeScript
