Organize Your SQL Queries with CTEs

recursive queries, CTE, common table expressions, WITH,

Common table expressions (CTEs) allow you to structure and organize your SQL code. Being able to write organized 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 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, 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 CTE. 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 Vertabelo Academy CTE course for some interactive exercises.

Getting to Know the CTE

In its basic form, a 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 their SQL code for better readability or performance.

Let’s consider an example of using a CTE 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)

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.

First, we define the total revenue per item:

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 CTE 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. The CTE is 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_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 * from customers_sales

The result of this 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. 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 SQL’s Common Table Expressions, 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 remember to give Vertabelo Academy a try!

Data Warehouse Architect

GET ACCESS TO EXPERT SQL CONTENT!