SQL FOR ANALYTICS:WINDOW FUNCTIONS,JOINS,CTEs AND SUBQUERIES

Introduction

The Hospital Dataset is Used in this Article
The dataset is structured as a star schema created from a single flat file containing hospital visit data called hospital_visits_flat_original. A main fact table keeps track of every visit, and six dimension tables explain who was involved, where the visit took place, and why it happened. Queries are written using PostgreSQL syntax, and there are notes explaining any differences when the same query would work in MySQL or SQL Server.

SQL Window Functions

Window function explanation
An average calculation like AVG(total_bill_kes) along with GROUP BY combines multiple rows into a single row for each group. A window function does a similar calculation but retains each original row, adding the result as a new column. It responds to the question “how does this visit’s bill compare with its department’s average?” without loosing the visit itself.

Group by: one rows per department
select department_id,avg(total_bill_kes) as avg_bill
from visits_fact
Group by department_id;

select visit_id,department_id,total_bill_kes,
Avg(totall_bill_kes) Over (partition by department_id) as department_avg_bill
From visits_facts;

The first query gives one row for each department. The second one brings back every visit, and each row shows the average for its department.
The anatomy of a window: OVER, PARTITION BY, ORDER BY and frames.

function_name(...)over(
partition by column_list
order by column_list
rows between -- and --

OVER() is what changes a regular function into a window function. It determines the range of rows that the function is able to view. An empty OVER () means the window is the entire result set.
Partition by splits rows into separate groups, similar to group by, but it doesn't combine the rows into a single result. The function restarts for each partition. Partitioning by department_id means that each department is ranked or summed up individually.
ORDER BY, which is used inside OVER, determines the order of rows within each group or partition. It is needed for ranking functions and for LAG and LEAD, and when used with aggregates, it helps create running calculations. It is different from the ORDER BY clause at the end of the query, which is used to sort the final result.
Window frames limit the window to a sliding set of rows around the current row. The frame is defined as ROWS BETWEEN AND , with the boundaries being:

1. unbounded preceding -the first row of the partition
2. n preceding - n rows before the current row
3. current row - the current row
4. n following - n rows after the current row
5. unbounded following - the last row of partition

Two frame facts

1. ROWS vs RANGE. ROWS counts physical rows. RANGE treats rows that have the same ORDER BY values (peers) as a single group, so rows that are tied are all included together.
2. The default frame. When a window has an ORDER BY clause but doesn't specify a frame, SQL uses the RANGE BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW by default. This is why a running total works automatically, and it's also why LAST_VALUE behaves incorrectly by default ### Ranking functions: ROW_NUMBER, RANK, DENSE_RANK and NTILE

Ranking functions assign a position to each row within its partition. They are different only in the way they handle ties.

Scenario: The management wishes to sort doctors based on the amount of revenue they generate within each department.
SELECT d.department_name,
doc.doctor_name,
SUM(f.total_bill_kes) AS total_billed,
ROW_NUMBER() OVER (PARTITION BY d.department_name ORDER BY SUM(f.total_bill_kes) DESC) AS row_num,
RANK() OVER (PARTITION BY d.department_name ORDER BY SUM(f.total_bill_kes) DESC) AS rnk,
DENSE_RANK() OVER (PARTITION BY d.department_name ORDER BY SUM(f.total_bill_kes) DESC) AS dense_rnk
FROM visits_fact f
JOIN dim_doctors doc ON doc.doctor_id = f.doctor_id
JOIN dim_departments d ON d.department_id = f.department_id
GROUP BY d.department_name, doc.doctor_name
ORDER BY d.department_name, total_billed DESC;

What it does: GROUP BY first calculates the total billing for each doctor within each department. The window functions then operate on those totals, starting fresh in each department (PARTITION BY) and sorting the highest total first. All three ranking functions are shown together so you can compare how ties are handled.

SELECT visit_id,
total_bill_kes,
NTILE(4) OVER (ORDER BY total_bill_kes DESC) AS cost_quartile
FROM visits_fact;
It organizes all visits by cost, starting with the most expensive and ending with the least, then divides the list into four equal parts. Quartile 1 includes the most expensive visits, while quartile 4 has the least costly ones. If the number of rows doesn't split evenly into the buckets, like 10 rows into 4 buckets, the first few buckets get an extra row.For example, it would be 3, 3, 2, 2.

Department name	Doctor_name	totall_billed	row number	Rank	Dense_rank
Cardiology	Dr.Otieno	900000	1	1	1
Cardiology	Dr.Wanjiru	900000	2	1	1
Cardiology	Dr.Kamau	700000	3	3	2
Cardiology	Dr.Achieng	500000	4	4	3

Otieno and Wanjiru tie. ROW_NUMBER still separates them (1 and 2). RANK starts at 1 and then goes to 3. DENSE_RANK assigns a 1 to both and then continues with 2.
When to use which: use ROW_NUMBER to select exactly one row from each group, such as one row per patient.
most recent visit, or removing duplicate records). Use RANK to show competition-style rankings where there can be gaps between positions. Use DENSE_RANK for “top N levels” questions.
Top-N per group. Window functions cannot be used with filters in the WHERE clause, so you should put the query inside a common table expression.
WITH doctor_revenue AS (
SELECT d.department_name, doc.doctor_name,
SUM(f.total_bill_kes) AS total_billed,
DENSE_RANK() OVER (PARTITION BY d.department_name ORDER BY SUM(f.total_bill_kes) DESC) AS dr
FROM visits_fact f
JOIN dim_doctors doc ON doc.doctor_id = f.doctor_id
JOIN dim_departments d ON d.department_id = f.department_id
GROUP BY d.department_name, doc.doctor_name
)
SELECT department_name, doctor_name, total_billed
FROM doctor_revenue
WHERE dr <= 2;

