Mastering SQL: Window Functions, Joins, CTEs and Subqueries

Introduction

Basic SELECT, WHERE and GROUP BY statements answer simple questions: how many visits happened, or how much money a department earned. Real hospital analytics asks harder ones. Which doctor’s most expensive visit was it? How long was it between a patient’s first and second visit? Which diagnoses appeared one week and vanished the next? Which doctors earn more per visit than the hospital average?
Four families of SQL tools answer these questions: window functions, joins, Common Table Expressions (CTEs) and subqueries. This article explains each one, shows why it exists, and demonstrates it on our hospital database with a query, an explanation of what the query does, and the expected result.
All results below were worked out by hand from the data in the creation script, so you can run each query and check it against the output shown.

SQL Window Functions

1. What is a window function?
   A window function performs a calculation across a set of rows that are related to the current row, without collapsing those rows into one.
   This lets you compare a row with its group, rank rows, look at the previous or next row, and compute running totals, all in a single query. Window functions are used for ranking, top-N per group, period-over-period comparison, running totals, moving averages and percentile bucketing.

2. The anatomy of a window: OVER, PARTITION BY, ORDER BY, and the frame
   

SELECT doctor_id, visit_id, visit_date, total_bill_kes, SUM(total_bill_kes) OVER ( PARTITION BY doctor_id ORDER BY visit_date, visit_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW ) AS running_revenue_kes FROM visits ORDER BY doctor_id, visit_date, visit_id; 

OVER() is what turns an ordinary function into a window function. It defines the window, the set of rows the function can see. An empty OVER () means the window is the whole result set.
PARTITION BY splits the rows into independent groups, like GROUP BY does, but without merging the rows. The function restarts for every partition. For example, PARTITION BY doctor_id means each doctor is calculated separately.
ORDER BY inside OVER sorts the rows within each partition. It is essential for ranking functions and for LAG/LEAD, and for aggregates it turns the calculation into a running one. It is separate from the ORDER BY at the end of the query, which only sorts the final output.
The window frame narrows the window further, relative to the current row. It is written as:
ROWS BETWEEN <start> AND <end>
where the boundaries can be UNBOUNDED PRECEDING, n PRECEDING, CURRENT ROW, n FOLLOWING or UNBOUNDED FOLLOWING.

1. Ranking functions ROW_NUMBER():assigns a unique sequential number (1, 2, 3 …) to each row in a partition, following the ORDER BY. Ties still get different numbers. Why it is used: top-N per group, removing duplicates, and pagination. Scenario: finance wants each doctor’s single most expensive visit.

SELECT doctor_name, visit_id, total_bill_kes FROM ( SELECT d.doctor_name, v.visit_id, v.total_bill_kes, ROW_NUMBER() OVER (PARTITION BY v.doctor_id ORDER BY v.total_bill_kes DESC) AS rn FROM visits v JOIN doctors d ON d.doctor_id = v.doctor_id ) ranked WHERE rn = 1; 

The inner query numbers each doctor’s visits from the highest bill down. Because window functions cannot be used in WHERE, the outer query keeps only rn = 1.

1. RANK() and DENSE_RANK() What they do: both give tied rows the same rank. They differ in what happens next: • RANK() skips numbers after a tie (1, 2, 2, 4). • DENSE_RANK() does not skip (1, 2, 2, 3). Why they are used: leaderboards where ties are real, for example ranking admissions by length of stay. Scenario: rank admitted patients by length of stay and compare all three numbering functions side by side.

SELECT visit_id, length_of_stay_days, ROW_NUMBER() OVER (ORDER BY length_of_stay_days DESC, visit_id) AS row_num, RANK() OVER (ORDER BY length_of_stay_days DESC) AS rnk, DENSE_RANK() OVER (ORDER BY length_of_stay_days DESC) AS dense_rnk FROM visits WHERE length_of_stay_days > 0; 

Only the ten visits with a hospital stay are kept. Several have the same number of days, so the three functions behave differently.

