What this article covers

  1. Window functions (ROW_NUMBER, RANK, LAG, NTILE, running totals and frames)
  2. Joins (INNER, LEFT, RIGHT, FULL OUTER, CROSS, SELF)
  3. Common table expressions (basic, multiple, recursive)
  4. Subqueries (scalar, correlated, IN, EXISTS, ANY, ALL)

The dataset

The database lives in a schema called morning and follows a star-style design. One fact table, visits, connects to dimension tables:

  • visits: one row per hospital visit (date, bill, admission type, stay length, triage level, satisfaction score)
  • patients, doctors, department, wards, diagnosis, procedures: descriptive tables linked by IDs

It has 30 visits, 25 patients and 4 doctors. Run SET search_path TO morning; first so the queries below work as written.

1. SQL Window Functions

A normal aggregate with GROUP BY collapses rows. Summing bills by department gives 4 rows, one per department. A window function does the same kind of calculation but keeps every row, so each visit can sit next to its department total. That is the idea behind everything below.

How OVER, PARTITION BY, ORDER BY and frames work

  • OVER() turns a function into a window function. An empty OVER() means "use the whole result set".
  • PARTITION BY splits rows into groups, and the calculation restarts in each group. It works like GROUP BY without collapsing rows. PARTITION BY doctor_id restarts for each doctor.
  • ORDER BY inside OVER sets the row order within each partition. Ranking functions, LAG and LEAD need it, and it turns aggregates like SUM into running totals.
  • The window frame is which rows around the current row are included, for example ROWS BETWEEN 2 PRECEDING AND CURRENT ROW. With an ORDER BY and no frame, the default runs from the first row of the partition to the current row and includes ties. With no ORDER BY, the frame is the whole partition.
  • When it runs: after WHERE, GROUP BY and HAVING, and before the final ORDER BY. This means you can't filter on a window function in WHERE. Wrap the query in a subquery or CTE and filter outside it.

ROW_NUMBER(), RANK() and DENSE_RANK()

All three number rows by an order. They differ in how they treat ties.

SELECT visit_id, patient_id, length_of_stay_days AS stay, ROW_NUMBER() OVER (ORDER BY length_of_stay_days DESC) 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 admission_type IN ('Inpatient', 'Emergency') ORDER BY length_of_stay_days DESC, visit_id;

Look at the two visits tied at 3 days:

  • ROW_NUMBER never ties. It gives 4 and 5, and which tied row gets which number is arbitrary, so add a tiebreaker like visit_id to the ORDER BY if you need it stable.
  • RANK gives both rows 4, then skips to 6.
  • DENSE_RANK gives both rows 4, then continues with 5, with no gaps.

Practical use: each doctor's most recent visit. ROW_NUMBER is the right choice because you want exactly one row per doctor.

SELECT * FROM ( SELECT doctor_id, visit_id, visit_date, total_bill_kes, ROW_NUMBER() OVER (PARTITION BY doctor_id ORDER BY visit_date DESC, visit_id DESC) AS rn FROM visits ) t WHERE rn = 1;

This returns 4 rows: visits 1030 (doctor 101), 1029 (102), 1027 (103) and 1028 (104). The subquery is needed because of the "no window functions in WHERE" rule.

NTILE()

NTILE(n) splits the ordered rows into n roughly equal buckets. This puts every visit into a billing quartile.

SELECT visit_id, total_bill_kes, NTILE(4) OVER (ORDER BY total_bill_kes) AS bill_quartile FROM visits;

With 30 visits, the buckets hold 8, 8, 7 and 7 rows, because the extra rows go to the earlier buckets. Quartile 1 covers bills from KES 1,800 to 3,500, and quartile 4 covers KES 28,000 to 82,000. It answers questions like "which visits are in the top 25% by cost?"

LAG() and LEAD()

LAG reads the previous row in the partition, and LEAD reads the next one. Five patients came back for a second visit, so we can compare their bills.

SELECT patient_id, visit_id, visit_date, total_bill_kes, LAG(total_bill_kes) OVER (PARTITION BY patient_id ORDER BY visit_date) AS prev_bill, LEAD(total_bill_kes) OVER (PARTITION BY patient_id ORDER BY visit_date) AS next_bill FROM visits WHERE patient_id IN (1, 5, 10) ORDER BY patient_id, visit_date;

