How to Write Multiple CTEs in SQL

Leverage the full potential of the CTE by combining two or more of them in a single SQL query.

Common table expressions, or CTEs, can be a powerful SQL tool. When you write two (or even more) CTEs together, this power multiplies. In this article, I’ll show you three ways of writing multiple CTEs:

• Using two independent CTEs in one SQL query.
• Using two CTEs where the second CTE refers to the first.
• Using two CTEs where one CTE is recursive.

If you’re not familiar with CTEs, I recommend our interactive Common Table Expressions course, which covers all the CTE types, including the recursive ones, in 114 hands-on exercises. You can read about the course in an episode of our Course of the Month series.

If you want to refresh your knowledge quickly, these articles about CTEs and when you should use them are a good start.

Introducing the Data

I’ll show you the CTE examples on a dataset consisting of two tables.

The first table is cars. It contains data about cars made by Renault and Nissan. Its columns are:

• id – The ID of the car and the primary key (PK) of the table.
• car_make – The manufacturer of the car.
• model – The model of the car.
• motor_type – The details about the motor type of the model.
• year_of_production – The year when the car was manufactured.

Here’s a sample data from the table:

The second table, car_sales, has these columns:

• id – The ID of the sale information and the primary key (PK) of the table.
• report_period – The date of the sales report.
• sales – The number of cars sold.
• cars_id – The foreign key (FK) that references the table cars.

Some sample data from the table:

Two CTEs: Independent

In this first example, I’ll show you how to output total sales overall as well as by make with two independent CTEs. The code is right here:

WITH sales_per_make AS (
	SELECT car_make,
		 SUM(sales) AS total_sales_per_make
	FROM cars c
JOIN car_sales cs
		ON c.id = cs.cars_id
GROUP BY car_make
),

sales_sum AS (
	SELECT SUM(sales) AS total_sales
	FROM car_sales
)
	
SELECT car_make,
	 total_sales_per_make,
	 total_sales
FROM sales_per_make, sales_sum ss;

I start writing the first CTE as if it were the one and only CTE in my query. The CTE name, sales_per_make, follows the keyword WITH, then comes the keyword AS. After that, I write what I want the CTE to do in parentheses.

In this case, I’m using the SUM() aggregate function to find the sales per car make. To do that, I have to join the two tables I have at my disposal.

After that comes the second CTE. The main thing here is that a comma must separate the two CTEs. Then, the second CTE does not start with the keyword WITH but rather immediately with the name of the second CTE. I’ve named it sales_sum. The SELECT statement in the parentheses calculates the total sales across all the car makes.

These two CTEs are independent because the second CTE does not reference the first one.

To use these queries, I have to write a SELECT statement (the main query) that references them. This is the same as when you write only one CTE. The SELECT statement here joins the results of the two CTEs to return this output:

The result means dealers sold 176,569 Renault cars and 185,359 Nissan cars, for a total of 361,928 cars.

Now you see writing two CTEs is not that hard. However, there are certain pitfalls when using multiple CTEs in a query. The main things you should watch out for when writing multiple CTEs are:

• Use only one WITH.
• Separate CTEs with commas.
• Do not use a comma before the main query.
• There’s only one main query.

Having Multiple CTEs work only if you write the WITH keyword once. But this is not just anywhere you want. You have to write it before the first CTE. The second and any following CTE starts with the name of the respective CTEs, unlike the first CTE which starts with the WITH keyword.

The first CTE is separated from the second one by a comma. This also goes if you write more than two CTEs: all the CTEs are separated by a comma. However, no matter how many CTEs you have, there’s no comma between the last CTE and the main query.

Finally, there’s only one main query. Whatever you want to calculate, it works only if there’s one main query. It seems logical because you can reference all the CTEs you want to join as any other table. That’s one of the benefits of CTEs, so take advantage!

Two CTEs: One Referencing the Other

In this slightly more complex calculation, I use two CTEs again. This time, the second one references the first one. This is the only option if you want one CTE to reference another. You can reference only the CTEs before the current one and not the CTEs that follow.

I’ll write them to calculate actual sales in 2022 (namely, in January and February), budget the 2022 annual sales using the average sales, and finally find the sales yet to be made in 2022.

I’ll lead you step by step through the code below, so you understand what’s going on here:

WITH sales_per_car AS (
	SELECT c.id,
		 c.car_make,
	   	 c.model,
	       c.motor_type,
	       c.year_of_production,
	       AVG(cs.sales)::INT AS average_sales_2022
	FROM cars c JOIN car_sales cs ON c.id = cs.cars_id
	WHERE c.year_of_production = 2022
	GROUP BY c.id, c.car_make, c.model, c.motor_type, c.year_of_production
),

