SQL Deep Dive: Window Functions, Joins, CTEs, and Subqueries

Introduction

This article covers four SQL concepts: window functions, joins, common table expressions (CTEs) and subqueries. Window functions let you rank rows, calculate running totals and compare a row to the ones around it without losing any data. Joins bring information from different tables together. CTEs break a long query into small named steps, and subqueries let one query use the result of another.

This article will use a hospital dataset which contains various tables. For each concept I explain what it does, show a query on that data and give the expected result.

1. Window Functions

A window function calculates across a set of rows related to the current row without collapsing them. Window function keeps every row and attaches the result to each one. Thus, for each row, SQL opens a "window" that shows some other rows. It looks through that window, calculates something, and writes the answer next to the row.

a. Ranking Functions

Ranking functions give each row a position inside its group. They include, ROW_NUMBER(), RANK(), and DENSE_RANK(), and differ only in how they handle ties that is rows with equal values.

• ROW_NUMBER()-Ignores ties. Gives every row a unique number
• RANK()-Tied rows get the same rank. The next rank skips numbers
• DENSE_RANK()-Tied rows get the same rank. The next rank has no gaps

Based on the hospital dataset, we rank the medicine department based on the highest satisfaction score from highest as follows:

The result will be:

Visit_id 1002 and 1029 both scored 4.1, so both get rank 2.
After that, RANK jumps to 4 because rank 3 was "used up" by the tie. DENSE_RANK simply moves to 3.
ROW_NUMBER never repeats. When scores tie, it uses visit_id to decide the order. That is why visit_id was added to the ORDER BY.

NTILE(n)

NTILE(n) splits the ordered rows into n buckets of roughly equal size and tells which bucket each row is in. It is useful for quartiles (NTILE(4)) as well as percentiles.
Based on the hospital dataset, split the cardiology visits into groups of 4 based on the bill size, from highest bill to lowest bill:

The result will be:

This translates that 9 rows are divided by 4 buckets and leaves 1 extra row. The extra row goes to the first bucket, so bucket 1 has 3 rows and the other buckets have 2 each. Bucket 1 holds the most expensive visits.

b. Offset Functions

Offset functions lets a row look at another row in the same group: the one before, the one after, the first, or the last.
LAG() and LEAD()
LAG(column, offset, default) looks backward (at an earlier row).
LEAD(column, offset, default) looks forward (at a later row).

The offset is how many rows to move. It is 1 if left out. The default is the value to show when there is no such row otherwise, it is NULL.

In the hospital dataset, for instance patients came back for a second visit of patient IDs 1, 2, and 5. To get how their bills changed and how many days passed between visits is as follows:

The result will be:

Partition by patient_id means each patient is handled separately, so no one patient is compared to another.
A patient's first visit has no earlier visit, so previous_bill is null. Their last visit has no later visit, so next_bill is null.

3. Joins

A join matches rows from two tables using a condition, usually a primary key on one side and a foreign key on the other:
Below is the syntax used for joins:

select column
from table 1 
join kind table 2 
on table 1.common_column=table 2.common_column;

a. Inner Join

Inner Join - returns only the rows with matches in both tables
For instance, show the patients and the doctor assigned to them

The result will provide the doctors assigned to the respective patients. The 'f' and 'd' are aliases that save typing, ease of referencing and make it clear which table each column comes from.

b. Full Join

Full join returns everything from both/all tables regardless whether there is a match or not. Where rows match, they are combined. Where a row has no match, the other side is filled with null.

It basically returns the left join result and the right join result in one query.

The result will provide a list of all the doctors and the procedures done

c. Left Join

Left Join - returns every record from the left join, which is the table written before the join and matches on the right.
For instance, list all doctors and their respective departments

The result will provide all doctors and their respective departments

d. Right Join

Right Join - Returns every record from the right table and matches to the left
For instance, list all doctors and their respective departments

The result will provide all doctors and their respective departments

e. Cross Join

Cross Join - gives every possible combination. It pairs every row of the first table with every row of the second.

No need for the on clause. The only join where no common column is needed

For instance, in the hospital dataset suppose we want a show visit counts for every department.

The result will provide the following visits of each department

f. Natural Join

Natural Join joins tables with the same column names. It looks at both tables, finds every column that has the same name, and joins on all of them. It does not need to have an ON condition.
For instance, suppose we find the visits with their department name and respective bill costs

The result of the query will be

4. Subqueries

A subquery is a query written inside another query. It sits in parentheses, and the outer query uses its result.
Depending on where the subqueries are written there are different types of subqueries

• where - lets the outer query filter rows using the answer of another query.
• having - filters aggregated rows
• select - generates a calculated column

For instance, the visits that costs more than the average bill:

select visit_id, total_bill_kes
from facts_visits 
where total_bill_kes > (select avg(total_bill_kes) from facts_visits);

The result generated will be

Subqueries can either be correlated or non-correlated

• Correlated - subquery that cannot be executed without the main query
• Non-correlated - subquery that can be executed without the main query. For instance

(select avg(total_bill_kes)

Subquery Categories

Scalar - returns a single value
Table - returns multiple rows and multiple columns
Column - returns 1 column, multiple rows
Rows - returns 1 row, multiple columns

5. Common Table Expressions (CTEs)

A Common Table Expression or CTE, is a named temporary result that is defined at the top of a query and then used like a table in the rest of that query. It is a way to give a name to a step in the query.
A CTE starts with the word with
Think of it as giving a name to a subquery. For instance, which departments earned more than 100,000.
This first version depicts the query with a subquery, that is a step inside another query

select d.department_name, f.total_revenue
from(select department_id, sum(total_bill_kes) as total_revenue
from facts_visits f
group by department_id) as f
join departments d 
on d.department_id = f.department_id
where f.total_revenue > 100000
order by f.total_revenue desc;

This second version depicts a query with a CTE

with dept_revenue as 
(select department_id, sum(total_bill_kes) as total_revenue
from facts_visits f
group by department_id)
select d.department_name, f. total_revenue
from dept_revenue f
join departments d 
on d.department_id = f.department_id
where f.total_revenue > 100000
order by f.total_revenue desc;

The result for both queries will be as follows:

Conclusion

Window functions, joins, subqueries and CTEs each solve a different problem. Window functions rank rows and calculate running totals without losing any data. Joins combine tables, and the join type decides what happens to rows with no match. Subqueries let one query use the result of another, and CTEs turn long queries into named steps that are easy to read.