The first visit has no previous row, so LAG returns NULL. You can pass an offset and a default, as in LAG(total_bill_kes, 1, 0). Then total_bill_kes - LAG(total_bill_kes, 1, 0) OVER (...) gives the change between visits. Patient 5's second bill is KES 3,600 higher than the first.

FIRST_VALUE() and LAST_VALUE()

These return the first or last value in the window frame. Here is doctor 101's first and last bill, with a common mistake shown next to the fix.

SELECT doctor_id, 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_default, LAST_VALUE(total_bill_kes) OVER (PARTITION BY doctor_id ORDER BY visit_date, visit_id ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) AS last_full FROM visits WHERE doctor_id = 101 WINDOW w AS (PARTITION BY doctor_id ORDER BY visit_date, visit_id);

last_default just repeats the current row's bill. The default frame stops at the current row, so the "last" row it can see is the current one. This is the biggest LAST_VALUE trap. To get the real last value, widen the frame to UNBOUNDED FOLLOWING, as last_full does. FIRST_VALUE doesn't have this problem because the frame always starts at the first row.

SUM(), AVG(), COUNT(), MIN() and MAX() as window functions

Any aggregate becomes a window function when you add OVER. This lets you compare each visit to its department.

SELECT visit_id, department_id, total_bill_kes, SUM(total_bill_kes) OVER (PARTITION BY department_id) AS dept_total, ROUND(AVG(total_bill_kes) OVER (PARTITION BY department_id), 2) AS dept_avg, COUNT(*) OVER (PARTITION BY department_id) AS dept_visits, MIN(total_bill_kes) OVER (PARTITION BY department_id) AS dept_min, MAX(total_bill_kes) OVER (PARTITION BY department_id) AS dept_max, ROUND(100.0 * total_bill_kes / SUM(total_bill_kes) OVER (PARTITION BY department_id), 1) AS pct_of_dept FROM visits WHERE visit_id <= 1010;

For department 10 (Cardiology), the three rows returned are:

Window functions only see rows that survive WHERE. Cardiology's real total across all 30 visits is KES 210,100. Here it shows 83,500 because the query is limited to the first 10 visits. Remove the WHERE to get full-hospital figures. The pct_of_dept column shows a single heart-failure admission making up 91% of this slice of the department's revenue.

Compare this with GROUP BY, which would return only 4 rows (one per department) with totals of 210,100, 97,800, 105,200 and 74,900. The window version keeps all the detail and adds the totals alongside it.

Window frames: running totals and moving averages 

