How to Use 2 CTEs in a Single SQL Query

Have you ever wondered how to use multiple CTEs in one SQL query? Read this article and find out about recursive CTEs.

After learning common table expressions or CTEs, a natural question is “Can I use several CTEs in one query?” Yes, you can! And you can do it quite easily, especially if you already have some basic knowledge of CTEs. Whether you know a bit about CTEs or you’re entirely new to the CTE world, reading about what a CTE is is always a good start.

What you’ll learn in this article can be practiced in the LearnSQL.com Recursive Queries course, which was our course of the month in August.

I’ll start by explaining how to use two CTEs in one query. Then I’ll teach you how to use CTEs where the second CTE refers to the first one. To spice this article up, I’ll finish by showing you two queries featuring CTEs, one of which is recursive.

2 CTEs, 1 SQL query, 0 problems

Let’s imagine there’s a table named logins that stores login data. It contains the columns:

• id – The ID of the login.
• username – The user who logged in.
• login_date – the date of that login.
• login_start – When the user logged in.
• login_end – When the user logged out.

If you look at the table, you’ll notice that every username shows up at least once.

If your task is to calculate the average time (in minutes) every user has spent logged in, how would you do it using CTEs?

You’ll need two CTEs, with the code looking like this:

WITH distinct_user AS (
	SELECT DISTINCT username
FROM logins),

minutes_logged AS (
	SELECT	username,
			DATEDIFF (minute, login_start, login_end) AS minutes
FROM logins)

SELECT	u.username,
		AVG (minutes) AS avg_time_logged
FROM distinct_user AS u 
JOIN minutes_logged AS m 
ON u.username = m.username
GROUP BY u.username;

First, I’ll just mention that you have to have distinct usernames for this to work. Now, let’s start writing the first CTE! Nothing unusual here – as with every CTE, you define it by WITH; they aren’t called WITH queries for nothing!

What follows is the CTE name; in this case, the first CTE is named distinct_user. You follow this with the AS keyword, and then you simply define the regular SELECT statement inside the parentheses. This SELECT statement simply selects unique usernames from the table logins.

Once you have distinct usernames, you have to calculate how much time the user was logged in. This is the critical part – writing the second CTE. My second CTE is named minutes_logged.

You’ll notice there is no WITH before the second CTE. This is extremely important! After you’ve defined the first CTE, it is separated from the second one only by the comma, i.e. you write WITH only once. After that, it doesn’t matter how many CTEs you define; it’s only important that you separate them by comma and start every CTE using its name.

Let’s now analyze what the minutes_logged CTE does. It first takes the column username from the table logins. Then it calculates the difference between the login_start and the login_end in minutes. This new value will be shown in the minutes column.

You’ll have to calculate the average time (in minutes) every user spent being logged in. For that part of the calculation, I’ve used the outer query. It selects the username from the CTE distinct_user and then calculates the average logged-in minutes using the column minutes from the CTE minutes_logged.

Those two CTEs are joined using the column username. Finally, the result is grouped by the same column, since we want the result on a user level.

But Can I Use One CTE Inside Another CTE?

A straightforward question deserves a straightforward answer: yes, you can. Now that you know how to use multiple CTEs, writing a CTE that references another CTE is just a variation of what you’ve learned. Since it’s a variation, I think it’s best to show you how to do it using an example you’re already familiar with.

Let’s go back to the previous example. I’ll tweak the code a little to show you how to write a CTE that references the first CTE. This is the new version of our earlier code:

WITH difference AS (
	SELECT 	username,
		 	DATEDIFF (minute, login_start, login_end) AS minutes
FROM logins),

average_logged AS (
SELECT	username,
			AVG (minutes) AS average
FROM difference
GROUP BY username)

SELECT DISTINCT 		username,
				average
FROM average_logged;

The logic is the same as in the first example, it’s just that the steps are in a different order. The first CTE, difference, calculates the minutes spent with every login; this is the same as in the first example. Now that I have the minutes, I have to calculate the average time (in minutes) spent by each user. That’s what I’ll use the CTE average for. As you remember, we put a comma between the two CTEs and start the second one by its name – no need to use WITH again! This second CTE calculates the average time for each login; for that, it uses the column minutes from the first CTE and stores the result in the column average.

