Back to articles list September 17, 2020 - 9 minutes read How to Use 2 CTEs in a Single SQL Query Tihomir Babic Tihomir is a financial and data analyst turned database designer from Zagreb, Croatia. He has extensive experience in the financial services industry, which helps him combine his finance background with his interest in data. When not tinkering with data, he likes to play the guitar in a band and pretend to be a musician. He also regularly writes columns for the Croatian LGBT news site CroL.hr and hopes to soon write his first novel. Tags: sql learn sql CTE 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. idusernamelogin_datelogin_startlogin_end 1JohnXYZ2020-07-037:02:547:08:12 2JohnXYZ2020-07-059:03:2111:08:04 3JohnXYZ2020-07-1214:08:1214:52:13 4Sarah822020-07-0814:05:1215:01:56 5Sarah822020-07-0816:22:4717:13:00 6SugarCane1232020-07-0218:22:4718:42:15 7SugarCane1232020-07-2510:12:5312:52:44 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. usernameavg_time_logged JohnXYZ58 Sarah8253 SugarCane12390 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: usernameavg_time_logged JohnXYZ58 Sarah8253 SugarCane12390 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: investment_amount 3,452,115 3,852,457 4,536,841 5,897,645 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: investors_numberamountindividual_amount 000 13,452,1153,452,115 23,452,1151,726,057 33,452,1151,150,705 43,452,115863,028 53,452,115690,423 63,452,115575,352 73,452,115493,159 83,452,115431,514 93,452,115383,568 103,452,115345,211 13,852,4573,852,457 23,852,4571,926,228 33,852,4571,284,152 43,852,457963,114 53,852,457770,491 63,852,457642,076 73,852,457550,351 83,852,457481,557 93,852,457428,050 103,852,457385,245 14,536,8414,536,841 24,536,8412,268,420 34,536,8411,512,280 44,536,8411,134,210 54,536,841907,368 64,536,841756,140 74,536,841648,120 84,536,841567,105 94,536,841504,093 104,536,841453,684 15,897,6455,897,645 25,897,6452,948,822 35,897,6451,965,881 45,897,6451,474,411 55,897,6451,179,529 65,897,645982,940 75,897,645842,520 85,897,645737,205 95,897,645655,293 105,897,645589,764 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. Tags: sql learn sql CTE You may also like Improving Query Readability with Common Table Expressions What is a Common Table Expression, or CTE? Where do you use them, and why? This post answers your questions. Read more How to Organize SQL Queries When They Get Long Long queries are very hard for beginners to structure and understand. Learn the best practices for writing and formatting complex SQL code! Read more Subquery vs. CTE: A SQL Primer SQL subqueries and CTEs seem similar, but even pros may not know all their quirks. Learn their similarities, differences, and best use cases. Read more Long SQL Query vs. Recursive SQL Query Explore recursive CTE hierarchy in SQL server and find out how to tune your SQL query performance. Read more How to Organize SQL Queries with CTEs Common table expressions (CTEs) allow you to structure and organize your SQL queries. It is a necessity when you begin to move deeper into SQL. Read more Simplify SQL Code: Recursive Queries in DBMS Are you repeating the same query in every report? Are they getting too complicated? Use recursive queries to simplify SQL code! Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.