SELECT visit_id, visit_date, total_bill_kes, SUM(total_bill_kes) OVER (ORDER BY visit_date) AS running_default, SUM(total_bill_kes) OVER (ORDER BY visit_date, visit_id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_rows, ROUND(AVG(total_bill_kes) OVER (ORDER BY visit_date, visit_id ROWS BETWEEN 2 PRECEDING AND CURRENT ROW), 1) AS moving_avg_3 FROM visits ORDER BY visit_date, visit_id LIMIT 6;

  • running_default uses the default frame, which includes ties. Visits 1001 and 1002 share a date, so both show 6,000.
  • running_rows uses ROWS, which counts physical rows, so it grows one visit at a time. For a true row-by-row running total, use ROWS and add a tiebreaker to the ORDER BY.
  • moving_avg_3 averages the current row and the 2 before it, giving a rolling 3-visit average. The first rows average over fewer rows because there aren't 2 previous ones yet.

2. SQL Joins

In this database, the visits table stores only IDs. A patient's name lives in patients, a doctor's name in doctors, and a diagnosis name in diagnosis. Almost every useful question needs a join, which combines rows from two tables based on a related column.

Join Rows kept
INNER JOIN Only rows that match in both tables
LEFT JOIN All rows from the left table, plus matches from the right (NULL where none)
RIGHT JOIN All rows from the right table, plus matches from the left
FULL OUTER JOIN All rows from both tables, matched where possible
CROSS JOIN Every row of one table paired with every row of the other
SELF JOIN A table joined to itself, using two aliases

INNER JOIN

An inner join keeps only rows with a match on both sides. Use it when you only care about records that exist in all the tables involved. Here it builds a readable visit report from four tables.

SELECT v.visit_id, p.patient_name, d.doctor_name, dg.diagnosis_name, v.total_bill_kes FROM visits v INNER JOIN patients p ON p.patient_id = v.patient_id INNER JOIN doctors d ON d.doctor_id = v.doctor_id INNER JOIN diagnosis dg ON dg.diagnosis_id = v.diagnosis_id ORDER BY v.visit_id LIMIT 5;

The full query returns all 30 visits, because every visit has a valid patient, doctor and diagnosis. Only unmatched rows would disappear.

LEFT JOIN

A left join keeps every row from the left table and fills in NULL where the right table has no match. In this data every diagnosis was used and every patient visited, so a plain left join would look the same as an inner join. To see the difference, I asked a real question: which diagnoses led to an inpatient admission?

SELECT dg.diagnosis_id, dg.diagnosis_name, v.visit_id, v.admission_type FROM diagnosis dg LEFT JOIN visits v ON v.diagnosis_id = dg.diagnosis_id AND v.admission_type = 'Inpatient' ORDER BY dg.diagnosis_id, v.visit_id;

The result has 20 rows: 9 matched visits and the rest NULL. Two things to notice:

  • There are only 18 diagnoses, but the result has 20 rows. Fracture and Heart Failure each matched two inpatient visits, so they repeat. A join can change the number of rows in your result.
  • The admission_type filter is in the ON clause on purpose. If you move it to WHERE, the NULL rows get filtered out and the query behaves like an inner join.

The anti-join pattern. Add WHERE v.visit_id IS NULL to keep only rows with no match:

SELECT dg.diagnosis_id, dg.diagnosis_name FROM diagnosis dg LEFT JOIN visits v ON v.diagnosis_id = dg.diagnosis_id AND v.admission_type = 'Inpatient' WHERE v.visit_id IS NULL;

This returns 11 diagnoses that never led to an inpatient admission: Hypertension, Malaria, Diabetes, Asthma, Back Pian, Gastritis, Flu, Anaemia, Sprain, Migraine and Chest Pain.

RIGHT JOIN

A right join is a left join with the tables swapped. It keeps every row from the right table.

SELECT COUNT(*) AS total_rows, COUNT(v.visit_id) AS matched_rows FROM visits v RIGHT JOIN diagnosis dg ON v.diagnosis_id = dg.diagnosis_id AND v.admission_type = 'Inpatient';

The result is 20 total rows and 9 matched rows, exactly the same as the left join above. Most people rewrite right joins as left joins by swapping the table order, because "start from the main table and join outward" is easier to read.

FULL OUTER JOIN

A full outer join keeps everything from both sides. Rows with no match on one side get NULLs there. It is useful when you are comparing two sets and want to see what is in one, the other, or both. Here I compare diagnoses seen in inpatient visits with diagnoses seen in outpatient visits.

SELECT i.diagnosis_id AS inpatient_dx, o.diagnosis_id AS outpatient_dx, CASE WHEN i.diagnosis_id IS NOT NULL AND o.diagnosis_id IS NOT NULL THEN 'both' WHEN i.diagnosis_id IS NOT NULL THEN 'inpatient only' ELSE 'outpatient only' END AS seen_as FROM (SELECT DISTINCT diagnosis_id FROM visits WHERE admission_type = 'Inpatient') i FULL OUTER JOIN (SELECT DISTINCT diagnosis_id FROM visits WHERE admission_type = 'Outpatient') o ON i.diagnosis_id = o.diagnosis_id;

seen_as diagnoses
both 1 (Typhoid, 506)
inpatient only 6
outpatient only 10

The result has 17 rows. Typhoid is the only diagnosis that appeared in both inpatient and outpatient visits. Chest Pain (518) is missing from both sides because its only visit was an Emergency.

CROSS JOIN

A cross join pairs every row of one table with every row of the other, with no ON condition. 4 departments times 4 triage levels gives 16 combinations.

SELECT g.department_name, g.triage_level, COUNT(v.visit_id) AS visits FROM ( SELECT d.department_id, d.department_name, t.triage_level FROM department d CROSS JOIN (VALUES ('Red'), ('Orange'), ('Yellow'), ('Green')) AS t(triage_level) ) g LEFT JOIN visits v ON v.department_id = g.department_id AND v.triage_level = g.triage_level GROUP BY g.department_name, g.triage_level;

The practical use is building a complete grid first, then left joining the data onto it. Without the cross join, combinations with no visits (like Cardiology with Orange) would be missing from the report instead of showing 0. Be careful with cross joins on big tables, because the row count is the product of both sizes.

SELF JOIN

A self join joins a table to itself using two aliases, so you can compare rows within the same table. Which patients 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;

The a.patient_id < b.patient_id condition does two jobs. It stops a patient being paired with themselves, and it stops each pair appearing twice (A-B and B-A).

Another example compares doctors by experience:

SELECT a.doctor_name AS more_experienced, b.doctor_name AS less_experienced, a.doctor_experience_years - b.doctor_experience_years AS gap FROM doctors a JOIN doctors b ON a.doctor_experience_years > b.doctor_experience_years ORDER BY gap DESC LIMIT 3;

Which join should I use?

  • Only records that exist in both tables: INNER JOIN
  • Everything from the main table, matched or not: LEFT JOIN
  • Find records with no match: LEFT JOIN ... WHERE right_key IS NULL
  • Compare two sets and see what is in one, the other, or both: FULL OUTER JOIN
  • Build every combination, such as a report grid: CROSS JOIN
  • Compare rows within one table: SELF JOIN

3. Common Table Expressions (CTEs)

A CTE is a named, temporary result that you define with WITH at the top of a query and then use like a table in the main query. It only exists for that one query. The main benefit is readability: instead of nesting one query inside another, you name each step and read the query from top to bottom.

Basic CTE

This query finds the number of visits and total revenue for each department. The CTE does the aggregation, and the main query adds the department names.

WITH dept_revenue AS ( SELECT department_id, SUM(total_bill_kes) AS revenue, COUNT(*) AS visits FROM visits GROUP BY department_id ) SELECT d.department_name, r.visits, r.revenue FROM dept_revenue r JOIN department d ON d.department_id = r.department_id ORDER BY r.revenue DESC;

dept_revenue is calculated first, then joined to department like a normal table. Cardiology brings in the most revenue.

Multiple CTEs

You can define several CTEs in one WITH clause, separated by commas. Later ones can use earlier ones. This query finds doctors whose average patient satisfaction is above the hospital average.

WITH doctor_stats AS ( SELECT doctor_id, COUNT(*) AS visits, SUM(total_bill_kes) AS revenue, ROUND(AVG(patient_satisfaction_score), 2) AS avg_satisfaction FROM visits GROUP BY doctor_id ), hospital_avg AS ( SELECT ROUND(AVG(patient_satisfaction_score), 2) AS avg_satisfaction FROM visits ) SELECT d.doctor_name, s.visits, s.revenue, s.avg_satisfaction, h.avg_satisfaction AS hospital_avg FROM doctor_stats s JOIN doctors d ON Source：https://dev.to/asma_salah_2e2a6926b57a2e/sql-window-functions-joins-ctes-and-subqueries-explained-with-a-hospital-dataset-58j
My favorites
PreviousDreamyNextFull-text search in Laravel on both SQLite and MySQL: FTS5, FULLTEXT and one interface

Related posts

How to upgrade Postgres in Docker to 18, and be ready for 19: dump and restore or pg_upgrade --link

How to upgrade Postgres in Docker to 18, and be ready for 19: dump and restore or pg_upgrade --link

dev.to Docker
Docker Compose: Run Your App and Its Dependencies Together

Docker Compose: Run Your App and Its Dependencies Together

dev.to Docker
Show HN: Vaire, Self-Hosting Postgres Through the One Glorious CLI

Show HN: Vaire, Self-Hosting Postgres Through the One Glorious CLI

Hacker News Show
Gabriele Bartolini: CloudNativePG reaches CNCF Incubation: another rung on the ladder

Gabriele Bartolini: CloudNativePG reaches CNCF Incubation: another rung on the ladder

Planet PostgreSQL
pandas chunks fixed my memory error, then quietly broke my averages

pandas chunks fixed my memory error, then quietly broke my averages

dev.to
Postgres LISTEN/NOTIFY vs Redis Pub/Sub: Latency, Write Cost and Failure Cases, Tested

Postgres LISTEN/NOTIFY vs Redis Pub/Sub: Latency, Write Cost and Failure Cases, Tested

dev.to Node