SQL Window Functions, Joins, CTEs and Subqueries Explained with a Hospital Dataset

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.

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;

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 d.doctor_id = s.doctor_id
CROSS JOIN hospital_avg h
WHERE s.avg_satisfaction > h.avg_satisfaction
ORDER BY s.avg_satisfaction DESC;

The hospital average is 4.28. Dr. Sarah Wanjiku (4.00) and Dr. Peter Otieno (4.23) are below it, so they are filtered out. hospital_avg returns a single row, so a CROSS JOIN attaches it to every doctor row for comparison.

CTEs with joins and aggregations

This query looks at admitted patients (inpatient and emergency) and summarizes them by diagnosis category.

WITH admitted AS (
  SELECT * FROM visits
  WHERE admission_type IN ('Inpatient', 'Emergency')
)
SELECT dg.diagnosis_category,
       COUNT(*) AS admissions,
       ROUND(AVG(a.length_of_stay_days), 1) AS avg_stay_days,
       SUM(a.total_bill_kes) AS revenue
FROM admitted a
JOIN diagnosis dg ON dg.diagnosis_id = a.diagnosis_id
GROUP BY dg.diagnosis_category
ORDER BY revenue DESC;

Chronic diseases (heart failure) bring the most revenue and the longest stays. The result also exposes a data quality problem: "Infectious" and "Infectious Disease" appear as separate categories, because Typhoid was entered with a shorter category name. Grouping makes inconsistent labels like this easy to spot.

Recursive CTEs

A recursive CTE refers to itself. It has two parts joined by UNION ALL:

• The anchor member runs once and gives the starting rows.
• The recursive member refers to the CTE's own name. It runs again and again on the rows from the previous step, and the CTE stops when a step returns no new rows.

Here is the simplest possible example, counting from 1 to 5:

WITH RECURSIVE n AS (
  SELECT 1 AS num                          -- anchor
  UNION ALL
  SELECT num + 1 FROM n WHERE num < 5      -- recursive step with a stopping condition
)
SELECT * FROM n;

The result is the numbers 1, 2, 3, 4, 5. The WHERE num < 5 is the stopping condition. Without one, the query would loop forever, so always include it.

A practical use in the hospital data: a calendar. Visits exist for 1 to 15 June. If I group visits by date, days with no visits simply don't appear. A recursive CTE can generate every date, and a LEFT JOIN fills the gaps with zeros.

WITH RECURSIVE calendar AS (
  SELECT DATE '2026-06-01' AS day
  UNION ALL
  SELECT day + 1 FROM calendar WHERE day < DATE '2026-06-17'
)
SELECT c.day, COUNT(v.visit_id) AS visits, COALESCE(SUM(v.total_bill_kes), 0) AS revenue
FROM calendar c
LEFT JOIN visits v ON v.visit_date = c.day
GROUP BY c.day
ORDER BY c.day;

The hospital had 2 visits every day from 1 to 15 June. The calendar adds 16 and 17 June with zero visits, which a plain GROUP BY would have left out. Recursive CTEs are also the standard tool for hierarchical data such as organization charts or category trees, where each row points to its parent.

CTEs vs subqueries

Both can answer the same question. Here is "which visits cost more than the average bill?" done both ways.

-- Subquery
SELECT visit_id, total_bill_kes
FROM visits
WHERE total_bill_kes > (SELECT AVG(total_bill_kes) FROM visits);

-- CTE
WITH avg_bill AS (SELECT AVG(total_bill_kes) AS avg_bill FROM visits)
SELECT v.visit_id, v.total_bill_kes
FROM visits v, avg_bill
WHERE v.total_bill_kes > avg_bill.avg_bill;

Both return the same 10 visits, with the average bill at KES 16,266.67. The highest are visit 1017 (KES 82,000) and visit 1010 (KES 76,000). The differences show up as queries get bigger:

Reuse example. This query uses dept_revenue twice, once for each department's revenue and once for the hospital total:

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

Cardiology accounts for 43.1% of the hospital's KES 488,000 total revenue.

On performance: CTEs are not automatically faster or slower. In PostgreSQL 12 and later, a simple non-recursive CTE that is used once is generally treated like an inline subquery, and you can control this with MATERIALIZED or NOT MATERIALIZED. Older versions always computed the CTE separately first. So choose between them for readability, not speed.

4. SQL Subqueries

A subquery is a query inside another query, written in parentheses. It can sit in the SELECT, FROM or WHERE clause, and the outer query uses its result. The main kinds are:

• Scalar subquery: returns exactly one value (one row, one column)
• Non-correlated subquery: runs on its own, independent of the outer query
• Correlated subquery: refers to a column from the outer query, so it depends on the current row
• Nested subquery: a subquery inside another subquery

Scalar subqueries

A scalar subquery returns a single value, so you can use it anywhere a single value fits. Which visit had the highest bill?

SELECT visit_id, patient_id, total_bill_kes
FROM visits
WHERE total_bill_kes = (SELECT MAX(total_bill_kes) FROM visits);

The subquery returns 82000, and the outer query finds the visit with that bill: visit 1017, patient 16, KES 82,000.

A scalar subquery in the SELECT list adds a computed column. This shows how far each bill is from the hospital average:

SELECT visit_id, total_bill_kes,
  (SELECT ROUND(AVG(total_bill_kes), 2) FROM visits) AS hospital_avg,
  ROUND(total_bill_kes - (SELECT AVG(total_bill_kes) FROM visits), 2) AS diff
FROM visits
ORDER BY visit_id
LIMIT 5;

If a scalar subquery returns more than one row, PostgreSQL throws an error.

Non-correlated subqueries and IN

A non-correlated subquery runs once on its own, and the outer query uses its result. Both scalar examples above are non-correlated. Here is a list version: which patients have had an inpatient visit?

SELECT patient_id, patient_name
FROM patients
WHERE patient_id IN (
  SELECT patient_id FROM visits WHERE admission_type = 'Inpatient'
);

The inner query produces a list of patient IDs, and IN keeps patients whose ID is in that list. NOT IN gives the opposite: 16 patients with no inpatient visit. Be careful with NOT IN. If the subquery returns even one NULL, NOT IN returns no rows at all. It is safe here because visits.patient_id can't be NULL, but NOT EXISTS (below) avoids the problem entirely.

Correlated subqueries

A correlated subquery uses a column from the outer query, so it is evaluated against each outer row. Which visits cost more than that doctor's own average bill?

SELECT v.visit_id, v.doctor_id, v.total_bill_kes,
  (SELECT ROUND(AVG(v2.total_bill_kes), 2)
   FROM visits v2
   WHERE v2.doctor_id = v.doctor_id) AS doctor_avg
FROM visits v
WHERE v.total_bill_kes > (
  SELECT AVG(v2.total_bill_kes)
  FROM visits v2
  WHERE v2.doctor_id = v.doctor_id
)
ORDER BY v.doctor_id, v.total_bill_kes DESC;

For each visit, the subquery recalculates the average for that visit's doctor (v2.doctor_id = v.doctor_id). That is what makes it correlated. A non-correlated subquery could only give one overall average, not one per doctor. (Window functions from Part 1 can answer this too, with AVG(...) OVER (PARTITION BY doctor_id).)

EXISTS and NOT EXISTS

EXISTS is true if the subquery returns at least one row. What the subquery selects doesn't matter, so SELECT 1 is the convention. Which patients have had an emergency visit?

SELECT p.patient_id, p.patient_name
FROM patients p
WHERE EXISTS (
  SELECT 1 FROM visits v
  WHERE v.patient_id = p.patient_id
    AND v.admission_type = 'Emergency'
);