This shows the top two billing levels in each department, including all doctors who are tied at those levels.
NTILE(n)
NTILE(n) splits the ordered rows in a group into n nearly equal parts and assigns each row a number based on which part it belongs to. It is used for quartiles, deciles, and other types of segmentation.
Scenario: The finance department wants to divide visits into four cost groups to identify the top 25% that are the most expensive.
Expected result (sample):
| visit_id | Total_bill_kes | Cost_quartile |
| ---|---|---:|
| 4120 | 980000 | 1 |
| 3771 | 760000 | 1 |
| 3050 | 410000 | 2 |
| 1204 | 9500 | 4 |

Offset functions: LAG and LEAD

LAG(column, offset, default) retrieves a value from a row that comes before the current row within the same partition. LEAD reads data from a row that comes after the current row, with an option to specify how many rows to skip and a default value if no data is found. The offset starts at 1, and if no row is found, the default value is set to NULL. They eliminate the need to join a table to itself in order to compare consecutive records.
Scenario (LAG): The quality team monitors readmissions, which are cases where patients come back to the hospital shortly after they were discharged. For every visit, we need the gap because that patient's previous stay has already ended. A stay is considered to begin on the visit date and continue for the number of days specified as the length of stay.

SELECT patient_id,
visit_id,
visit_date,
LAG(visit_date + length_of_stay_days)
OVER (PARTITION BY patient_id ORDER BY visit_date) AS previous_discharge,
visit_date - LAG(visit_date + length_of_stay_days)
OVER (PARTITION BY patient_id ORDER BY visit_date) AS days_since_last_stay
FROM visits_fact;

For each patient, the visits are arranged in order based on the date. LAG(...) gets the end date of the patient's last stay, and by taking the current visit date and subtracting that end date, it calculates the number of days between them. PostgreSQL gives an integer when you subtract dates; in MySQL, you can use DATEDIFF(visit_date, LAG(...)), and in SQL Server, you use DATEDIFF(day, LAG(...), visit_date), and if you need to add days, you can use DATEADD.
The first visit of each patient does not have an earlier stay, so it is shown as NULL. Wrapping the query in a CTE and filtering based on days since the last stay.

SELECT patient_id,
visit_date,
LEAD(visit_date) OVER (PARTITION BY patient_id ORDER BY visit_date) AS next_visit_date
FROM visits_fact;

The expected result is that each row displays a visit date along with the date of the patient’s next visit, and for the patient’s last visit, it shows NULL. LAG looks back and LEAD looks ahead; besides the direction, they are the same.

FIRST_VALUE and LAST_VALUE

