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 BY diagnosis_category
ORDER BY revenue_kes DESC;

Recursive CTEs

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

1. The anchor member, which runs once and produces the starting rows.
2. The recursive member, which refers to the CTE’s own name and runs repeatedly on the rows produced in the previous round, until it returns no new rows. You must use the RECURSIVE keyword in PostgreSQL, and you m_ust make sure the recursion ends, either through a condition in the recursive member or a depth limit_, otherwise the query runs forever. Example 1: generating a calendar to find days with no visits. Our data stops on 15 June, so we extend the calendar to 17 June to show how gaps appear.

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
FROM calendar c
LEFT JOIN visits v ON v.visit_date = c.day
GROUP BY c.day
ORDER BY c.day;

The anchor produces 1 June; each round adds one more day until 17 June is reached, giving 17 rows. A left join then counts visits per day, with 0 for days that have none.

CTEs versus subqueries

Both let you use the result of one query inside another, and for simple cases they give identical results and plans. They differ in readability, reuse and capability.
Performance
Usually the same; PostgreSQL 12+ may inline it, older versions always materialised it
Optimiser can always inline it
Rule of thumb: use a CTE when the logic has several steps, when you need the same result twice, or when you need recursion. Use a subquery for a short, single-use filter such as WHERE x IN (SELECT ...), or when you need a correlated check against each outer row.

SELECT d.doctor_name,
       ROUND(AVG(v.total_bill_kes), 2) AS doctor_avg
FROM visits v
JOIN doctors d ON d.doctor_id = v.doctor_id
GROUP BY d.doctor_name
HAVING AVG(v.total_bill_kes) > (SELECT AVG(total_bill_kes) FROM visits);

For a problem this small the subquery is more compact. The CTE wins as the number of steps grows.

SQL Subqueries

What is a subquery?

A subquery (inner query) is a SELECT statement nested inside another statement, always written in parentheses. The inner query produces a value, a list or a table, and the outer query uses it. Subqueries let you answer questions in two steps without creating temporary tables, for example: first find the average bill, then find the visits above it.
A subquery can appear in three places:
Clause
What the subquery returns
Example use
WHERE (or HAVING)
A single value, a list, or an existence test
Filter visits above the average bill
FROM
A table (called a derived table; it needs an alias)
Aggregate first, then aggregate the aggregates
SELECT
A single value per outer row (scalar)
Show a count beside each doctor

Scalar subqueries

What it is: a subquery that returns exactly one row and one column, so it can be used anywhere a single value is allowed. If it returns more than one row, PostgreSQL raises an error.
Scenario: list visits billed above the hospital average.

SELECT visit_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 returns one number, 16,266.67. The outer query compares each bill with it. This cannot be written as WHERE total_bill_kes > AVG(total_bill_kes), because aggregates are not allowed in WHERE.

Non-correlated (independent) subqueries

What it is: a subquery that does not refer to the outer query. It can run completely on its own, and the database evaluates it once. The scalar example above is non-correlated, and so is any IN (SELECT ...).
Scenario: list the patients who have ever been admitted as inpatients.

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

The inner query returns the list of patient IDs with an inpatient visit; the outer query keeps patients whose ID is in that list. Duplicates in the list do not matter.
Caution with NOT IN: if the inner list contains even one NULL, NOT IN returns no rows at all, because the comparison with NULL is unknown. Columns such as ward_id and diagnosis_id in visits are nullable, so prefer NOT EXISTS for “not in” logic.

Correlated subqueries

What it is: a subquery that refers to a column of the outer query, so it is conceptually re-evaluated for every outer row.
Scenario: find visits where the bill is higher than that same doctor’s own average bill.

SELECT v.visit_id, v.doctor_id, v.total_bill_kes
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.visit_id;

For each outer visit v, the inner query calculates the average for that visit’s doctor (v2.doctor_id = v.doctor_id) and compares. The comparison value changes from doctor to doctor: 23,344.44 for Dr. Mwangi, 10,866.67 for Dr. Wanjiku, 17,533.33 for Dr. Otieno and 12,483.33 for Dr. Chebet.

