Joins In SQL

Introduction-SQL window functions

A window function calculates a value for each row using a set of related rows, called the window, and it keeps every input row. GROUP BY is the other aggregate tool: it collapses each group into one row. Use a window when the hospital still needs the visit, and also needs a rank, a running total, or a comparison with the previous visit.

OVER(), PARTITION BY, ORDER BY, and window frames

-OVER() is what makes an aggregate or ranking function a window function. Everything inside OVER() defines which rows are visible to the current row.

• PARTITION BYsplits the result into independent groups.PARTITION BY department_idrestarts the calculation for each department. With noPARTITION BY`, the window is the whole result.
• ORDER BY inside OVER() puts rows in a sequence inside each partition. Ranking functions need it. Aggregates use it to define a running calculation.
• A frame is the slice of the partition that the current row can see. It is written as ROWS or RANGE plus a start and an end, for example UNBOUNDED PRECEDING, CURRENT ROW, and UNBOUNDED FOLLOWING.

Two defaults matter in practice:

• An aggregate with an ORDER BY and no frame uses RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW. That is a running total from the first row through the current row.
• An aggregate with no ORDER BY uses the whole partition. Each row in the department then sees the same department total.
• ROWS counts physical rows. RANGE also pulls in peer rows that share the same ORDER BY value. Two Cardiology visits both billed at KES 30,000 are peers if the window is ordered only by total_bill_kes.

sql
SELECT
visit_id,
department_id,
visit_date,
total_bill_kes,
SUM(total_bill_kes) OVER (
PARTITION BY department_id
ORDER BY visit_date
ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
) AS running_department_bill
FROM visits_fact;


This lists every visit and adds a running bill within its department, in date order. Cardiology comes out as 45,000, then 57,000, then 87,000, then 117,000.

Ranking functions

These four answer “where does this visit sit relative to the others?” They need ORDER BY inside OVER().

ROW_NUMBER() assigns a unique sequence 1, 2, 3… inside the partition. Ties still get different numbers. The extra sort key decides which tied row comes first. Use it to keep one row per patient, such as the latest visit.

RANK() gives tied rows the same rank and then skips. Two visits tied for 3rd both get 3, and the next visit gets 5. Use it for published league tables, where a shared place should open a gap.

DENSE_RANK() gives tied rows the same rank and does not skip. The next visit gets 4. Use it when the next band must stay consecutive, such as fee bands.

NTILE(n) splits the ordered partition into n buckets that are as equal as possible. Extra rows go into the earlier buckets. It splits rows, so two visits with the same bill can fall into different buckets. Use it to flag the top quarter of bills for audit.

sql
SELECT
visit_id,
total_bill_kes,
ROW_NUMBER() OVER (ORDER BY total_bill_kes DESC, visit_id) AS row_num,
RANK() OVER (ORDER BY total_bill_kes DESC) AS rank_bill,
DENSE_RANK() OVER (ORDER BY total_bill_kes DESC) AS dense_rank_bill,
NTILE(4) OVER (ORDER BY total_bill_kes DESC, visit_id) AS bill_quartile
FROM visits_fact;

ROW_NUMBER() uses visit_id as a tie-break so the two KES 30,000 visits (106 and 109) have a stable order. RANK() and DENSE_RANK() are ordered only by bill, so those two visits share a rank.
| visit_id | bill | row_num | rank | dense_rank | quartile |
|---|---:|---:|---:|---:|---:|
| 105 | 60,000 | 1 | 1 | 1 | 1 |
| 101 | 45,000 | 2 | 2 | 2 | 1 |
| 106 | 30,000 | 3 | 3 | 3 | 1 |
| 109 | 30,000 | 4 | 3 | 3 | 2 |
| 107 | 18,000 | 5 | 5 | 4 | 2 |
| 104 | 15,000 | 6 | 6 | 5 | 3 |
| 102 | 12,000 | 7 | 7 | 6 | 3 |
| 108 | 9,000 | 8 | 8 | 7 | 4 |
| 103 | 8,000 | 9 | 9 | 8 | 4 |

Visit 109 shows the difference: RANK() jumps from 3 to 5, DENSE_RANK() continues at 4, and NTILE(4) places 106 in quartile 1 and 109 in quartile 2 even though the bill is the same. Nine rows into four tiles gives sizes 3, 2, 2, 2.

Within a department, the same pattern marks the single most expensive visit:

sql
SELECT visit_id, department_id, total_bill_kes
FROM (
SELECT
visit_id,
department_id,
total_bill_kes,
ROW_NUMBER() OVER (
PARTITION BY department_id
ORDER BY total_bill_kes DESC, visit_id
) AS rn
FROM visits_fact
) ranked
WHERE rn = 1;


Expected rows: visit 101 for Cardiology (45,000), 104 for General Medicine (15,000), 105 for Orthopedics (60,000), and 107 for Pediatrics (18,000).

LAG() and LEAD()
LAG(column, offset, default) reads a value from an earlier row in the ordered window. LEAD() reads a later row. The offset defaults to 1. The default value is returned when that row does not exist; otherwise the result is NULL.
Finance uses this to see whether a return visit cost more than the previous one for the same patient.

sql
SELECT
patient_id,
visit_date,
total_bill_kes,
LAG(total_bill_kes) OVER (
PARTITION BY patient_id
ORDER BY visit_date
) AS previous_bill,
total_bill_kes - LAG(total_bill_kes) OVER (
PARTITION BY patient_id
ORDER BY visit_date
) AS change_from_previous,
LEAD(visit_date) OVER (
PARTITION BY patient_id
ORDER BY visit_date
) AS next_visit_date
FROM visits_fact;


Patient 1:

visit_date	bill	previous_bill	change	next_visit_date
2026-01-05	45,000	NULL	NULL	2026-01-12
2026-01-12	12,000	45,000	−33,000	2026-02-02
2026-02-02	30,000	12,000	+18,000	NULL

The first visit has no previous bill. The last visit has no next date. Patient 2’s second visit rises by 7,000 (8,000 to 15,000). Patient 4’s second visit falls by 9,000 (18,000 to 9,000).

FIRST_VALUE() and LAST_VALUE()

FIRST_VALUE() returns the first value in the ordered frame. LAST_VALUE() returns the last value in the frame. Both depend on the frame. With the default frame, LAST_VALUE() stops at the current row, so it repeats the current visit and does not mean “the patient’s latest visit.” Extend the frame to the end of the partition when the latest value is what you want.
sql
SELECT
patient_id,
visit_id,
visit_date,
total_bill_kes,
FIRST_VALUE(total_bill_kes) OVER (
PARTITION BY patient_id
ORDER BY visit_date
) AS first_visit_bill,
LAST_VALUE(total_bill_kes) OVER (
PARTITION BY patient_id
ORDER BY visit_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS last_visit_bill
FROM visits_fact;


Every row for a patient shows that patient’s first and last bill. Patient 1 shows 45,000 and 30,000 on all three rows. Patient 2 shows 8,000 and 15,000. Patient 3 shows 60,000 and 30,000. Patient 4 shows 18,000 and 9,000. Drop the frame on LAST_VALUE() and visit 101 would show 45,000 as its “last” bill, because the frame would end on that row.

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

These are the same aggregates used with GROUP BY. Written with OVER(), they attach the aggregate back onto each visit.

sql
SELECT
visit_id,
department_id,
total_bill_kes,
SUM(total_bill_kes) OVER (PARTITION BY department_id) AS department_bill,
AVG(total_bill_kes) OVER (PARTITION BY department_id) AS department_avg,
COUNT(*) OVER (PARTITION BY department_id) AS department_visits,
MIN(total_bill_kes) OVER (PARTITION BY department_id) AS department_min,
MAX(total_bill_kes) OVER (PARTITION BY department_id) AS department_max,
total_bill_kes - AVG(total_bill_kes) OVER (PARTITION BY department_id)
AS variance_from_department_avg
FROM visits_fact;


There is no ORDER BY, so each function sees the whole department and repeats the same aggregate on every visit in that department.

department	visits	sum	avg	min	max
Cardiology	4	117,000	29,250	12,000	45,000
General Medicine	2	23,000	11,500	8,000	15,000
Orthopedics	1	60,000	60,000	60,000	60,000
Pediatrics	2	27,000	13,500	9,000	18,000

Visit 101 (45,000) is 15,750 above the Cardiology average. Visit 102 (12,000) is 17,250 below it. A GROUP BY department_id query would return the four summary rows and drop the visit identity. The window keeps both.

Add ORDER BY visit_date and a ROWS frame when the figure must accumulate. A running average of length of stay in Cardiology, ordered by date, is 3.0 after visit 101, then 2.0 after 102 ((3+1)/2), then 2.0 after 106 ((3+1+2)/3), then 2.0 after 109 ((3+1+2+2)/4).

SQL joins

A join combines rows from two tables by matching them. In this model the match is a key: visits_fact.department_id = dim_departments.department_id, and the same pattern for patients and doctors. The join type decides what happens to rows that have no match.

INNER JOIN

INNER JOIN returns only rows that match on both sides. A visit whose department id exists, and a department that has at least one visit, both appear. Maternity and patient 5 disappear.
sql
SELECT
v.visit_id,
d.department_name,
v.total_bill_kes
FROM visits_fact AS v
INNER JOIN dim_departments AS d
ON v.department_id = d.department_id;


Nine rows, one per visit, with Cardiology, General Medicine, Orthopedics, or Pediatrics. Use this when an unmatched row would be an incomplete fact, such as a bill that must belong to a known department.

LEFT JOIN

LEFT JOIN keeps every row from the left table. Columns from the right table are NULL when nothing matches. Put the table you must not lose on the left.
sql
SELECT
d.department_name,
v.visit_id,
v.total_bill_kes
FROM dim_departments AS d
LEFT JOIN visits_fact AS v
ON d.department_id = v.department_id;


The nine visits appear, plus one extra row: Maternity with a NULL visit and a NULL bill. Use this for “show every department, including those with no activity this period.” Putting visits_fact on the left instead would keep every visit and drop Maternity, which is a different question.

RIGHT JOIN

RIGHT JOIN keeps every row from the right table. It is the same match rule as LEFT JOIN with the tables swapped. This query returns the same rows as the LEFT JOIN above:
sql
SELECT
d.department_name,
v.visit_id,
v.total_bill_kes
FROM visits_fact AS v
RIGHT JOIN dim_departments AS d
ON v.department_id = d.department_id;

LEFT JOIN is the usual way to write this, because reading from the preserved table on the left is easier to follow. RIGHT JOIN is the right tool when a query is already built from the fact table and you need to preserve the dimension without rewriting the FROM clause.

FULL OUTER JOIN

FULL OUTER JOIN keeps unmatched rows from both tables. A patient with no visit and a visit with no patient would both appear. In this extract every visit has a patient, so the only extra row is patient 5.
sql
SELECT
p.patient_id,
p.county,
v.visit_id,
v.total_bill_kes
FROM dim_patients AS p
FULL OUTER JOIN visits_fact AS v
ON p.patient_id = v.patient_id;


Nine visit rows, plus patient 5 (Kiambu) with NULL visit columns. Use it for a reconciliation: registered patients who never attended, and visit records that do not match a patient.

MySQL writes the same result as a union of both outer joins:

sql
SELECT p.patient_id, p.county, v.visit_id, v.total_bill_kes
FROM dim_patients AS p
LEFT JOIN visits_fact AS v ON p.patient_id = v.patient_id
UNION ALL
SELECT p.patient_id, p.county, v.visit_id, v.total_bill_kes
FROM dim_patients AS p
RIGHT JOIN visits_fact AS v ON p.patient_id = v.patient_id
WHERE p.patient_id IS NULL;


The second leg is empty on this extract, because no visit lacks a patient.

## CROSS JOIN
CROSS JOIN matches every row on the left with every row on the right. There is no ON. Five departments and six doctors produce 30 rows. Use it only when the product is the real question, such as a blank roster of every doctor against every department before you mark who is actually assigned.
sql
SELECT
doc.doctor_name,
dep.department_name
FROM dim_doctors AS doc
CROSS JOIN dim_departments AS dep;


Crossing visits_fact with dim_departments would invent department combinations the patient never attended. That is the failure mode to avoid: a missing ON clause in an old comma-style join behaves as a cross join.

SELF JOIN

A self join uses the same table twice, under two aliases, and relates rows to other rows in that table. Follow-up visits are a visit matched to an earlier visit for the same patient.

sql
SELECT
earlier.patient_id,
earlier.visit_id AS first_visit,
earlier.visit_date AS first_date,
later.visit_id AS follow_up_visit,
later.visit_date AS follow_up_date
FROM visits_fact AS earlier
INNER JOIN visits_fact AS later
ON earlier.patient_id = later.patient_id
AND earlier.visit_date < later.visit_date;


patient	first visit	follow-up
1	101 on 5 Jan	102 on 12 Jan
1	101 on 5 Jan	109 on 2 Feb
1	102 on 12 Jan	109 on 2 Feb
2	103 on 6 Jan	104 on 20 Jan
3	105 on 8 Jan	106 on 15 Jan
4	107 on 9 Jan	108 on 18 Jan

Patient 1 has three pairs because three visits can be ordered in three earlier/later combinations. The date condition stops a visit from matching itself and stops each pair from appearing twice.

Which join answers which hospital question

Join	Rows that survive	Hospital use
INNER JOIN	Matches only	Bills that must have a known department and doctor
LEFT JOIN	All of the left table, plus matches	Every department, including those with zero visits
RIGHT JOIN	All of the right table, plus matches	Same as LEFT JOIN, with the preserved table written on the right
FULL OUTER JOIN	Matches plus unmatched rows from both sides	Patients with no visit and visits with no patient, together
CROSS JOIN	Every combination	A blank coverage grid, not a list of real visits
SELF JOIN	Rows related to other rows in the same table	A visit compared with the same patient’s earlier visit

---

## Common table expressions

A CTE is a named result defined with WITH and used by the main query that follows. It lasts for that one statement. It does not create a table. The reason to use one is to name an intermediate result, reuse it, and keep a multi-step hospital question readable.

A basic CTE

This names each department’s revenue, then keeps departments above the average department revenue.

sql
WITH department_revenue AS (
SELECT
d.department_id,
d.department_name,
SUM(v.total_bill_kes) AS revenue
FROM dim_departments AS d
INNER JOIN visits_fact AS v
ON v.department_id = d.department_id
GROUP BY d.department_id, d.department_name
)
SELECT *
FROM department_revenue
WHERE revenue > (SELECT AVG(revenue) FROM department_revenue);


The four department revenues are 117,000, 23,000, 60,000, and 27,000. Their average is 56,750. Cardiology (117,000) and Orthopedics (60,000) are returned. The same CTE is referenced twice: once as the source, once inside the average. A subquery would have to repeat the aggregation text.

Multiple CTEs

Separate steps by commas. Later CTEs may use earlier ones.

sql
WITH visit_detail AS (
SELECT
v.visit_id,
v.total_bill_kes,
p.county,
p.patient_gender,
d.department_name
FROM visits_fact AS v
INNER JOIN dim_patients AS p
ON p.patient_id = v.patient_id
INNER JOIN dim_departments AS d
ON d.department_id = v.department_id
),
county_summary AS (
SELECT
county,
COUNT(*) AS visits,
SUM(total_bill_kes) AS revenue
FROM visit_detail
GROUP BY county
)
SELECT
c.county,
c.visits,
c.revenue,
vd.department_name,
vd.total_bill_kes
FROM county_summary AS c
INNER JOIN visit_detail AS vd
ON vd.county = c.county
ORDER BY c.revenue DESC, vd.visit_id;

visit_detail joins visits to patients and departments. county_summary aggregates that result. The main query joins the summary back to the detail so each visit sits beside its county total.
| county | visits | revenue | visit bills |
|---|---:|---:|---|
| Nairobi | 3 | 87,000 | 45,000, 12,000, 30,000 |
| Mombasa | 2 | 90,000 | 60,000, 30,000 |
| Kisumu | 2 | 23,000 | 8,000, 15,000 |
| Nakuru | 2 | 27,000 | 18,000, 9,000 |

Mombasa sorts first on revenue. Kiambu is absent because patient 5 never reaches visit_detail.

A recursive CTE

A recursive CTE has an anchor member, UNION ALL, and a recursive member that reads the CTE itself. The anchor starts the set. Each pass adds the next level until no new rows appear. The Cardiology roster is a tree: Achieng, then Otieno, then Mwangi.

`sql
WITH RECURSIVE reporting_line AS (
SELECT
doctor_id,
doctor_name,
reports_to_doctor_id,
0 AS level
FROM dim_doctors
WHERE doctor_name = 'Dr Achieng'

UNION ALL

SELECT
    doc.doctor_id,
    doc.doctor_name,
    doc.reports_to_doctor_id,
    line.level + 1
FROM dim_doctors AS doc
INNER JOIN reporting_line AS line
    ON doc.reports_to_doctor_id = line.doctor_id

)
SELECT *
FROM reporting_line
ORDER BY level;
`

level	doctor	reports to
0	Dr Achieng	nobody (anchor)
1	Dr Otieno	Dr Achieng
2	Dr Mwangi	Dr Otieno

SQL Server writes WITH reporting_line without the word RECURSIVE. The query needs a condition that eventually returns no rows. A cycle in reports_to_doctor_id would run until the engine’s recursion limit.

How CTEs differ from subqueries

Both can sit in FROM and both can be joined and aggregated. The CTE name is the practical difference.

• A CTE is declared before the main query and can be joined more than once under one name. department_revenue was used as the list and as the input to AVG.
• A subquery is written where it is used. The logic is local, and repeating it means copying the SQL.
• A recursive walk of the doctor tree can be expressed as a CTE. A subquery in FROM cannot call itself.
• The optimizer may inline either form. Readability and reuse are the reasons to choose; one is not automatically faster.

Use a subquery for a short, single-use test. Use a CTE when the statement has several named steps, or when the step is recursive.

---

## SQL subqueries

A subquery is a query inside another query. Its place in the outer query, and whether it refers to the outer query, decide its type.

Where a subquery can sit

In SELECT. A scalar subquery returns one column and one row, and it is printed beside each outer row. This attaches the patient’s visit count to every visit:

sql
SELECT
v.visit_id,
v.patient_id,
v.total_bill_kes,
(
SELECT COUNT(*)
FROM visits_fact AS v2
WHERE v2.patient_id = v.patient_id
) AS visits_for_this_patient
FROM visits_fact AS v;


Visits 101, 102, and 109 show 3. Visits 103 and 104 show 2. Visits 105 and 106 show 2. Visits 107 and 108 show 2. More than one row, or more than one column, in this position is an error.

In FROM. The inner query is a derived table and must have an alias. This builds department totals, then keeps those at or above KES 50,000:

sql
SELECT
dept.department_name,
dept.revenue
FROM (
SELECT
d.department_name,
SUM(v.total_bill_kes) AS revenue
FROM dim_departments AS d
INNER JOIN visits_fact AS v
ON v.department_id = d.department_id
GROUP BY d.department_name
) AS dept
WHERE dept.revenue >= 50000;


Cardiology (117,000) and Orthopedics (60,000). General Medicine and Pediatrics fall below the cut. Maternity never enters the inner join.

In WHERE. The subquery filters the outer row. The IN, EXISTS, ANY, and ALL examples below all sit in WHERE. A scalar comparison sits there too: total_bill_kes > (SELECT AVG(total_bill_kes) FROM visits_fact). The overall average is 227,000 / 9 = 25,222.22. Visits above it are 101 (45,000), 105 (60,000), 106 (30,000), and 109 (30,000).

##Scalar subqueries

A scalar subquery returns a single value. The average above is scalar and non-correlated: it does not mention the outer query, so the engine computes it once. A scalar subquery in SELECT that filters on the outer row, such as the visit count, is correlated and runs per outer row.

##Correlated and non-correlated subqueries

A correlated subquery refers to a column from the outer query. It is re-evaluated for each outer row. This keeps visits that cost more than that same patient’s own average:

sql
SELECT
v.patient_id,
v.visit_id,
v.total_bill_kes
FROM visits_fact AS v
WHERE v.total_bill_kes > (
SELECT AVG(v2.total_bill_kes)
FROM visits_fact AS v2
WHERE v2.patient_id = v.patient_id
);


patient	average	visits above that average
1	29,000	101 (45,000), 109 (30,000)
2	11,500	104 (15,000)
3	45,000	105 (60,000)
4	13,500	107 (18,000)

v2.patient_id = v.patient_id is the correlation. Visit 102 is under patient 1’s average, so it is excluded even though 12,000 would be above patient 2’s average.
A non-correlated subquery can run on its own. This list of active department ids does not look at the outer row:

sql
SELECT department_name
FROM dim_departments
WHERE department_id IN (
SELECT DISTINCT department_id
FROM visits_fact
);


Cardiology, General Medicine, Orthopedics, and Pediatrics. Maternity is excluded because its id is not in visits_fact.

Nested subqueries

A nested subquery is a subquery inside another subquery. Here the innermost query finds departments whose average bill is above the hospital average (25,222.22). Those departments are Cardiology (29,250) and Orthopedics (60,000). The middle query turns those ids into doctor ids. The outer query prints the doctors.

sql
SELECT doctor_name
FROM dim_doctors
WHERE department_id IN (
SELECT department_id
FROM visits_fact
GROUP BY department_id
HAVING AVG(total_bill_kes) > (
SELECT AVG(total_bill_kes) FROM visits_fact
)
);


Dr Achieng, Dr Otieno, Dr Mwangi, and Dr Kamau. Dr Wanjiku and Dr Njeri work in departments below the hospital average.

IN, EXISTS, ANY, and ALL

IN keeps the outer row when its value equals any value in the subquery list. Patients who had a Cardiology visit:

sql
SELECT patient_id, county
FROM dim_patients
WHERE patient_id IN (
SELECT patient_id
FROM visits_fact
WHERE department_id = 1
);


Patients 1 (Nairobi) and 3 (Mombasa). NOT IN means “none of these values.” It becomes unsafe if the subquery can return NULL, because a comparison with NULL is unknown. Prefer NOT EXISTS for the negative test.

EXISTS keeps the outer row when the subquery returns at least one row. It does not care what the columns contain. Departments that raised at least one bill over KES 40,000:

sql
SELECT d.department_name
FROM dim_departments AS d
WHERE EXISTS (
SELECT 1
FROM visits_fact AS v
WHERE v.department_id = d.department_id
AND v.total_bill_kes > 40000
);


Cardiology (visit 101, 45,000) and Orthopedics (visit 105, 60,000). This subquery is correlated through d.department_id. EXISTS is the usual choice for a yes-or-no check. IN is the usual choice when you already have a list of keys.

ANY compares the outer value with each subquery value and succeeds if the comparison is true for at least one of them. > ANY (...) means “greater than the smallest value in the list.” Pediatrics bills are 18,000 and 9,000, so > ANY of those bills means greater than 9,000.

sql
SELECT visit_id, total_bill_kes
FROM visits_fact
WHERE total_bill_kes > ANY (
SELECT total_bill_kes
FROM visits_fact
WHERE department_id = 4
);


Every visit except 103 (8,000) and 108 (9,000). Visit 108 is not greater than either Pediatrics bill.

ALL succeeds only when the comparison is true for every subquery value. > ALL of the Pediatrics bills means greater than 18,000, the larger one.

sql
SELECT visit_id, total_bill_kes
FROM visits_fact
WHERE total_bill_kes > ALL (
SELECT total_bill_kes
FROM visits_fact
WHERE department_id = 4
);


Visits 101 (45,000), 105 (60,000), 106 (30,000), and 109 (30,000). Visit 107 equals 18,000, so it fails a strict > test.
IN is the same test as = ANY. NOT IN matches <> ALL only when the list contains no NULL.

---

##How the four tools fit one hospital question

A monthly review can use all four in one statement: a CTE names the joined visit, a window ranks bills inside each department, and a subquery keeps departments that actually billed.

sql
WITH billed_visits AS (
SELECT
v.visit_id,
v.visit_date,
v.total_bill_kes,
d.department_name,
doc.doctor_name
FROM visits_fact AS v
INNER JOIN dim_departments AS d
ON d.department_id = v.department_id
INNER JOIN dim_doctors AS doc
ON doc.doctor_id = v.doctor_id
)
SELECT
department_name,
doctor_name,
visit_id,
total_bill_kes,
RANK() OVER (
PARTITION BY department_name
ORDER BY total_bill_kes DESC
) AS bill_rank_in_department
FROM billed_visits
WHERE department_name IN (
SELECT d.department_name
FROM dim_departments AS d
WHERE EXISTS (
SELECT 1
FROM visits_fact AS v
WHERE v.department_id = d.department_id
)
);


The inner joins attach a department and a doctor to each real visit. EXISTS limits the list to departments with activity, so Maternity stays out. RANK() marks the most expensive visit in each department as 1. Cardiology’s rank 1 is visit 101. The two KES 30,000 Cardiology visits share rank 2, and visit 102 is rank 4.