FIRST_VALUE(col) gets the value from the first row in the window frame, and LAST_VALUE(col) gets the value from the last row. They help in comparing every row with a beginning point or an ending point.
For each visit, display the patient's first and latest diagnosis, allowing clinicians to understand the patient's medical journey from the beginning up to the present.

SELECT f.patient_id,
f.visit_date,
dg.diagnosis_name,
FIRST_VALUE(dg.diagnosis_name) OVER (
PARTITION BY f.patient_id ORDER BY f.visit_date
) AS first_diagnosis,
LAST_VALUE(dg.diagnosis_name) OVER (
PARTITION BY f.patient_id ORDER BY f.visit_date
ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING
) AS latest_diagnosis
FROM visits_fact f
JOIN dim_diagnosis dg ON dg.diagnosis_id = f.diagnosis_id;

What it does: FIRST_VALUE uses the default frame since the first row in a partition is always part of it. LAST_VALUE requires a clearly defined frame that extends to UNBOUNDED FOLLOWING.
The classic LAST_VALUE trap. With the default frame, which stops at the current row, LAST_VALUE just gives back the value from the current row, making it seem like the function isn't working properly. Always include ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING to correctly identify the actual last row within the partition.
Expected result (sample, patient 101):

patient_id	visit_date	Diagnosis_name	First_diagnosis	latest_diagnosis
101	2026-01-05	Hypertension	Hypertension	Malaria
101	2026-01-25	Pneumonia	Hypertension	Malaria
101	2026-06-02	Malaria	Hypertension	Malaria

Aggregate window functions include SUM, AVG, COUNT, MIN, and MAX.These functions are used to perform calculations across a set of rows that are related to the current row.They help in analyzing data by providing summary statistics within a specific window of data.
The familiar aggregates become window functions when they are followed by OVER. Three patterns cover most real-world uses:

1. Whole-partition value (OVER (PARTITION BY ...) with no ORDER BY): each row sees the total, average, count, minimum, or maximum of the entire group.
2. Running calculation (ORDER BY added): the aggregate adds up each row one at a time.
3. Moving calculation, which uses an explicit window of rows between a certain number of preceding rows and the current row, is known as a sliding window. Scenario A: whole-partition statistics. Compare every visit with its department’s profile.

SELECT visit_id,
department_id,
total_bill_kes,
COUNT(*) OVER (PARTITION BY department_id) AS dept_visits,
SUM(total_bill_kes) OVER (PARTITION BY department_id) AS dept_total_billed,
ROUND(AVG(total_bill_kes) OVER (PARTITION BY department_id), 2) AS dept_avg_bill,
MIN(total_bill_kes) OVER (PARTITION BY department_id) AS dept_min_bill,
MAX(total_bill_kes) OVER (PARTITION BY department_id) AS dept_max_bill,
total_bill_kes - AVG(total_bill_kes) OVER (PARTITION BY department_id) AS diff_from_avg
FROM visits_fact;

Each time you visit, it keeps a separate bill and displays five statistics at the department level. The final column indicates whether a visit is more (positive) or less (negative) than the average for its department, highlighting visits that are unusually costly and not connected through a join or GROUP BY clause.

Window functions vs GROUP BY, and a few rules

GROUP BY
Window function
Rows returned
One per group
One per original row
Detail columns
Lost unless grouped
Fully retained
Best for
Summary reports
Rankings, running totals, comparisons with a group
Three important rules to keep in mind: window functions can only be used in the SELECT clause or the ORDER BY clause, so you should use a CTE or a subquery to filter them first; they are processed after the WHERE, GROUP BY, and HAVING clauses; and ranking functions require an ORDER BY statement inside the OVER clause to work properly.

SQL Joins

A join combines rows from two tables by matching a join condition, which is usually a foreign key to a primary key. In this star schema, the data is intentionally split into different tables, like visits stored in visits_fact and patient information in dim_patients, so you have to use joins to put everything back together and see the complete picture.
To make the differences clearer, imagine these small examples:
dim_patients: (1, Wanjiku, Nairobi), (2, Otieno, Kisumu), (3, Halima, Mombasa), (4, Kiprono, Nakuru)
Visits fact: (V1, patient 1, Cardiology), (V2, patient 1, Orthopaedics), (V3, patient 2, Paediatrics), (V4, patient 99, Cardiology)
Halima, patient 3, and Kiprono, patient 4, have never had any visits recorded.The visit V4 is linked to patient 99, who doesn't exist, which is due to a data entry mistake that created an orphan record.