Nested subqueries

What it is: a subquery inside another subquery. Each level feeds the one above it.
Scenario: list the patients treated by the highest-earning doctor.

SELECT patient_name
FROM patients
WHERE patient_id IN (
        SELECT patient_id
        FROM visits
        WHERE doctor_id = (
                SELECT doctor_id
                FROM visits
                GROUP BY doctor_id
                ORDER BY SUM(total_bill_kes) DESC
                LIMIT 1));

From the inside out: the innermost query ranks doctors by revenue and returns the top one (doctor 101, KES 210,100). The middle query returns the IDs of patients that doctor saw (1, 2, 5, 10, 12 and 16). The outer query turns those IDs into names.

Subqueries with IN, EXISTS, ANY and ALL

IN
expression IN (subquery) is true if the value equals any value the subquery returns.NOT IN is its negation, with the NULL caveat above.
EXISTS and NOT EXISTS
What it does: _EXISTS _(subquery) is true if the subquery returns at least one row; the columns selected do not matter (by convention SELECT

1. It is almost always correlated, and it stops as soon as one match is found. Scenario: which doctors have treated at least one Red-triage (critical) patient, and which have not?

SELECT d.doctor_name
FROM doctors d
WHERE EXISTS (SELECT 1
              FROM visits v
              WHERE v.doctor_id = d.doctor_id
                AND v.triage_level = 'Red');

SELECT d.doctor_name
FROM doctors d
WHERE NOT EXISTS (SELECT 1
                  FROM visits v
                  WHERE v.doctor_id = d.doctor_id
                    AND v.triage_level = 'Red');

ANY and ALL
What they do: compare a value with every value returned by a subquery.

1. ANY (subquery) is true if the value is greater than at least one returned value (equivalent to greater than the minimum).
2. ALL (subquery) is true if the value is greater than every returned value (equivalent to greater than the maximum).
3. = ANY (...) is the same as IN (...), and <> ALL (...) is the same as NOT IN (...). Scenario: compare General Medicine bills (department 20) with Orthopedics bills (department 30). Orthopedic bills range from KES 6,500 to KES 33,000.

• Greater than at least one Orthopedics bill (i.e. above 6,500)

SELECT visit_id, total_bill_kes
FROM visits
WHERE department_id = 20
  AND total_bill_kes > ANY (SELECT total_bill_kes FROM visits WHERE department_id = 30);
-- Greater than every Orthopedics bill (i.e. above 33,000)
SELECT visit_id, total_bill_kes
FROM visits
WHERE department_id = 20
  AND total_bill_kes > ALL (SELECT total_bill_kes FROM visits WHERE department_id = 30);

Subqueries in SELECT, FROM and WHERE

In SELECT (scalar subquery per row):

SELECT d.doctor_name,
       (SELECT COUNT(*)
        FROM visits v
        WHERE v.doctor_id = d.doctor_id) AS visits
FROM doctors d
ORDER BY d.doctor_id;

This returns one row per doctor with 9, 9, 6 and 6 visits (Mwangi, Wanjiku, Otieno, Chebet). It is correlated, since it uses d.doctor_id. A join with GROUP BY is usually more efficient, but this form is convenient for adding one extra column to an existing query without disturbing its grouping.
*In FROM *(derived table):

SELECT ROUND(AVG(revenue), 2) AS avg_revenue_per_doctor
FROM (SELECT doctor_id, SUM(total_bill_kes) AS revenue
      FROM visits
      GROUP BY doctor_id) AS doctor_revenue;

The inner query produces four revenue totals; the outer query averages them. The result is 122000.00 (488,000 divided by 4 doctors). This “aggregate of an aggregate” cannot be written as AVG(SUM(...)) in a single SELECT, so a derived table (or CTE) is required. The alias doctor_revenue is mandatory.

Conclusion

Learning these four concepts changes what SQL feels like. It stops being a way to retrieve rows and becomes a way to ask questions about data.