Back to articles list Articles Cookbook
5 minutes read

How to Organize SQL Queries with CTEs

Table of Contents

Common table expressions (CTEs) allow you to structure and organize SQL queries. Knowing how to organize SQL queries is a necessity when you begin to move deeper into SQL, so if you want to become an SQL master, you need to know CTEs.

The SQL CTE has been part of standard SQL for some time now. CTEs – which are also called WITH statements – are available in all major RDBMS. When you use a CTE in SQL, it’s quicker and easier to write code. So let’s introduce ourselves to the Common Table Expression in SQL.

The goal of this article is to show you the basics of the SQL CTE and explain how to organize SQL queries using them. But you won’t be an expert; you’ll need more experience and hands-on learning for that. I suggest that after reading this article you check out the LearnSQL.com SQL CTE course for some interactive exercises.

Getting to Know CTEs

In its basic form, a SQL CTE is a named temporary result set within a SELECT, INSERT, UPDATE, or DELETE statement. CTEs replace subqueries, views, and inline user-defined functions.

SQL developers use CTEs to create hierarchical queries and to re-factor and organize SQL queries for better readability or performance.

Let’s consider an SQL CTE example for code organization. We’ll start by looking at three tables containing information about “sales”, “customers” and sale “items”.

For every row in the “sales” table, we know:

  • The salesperson who sold the item(s)
  • The number of items sold in the transaction
  • The price per each item
  • The time of the sale
  • The customer who bought the item(s)

In our SQL CTE example, we would like to know the percentage of each item’s revenue for different customers, or how one item has been distributed to different customers.

SELECT 
	sum(sales_num*sales_price) sum_sales_revenue,
	sales_item_id  
FROM sales  
GROUP BY sales_item_id 
ORDER BY sum_sales_revenue

The result set is of this query is:

SUM_SALES_REVENUE SALES_ITEM_ID
202 100
242 80
408 40
700 2
1100 20
2150 600
2755 10
2959 200
3531 8
5147 50
5424 25
17710 6

Now let’s say that we define this result set as summary_sales. Instead of creating a view or an intermediary table, we use a SQL CTE example to define this virtual grouping:

WITH summary_sales AS (
	SELECT 
		sum(sales_num*sales_price) sum_sales_revenue,
		sales_item_id  
	FROM sales  
	GROUP BY sales_item_id 
	ORDER BY sum_sales_revenue)
SELECT * FROM summary_sales

Notice the syntax: the WITH keyword and the CTE name with the AS keyword. In parentheses, we see the query. When you use a CTE in SQL, it’s queryable, just like a normal table; you can see it right after the closing bracket.

Now let’s calculate the percentage of every item sold to each customer by total revenue to that customer. We accomplish this by defining a new CTE group called customers_sales. The new group, since it is part of a CTE statement, does not need a new WITH clause. We just chain it under the previous group:

WITH sumary_sales AS (  
	SELECT
		sum(sales_num*sales_price) sum_sales_revenue,
		sales_item_id  
	FROM sales
	GROUP BY sales_item_id ORDER BY sum_sales_revenue
	),
customers_sales AS (
	SELECT 
		sales_customer_id,
		round(sum(sales_num*sales_price)/sum_sales_revenue,2)*100 sales_percent,
		sumary_sales.sales_item_id 
	FROM sales 
	JOIN sumary_sales ON sumary_sales.sales_item_Id = sales.sales_item_id
	GROUP BY sales_customer_id, sumary_sales.sales_item_id, sum_sales_revenue
	)

SELECT * FROM customers_sales

The result of this SQL CTE is:

SALES_CUSTOMER_ID sales_percent SALES_ITEM_ID
1 100 100
3 50 40
1 74 25
3 53 50
3 100 8
2 100 2
2 47 50
2 50 6
3 25 25
2 100 20
1 99 200
2 1 25
3 50 6
2 1 200
1 100 10
2 100 600
2 100 80
2 50 40

Finally, we will add customer and item names to the result table in our SQL CTE example. We do this by joining our customer_sales CTE to the “customer” and “items” tables.

WITH sumary_sales AS (  
	SELECT 
		sum(sales_num*sales_price) sum_sales_revenue,
		sales_item_id  
	FROM sales  
	GROUP BY sales_item_id 
	ORDER BY sum_sales_revenue
	),
customers_sales AS (
	SELECT 
	  sales_customer_id,
	  round(sum(sales_num*sales_price)/sum_sales_revenue,2)*100 sales_perchent,
	  sumary_sales.sales_item_id 
	FROM sales 
	JOIN sumary_sales
	ON sumary_sales.sales_item_Id = sales.sales_item_id
	GROUP BY sales_customer_id, sumary_sales.sales_item_id, sum_sales_revenue
	)

SELECT
	customer_name,
	sales_perchent,
	items.item_name
FROM customers_sales
JOIN customers
ON customers.id = sales_customer_id
JOIN items
ON items.id = sales_item_id
ORDER BY item_name

Here is the final result set:

CUSTOMER_NAME sales_percent ITEM_NAME
Big Co 99 Dollhouses
Medium Co 1 Dollhouses
Medium Co 100 Legos
Medium Co 100 Model Airplanes
Small Co 100 Model Cars
Medium Co 100 Pens
Medium Co 100 Pins
Medium Co 50 Rugs
Small Co 50 Rugs
Medium Co 50 Toy Airplanes
Small Co 50 Toy Airplanes
Big Co 100 Toy Cars
Big Co 74 Toy Dolls
Medium Co 1 Toy Dolls
Small Co 25 Toy Dolls
Medium Co 47 Toy Kitchen
Small Co 53 Toy Kitchen
Big Co 100 Toy Tools

Now that you’ve been introduced to the topic of the CTE in SQL, it’s time to practice. The WITH statement is a precursor to recursion, which is a useful and interesting feature of SQL. Why not revisit any of your old queries that have many subqueries and rewrite them using CTEs? And if you don’t feel ready for doing it on your own yet, improve your skills with LearnSQL.com Recursive Queries course!