INNER JOIN

Returns only the rows that have a match in both tables. Unmatched rows on either side disappear.
SELECT p.county, p.patient_gender, f.visit_id, f.total_bill_kes
FROM visits_fact f
INNER JOIN dim_patients p ON p.patient_id = f.patient_id;
The results are based on the samples: V1, V2 (Wanjiku), and V3 (Otieno). Halima and Kiprono disappear because they have no visits, and V4 disappears because it has no patient.
Use this when you only want records that are present on both sides, like "total billing by county," because a visit without a known patient can't be assigned to a county in the first place. A standard star-schema report connects multiple inner joins from the fact table.
SELECT d.department_name, doc.doctor_name, p.county, f.total_bill_kes
FROM visits_fact f
JOIN dim_departments d ON d.department_id = f.department_id
JOIN dim_doctors doc ON doc.doctor_id = f.doctor_id
JOIN dim_patients p ON p.patient_id = f.patient_id;
This provides one readable row for each visit, including the names of the department, doctor, and county.(Joining on its own refers to an INNER JOIN.)

LEFT JOIN (LEFT OUTER JOIN)

Returns every row from the left table along with any matching rows from the right table. When there's no match, the columns on the right side will show as NULL.
SELECT p.patient_id, p.county, f.visit_id
FROM dim_patients p
LEFT JOIN visits_fact f ON f.patient_id = p.patient_id;
Anti-join pattern. Add WHERE f.visit_id IS NULL to keep only the unmatched left rows. This answers “which registered patients have never had a visit?”:

SELECT p.patient_id, p.county
FROM dim_patients p
LEFT JOIN visits_fact f ON f.patient_id = p.patient_id
WHERE f.visit_id IS NULL;

RIGHT JOIN (RIGHT OUTER JOIN)

The mirror image: all rows from the right table, with matching left rows or NULLs.

SELECT p.county, f.visit_id, f.total_bill_kes
FROM dim_patients p
RIGHT JOIN visits_fact f ON f.patient_id = p.patient_id;

FULL OUTER JOIN

Returns all rows from both tables, matching where possible and filling NULLs on whichever side lacks a partner.

SELECT p.patient_id AS dim_patient_id,
f.patient_id AS fact_patient_id,
f.visit_id
FROM dim_patients p
FULL OUTER JOIN visits_fact f ON f.patient_id = p.patient_id;

CROSS JOIN

Returns the Cartesian product by combining each row from the first table with every row from the second table, without any specific condition for matching rows. Five departments and two genders result in ten different rows.

SELECT d.department_name, g.patient_gender, COUNT(f.visit_id) AS visits
FROM dim_departments d
CROSS JOIN (SELECT DISTINCT patient_gender FROM dim_patients) g
LEFT JOIN dim_patients p ON p.patient_gender = g.patient_gender
LEFT JOIN visits_fact f ON f.patient_id = p.patient_id
AND f.department_id = d.department_id
GROUP BY d.department_name, g.patient_gender
ORDER BY d.department_name, g.patient_gender;

The cross join builds a complete department-by-gender grid first. The LEFT JOINs then attach the actual visits, so a combination with no visits still appears with a count of 0 instead of being missing. This “report skeleton” use is the most practical reason to cross join. Use it deliberately, because crossing two large tables produces enormous results.

SELF JOIN

Not a separate keyword but a technique: a table joined to itself under two aliases. It suits comparisons between rows of the same table. The hospital tables have no supervisor column, so the natural self join here is on the fact table, to find repeat visits by the same patient within 30 days.

SELECT a.patient_id,
a.visit_id AS first_visit,
a.visit_date AS first_date,
b.visit_id AS repeat_visit,
b.visit_date AS repeat_date,
b.visit_date - a.visit_date AS days_apart
FROM visits_fact a
JOIN visits_fact b
ON b.patient_id = a.patient_id
AND b.visit_date > a.visit_date
AND b.visit_date <= a.visit_date + 30
ORDER BY a.patient_id, a.visit_date;