budget AS (
	SELECT *,
		 average_sales_2022 * 12 AS annual_planned_sales_2022
	FROM sales_per_car
)
	
SELECT b.car_make,
	 b.model,
	 b.motor_type,
	 b.year_of_production,
	 SUM(cs.sales) AS actual_ytd_sales_2022,
	 b.annual_planned_sales_2022,
	 b.annual_planned_sales_2022 - SUM(cs.sales) AS remaining_annual_sales_2022
FROM budget b
JOIN car_sales cs
ON b.id = cs.cars_id
GROUP BY b.car_make, b.model, b.motor_type, b.year_of_production, b.annual_planned_sales_2022;

The syntax here is the same as in the previous example. The CTE sales_per_car returns some columns from the table cars. I also use the AVG() function to calculate the average sales in 2022. This number is converted into an integer because I’m talking about cars, so I want to see the average as a whole number of cars.

This CTE gets me the average sales for every model produced in 2022. It also hints at why I know the sales are only for 2022: cars produced in 2022 couldn’t be sold in 2021. That’s the assumption here, at least.

A comma separates this CTE from the second one, which starts with its name, budget. This CTE now references the first CTE as any other table. You can see that in the FROM clause. I’m using the column average_sales_2022 because it’s the average actual monthly sale for 2022. Let’s say the method for budgeting here is multiplying the average monthly sales for that year by 12 to get the planned annual sales. That’s exactly what this second CTE is doing, and this is the reason it has to reference the first CTE.

The main query joins the CTE budget and the table car_sales. I’m using this query to find the actual sales per model in 2022. Then, I’m showing the column annual_planned_sales_2022 from the second CTE. Finally, by calculating the difference between these two columns, I get the number of sales yet to be made for the remainder of 2022.

Here’s the report I get by running the query:

Two CTEs: One of the CTEs Is Recursive

I’ll now move on from this data but stay within these car brands. Let’s imagine Renault is considering three investments: buying Jaguar for 2,300,000,000, Volvo for 1,548,470,000, or Alfa Romeo for 2,450,000,000. It’s considering doing that on its own, together with Nissan, or with both Nissan and Citroën.

This example is perfect for writing two CTEs, with one being recursive:

WITH RECURSIVE company_purchase AS (
	SELECT 2300000000 AS amount
UNION
	SELECT 1548470000 AS amount
UNION
	SELECT 2450000000 AS amount
),

per_buyer AS (
	SELECT  0 AS number_of_buyers,
		  0::DECIMAL AS purchase_amount,
		  0::DECIMAL AS amount_per_buyer
UNION
	SELECT  number_of_buyers + 1,
		  amount,
		  amount/(number_of_buyers + 1)::DECIMAL
	FROM company_purchase, per_buyer
	WHERE number_of_buyers <= 3)

SELECT *
FROM per_buyer
ORDER BY purchase_amount, number_of_buyers;

Whenever you want a recursive CTE, you need to start writing CTEs with WITH RECURSIVE. You always announce your intention to write a recursive CTE, whether this recursive query is the first or the second CTE. In this case, my first CTE is non-recursive.

I use the company_purchase CTE to create different investments in Jaguar, Volvo, or Alfa Romeo.

Then comes the recursive query. The principle is the same: separate CTEs by a comma and start the second CTE without the keyword WITH. In this second CTE, my starting point is no investment at all and no buyers. Values everywhere will be zero. Then I use recursion, and the query calculates the amount per buyer for one, two, or three investors for the first investment. The recursion then repeats the same calculation for the second and third investments. Of course, to do that, I have to join the recursive query with the non-recursive one.

Finally, the main query selects all data from the per_buyer CTE, with the following output:

What does this data tell me? For example, if three buyers (Renault, Nissan, and Citroën) buy Volvo for 1,548,470,000, each company should invest 516,156,666.67.

Further examples can be found in the article about the top 5 SQL CTE interview questions and in an additional article talking about using two CTEs.

Multiply the Power of the CTEs Further

These three are just examples of what the CTEs can do, especially of what they can do if you combine multiple CTEs in different ways. This is not an easy concept and requires a lot of practice.

Instead of making up your own data and scenarios, take our interactive Common Table Expressions course! It gives you all that and eliminates all the worries you have about how to practice CTEs, with over 100 hands-on exercises!