5._ NTILE(n)_
What it does: splits the ordered rows into n roughly equal buckets and returns the bucket number. When rows do not divide evenly, the earlier buckets get one extra row.
Why it is used: quartiles, deciles and tiering customers or patients by spend.
Scenario: find the top quartile of bills, the highest-spending 25% of visits.

SELECT visit_id, total_bill_kes FROM ( SELECT visit_id, total_bill_kes, NTILE(4) OVER (ORDER BY total_bill_kes DESC) AS quartile FROM visits ) q WHERE quartile = 1 ORDER BY total_bill_kes DESC; 

30 visits divided by 4 is 7 remainder 2, so buckets 1 and 2 hold 8 rows each and buckets 3 and 4 hold 7 each. Bucket 1 therefore contains the 8 largest bills.

Value (navigation) functions

1. LAG() and LEAD() What they do: LAG(col, n, default) reads the value from n rows before the current row within the partition; LEAD(col, n, default) reads n rows after. With no offset, n is 1. If no such row exists, the result is NULL (or the default you supply). Why they are used: comparing a row with its neighbour without a self join: change since the last period, time between events, readmission checks. Scenario: for patients who came back to the hospital, show the previous bill, the next visit date, and the number of days since the last visit.

SELECT patient_id, visit_id, visit_date, total_bill_kes, LAG(total_bill_kes) OVER (PARTITION BY patient_id ORDER BY visit_date) AS previous_bill, LEAD(visit_date) OVER (PARTITION BY patient_id ORDER BY visit_date) AS next_visit_date, visit_date - LAG(visit_date) OVER (PARTITION BY patient_id ORDER BY visit_date) AS days_since_last FROM visits WHERE patient_id IN (1, 2, 5, 7, 10) ORDER BY patient_id, visit_date; 

Each patient is its own partition, ordered by date. A patient’s first visit has no previous row, so previous_bill and days_since_last are NULL; their last visit has no next row, so next_visit_date is NULL. In PostgreSQL, subtracting two dates returns the number of days.

1. FIRST_VALUE() and LAST_VALUE() What they do: return the value of an expression from the first or last row of the window frame. Why they are used: compare every row to the beginning or end of a sequence, for example to a doctor’s first bill of the period. The classic trap: because the default frame stops at the current row, LAST_VALUE with only an ORDER BY simply returns the current row’s own value. You must widen the frame to ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING. Scenario: for Dr. Peter Otieno (103), show each visit’s bill alongside his first and last bill of the period.