Alias A represents the earlier visit, and alias B represents the later visit by the same patient. The condition b.visit_date > a.visit_date prevents a visit from being matched with itself and ensures each pair is not reported twice, and + 30 limits the time difference to 30 days. Every row returned is a repeat visit.

Common Table Expressions (CTEs)

CTEs is a common whose table expression is a named, temporary set of results that is created at the beginning of a query using the WITH keyword. It lasts only as long as that single statement is made. Think of it as giving a name to an intermediate step so the main query can use it just like a table. Common Table Expressions (CTEs) are important because they improve the readability of complex queries by organizing steps in a top-to-bottom flow rather than having nested structures.They also allow for reusability, as a single CTE can be used multiple times within a query.Additionally, CTEs make debugging easier since each step can be executed separately to test and verify its functionality.

WITH cte_name AS (
SELECT ...
)
SELECT ... FROM cte_name;

Basic CTE

Scenario: Find departments whose average bill per visit is above the hospital-wide average.

WITH dept_avg AS (
SELECT department_id,
AVG(total_bill_kes) AS avg_bill
FROM visits_fact
GROUP BY department_id
)
SELECT d.department_name, ROUND(da.avg_bill, 2) AS avg_bill
FROM dept_avg da
JOIN dim_departments d ON d.department_id = da.department_id
WHERE da.avg_bill > (SELECT AVG(total_bill_kes) FROM visits_fact)
ORDER BY da.avg_bill DESC;

What it does: The CTE dept_avg calculates one average for each department. The main query connects that small result with dim_departments to get the department names in a readable format, then filters to include only those departments with bills higher than the overall average. Without the CTE, you would have to repeat the AVG in a HAVING clause or nest the query inside another one.

Multiple CTEs

Several CTEs can be chained under a single WITH, separated by commas. Each can refer to the ones defined before it, so a complex analysis reads like a sequence of steps.
Scenario: For each county, compare total billing with average length of stay, and show each county’s share of total revenue.

WITH county_revenue AS (
SELECT p.county,
COUNT(*) AS visits,
SUM(f.total_bill_kes) AS total_billed
FROM visits_fact f
JOIN dim_patients p ON p.patient_id = f.patient_id
GROUP BY p.county
),
county_stay AS (
SELECT p.county,
ROUND(AVG(f.length_of_stay_days), 1) AS avg_stay_days
FROM visits_fact f
JOIN dim_patients p ON p.patient_id = f.patient_id
GROUP BY p.county
),
grand_total AS (
SELECT SUM(total_billed) AS all_billed FROM county_revenue
)
SELECT r.county,
r.visits,
r.total_billed,
s.avg_stay_days,
ROUND(100.0 * r.total_billed / g.all_billed, 1) AS pct_of_revenue
FROM county_revenue r
JOIN county_stay s ON s.county = r.county
CROSS JOIN grand_total g
ORDER BY r.total_billed DESC;

What it does: county_revenue and county_stay each capture one specific aspect for each county.grand_total is based on county_revenue, which is a CTE that refers back to an earlier CTE. The last query combines them and uses the overall total to calculate each county's share as a percentage. A single-row CTE like grand_total is a clean method to include a single number in every row.

Common Table Expressions with joins, aggregations, and window functions.

Common Table Expressions (CTEs) are the standard method for filtering within a window function, and they cannot be used in the WHERE clause. This example identifies each patient's latest visit, including the doctor and the department involved.

WITH numbered AS (
SELECT f.patient_id,
f.visit_date,
f.doctor_id,
f.department_id,
f.total_bill_kes,
ROW_NUMBER() OVER (PARTITION BY f.patient_id ORDER BY f.visit_date DESC) AS rn
FROM visits_fact f
)
SELECT p.patient_id, p.county, n.visit_date AS last_visit,
doc.doctor_name, d.department_name, n.total_bill_kes
FROM numbered n
JOIN dim_patients p ON p.patient_id = n.patient_id
JOIN dim_doctors doc ON doc.doctor_id = n.doctor_id
JOIN dim_departments d ON d.department_id = n.department_id
WHERE n.rn = 1;
What it does: The CTE lists each patient's visits starting with the most recent one and moving to the oldest. The main query retains only the most recent visit, where rn equals 1, and incorporates the descriptive names through joins. The result shows one row for each patient who has ever been visited.