To reference the CTE difference in the second CTE, you treat it as a table: FROM difference. Finally, you group the result by the username, since you don’t want the average for all users.

The outer query then simply selects the columns username and average from the CTE average_logged. That way, you get the same result as in the first example:

If those examples are not enough for you, there’s an article that gives you several more examples to practice. This includes using more than one CTE as well.

Using Recursive CTEs with Non-Recursive CTEs

There’s a reason why CTEs are taught in our Recursive Queries course. It’s because CTEs can also be recursive. This is the most complicated part of learning CTEs. (I’m assuming that you already know what a recursive CTE is and how to write it. If not, see Long SQL Query vs. Recursive SQL Query for an introduction and Do It in SQL: Recursive SQL Tree Traversal for an in-depth example. ) However, maybe you didn’t realize that you can use more than one CTE, even if one of them is recursive. Let’s see how it’s done.

For this example, let’s imagine that you want to buy a company with your friends. There are four options for the investment, each requiring a different amount of money. You’re still in early talks. You’re not sure how many friends will participate; their participation depends on the amount of money they have to invest. This amount depends on the total number of investors involved and the chosen investment option.

To help them decide, you’ve decided to calculate the amount required per investor for all four investment options with a number of investors from one to ten.

In this example, we first have a non-recursive CTE followed by a recursive CTE:

WITH RECURSIVE investment AS (
	SELECT amount 5897645 AS investment_amount
	UNION 
SELECT 4536841 AS investment_amount
	UNION 
SELECT 3852457 AS investment_amount
	UNION 
SELECT 3452115 AS investment_amount
),

per_investor AS (
	SELECT	0 AS investors_number,
			0 AS amount,
			0 AS individual_amount
	UNION 
	SELECT	investors_number + 1,
			investment_amount,
			investment_amount/(investors_number + 1)
	FROM investment, per_investor
	WHERE investors_number < 10)

SELECT *
FROM per_investor
ORDER BY  amount, investors_number;

The main distinction is evident from the start! When writing recursive queries, you have to start your code using WITH RECURSIVE. As you’ll notice, you don’t have to start with the recursive CTE itself; in this example, a non-recursive CTE comes first, even though we use WITH RECURSIVE immediately before defining it. You can write CTEs in whatever order you want; the recursive one can be first or it can be last. But it’s important to remember that if you’re going to have at least one recursive query, writing WITH RECURSIVE is mandatory.

In the current example, my first (non-recursive) CTE is named investment. Since I don’t have the table with all the investment amounts, I’ve decided to use the CTE as a temporary table. As you can see, possible investment amounts are as follows:

• 5,897,645
• 4,536,841
• 3,852,457
• 3,452,115

By using UNION with SELECT in the first CTE, I’m virtually creating a table that contains those four investment possibilities. They will be shown in the column investment_amount. The resulting table for this CTE is:

The second CTE is recursive. However, this changes nothing compared to writing two non-recursive CTEs: the comma again separates the CTEs.

Let’s analyze the second CTE a little bit. The first SELECT statement defines three columns that have the value 0: investors_number, amount, and individual_amount. As I said, the recursive CTE refers to itself, i.e. the UNION operator is required.

The UNION operator is followed by one or more SELECT statements, which will perform the desired operation on the first SELECT statement. This means it will add 1 to the column investors_number. Then it will put investment_amount from the first CTE in the column amount. It will divide the investment amount with the number of involved investors; the result is shown in the column individual_amount. It will perform this operation for all four investment amounts until it reaches ten investors.

The outer query simply selects all the data from the CTE per_investor, with the data being ordered by the columns amount and investors_number. Run this query and enjoy all the possibilities you’ve calculated:

Pretty impressive, isn’t it?

Combining Two or More CTEs Really Works

SQL common table expressions are a powerful tool. They are helpful to have if you want your code to be more readable, but their value is not only cosmetic; their real possibilities shine if you know how to use several CTEs in one query or even write a recursive CTE in SQL.

As you saw, it’s not difficult to combine two or more CTEs. Once you know that, it really unlocks the possibilities of using CTEs in various situations. Now it’s up to you to find yourself cases where you’ll have to use CTEs. And practice, practice, practice! It’s always advisable to use our Recursive Queries course for that. It will give you tables to practice on, so you don’t have to worry about creating scenarios yourself.

Feel free to share your experience with the SQL CTEs in our comments section.