Window Functions, Joins, CTEs and Subqueries

Once you've got filtering and grouping down, SQL starts asking harder questions. Which doctor handles the most visits? Which department has the most unpaid bills? You can't answer those with a single SELECT ... WHERE. You need window functions, joins, CTEs and subqueries.

I worked through all four on a real dataset: 672 visits from a mock data: Hospital Health Records workbook, split into a star schema (visits_fact plus dim_patients, dim_doctors and dim_departments). Everything below is PostgreSQL.

Window functions: keep the rows, add the context

GROUP BY squashes rows together. A window function doesn't. It calculates across related rows and leaves every row in place, so you can see detail and group level numbers side by side.

SELECT doctor_id, visit_id, total_bill_kes,
       SUM(total_bill_kes) OVER (PARTITION BY doctor_id) AS doctor_total
FROM visits_fact;

ROW_NUMBER is great for "latest visit per doctor". RANK leaves gaps after ties (1, 2, 2, 4), while DENSE_RANK doesn't (1, 2, 2, 3). LAG and LEAD let you compare a row with its neighbour, like month on month revenue. One trap: with LAST_VALUE, always spell out the frame, because the default stops at the current row. For running totals, use ROWS rather than the default RANGE, or tied dates will make your numbers jump.

Joins: it's all about the unmatched rows

Every join type answers the same question: what happens to rows with no match? INNER drops them. LEFT keeps the left side's rows. FULL OUTER keeps both. CROSS pairs everything with everything, so use it deliberately.

The mistake that got me: putting a filter on the right hand table in WHERE instead of ON. It quietly turns your LEFT JOIN into an inner join.

SELECT d.doctor_name, COUNT(v.visit_id) AS visits_2025
FROM dim_doctors d
LEFT JOIN visits_fact v
  ON v.doctor_id = d.doctor_id AND v.visit_date >= '2025-01-01'
GROUP BY d.doctor_name;

Dr. Mutua had zero visits in 2025, and this is the version that shows him. Use COUNT(v.visit_id), not COUNT(*), or he'll show up with a count of 1.

CTEs: write SQL in the order you think

A CTE gives a query a name, so you can build on it step by step. One CTE aggregates, the next calculates, and you can test each on its own. It also gets around not being able to filter on a window function in WHERE. WITH RECURSIVE handles hierarchies and can generate gap free calendars, but always give it a stop condition. A CTE pipeline is how I found that 41.1% of Oncology's billing was still pending.

Subqueries: a question inside a question

A scalar subquery returns one value ("bills above the average"). IN, ANY and ALL work with lists. A correlated subquery references the outer row, like bills above their own department's average, which is handy but can be slow on big tables.

For "not in this list" checks, prefer NOT EXISTS. A single NULL in a NOT IN list makes the whole query return zero rows, with no error to warn you.

Quick comparison

Tool	Core idea	Reach for it when...	Watch out for
Window functions	Calculate across rows without collapsing them	You need ranks, running totals or previous/next values	LAST_VALUE frame; ROWS vs RANGE; no filtering in WHERE
Joins	Combine tables by matching keys	Data lives in more than one table	Filters in WHERE on outer joins; accidental CROSS JOIN
CTEs	Name a query, then build on it	Logic has several steps or recursion	Recursion with no stop condition
Subqueries	A query inside a query	A value or list depends on another query	NOT IN with NULLs; slow correlated queries

Three habits worth keeping

Know your grain: decide what one row of the result should represent before you write anything. Test each stage on its own. And try your query on the awkward rows, like ties, NULLs and groups with no data, because that's where the bugs hide.

What was the SQL mistake that taught you the most? Let's learn from one another.