Introduction
As I continue learning SQL for data analysis, I have come across some concepts that help answer more complex questions than basic SELECT, WHERE, and GROUP BY.
In this article, I will explain four of them using examples from a hospital dataset: Window Functions, Joins, Common Table Expressions (CTEs), and Subqueries.
The goal is to understand what each one does and when to use it in a real situation.
The examples use assumed table and column names. They may need to be adjusted to match the actual hospital dataset.
1. SQL Window Functions
What are Window Functions?
Window functions allow us to calculate values across related rows without removing the individual records.
For example, a hospital analyst may want to rank patients by billing amount while still seeing each patient's record.
Basic Syntax
SELECT
VisitID,
Amount,
SUM(Amount) OVER () AS TotalBilling
FROM Billing;
OVER() tells SQL which set of rows to use for the calculation.
-
PARTITION BYdivides rows into groups. -
ORDER BYdetermines the order of rows. - A window frame determines which rows are included in a calculation.
Example of a running total:
SUM(Amount) OVER (
ORDER BY VisitID
ROWS BETWEEN UNBOUNDED PRECEDING
AND CURRENT ROW
)
This adds the current bill to all the preceding bills in the specified order.
Ranking Functions
1. ROW_NUMBER()
Assigns a unique number to each row.
ROW_NUMBER() OVER (
PARTITION BY PatientID
ORDER BY VisitDate
)
This numbers each patient's visits from earliest to latest.
2. RANK()
Ranks records and gives tied values the same rank, leaving gaps afterward.
RANK() OVER (ORDER BY Amount DESC)
This ranks hospital bills from highest to lowest.
3. DENSE_RANK()
Works like RANK(), but does not leave gaps between ranks.
DENSE_RANK() OVER (ORDER BY Amount DESC)
This is useful when ranking patients or departments by billing amount.
4. NTILE()
Divides ordered records into approximately equal groups.
NTILE(4) OVER (ORDER BY Amount DESC)
This divides billing records into four groups for cost analysis.
Navigation Functions
5. LAG()
Returns a value from a previous row.
LAG(VisitDate) OVER (
PARTITION BY PatientID
ORDER BY VisitDate
)
This helps identify a patient's previous visit.
6. LEAD()
Returns a value from a following row.
LEAD(VisitDate) OVER (
PARTITION BY PatientID
ORDER BY VisitDate
)
This helps compare a visit with the next recorded visit.
7. FIRST_VALUE()
Returns the first value in the specified window.
FIRST_VALUE(VisitDate) OVER (
PARTITION BY PatientID
ORDER BY VisitDate
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
)
This identifies the first recorded visit for each patient.
8. LAST_VALUE()
Returns the last value within the specified window frame.
LAST_VALUE(VisitDate) OVER (
PARTITION BY PatientID
ORDER BY VisitDate
ROWS BETWEEN UNBOUNDED PRECEDING
AND UNBOUNDED FOLLOWING
)
This displays the latest recorded visit date for each patient.
Aggregate Window Functions
These functions calculate values across rows.
| Function | What it does | Hospital example |
|---|---|---|
SUM() |
Adds values | Total billing |
AVG() |
Calculates the average | Average bill |
COUNT() |
Counts rows or values | Number of visits |
MIN() |
Finds the smallest value | Lowest bill |
MAX() |
Finds the largest value | Highest bill |
Example:
SELECT
VisitID,
Amount,
AVG(Amount) OVER () AS AverageBill
FROM Billing;
This displays each bill alongside the average bill across the dataset.
The main difference between a normal aggregate and a window aggregate is that GROUP BY usually reduces records to one row per group, while a window function keeps the individual rows.
2. SQL Joins
What are Joins?
Hospital data is usually stored in different tables. For example, patient details may be in Patients, visits in Visits, and charges in Billing.
Joins allow us to combine related records using a common column.
INNER JOIN
Returns only matching records from both tables.
SELECT v.VisitID, b.Amount
FROM Visits AS v
INNER JOIN Billing AS b
ON v.VisitID = b.VisitID;
Use this when you only need visits with matching billing records.
LEFT JOIN
Returns all records from the left table and matching records from the right.
SELECT p.PatientID, v.VisitID
FROM Patients AS p
LEFT JOIN Visits AS v
ON p.PatientID = v.PatientID;
This includes patients who have never visited the hospital. Their visit columns will contain NULL.
RIGHT JOIN
Returns all records from the right table and matching records from the left.
SELECT v.VisitID, b.BillID
FROM Visits AS v
RIGHT JOIN Billing AS b
ON v.VisitID = b.VisitID;
This is useful when every billing record must appear, even if a matching visit is missing.
FULL OUTER JOIN
Returns matching and unmatched records from both tables.
SELECT v.VisitID, b.BillID
FROM Visits AS v
FULL OUTER JOIN Billing AS b
ON v.VisitID = b.VisitID;
This can help identify missing links during a billing audit. Note that MySQL does not directly support FULL OUTER JOIN.
CROSS JOIN
Returns every possible combination of rows from two tables.
SELECT d.Department, s.ShiftName
FROM Departments AS d
CROSS JOIN Shifts AS s;
This can help generate department and shift combinations for staffing plans.
SELF JOIN
Joins a table to itself.
SELECT
d.DoctorName AS Doctor,
m.DoctorName AS Manager
FROM Doctors AS d
LEFT JOIN Doctors AS m
ON d.ManagerID = m.DoctorID;
This displays each doctor alongside their manager.
Quick Comparison
| Join | Main purpose |
|---|---|
INNER JOIN |
Only matching records |
LEFT JOIN |
All left-side records |
RIGHT JOIN |
All right-side records |
FULL OUTER JOIN |
All records from both sides |
CROSS JOIN |
Every combination |
SELF JOIN |
Relate records in the same table |
One thing I have learned is that joins can increase the number of rows when multiple records match. This is important when calculating billing totals because careless joins can duplicate amounts.
3. Common Table Expressions (CTEs)
What is a CTE?
A CTE is a named query result created using WITH. It makes complex SQL easier to read by breaking it into smaller steps.
Basic CTE
WITH PatientBilling AS (
SELECT
PatientID,
SUM(Amount) AS TotalBilling
FROM PatientBillingData
GROUP BY PatientID
)
SELECT *
FROM PatientBilling
WHERE TotalBilling > 5000;
This calculates each patient's total billing and then returns patients whose total exceeds 5,000. PatientBillingData is an illustrative table name.
Multiple CTEs
We can define more than one CTE when a problem requires several calculations.
WITH VisitCounts AS (
SELECT PatientID, COUNT(*) AS TotalVisits
FROM Visits
GROUP BY PatientID
),
BillingTotals AS (
SELECT v.PatientID, SUM(b.Amount) AS TotalBilling
FROM Visits AS v
JOIN Billing AS b
ON v.VisitID = b.VisitID
GROUP BY v.PatientID
)
SELECT vc.PatientID, vc.TotalVisits, bt.TotalBilling
FROM VisitCounts AS vc
JOIN BillingTotals AS bt
ON vc.PatientID = bt.PatientID;
The first CTE counts visits, the second calculates billing totals, and the final query combines them.
Recursive CTEs
Recursive CTEs reference their own results to work through hierarchical data, such as a hospital's doctor-manager structure.
WITH RECURSIVE DoctorHierarchy AS (
SELECT DoctorID, DoctorName, ManagerID, 0 AS Level
FROM Doctors
WHERE ManagerID IS NULL
UNION ALL
SELECT d.DoctorID, d.DoctorName,
d.ManagerID, dh.Level + 1
FROM Doctors AS d
JOIN DoctorHierarchy AS dh
ON d.ManagerID = dh.DoctorID
)
SELECT *
FROM DoctorHierarchy;
The first part identifies the top-level doctors. The recursive part finds the doctors reporting to them and continues through the hierarchy.
This syntax is for PostgreSQL and other databases supporting WITH RECURSIVE. SQL Server uses different recursive CTE syntax.
CTEs with Joins and Aggregations
CTEs can summarize data before joining it to another table.
For example, we can calculate department billing using SUM() and GROUP BY, then join the result to a department table to display department names and totals.
CTEs vs Subqueries
A subquery is a query inside another query. A CTE gives a query a name before the main query uses it.
Both can solve similar problems, but CTEs often make long queries easier to understand and maintain. Neither is automatically faster; performance depends on the database and execution plan.
4. SQL Subqueries
What is a Subquery?
A subquery is a query placed inside another SQL query. It helps us use the result of one calculation to answer another question.
Scalar Subquery
Returns one value.
SELECT VisitID, Amount
FROM Billing
WHERE Amount > (
SELECT AVG(Amount)
FROM Billing
);
This returns bills that are higher than the average bill.
Non-Correlated Subquery
Runs independently of the outer query because it does not reference the outer query's columns.
SELECT VisitID, Department
FROM Visits
WHERE Department IN (
SELECT Department
FROM Doctors
WHERE Department = 'Cardiology'
);
This finds visits in a department returned by the inner query.
Correlated Subquery
References a column from the outer query.
SELECT p.PatientID, p.PatientName
FROM Patients AS p
WHERE EXISTS (
SELECT 1
FROM Visits AS v
WHERE v.PatientID = p.PatientID
);
This returns patients who have at least one visit.
Nested Subquery
A subquery placed inside another subquery.
SELECT PatientID
FROM Billing
GROUP BY PatientID
HAVING SUM(Amount) > (
SELECT AVG(TotalAmount)
FROM (
SELECT SUM(Amount) AS TotalAmount
FROM Billing
GROUP BY PatientID
) AS PatientTotals
);
This example assumes billing records contain PatientID. It compares each patient's total billing with the average of patient-level totals.
IN
Checks whether a value matches any value returned by a subquery.
SELECT PatientID
FROM Visits
WHERE Department IN (
SELECT Department
FROM Doctors
WHERE Department = 'Cardiology'
);
This finds visits in Cardiology when that department is present in the doctor records.
EXISTS
Checks whether a subquery returns at least one row.
SELECT p.PatientID
FROM Patients AS p
WHERE EXISTS (
SELECT 1
FROM Visits AS v
WHERE v.PatientID = p.PatientID
);
This returns patients with recorded visits.
ANY
The comparison needs to be true for at least one value returned by the subquery.
SELECT VisitID, Amount
FROM Billing
WHERE Amount > ANY (
SELECT Amount
FROM Billing
WHERE Amount < 5000
);
This finds bills that exceed at least one bill below 5,000.
ALL
The comparison must be true for every value returned by the subquery.
SELECT VisitID, Amount
FROM Billing
WHERE Amount > ALL (
SELECT Amount
FROM Billing
WHERE Amount < 5000
);
This finds bills that exceed every bill below 5,000.
Subqueries in SELECT, FROM and WHERE
In SELECT: Add a calculated value to each row.
SELECT VisitID,
(SELECT AVG(Amount) FROM Billing) AS AverageBill
FROM Billing;
In FROM: Create a derived table that can be queried.
SELECT PatientID, TotalBilling
FROM (
SELECT PatientID, SUM(Amount) AS TotalBilling
FROM Billing
GROUP BY PatientID
) AS PatientTotals;
In WHERE: Filter records based on another query's result.
SELECT VisitID, Amount
FROM Billing
WHERE Amount > (SELECT AVG(Amount) FROM Billing);
The main difference is where the result of the subquery is used: as a selected value, as a dataset, or as a filtering condition.
Conclusion
These four SQL concepts help me move beyond basic queries and answer more practical data analysis questions.
- Window functions help with ranking, comparisons, and calculations across rows.
- Joins bring related data together from different tables.
- CTEs make complex queries easier to organize.
- Subqueries allow one query to use the results of another.
For me, the important part is not just memorizing the syntax. It is understanding the problem first and choosing the SQL technique that solves it.
As I continue working with hospital data, these concepts will help me analyse patient visits, billing, department activity, and missing records more effectively.
Top comments (0)