SELECT visit_id, visit_date, total_bill_kes, FIRST_VALUE(total_bill_kes) OVER w AS first_bill, LAST_VALUE(total_bill_kes) OVER w AS last_bill_wrong, LAST_VALUE(total_bill_kes) OVER (PARTITION BY doctor_id ORDER BY visit_date ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_bill_correct FROM visits WHERE doctor_id = 103 WINDOW w AS (PARTITION BY doctor_id ORDER BY visit_date) ORDER BY visit_date; 

The named window w (defined with the WINDOW clause) is reused to avoid repetition. last_bill_wrong uses the default frame; last_bill_correct uses the full partition.
A related function, NTH_VALUE(col, n), returns the n-th row of the frame and has the same frame caveat.

Aggregate window functions: SUM, AVG, COUNT, MIN, MAX

The familiar aggregates become window functions when followed by OVER. They calculate across the window but keep every row. Without ORDER BY they see the whole partition; with ORDER BY they become running calculations.
Per-partition aggregates (no ORDER BY)
Scenario: for Dr. Faith Chebet’s visits, show each bill next to her visit count, total, average, smallest and largest bill, plus each visit’s share of her total.

SELECT visit_id, total_bill_kes, COUNT(*) OVER (PARTITION BY doctor_id) AS doctor_visits, SUM(total_bill_kes) OVER (PARTITION BY doctor_id) AS doctor_total, ROUND(AVG(total_bill_kes) OVER (PARTITION BY doctor_id), 2) AS doctor_avg, MIN(total_bill_kes) OVER (PARTITION BY doctor_id) AS doctor_min, MAX(total_bill_kes) OVER (PARTITION BY doctor_id) AS doctor_max, ROUND(100.0 * total_bill_kes / SUM(total_bill_kes) OVER (PARTITION BY doctor_id), 1) AS pct_of_doctor_total FROM visits WHERE doctor_id = 104 ORDER BY visit_id; 

The five aggregates all see the same six rows (doctor 104’s visits), so every row repeats the same count, total, average, minimum and maximum. The percentage column shows why this is powerful: a row-level value divided by a group-level value, with no join or subquery.

SQL Joins

1. Why joins exist Our database is normalised: a visit stores only doctor_id, not the doctor’s name. To produce a readable report you must combine tables, and a join does that by matching rows on a related column, normally a foreign key to a primary key (visits.doctor_id = doctors.doctor_id). Join Returns Typical question

• INNER JOIN
  Only rows that match in both tables
  Visits with their doctor and department

• LEFT JOIN

All rows from the left table, plus matches from the right (NULL if none)
Every doctor, even those with no emergency visits

• RIGHT JOIN
  All rows from the right table, plus matches from the left
  The mirror image of LEFT JOIN

• FULL OUTER JOIN

All rows from both tables, matched where possible
What is in one data set but not the other

• CROSS JOIN
  Every combination of rows (Cartesian product)
  Build a complete grid of doctors × admission types

• SELF JOIN

A table joined to itself (any join type)
Compare rows of the same table, such as repeat visits

1. INNER JOIN What it does: keeps only rows that have a match on both sides. Rows without a match are dropped silently. Why it is used: it is the default way to attach descriptive information to a fact table when every row is expected to have a match. Scenario: hospital management wants visits and revenue by department and doctor.

SELECT dep.department_name, d.doctor_name, COUNT(*) AS visits, SUM(v.total_bill_kes) AS revenue_kes FROM visits v INNER JOIN doctors d ON d.doctor_id = v.doctor_id INNER JOIN department dep ON dep.department_id = v.department_id GROUP BY dep.department_name, d.doctor_name ORDER BY revenue_kes DESC; 

Each of the 30 visits is matched to exactly one doctor and one department (both are NOT NULL foreign keys), then grouped. INNER is optional; plain JOIN means the same thing.

1. LEFT JOIN (LEFT OUTER JOIN) What it does: returns every row from the left table. Where the right table has no match, its columns are NULL. Why it is used: reports that must list all members of a group, including those with zero activity, and anti-joins that find rows with no match. Scenario 1: list every doctor with the number of emergency visits, including doctors with none.

SELECT d.doctor_name, COUNT(v.visit_id) AS emergency_visits FROM doctors d LEFT JOIN visits v ON v.doctor_id = d.doctor_id AND v.admission_type = 'Emergency' GROUP BY d.doctor_name; 

The emergency filter sits in the ON clause, not in WHERE. This matters: if you put WHERE v.admission_type = 'Emergency', doctors without emergencies would be removed and the query would behave like an inner join. We count v.visit_id rather than * because COUNT(*) would count the NULL placeholder row as 1.

Scenario 2 (anti-join): which patients have never been admitted as inpatients?

SELECT p.patient_id, p.patient_name FROM patients p LEFT JOIN visits v ON v.patient_id = p.patient_id AND v.admission_type = 'Inpatient' WHERE v.visit_id IS NULL; 

Patients with no inpatient visit get a NULL visit_id from the left join, and the WHERE keeps exactly those. Nine patients (3, 4, 10, 14, 15, 16, 22, 23, 24) have inpatient visits, so 16 patients are returned: Amina Otieno, John Kariuki, Esther Achieng, Mohamed Ali, Lucy Wambui, Samuel Kiptoo, Mary Wairumu, Brian Omondi, Catherine Musyoka, Daniel Maina, Isaac Kamau, Jane Muthoni, Kevin Barasa, Lilian Adhiambo, Moses Cheruiyot and Josephine Wekesa.

1. RIGHT JOIN (RIGHT OUTER JOIN) What it does: the mirror of LEFT JOIN. All rows from the right table are kept. Why it is used: rarely, because any right join can be rewritten as a left join by swapping the table order, and most people find left joins easier to read. It is useful when you are adding a join to an existing query and the table you want to preserve is already on the right.

SELECT d.doctor_name, COUNT(v.visit_id) AS emergency_visits FROM visits v RIGHT JOIN doctors d ON v.doctor_id = d.doctor_id AND v.admission_type = 'Emergency' GROUP BY d.doctor_name; 

Here doctors is on the right, so all four doctors are preserved. The result is identical to Scenario 1 above. Compare the two queries to see that A LEFT JOIN B and B RIGHT JOIN A are the same thing.

1. FULL OUTER JOIN What it does: keeps all rows from both tables. Matching rows are combined; unmatched rows from either side appear with NULLs for the other side. Why it is used: comparing or reconciling two sets, such as this week versus last week, or system A versus system B. Scenario: which diagnoses were seen in only one of the two weeks? Week 1 is 1–7 June and week 2 is 8–15 June.

SELECT w1.diagnosis_id AS week1_diagnosis, w2.diagnosis_id AS week2_diagnosis FROM (SELECT DISTINCT diagnosis_id FROM visits WHERE visit_date BETWEEN '2026-06-01' AND '2026-06-07') w1 FULL OUTER JOIN (SELECT DISTINCT diagnosis_id FROM visits WHERE visit_date BETWEEN '2026-06-08' AND '2026-06-15') w2 ON w1.diagnosis_id = w2.diagnosis_id WHERE w1.diagnosis_id IS NULL OR w2.diagnosis_id IS NULL ORDER BY COALESCE(w1.diagnosis_id, w2.diagnosis_id); 

Each side is a de-duplicated list of diagnosis IDs for one week. The full join lines them up; the WHERE keeps only the rows where one side is missing. Week 1 has 11 diagnoses, week 2 has 15, and 8 appear in both.

1. CROSS JOIN

What it does: pairs every row of the first table with every row of the second. With 4 doctors and 3 admission types you get 12 rows. There is no ON clause.
Why it is used: generating complete grids (all combinations, including those with no data), calendars and scenario tables. It is dangerous on large tables, since 1,000 × 1,000 rows is a million.
Scenario: a complete doctor × admission-type matrix, with zeros where a combination never occurred.

SELECT d.doctor_name, t.admission_type, COUNT(v.visit_id) AS visits FROM doctors d CROSS JOIN (VALUES ('Outpatient'), ('Inpatient'), ('Emergency')) AS t(admission_type) LEFT JOIN visits v ON v.doctor_id = d.doctor_id AND v.admission_type = t.admission_type GROUP BY d.doctor_name, t.admission_type ORDER BY d.doctor_name, t.admission_type; 

The cross join builds all 12 combinations, then the left join counts the real visits for each. Without the cross join, combinations with zero visits would simply be missing from the report.

1. SELF JOIN What it does: joins a table to itself, using two aliases so the database treats them as two separate copies. It is not a different keyword, just any join where both sides are the same table. Why it is used: to compare rows within one table: employees and their managers, pairs of records with the same attribute, or an event and its previous event. Scenario 1: find repeat visits by the same patient and the number of days between them.

SELECT p.patient_name, v1.visit_id AS first_visit, v2.visit_id AS return_visit, v2.visit_date - v1.visit_date AS days_between FROM visits v1 JOIN visits v2 ON v1.patient_id = v2.patient_id AND v2.visit_date > v1.visit_date JOIN patients p ON p.patient_id = v1.patient_id ORDER BY p.patient_name; 

v1 is the earlier visit and v2 the later one. The condition v2.visit_date > v1.visit_date stops a visit being paired with itself and prevents each pair appearing twice.
This gives the same facts as the LAG example in Section 3.4. Both approaches work; the window function is shorter and usually faster, while the self join is more flexible when the matching rule is complicated.
Scenario 2: pairs of patients who come from the same county.

SELECT a.patient_name AS patient_1, b.patient_name AS patient_2, a.county FROM patients a JOIN patients b ON a.county = b.county AND a.patient_id < b.patient_id ORDER BY a.county; 

Two pairs: Esther Achieng and Brian Omondi (Kisumu) and Amina Otieno and Isaac Kamau (Nairobi). The condition a.patient_id < b.patient_id removes mirror-image duplicates (A with B and B with A). This is also how you would query a hierarchy such as staff and their managers in a single table, which we revisit with recursion in the CTE section.

1. Choosing the right join

• Need only complete matches, and every row should match? INNER JOIN.

• Need every row of a main list, with optional details? LEFT JOIN (and WHERE right.key IS NULL to find the missing ones).

• Need to compare two lists and see what is unique to each? FULL OUTER JOIN.

• Need all combinations, or a skeleton to fill with counts? CROSS JOIN.

• Need to relate rows of a table to other rows of the same table? SELF JOIN.

• Think RIGHT JOIN? Swap the tables and use LEFT JOIN.
  Common mistakes: filtering the right table in WHERE after a left join (turns it into an inner join), forgetting the join condition (accidental cross join), and joining on a column that is not unique (row multiplication, which inflates sums). Always check that totals still match after adding a join.

Common Table Expressions (CTEs)

_ What is a CTE?_

A Common Table Expression is a named, temporary result set defined with the WITH keyword at the start of a statement. You can then use that name in the main query as if it were a table. It exists only for the duration of that one statement; nothing is stored in the database.
CTEs are used to break a complex query into readable steps, to reuse a result several times in one query, to filter on window-function results, and to walk hierarchies with recursion. In PostgreSQL 12 and later, a non-recursive CTE that is referenced once is normally inlined by the planner (like a subquery), and you can force the behaviour with_ AS MATERIALIZED or AS NOT MATERIALIZED._

Basic CTE

Scenario: produce a doctor performance summary. First calculate the numbers per doctor, then attach names and specialties.

WITH doctor_summary AS ( SELECT doctor_id, COUNT(*) AS visits, SUM(total_bill_kes) AS revenue_kes, ROUND(AVG(total_bill_kes), 2) AS avg_bill_kes FROM visits GROUP BY doctor_id ) SELECT d.doctor_name, d.doctor_specialty, s.visits, s.revenue_kes, s.avg_bill_kes FROM doctor_summary s JOIN doctors d ON d.doctor_id = s.doctor_id ORDER BY s.revenue_kes DESC; 

The CTE doctor_summary reduces 30 visit rows to 4 doctor rows. The main query then joins those 4 rows to doctors. Aggregating first and joining second is cleaner and often faster than joining first.

Multiple CTEs

You can define several CTEs in one WITH, separated by commas. Later CTEs can refer to earlier ones, so a query reads as a pipeline of steps.
Scenario: which doctors charge more per visit than the hospital average, and by how much?

WITH doctor_stats AS ( SELECT doctor_id, AVG(total_bill_kes) AS avg_bill FROM visits GROUP BY doctor_id ), hospital_avg AS ( SELECT AVG(total_bill_kes) AS avg_bill FROM visits ) SELECT d.doctor_name, ROUND(s.avg_bill, 2) AS doctor_avg, ROUND(h.avg_bill, 2) AS hospital_avg, ROUND(s.avg_bill - h.avg_bill, 2) AS above_average_by FROM doctor_stats s CROSS JOIN hospital_avg h JOIN doctors d ON d.doctor_id = s.doctor_id WHERE s.avg_bill > h.avg_bill ORDER BY above_average_by DESC; 

Step one finds each doctor’s average bill; step two finds the single hospital-wide average (KES 16,266.67). Because hospital_avg has exactly one row, a CROSS JOIN safely attaches it to every doctor. The WHERE then keeps doctors above the average.

CTEs with joins and aggregations

Scenario: the hospital board wants revenue by diagnosis category. The CTE joins the fact table to its dimension first; the final query aggregates.

WITH visit_detail AS ( SELECT v.visit_id, v.total_bill_kes, dg.diagnosis_category FROM visits v JOIN diagnosis dg ON dg.diagnosis_id = v.diagnosis_id ) SELECT diagnosis_category, COUNT(*) AS visits, SUM(total_bill_kes) AS revenue_kes FROM visit_detail GROUP ...