The result is one patient: Peter Ouma (ID 10). NOT EXISTS flips it. Patients with no inpatient visit, using NOT EXISTS, gives 16 patients, the same as NOT IN, but without the NULL problem.

IN vs EXISTS:

• IN compares a value against a list. It reads naturally for short, simple lists.
• EXISTS only checks whether a matching row exists, and can stop at the first one. It is the safer choice for "does a related record exist?" questions, and NOT EXISTS is safer than NOT IN.

ANY and ALL

ANY and ALL compare a value against every value the subquery returns. ALL means the comparison must be true for every value. ANY means it must be true for at least one.

Which visits cost more than every Pediatrics visit?

SELECT visit_id, total_bill_kes
FROM visits
WHERE total_bill_kes > ALL (
  SELECT total_bill_kes FROM visits WHERE department_id = 40
);

Pediatrics bills range from KES 1,800 to 42,000, so only two visits beat all of them: visit 1017 (82,000) and visit 1010 (76,000). > ALL works like > MAX.

SELECT COUNT(*)
FROM visits
WHERE total_bill_kes > ANY (
  SELECT total_bill_kes FROM visits WHERE department_id = 40
);

This returns 29. A bill only has to beat at least one Pediatrics bill, so the cutoff is the smallest one (1,800). Only the KES 1,800 visit itself fails. > ANY works like > MIN.

= ANY is the same as IN. For example, visits handled by doctors with more than 10 years of experience:

SELECT visit_id, doctor_id
FROM visits
WHERE doctor_id = ANY (
  SELECT doctor_id FROM doctors WHERE doctor_experience_years > 10
);

This returns 15 visits, from Dr. Brian Mwangi (12 years) and Dr. Peter Otieno (15 years).

Subqueries in SELECT, FROM and WHERE

• In SELECT: a scalar subquery adds a computed column, like the hospital_avg column above.
• In WHERE: filters rows using a comparison, IN, EXISTS, ANY or ALL, as in the examples above.
• In FROM: the subquery acts as a temporary table, called a derived table. PostgreSQL requires it to have an alias.

SELECT d.doctor_name, t.visits, t.avg_bill
FROM (
  SELECT doctor_id, COUNT(*) AS visits, ROUND(AVG(total_bill_kes), 2) AS avg_bill
  FROM visits
  GROUP BY doctor_id
) t
JOIN doctors d ON d.doctor_id = t.doctor_id
ORDER BY t.avg_bill DESC;

The subquery summarizes visits per doctor first, then the outer query joins it to doctor names.

Nested subqueries

A subquery can contain another subquery. Which patients were seen in the Cardiology department?

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'
  )
);

It runs from the inside out. The innermost query finds the Cardiology department ID, the middle one finds the patients with visits in that department, and the outer one gets their names. This works, but more than two levels of nesting gets hard to read. At that point a CTE or joins is usually clearer.

Conclusion

Working through these four topics on one dataset showed me how they relate:

• Window functions add calculations (ranks, running totals, comparisons with the previous row) without losing row detail.
• Joins combine tables, and they can change the number of rows in a result, as the left join on diagnoses showed.
• CTEs let me name each step of a query so it reads top to bottom, and they are the only option when I need recursion.
• Subqueries are the quickest way to answer a one-off question inside another query, and correlated subqueries can compare each row against its own group.

Many questions can be answered more than one way. "Which visits cost more than their doctor's average?" works as a correlated subquery or as a window function, and either can be wrapped in a CTE. The skill is choosing the version that is easiest to read and check. The checking matters too: running these queries also exposed data problems I would have missed otherwise, like the two spellings of "Infectious" in the diagnosis categories.

References

• PostgreSQL Tutorial: Window Functions
• PostgreSQL: Window Function Calls
• PostgreSQL Window Functions (Neon)
• PostgreSQL: Table Expressions and Joins
• PostgreSQL: WITH Queries (Common Table Expressions)
• PostgreSQL: Subquery Expressions