Recursive CTEs

A recursive CTE references itself. It is built for data that is organized in a hierarchy or sequence, and it has two sections connected by UNION ALL.

1. Anchor member: the starting rows (runs once).
2. A recursive member is a query that uses the results from the CTE itself to generate the next set of rows, and this process keeps repeating until there are no more rows to return. In PostgreSQL and MySQL, the keyword is WITH RECURSIVE; in SQL Server, it is simply WITH. The hospital tables don't have a parent-child structure like a doctor-to-supervisor hierarchy, so the best recursive CTE to use here creates a continuous list of dates. This solves an actual reporting issue: days with no visits are not included in the GROUP BY visit_date, which affects trends and the 7-day moving average in Section 1.6.

WITH RECURSIVE calendar AS (
-- Anchor: the first date in the data
SELECT MIN(visit_date)::date AS day
FROM visits_fact
SELECT c.day,
COUNT(f.visit_id) AS visits,
COALESCE(SUM(f.total_bill_kes), 0) AS revenue
FROM calendar c
LEFT JOIN visits_fact f ON f.visit_date::date = c.day
GROUP BY c.day
ORDER BY c.day;

What it does: The anchor returns the earliest visit date. Each recursion adds one day, stopping when the latest visit date is reached. The calendar is then LEFT JOINed to the visits, so a quiet day shows 0 visits and 0 revenue instead of disappearing. (In MySQL use day + INTERVAL 1 DAY; in SQL Server use DATEADD(day, 1, day).)

CTEs vs subqueries

Both let you use the result of one query inside another, and for simple needs they give the same answer. The differences are about structure and capability.
The same question written both ways, “visits that cost more than the hospital average”:

SELECT visit_id, total_bill_kes
FROM visits_fact
WHERE total_bill_kes > (SELECT AVG(total_bill_kes) FROM visits_fact);

-- CTE
WITH avg_bill AS (SELECT AVG(total_bill_kes) AS overall_avg FROM visits_fact)
SELECT f.visit_id, f.total_bill_kes
FROM visits_fact f
CROSS JOIN avg_bill a
WHERE f.total_bill_kes > a.overall_avg;

What it does: The CTE lists each patient's visits starting with the most recent one and moving to the oldest. The main query retains only the most recent visit, where rn equals 1, and incorporates the descriptive names through joins. The result shows one row for each patient who has ever been visited.

Recursive CTEs

A recursive CTE references itself. It is built for data that is organized in a hierarchy or sequence, and it has two sections connected by UNION ALL.

1. Anchor member: the starting rows (runs once).
2. A recursive member is a query that uses the results from the CTE itself to generate the next set of rows, and this process keeps repeating until there are no more rows to return. In PostgreSQL and MySQL, the keyword is WITH RECURSIVE; in SQL Server, it is simply WITH. The hospital tables don't have a parent-child structure like a doctor-to-supervisor hierarchy, so the best recursive CTE to use here creates a continuous list of dates. This solves an actual reporting issue: days with no visits are not included in the GROUP BY visit_date, which affects trends and the 7-day moving average in Section 1.6. ### SQL Subqueries A subquery is a SELECT statement that is placed inside another SQL statement. The inner query executes, and its outcome is used by the outer query. Subqueries allow you to answer questions that rely on the result of another question.For example, to find out which visits cost more than the average, you first need to know what the average cost is.

Scalar subqueries

A scalar subquery gives back just one row and one column, which is a single value, so it can be used in any place where a single value is needed. If the query brings back more than one row, the database will show an error.
Scenario: Show all visits where the cost is higher than the average bill for the entire hospital.

SELECT visit_id, patient_id, total_bill_kes
FROM visits_fact
WHERE total_bill_kes > (SELECT AVG(total_bill_kes) FROM visits_fact);

What it does: The inner query returns one number (say 21,500). The outer query keeps only visits above it. The same idea works in the SELECT list:
SELECT visit_id,
total_bill_kes,
total_bill_kes - (SELECT AVG(total_bill_kes) FROM visits_fact) AS diff_from_hospital_avg
FROM visits_fact;

Non-correlated (independent) subqueries

A subquery that isn't related can work by itself; it doesn't need to look at the main query. It runs once and the result is used again. The scalar examples above are non-correlated,

Correlated subqueries

A correlated subquery refers to a column of the outer query, so it is logically re-evaluated once per outer row. It cannot run on its own.

Example: Find visits that cost more than the average for their own department.

SELECT f.visit_id, f.department_id, f.total_bill_kes
FROM visits_fact f
WHERE f.total_bill_kes > (
SELECT AVG(f2.total_bill_kes)
FROM visits_fact f2
WHERE f2.department_id = f.department_id -- correlation with the outer row
);

Nested subqueries

Subqueries can be placed inside other subqueries to any depth. Scenario: Find patients who were treated in the department with the highest total billing.

SELECT patient_id, county
FROM dim_patients
WHERE patient_id IN (
SELECT patient_id
FROM visits_fact
WHERE department_id = (
SELECT department_id
FROM visits_fact
GROUP BY department_id
ORDER BY SUM(total_bill_kes) DESC
LIMIT 1
)
);

What it does, from the inside out: first, it identifies the department that has the highest total billing; then, it lists all the patients who have visited that department; finally, it provides the details of those patients. It works, but reading through three levels already requires some effort, and that's when using a CTE or a join would be a better option.(LIMIT 1 is used in PostgreSQL and MySQL; SQL Server uses TOP 1 instead.)

Subqueries with IN, EXISTS, ANY and ALL

IN checks if a value is found in the list of results from the subquery, as shown in section 4.2.
EXISTS returns TRUE when the subquery has at least one row; it only checks if a match is present, not what the actual values are. It often goes along with that and stops at the first match.

-- Patients who have at least one visit longer than 7 days
SELECT p.patient_id, p.county
FROM dim_patients p
WHERE EXISTS (
SELECT 1
FROM visits_fact f
WHERE f.patient_id = p.patient_id
AND f.length_of_stay_days > 7
);
The use of SELECT 1 is a common practice, as the actual value being selected does not matter. NOT EXISTS reverses the test and identifies patients who do not have such a visit.
IN vs EXISTS, and the NOT IN trap. For positive checks, the two usually bring back the same rows. Using NOT IN with a subquery that might return NULL can be risky.If the subquery gives a NULL, the comparison becomes unknown, and the entire query ends up returning no rows. "NOT EXISTS is safe when dealing with NULLs, so it's better to use it for questions about things that do not exist."

-- Patients who never had a visit (safe even if visits_fact.patient_id contains NULLs)
SELECT p.patient_id, p.county
FROM dim_patients p
WHERE NOT EXISTS (SELECT 1 FROM visits_fact f WHERE f.patient_id = p.patient_id);

Putting It All Together: A Real-World Hospital Question

Question: Which visits were readmissions within 30 days of the patient’s previous discharge, in which county and department, and how does each bill compare with its department average?

WITH stays AS (
SELECT patient_id, visit_id, department_id, visit_date, total_bill_kes,
LAG(visit_date + length_of_stay_days)
OVER (PARTITION BY patient_id ORDER BY visit_date) AS prev_discharge
FROM visits_fact
),
readmits AS (
SELECT *, visit_date - prev_discharge AS gap_days
FROM stays
WHERE prev_discharge IS NOT NULL
AND visit_date - prev_discharge <= 30
),
dept_avg AS (
SELECT department_id, AVG(total_bill_kes) AS avg_bill
FROM visits_fact
GROUP BY department_id
)
SELECT p.county,
d.department_name,
r.visit_id,
r.gap_days,
r.total_bill_kes,
ROUND(r.total_bill_kes - da.avg_bill, 2) AS vs_dept_avg
FROM readmits r
JOIN dim_patients p ON p.patient_id = r.patient_id
JOIN dim_departments d ON d.department_id = r.department_id
JOIN dept_avg da ON da.department_id = r.department_id
ORDER BY r.gap_days;
With the concepts combine: a window function (LAG) finds the previous discharge; a CTE chain keeps the steps readable and lets us filter on the window result; an aggregation inside dept_avg supplies a benchmark; and joins attach county and department names. The output is a ready-to-use quality-improvement list: one row per readmission with the gap in days and how costly the stay was relative to its department.