Introduction
Structured Query Language (SQL) is used to retrieve, manipulate and analyse data stored in relational databases. In a hospital environment, SQL can help management analyse patient visits, compare departments, monitor outstanding bills and evaluate service delivery.
This article explains four important SQL concepts:
- Window Functions
- SQL Joins
- Common Table Expressions (CTEs)
- SQL Subqueries
Hospital Dataset
| Column | Description |
|---|---|
patient_id | Patient identifier |
first_name | Patient's first name |
last_name | Patient's last name |
gender | Patient's gender |
age | Patient's age |
phone_number | Patient's phone number |
date_of_visit | Date of hospital visit |
diagnosis | Recorded diagnosis |
treatment_plan | Recommended treatment |
doctor_name | Responsible doctor |
hospital_department | Hospital department |
insurance_provider | Insurance provider |
bill_amount_kes | Bill amount in Kenyan shillings |
payment_status | Payment status |
1. SQL Window Functions
1.1 Meaning and Purpose
Window functions perform calculations across a set of related rows while retaining individual rows in the output.
They are useful for ranking records, calculating running totals, comparing billing amounts and analysing changes over time.
1.2 The OVER() Clause
The OVER() clause specifies the window of rows over which a function operates.
SELECT patient_id, bill_amount_kes, AVG(bill_amount_kes) OVER () AS overall_average_bill FROM hospital_records; The query displays each patient's bill alongside the average bill across all records.
Every row contains the individual bill and the same overall average.
Hospital application: Compare individual billing records with the hospital-wide average.
1.3 The PARTITION BY Clause
PARTITION BY divides rows into groups for calculation without combining them into summary rows.
SELECT patient_id, hospital_department, bill_amount_kes, AVG(bill_amount_kes) OVER ( PARTITION BY hospital_department ) AS department_average_bill FROM hospital_records; The query calculates a separate average bill for each department.
Each patient record remains visible, but records in the same department share the same departmental average.
Hospital application: Compare individual bills against the relevant departmental average.
1.4 The ORDER BY Clause
Within OVER(), ORDER BYdetermines the sequence used for ranking, comparisons and ordered calculations.
SELECT patient_id, date_of_visit, ROW_NUMBER() OVER ( ORDER BY date_of_visit, patient_id ) AS record_sequence FROM hospital_records; The query numbers records in chronological order. The patient identifier acts as a tie-breaker.
Each record receives a sequential number beginning at 1.
1.5 Window Frames
A window frame determines which rows within a window are included in a calculation for the current row.
SELECT patient_id, date_of_visit, bill_amount_kes, SUM(bill_amount_kes) OVER ( ORDER BY date_of_visit, patient_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_billing_total FROM hospital_records; The query calculates a running total of bills, starting from the first record and adding each subsequent record.
The cumulative total increases as rows are processed in the specified order.
Common frame boundaries include:
UNBOUNDED PRECEDING: the beginning of the partition.
CURRENT ROW: the current row, subject to frame semantics.
UNBOUNDED FOLLOWING: the end of the partition.
n PRECEDING: a specified number of preceding rows in a ROWS frame.
n FOLLOWING: a specified number of following rows in a ROWS frame.
1.6 Ranking Functions
1.6.1. ROW_NUMBER()
ROW_NUMBER() assigns a unique sequential number to every row according to the specified ordering.
SELECT patient_id, hospital_department, bill_amount_kes, ROW_NUMBER() OVER ( PARTITION BY hospital_department ORDER BY bill_amount_kes DESC, patient_id ) AS bill_position FROM hospital_records; Records are numbered within each department, starting with the highest bill.
Each department has its own numbering sequence. Tied bills still receive different numbers.
Hospital application: Identify the highest-billed records in each department.
1.6.2 RANK()
RANK() assigns the same rank to tied values but leaves gaps after ties.
SELECT patient_id, bill_amount_kes, RANK() OVER ( ORDER BY bill_amount_kes DESC ) AS bill_rank FROM hospital_records; Records are ranked by bill amount in descending order.
If two records tie for rank 1, the next record receives rank 3.
Hospital application: Rank billing records while recognising equal values.
1.6.3DENSE_RANK()
DENSE_RANK() assigns the same rank to tied values without leaving gaps.
SELECT patient_id, bill_amount_kes, DENSE_RANK() OVER ( ORDER BY bill_amount_kes DESC ) AS dense_bill_rank FROM hospital_records; Records are ranked by bill amount, with tied values sharing the same rank.
If two records receive rank 1, the next distinct amount receives rank 2.
Hospital application: Rank distinct billing amounts without gaps.
ROW_NUMBER() assigns a different number to every row. RANK() allows ties and leaves gaps. DENSE_RANK() allows ties without gaps.
1.6.4 NTILE()
NTILE(n) divides ordered rows into approximately equal groups.
SELECT patient_id, bill_amount_kes, NTILE(4) OVER ( ORDER BY bill_amount_kes ) AS billing_quartile FROM hospital_records; The query divides records into four groups according to billing amount.
Every record receives a group number from 1 to 4. The groups contain approximately equal numbers of rows.
Hospital application: Analyse the distribution of lower- and higher-billing records.
Equal billing amounts may fall into different groups because NTILE() divides rows rather than distinct values.
1.7 Offset Window Functions
1.7.1 LAG()
LAG() retrieves a value from a preceding row.
SELECT patient_id, date_of_visit, bill_amount_kes, LAG(bill_amount_kes) OVER ( ORDER BY date_of_visit, patient_id ) AS previous_record_bill FROM hospital_records; The query retrieves the bill from the preceding record.
The first row has NULL for the previous bill because no preceding row exists.
Hospital application: Compare consecutive records to identify billing changes.
To compare previous visits for the same patient, use PARTITION BY patient_id if the dataset contains multiple visits per patient.
1.7.2 LEAD()
LEAD() retrieves a value from a following row.
SELECT patient_id, date_of_visit, bill_amount_kes, LEAD(bill_amount_kes) OVER ( ORDER BY date_of_visit, patient_id ) AS next_record_bill FROM hospital_records; The query retrieves the bill from the next record.
The last row has NULL because no subsequent row exists.
Hospital application: Compare current and subsequent records in chronological billing analyses.
LAG() looks backwards, while LEAD() looks forwards.
1.8 First and Last Value Functions
1.8.1 FIRST_VALUE()
FIRST_VALUE() returns a value from the first row in the window frame.
SELECT patient_id, hospital_department, bill_amount_kes, FIRST_VALUE(patient_id) OVER ( PARTITION BY hospital_department ORDER BY bill_amount_kes DESC, patient_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS highest_bill_patient_id FROM hospital_records; The query identifies the record with the highest bill in each department.
Every record in a department displays the identifier of the highest-billed record.
Hospital application: Identify high-billing records for further financial review.
1.8.2 LAST_VALUE()
LAST_VALUE() returns a value from the last row in the current window frame.
SELECT patient_id, hospital_department, bill_amount_kes, LAST_VALUE(patient_id) OVER ( PARTITION BY hospital_department ORDER BY bill_amount_kes DESC, patient_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING ) AS lowest_bill_patient_id FROM hospital_records; The query identifies the final record in each department when bills are sorted in descending order.
Every record in a department displays the identifier of the lowest-billed record, assuming non-null billing amounts.
Hospital application: Identify the lowest-billed record in each department.
The explicit frame is important because the default ordered frame may end at the current row or its peer group rather than the final row in the partition.
1.9 Aggregate Window Functions
1.9.1 SUM()
SUM() calculates the total of values within a window.
SELECT patient_id, hospital_department, bill_amount_kes, SUM(bill_amount_kes) OVER ( PARTITION BY hospital_department ) AS department_total_billing FROM hospital_records; The query calculates total recorded billing for each department while preserving individual records.
Every record in a department displays the same departmental total.
Hospital application: Monitor departmental billing totals alongside individual records.
1.9.2 AVG()
AVG() calculates the average of values within a window.
SELECT patient_id, hospital_department, bill_amount_kes, AVG(bill_amount_kes) OVER ( PARTITION BY hospital_department ) AS department_average_bill FROM hospital_records; The query calculates the average bill for each department.
Each record displays its bill and the average bill for its department. Null billing amounts are ignored.
Hospital application: Compare individual bills against departmental averages.
1.9.3 COUNT()
COUNT() counts rows or non-null values within a window.
SELECT patient_id, hospital_department, COUNT(*) OVER ( PARTITION BY hospital_department ) AS department_record_count FROM hospital_records; The query counts records associated with each department.
Every record in a department displays the same departmental record count.
Hospital application: Understand the distribution of records across departments.
COUNT(*) counts rows, while COUNT(bill_amount_kes) counts only rows where the bill is not null. Record counts should not automatically be interpreted as unique patient counts.
1.9.4 MIN()
MIN() returns the minimum non-null value within a window.
SELECT patient_id, hospital_department, bill_amount_kes, MIN(bill_amount_kes) OVER ( PARTITION BY hospital_department ) AS minimum_department_bill FROM hospital_records; The query identifies the lowest recorded bill in each department.
Each record displays the minimum non-null bill for its department.
Hospital application: Review the distribution of recorded billing values.
1.9.5 MAX()
MAX() returns the maximum non-null value within a window.
SELECT patient_id, hospital_department, bill_amount_kes, MAX(bill_amount_kes) OVER ( PARTITION BY hospital_department ) AS maximum_department_bill FROM hospital_records; The query identifies the highest recorded bill in each department.
Each record displays the maximum non-null bill for its department.
Hospital application: Identify high-billing records for financial review.
2. SQL Joins
2.1 Meaning and Purpose
A SQL join combines rows from two tables or query results based on a relationship between them.
Hospital databases often store patient details, billing information, doctor records and department information separately. Joins allow these datasets to be combined for reporting and analysis.
The following examples create two departmental summaries using CTEs.
WITH department_totals AS ( SELECT hospital_department, SUM(bill_amount_kes) AS total_billing FROM hospital_records GROUP BY hospital_department ), unpaid_totals AS ( SELECT hospital_department, COUNT(*) AS unpaid_record_count FROM hospital_records WHERE payment_status = 'Unpaid' GROUP BY hospital_department ) SELECT * FROM department_totals; The first CTE calculates total billing by department. The second calculates the number of records marked as unpaid.
2.2 INNER JOIN
An INNER JOIN returns only rows that match the join condition in both inputs.
WITH department_totals AS ( SELECT hospital_department, SUM(bill_amount_kes) AS total_billing FROM hospital_records GROUP BY hospital_department ), unpaid_totals AS ( SELECT hospital_department, COUNT(*) AS unpaid_record_count FROM hospital_records WHERE payment_status = 'Unpaid' GROUP BY hospital_department ) SELECT d.hospital_department, d.total_billing, u.unpaid_record_count FROM department_totals d INNER JOIN unpaid_totals u ON d.hospital_department = u.hospital_department; The query combines billing totals with unpaid-record counts for departments appearing in both summaries.
Departments without unpaid records are excluded.
Hospital application: Review billing totals alongside outstanding-payment records.
2.3 LEFT JOIN
A LEFT JOIN preserves every row from the left input and includes matching rows from the right input. Missing matches produce null values for right-side columns.
WITH department_totals AS ( SELECT hospital_department, SUM(bill_amount_kes) AS total_billing FROM hospital_records GROUP BY hospital_department ), unpaid_totals AS ( SELECT hospital_department, COUNT(*) AS unpaid_record_count FROM hospital_records WHERE payment_status = 'Unpaid' GROUP BY hospital_department ) SELECT d.hospital_department, d.total_billing, COALESCE(u.unpaid_record_count, 0) AS unpaid_record_count FROM department_totals d LEFT JOIN unpaid_totals u ON d.hospital_department = u.hospital_department; The query preserves all departments with recorded billing.
Departments without unpaid records display zero because COALESCE() replaces the missing count with zero.
Hospital application: Prepare a complete departmental report, including departments without unpaid records.
2.4 RIGHT JOIN
A RIGHT JOIN preserves all rows from the right input and includes matching rows from the left input.
WITH department_totals AS ( SELECT hospital_department, SUM(bill_amount_kes) AS total_billing FROM hospital_records GROUP BY hospital_department ), unpaid_totals AS ( SELECT hospital_department, COUNT(*) AS unpaid_record_count FROM hospital_records WHERE payment_status = 'Unpaid' GROUP BY hospital_department ) SELECT d.hospital_department, d.total_billing, u.unpaid_record_count FROM department_totals d RIGHT JOIN unpaid_totals u ON d.hospital_department = u.hospital_department; The query preserves every department represented in the unpaid-record summary.
Every department with unpaid records appears, even if it has no matching billing summary.
Hospital application: Preserve all departments from an unpaid-record report during reconciliation.
With these particular CTEs, every department in the unpaid summary should also appear in the billing summary. Consequently, the result will normally match the inner join.
2.5 FULL OUTER JOIN
A FULL OUTER JOIN preserves matching rows and unmatched rows from both inputs.
WITH department_totals AS ( SELECT hospital_department, SUM(bill_amount_kes) AS total_billing FROM hospital_records GROUP BY hospital_department ), unpaid_totals AS ( SELECT hospital_department, COUNT(*) AS unpaid_record_count FROM hospital_records WHERE payment_status = 'Unpaid' GROUP BY hospital_department ) SELECT COALESCE( d.hospital_department, u.hospital_department ) AS hospital_department, d.total_billing, u.unpaid_record_count FROM department_totals d FULL OUTER JOIN unpaid_totals u ON d.hospital_department = u.hospital_department; The query preserves departments from both summaries, including unmatched departments.
Matching departments display both values. Unmatched departments have a null value for the missing side.
Hospital application: Reconcile billing reports against unpaid-record reports.
2.6 CROSS JOIN
A CROSS JOIN returns every possible combination of rows from two inputs.
WITH departments AS ( SELECT DISTINCT hospital_department FROM hospital_records ), statuses AS ( SELECT DISTINCT payment_status FROM hospital_records ) SELECT d.hospital_department, s.payment_status FROM departments d CROSS JOIN statuses s ORDER BY d.hospital_department, s.payment_status; The query combines every department with every distinct payment status.
If the dataset contains five departments and three payment statuses, the query returns 15 combinations.
Hospital application: Build the structure for a department-by-payment-status monitoring matrix.
Some combinations may not exist in the actual data. A later left join to observed counts can show zero for those combinations.
2.7 SELF JOIN
A self join joins a table to itself.
SELECT a.patient_id AS patient_a, b.patient_id AS patient_b, a.diagnosis, a.hospital_department FROM hospital_records a INNER JOIN hospital_records b ON a.diagnosis = b.diagnosis AND a.patient_id < b.patient_id WHERE a.diagnosis IS NOT NULL; The query pairs records belonging to different patient identifiers when their diagnoses match.
Each row represents a pair of records with the same recorded diagnosis. The comparison condition prevents the same pair from appearing in reverse order, assuming patient identifiers are unique and comparable.
Hospital application: Explore diagnoses shared across patient records to support service planning.
Matching diagnoses do not prove that patients have identical clinical circumstances, and common diagnoses can produce many pairs.
3. Common Table Expressions (CTEs)
3.1 Meaning and Purpose
A Common Table Expression (CTE) is a named result set defined using the WITH clause. It exists for the duration of the SQL statement in which it is defined.
CTEs improve readability by separating complex queries into logical steps.
3.2 Basic CTE
WITH department_billing AS ( SELECT hospital_department, SUM(bill_amount_kes) AS total_billing FROM hospital_records GROUP BY hospital_department ) SELECT * FROM department_billing ORDER BY total_billing DESC; The CTE calculates total billing for each department. The main query displays the totals in descending order.
One row per department with its total billing amount.
Hospital application: Identify departments with the highest and lowest recorded billing totals.
3.3 Multiple CTEs
A query can define several CTEs separated by commas. Later CTEs can reference earlier ones.
WITH department_billing AS ( SELECT hospital_department, SUM(bill_amount_kes) AS total_billing FROM hospital_records GROUP BY hospital_department ), department_payments AS ( SELECT hospital_department, COUNT(*) FILTER ( WHERE payment_status = 'Paid' ) AS paid_record_count, COUNT(*) FILTER ( WHERE payment_status = 'Unpaid' ) AS unpaid_record_count FROM hospital_records GROUP BY hospital_department ) SELECT b.hospital_department, b.total_billing, p.paid_record_count, p.unpaid_record_count FROM department_billing b INNER JOIN department_payments p ON b.hospital_department = p.hospital_department ORDER BY b.total_billing DESC; The first CTE calculates departmental billing totals. The second calculates counts for paid and unpaid records. The final query combines the summaries.
One row per department with billing totals and payment-status counts.
Hospital application: Produce a consolidated departmental billing report.
3.4 Recursive CTEs
A recursive CTE refers to its own result and is useful for generating sequences or processing hierarchical data.
A recursive CTE generally contains:
- An anchor query, which establishes the starting rows.
- A recursive query, which generates additional rows from the previous result. Example: Generate Monthly Reporting Periods
WITH RECURSIVE months(month_start, last_month) AS ( SELECT DATE_TRUNC('month', MIN(date_of_visit))::date, DATE_TRUNC('month', MAX(date_of_visit))::date FROM hospital_records WHERE date_of_visit IS NOT NULL UNION ALL SELECT (month_start + INTERVAL '1 month')::date, last_month FROM months WHERE month_start < last_month ) SELECT month_start FROM months ORDER BY month_start; The anchor query identifies the earliest and latest months in the dataset. The recursive query adds one month at a time until it reaches the last month.
A chronological list of month-start dates spanning the observed reporting period.
Hospital application: Generate a reporting calendar that can be joined to monthly visit counts to identify months without recorded visits.
A recursive CTE requires a termination condition. Without one, the query may continue generating rows indefinitely or until execution is stopped.
3.5 CTEs with Joins
CTEs can prepare data for a join, making a multi-step query easier to understand.
WITH department_billing AS ( SELECT hospital_department, SUM(bill_amount_kes) AS total_billing FROM hospital_records GROUP BY hospital_department ), department_unpaid AS ( SELECT hospital_department, COUNT(*) AS unpaid_records FROM hospital_records WHERE payment_status = 'Unpaid' GROUP BYSource：https://dev.to/alex_majale_d64efa6d81883/sql-concepts-window-functions-joins-ctes-and-subqueries-567cRelated posts
Show HN: Vaire, Self-Hosting Postgres Through the One Glorious CLI
Gabriele Bartolini: CloudNativePG reaches CNCF Incubation: another rung on the ladder
pandas chunks fixed my memory error, then quietly broke my averages
Postgres LISTEN/NOTIFY vs Redis Pub/Sub: Latency, Write Cost and Failure Cases, Tested
LlamaIndex Workflows vs LangGraph: Event-Driven Architecture and Async State Overhead Showdown
