If you're learning SQL, you've probably already used SELECT, WHERE, INSERT, and UPDATE. These commands are a great starting point, but real-world database work requires more than retrieving individual records.
Imagine you're working with a hospital database and the management team asks questions like:
- Which doctors handle the most patient visits?
- Which department has the highest total billing?
- How many times has each patient visited the hospital?
- Which visits have bills above the department average?
- Which patients require follow-up?
- How can we compare a patient's current visit with their previous visit?
You can answer these questions using four powerful SQL concepts:
**1. Window functions
- SQL joins
- Common Table Expressions (CTEs)
- Subqueries**
In this article, we'll explore each concept using PostgreSQL and a hospital database containing tables such as patients, doctors, department, diagnosis, procedures, wards, and visits.
Let's get started!
- *SQL Window Functions * What are window functions?
Window functions perform calculations across related rows without collapsing those rows into a single result.
For example, suppose we want to display every patient visit alongside the total number of visits made by that patient.
We could use GROUP BY, but that would return a summary for each patient. A window function allows us to keep the individual visit records.
Understanding OVER() and PARTITION BY
Before exploring individual functions, let's understand the syntax:
SELECT
visit_id,
patient_id,
COUNT(*) OVER (
PARTITION BY patient_id
) AS total_patient_visits
FROM visits;
Here's what's happening:
-
COUNT(*)counts rows. -
OVER()tells PostgreSQL to calculate the function as a window function. -
PARTITION BY patient_idcreates a separate window for each patient.
The hospital can see every visit while also knowing how many visits belong to each patient.
Unlike GROUP BY, PARTITION BY doesn't remove the individual rows.
1.1 ROW_NUMBER()
ROW_NUMBER() assigns a unique sequential number to each row within a window.
Let's number each patient's visits, starting with their most recent visit.
SELECT
patient_id,
visit_id,
visit_date,
ROW_NUMBER() OVER (
PARTITION BY patient_id
ORDER BY visit_date DESC, visit_id DESC
) AS visit_number
FROM visits
ORDER BY patient_id, visit_number;
The most recent visit for each patient receives row number 1.
This technique can help identify each patient's latest recorded visit.
1.2 RANK()
RANK() assigns ranks based on a value. When two rows have the same value, they receive the same rank, and the next rank is skipped.
Let's rank doctors by the number of visits they've handled.
SELECT
d.doctor_name,
COUNT(v.visit_id) AS total_visits,
RANK() OVER (
ORDER BY COUNT(v.visit_id) DESC
) AS doctor_rank
FROM doctors d
LEFT JOIN visits v
ON d.doctor_id = v.doctor_id
GROUP BY d.doctor_id, d.doctor_name
ORDER BY doctor_rank;
This query counts visits per doctor and ranks doctors from highest to lowest workload.
For example, if the visit counts are 10, 10, 7, the ranks will be 1, 1, 3.
Hospital management can use this to understand how recorded visits are distributed among doctors.
1.3 DENSE_RANK()
DENSE_RANK() works like RANK(), except it doesn't skip rank numbers after ties.
SELECT
d.department_name,
SUM(v.total_bill_kes) AS total_billing_kes,
DENSE_RANK() OVER (
ORDER BY SUM(v.total_bill_kes) DESC
) AS billing_rank
FROM department d
JOIN visits v
ON d.department_id = v.department_id
GROUP BY d.department_id, d.department_name
ORDER BY billing_rank;
This query calculates the total recorded billing for each department and ranks the departments.
If two departments tie for first place, the next department receives rank 2, not 3.
ROW_NUMBER() vs. RANK() vs. DENSE_RANK()
| Function | Values being ranked | Result |
|---|---|---|
ROW_NUMBER() |
100, 100, 80 | 1, 2, 3 |
RANK() |
100, 100, 80 | 1, 1, 3 |
DENSE_RANK() |
100, 100, 80 | 1, 1, 2 |
The difference matters when multiple records have identical values.
1.4 NTILE()
NTILE() divides ordered rows into approximately equal groups.
For example, we can divide hospital visits into four groups based on their bill amounts.
`SELECT
visit_id,
patient_id,
total_bill_kes,
NTILE(4) OVER (
ORDER BY total_bill_kes DESC
) AS billing_group
FROM visits
ORDER BY billing_group;`
The function assigns each visit to a group numbered from 1 to 4.
Group 1 contains the highest bills, while group 4 contains the lowest bills.
This can help the finance department compare visits across billing quartiles. Equal bill amounts may be split across groups because NTILE() balances row counts rather than keeping tied values together.
1.5 LAG() and LEAD()
Sometimes, we need to compare a row with a previous or subsequent row.
LAG() looks backward.
`SELECT
patient_id,
visit_id,
visit_date,
total_bill_kes,
LAG(total_bill_kes) OVER (
PARTITION BY patient_id
ORDER BY visit_date, visit_id
) AS previous_bill
FROM visits
ORDER BY patient_id, visit_date, visit_id;`
This retrieves the bill amount from the previous recorded visit by the same patient.
The first visit for each patient has NULL for previous_bill because there is no earlier visit in that patient's window.
LEAD() looks forward.
`SELECT
patient_id,
visit_id,
visit_date,
LEAD(visit_date) OVER (
PARTITION BY patient_id
ORDER BY visit_date, visit_id
) AS next_visit_date
FROM visits;`
This displays the next recorded visit date for each patient.
These functions can help analyze changes in billing and the intervals between recorded visits. They retrieve existing data rather than predicting future appointments.
1.6 FIRST_VALUE() and LAST_VALUE()
These functions retrieve values from specific positions within a window frame.
FIRST_VALUE() returns the first value.
`SELECT
patient_id,
visit_id,
visit_date,
FIRST_VALUE(visit_date) OVER (
PARTITION BY patient_id
ORDER BY visit_date, visit_id
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS first_visit_date
FROM visits;`
Every visit for the same patient displays their earliest recorded visit date.
Now let's retrieve the latest recorded visit date using LAST_VALUE().
`SELECT
patient_id,
visit_id,
visit_date,
LAST_VALUE(visit_date) OVER (
PARTITION BY patient_id
ORDER BY visit_date, visit_id
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
) AS last_visit_date
FROM visits;`
The window frame tells PostgreSQL to consider the entire patient's partition.
Without an explicit full-partition frame, LAST_VALUE() may return the current row's value or the last value among its peers instead of the final value in the partition.
1.7 Aggregate window functions
Aggregate functions can also be used as window functions.
SUM()
SUM() calculates a total.
`SELECT
visit_id,
patient_id,
total_bill_kes,
SUM(total_bill_kes) OVER (
PARTITION BY patient_id
) AS patient_total_billing
FROM visits;`
Every visit displays the total recorded billing across that patient's visits.
AVG()
AVG() calculates an average.
`SELECT
visit_id,
department_id,
patient_satisfaction_score,
AVG(patient_satisfaction_score) OVER (
PARTITION BY department_id
) AS department_average_satisfaction
FROM visits;`
This allows us to compare each visit's satisfaction score with its department's average. AVG() ignores NULL satisfaction scores.
COUNT()
COUNT() counts records.
`SELECT
visit_id,
patient_id,
COUNT(*) OVER (
PARTITION BY patient_id
) AS patient_visit_count
FROM visits;`
Every visit displays the total number of recorded visits for that patient.
MIN()
MIN() finds the minimum value.
`SELECT
visit_id,
department_id,
total_bill_kes,
MIN(total_bill_kes) OVER (
PARTITION BY department_id
) AS lowest_department_bill
FROM visits;`
This displays the lowest recorded bill in each department alongside every visit.
MAX()
MAX() finds the maximum value.
`SELECT
visit_id,
department_id,
total_bill_kes,
MAX(total_bill_kes) OVER (
PARTITION BY department_id
) AS highest_department_bill
FROM visits;`
This displays the highest recorded bill in each department.
1.8 Understanding ORDER BY and window frames
ORDER BY inside OVER() determines the order used by the window function.
For example, we can calculate a running total of hospital billing:
`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_billing
FROM visits
ORDER BY visit_date, visit_id;`
The first row contains its own bill. Each subsequent row adds its bill to the previous running total.
Common window-frame boundaries include:
-
UNBOUNDED PRECEDING: the beginning of the window. -
CURRENT ROW: the current row, with behavior depending on the frame type. -
UNBOUNDED FOLLOWING: the end of the window. -
n PRECEDINGandn FOLLOWING: positions before or after the current row, depending on the frame unit.
PostgreSQL supports ROWS, RANGE, and GROUPS frame units.
An ORDER BY inside OVER() does not necessarily sort the final output. Use the outer ORDER BY when you want the displayed records sorted.
- SQL Joins
What are joins?
Joins allow us to combine data from multiple tables using a relationship between them.
In our hospital database, patients contains patient information while visits contains visit records. We can join the tables using patient_id.
2.1 INNER JOIN
An INNER JOIN returns only rows with matching values in both tables.
`SELECT
p.patient_id,
p.patient_name,
v.visit_id,
v.visit_date
FROM patients p
INNER JOIN visits v
ON p.patient_id = v.patient_id;`
This returns patients who have matching visit records. Patients without recorded visits are excluded.
2.2 LEFT JOIN
A LEFT JOIN returns every row from the left table, together with matching rows from the right table.
`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;`
All registered patients appear, even if they have never visited the hospital. For patients without matching visits, the visit columns contain NULL.
To find patients without recorded visits:
`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 is an anti-join: it finds records in one table that have no match in another.
2.3 RIGHT JOIN
A RIGHT JOIN retains every row from the right table.
`SELECT
p.patient_name,
v.visit_id,
v.visit_date
FROM patients p
RIGHT JOIN visits v
ON p.patient_id = v.patient_id;`
Every visit is retained, along with matching patient information.
Because the supplied schema has a foreign key from visits.patient_id to patients.patient_id, unmatched visit records should not normally exist.
A RIGHT JOIN can often be rewritten as a LEFT JOIN by reversing the order of the tables.
2.4 FULL OUTER JOIN
A FULL OUTER JOIN retains all rows from both tables.
`SELECT
p.patient_id,
p.patient_name,
v.visit_id,
v.visit_date
FROM patients p
FULL OUTER JOIN visits v
ON p.patient_id = v.patient_id;`
Matching records appear together. Patients without visits also appear, and unmatched visits would appear with missing patient details.
This can help reconcile records from different systems.
2.5 CROSS JOIN
A CROSS JOIN creates every possible combination of rows from two inputs.
`SELECT
d.department_name,
shifts.shift_name
FROM department d
CROSS JOIN (
VALUES
('Morning'),
('Evening'),
('Night')
) AS shifts(shift_name);`
If there are four departments, this query produces 12 department-and-shift combinations.
This can help create a template for planning staffing across departments and shifts. Be careful, because cross joins can produce very large result sets.
2.6 SELF JOIN
A self join joins a table to itself.
`SELECT
v1.patient_id,
v1.visit_id AS earlier_visit_id,
v1.visit_date AS earlier_visit_date,
v2.visit_id AS later_visit_id,
v2.visit_date AS later_visit_date
FROM visits v1
JOIN visits v2
ON v1.patient_id = v2.patient_id
AND (
v1.visit_date < v2.visit_date
OR (
v1.visit_date = v2.visit_date
AND v1.visit_id < v2.visit_id
)
)
ORDER BY v1.patient_id, v1.visit_date, v2.visit_date;`
The aliases v1 and v2 represent two different uses of the same table.
The query pairs distinct visits belonging to the same patient, with the earlier visit appearing first.
This can help analyze repeat visits and patterns of hospital attendance.
Quick comparison of joins
| Join | What it returns |
|---|---|
INNER JOIN |
Matching rows only |
LEFT JOIN |
All left-side rows and matching right-side rows |
RIGHT JOIN |
All right-side rows and matching left-side rows |
FULL OUTER JOIN |
All rows from both sides |
CROSS JOIN |
Every possible combination |
SELF JOIN |
Rows compared with other rows in the same table |
The key question when choosing a join is: Which records must remain in my result, even if a match is missing?
- Common Table Expressions (CTEs)
What is a CTE?
A Common Table Expression is a named result defined using WITH.
CTEs make complex SQL queries easier to read by dividing them into smaller steps.
3.1 Basic CTE
Let's identify patients who have visited the hospital more than once.
`WITH patient_visit_counts AS (
SELECT
patient_id,
COUNT(*) AS total_visits
FROM visits
GROUP BY patient_id
)
SELECT
patient_id,
total_visits
FROM patient_visit_counts
WHERE total_visits > 1
ORDER BY total_visits DESC;`
The CTE calculates each patient's visit count. The outer query filters the result to patients with more than one visit.
3.2 Multiple CTEs
You can define multiple CTEs in the same query.
`WITH doctor_visit_counts AS (
SELECT
doctor_id,
COUNT(*) AS total_visits
FROM visits
GROUP BY doctor_id
),
doctor_details AS (
SELECT
doctor_id,
doctor_name,
doctor_specialty
FROM doctors
)
SELECT
dd.doctor_name,
dd.doctor_specialty,
COALESCE(dvc.total_visits, 0) AS total_visits
FROM doctor_details dd
LEFT JOIN doctor_visit_counts dvc
ON dd.doctor_id = dvc.doctor_id
ORDER BY total_visits DESC, dd.doctor_name;`
The first CTE calculates each doctor's visit count. The second selects the doctor information we need. The final query joins the results and displays zero for doctors without visits.
COALESCE() returns the first non-NULL value from its arguments.
3.3 Recursive CTEs
A recursive CTE references itself to build a result iteratively.
The hospital schema doesn't contain a hierarchical department relationship, but we can demonstrate recursion by generating a calendar.
WITH RECURSIVE visit_calendar AS (
`SELECT DATE '2026-06-01' AS calendar_date
UNION ALL
SELECT calendar_date + 1
FROM visit_calendar
WHERE calendar_date < DATE '2026-06-15'
)
SELECT calendar_date
FROM visit_calendar
ORDER BY calendar_date;`
The anchor query starts on June 1. The recursive query adds one day at a time until June 15.
The result contains 15 consecutive dates.
We can then combine the calendar with hospital visits:
WITH RECURSIVE visit_calendar AS (
`SELECT DATE '2026-06-01' AS calendar_date
UNION ALL
SELECT calendar_date + 1
FROM visit_calendar
WHERE calendar_date < DATE '2026-06-15'
)
SELECT
c.calendar_date,
COUNT(v.visit_id) AS total_visits
FROM visit_calendar c
LEFT JOIN visits v
ON v.visit_date = c.calendar_date
GROUP BY c.calendar_date
ORDER BY c.calendar_date;`
This query returns every date in the period, including dates with zero visits.
The dates are illustrative. Change them to match the period you need to analyze.
3.4 CTEs with joins and aggregations
CTEs are useful when we need to calculate totals before joining them to descriptive tables.
WITH patient_billing AS (
` SELECT
patient_id,
SUM(total_bill_kes) AS total_billed_kes
FROM visits
GROUP BY patient_id
)
SELECT
p.patient_id,
p.patient_name,
COALESCE(pb.total_billed_kes, 0) AS total_billed_kes
FROM patients p
LEFT JOIN patient_billing pb
ON p.patient_id = pb.patient_id
ORDER BY total_billed_kes DESC;`
The CTE calculates each patient's total recorded billing. The final query joins those totals to the patient records.
Patients without visits receive a total of zero. These amounts represent recorded bills, not necessarily payments collected.
3.5 CTEs vs. subqueries
A subquery is a query nested inside another query. A CTE is a named result defined with WITH.
Here's a subquery:
`SELECT *
FROM (
SELECT
patient_id,
COUNT(*) AS total_visits
FROM visits
GROUP BY patient_id
) AS patient_counts
WHERE total_visits > 1;`
And here's the equivalent CTE:
WITH patient_counts AS (
` SELECT
patient_id,
COUNT(*) AS total_visits
FROM visits
GROUP BY patient_id
)
SELECT *
FROM patient_counts
WHERE total_visits > 1;`
Both return patients with more than one visit.
CTEs often make multi-step queries easier to understand, while subqueries can be convenient for short, localized calculations.
A CTE isn't automatically faster. PostgreSQL's query planner determines how eligible CTEs and subqueries are executed.
- SQL Subqueries
What is a subquery?
A subquery is a query inside another SQL query.
It can return a single value, a set of values, or a table-like result, depending on how it's written.
4.1 Scalar subqueries
A scalar subquery returns one row and one column.
Let's find visits with bills above the overall average.
`SELECT
visit_id,
patient_id,
total_bill_kes
FROM visits
WHERE total_bill_kes > (
SELECT AVG(total_bill_kes)
FROM visits
)
ORDER BY total_bill_kes DESC;`
The inner query calculates the average bill. The outer query returns visits whose bills exceed that average.
If a scalar subquery returns more than one row, PostgreSQL raises an error.
4.2 Non-correlated subqueries
A non-correlated subquery doesn't reference columns from the outer query.
`SELECT
patient_id,
patient_name
FROM patients
WHERE patient_id IN (
SELECT patient_id
FROM visits
);`
The inner query returns patient IDs from visits. The outer query retrieves the matching patient records.
This lists patients who have visited the hospital.
4.3 Correlated subqueries
A correlated subquery refers to a column from the outer query.
`SELECT
p.patient_id,
p.patient_name
FROM patients p
WHERE EXISTS (
SELECT 1
FROM visits v
WHERE v.patient_id = p.patient_id
);`
The subquery checks whether a matching visit exists for each patient.
This identifies patients with at least one recorded visit.
4.4 Nested subqueries
Nested subqueries contain another subquery inside them.
`SELECT
patient_id,
patient_name
FROM patients
WHERE patient_id IN (
SELECT patient_id
FROM visits
WHERE department_id IN (
SELECT department_id
FROM department
WHERE department_name = 'Cardiology'
)
);`
The innermost query finds the Cardiology department. The next query finds patients with visits in that department. The outer query retrieves their details.
4.5 Using IN
IN checks whether a value matches one of the values returned by a subquery.
`SELECT
patient_id,
patient_name
FROM patients
WHERE patient_id IN (
SELECT patient_id
FROM visits
WHERE follow_up_required = 'Yes'
);`
The query returns patients with at least one visit marked as requiring follow-up.
The value 'Yes' assumes that this is how the database stores the field. Use the actual value from your dataset if it differs.
4.6 Using EXISTS
EXISTS checks whether a subquery returns at least one row.
`SELECT
d.doctor_id,
d.doctor_name
FROM doctors d
WHERE EXISTS (
SELECT 1
FROM visits v
WHERE v.doctor_id = d.doctor_id
);`
This returns doctors with recorded visits.
To find doctors without visits, use NOT EXISTS:
`SELECT
d.doctor_id,
d.doctor_name
FROM doctors d
WHERE NOT EXISTS (
SELECT 1
FROM visits v
WHERE v.doctor_id = d.doctor_id
);`
EXISTS is useful when the existence of a related record matters more than the values it contains.
4.7 Using ANY
ANY makes a comparison against a set of values. The comparison succeeds if it is true for at least one value.
`SELECT
visit_id,
patient_id,
total_bill_kes
FROM visits
WHERE total_bill_kes > ANY (
SELECT total_bill_kes
FROM visits
WHERE triage_level = 'Red'
);`
The query returns visits whose bills exceed at least one non-NULL bill amount from a Red-triage visit.
This is a financial comparison, not a clinical judgment about severity.
4.8 Using ALL
ALL requires the comparison to be true for every value returned by the subquery.
`SELECT
visit_id,
patient_id,
total_bill_kes
FROM visits
WHERE total_bill_kes > ALL (
SELECT total_bill_kes
FROM visits
WHERE triage_level = 'Red'
);`
This returns visits whose bills exceed every non-NULL bill amount in the Red-triage group, provided NULL values do not make the comparison unknown.
The difference between ANY and ALL is:
-
> ANYmeans greater than at least one value. -
> ALLmeans greater than every value.
If the subquery returns no rows, ANY evaluates to false and ALL evaluates to true under SQL's logical rules. NULL values can also affect comparisons.
4.9 Subqueries in the SELECT clause
A scalar subquery can calculate a value for each row in the outer query.
`SELECT
p.patient_id,
p.patient_name,
(
SELECT COUNT(*)
FROM visits v
WHERE v.patient_id = p.patient_id
) AS total_visits
FROM patients p
ORDER BY p.patient_id;`
Each patient appears with their visit count. Patients without visits receive zero because COUNT(*) returns zero when no rows match.
4.10 Subqueries in the FROM clause
A subquery in FROM acts like a temporary table for the outer query. PostgreSQL requires it to have an alias.
`SELECT
patient_summary.patient_id,
patient_summary.total_billing_kes
FROM (
SELECT
patient_id,
SUM(total_bill_kes) AS total_billing_kes
FROM visits
GROUP BY patient_id
) AS patient_summary
WHERE patient_summary.total_billing_kes > 50000
ORDER BY patient_summary.total_billing_kes DESC;`
The inner query calculates total billing per patient. The outer query filters those totals to amounts above KES 50,000.
The threshold is illustrative and can be changed.
4.11 Subqueries in the WHERE clause
A subquery in WHERE filters rows based on another query's result.
`SELECT
visit_id,
patient_id,
total_bill_kes
FROM visits
WHERE total_bill_kes > (
SELECT AVG(total_bill_kes)
FROM visits
);`
The inner query calculates the average bill, and the outer query returns visits with bills above that average.
Subqueries in WHERE can work with operators such as =, >, IN, ANY, ALL, EXISTS, and NOT EXISTS.
- *Bringing Everything Together * The real value of SQL comes from combining these techniques.
Example 1: Rank departments by billing
WITH department_billing AS (
`SELECT
d.department_id,
d.department_name,
SUM(v.total_bill_kes) AS total_billing_kes
FROM department d
JOIN visits v
ON d.department_id = v.department_id
GROUP BY d.department_id, d.department_name
)
SELECT
department_name,
total_billing_kes,
RANK() OVER (
ORDER BY total_billing_kes DESC
) AS billing_rank
FROM department_billing
ORDER BY billing_rank, department_name;`
This combines a join, aggregation, a CTE, and a window function.
The result shows each department's total recorded billing and its rank.
Example 2: Find visits above their department's average bill
WITH department_averages AS (
` SELECT
department_id,
AVG(total_bill_kes) AS average_bill_kes
FROM visits
GROUP BY department_id
)
SELECT
v.visit_id,
p.patient_name,
d.department_name,
v.total_bill_kes,
ROUND(da.average_bill_kes, 2) AS average_department_bill
FROM visits v
JOIN patients p
ON v.patient_id = p.patient_id
JOIN department d
ON v.department_id = d.department_id
JOIN department_averages da
ON v.department_id = da.department_id
WHERE v.total_bill_kes > da.average_bill_kes
ORDER BY v.total_bill_kes DESC;`
The CTE calculates the average bill for each department. The joins add patient and department details. Finally, the WHERE clause selects visits above their department's average.
This could help the finance department identify bills for further review, although a higher bill does not automatically indicate an error.
*Final Thoughts
*
Learning SQL isn't just about memorizing syntax. It's about understanding how to ask questions of data and choosing the right technique to answer them.
Window functions calculate rankings, totals, averages, and comparisons while keeping individual rows. Joins combine related tables while controlling which unmatched records remain. CTEs break complex queries into named, understandable steps. Subqueries use one query's result inside another query.
Our hospital database provides practical examples of how these concepts can support patient visit analysis, doctor workload reporting, departmental billing comparisons, and follow-up identification.
Top comments (0)