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 BY divides rows into groups.
• ORDER BY determines 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.

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

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.

SQL #DataAnalytics #DataAnalysis #Database #LearningInPublic