In the past few months, I have learned the importance of writing clean, readable, and efficient SQL queries. They are essential for integrating information from different tables.
However, writing complex queries from scratch can be time-consuming, especially when you frequently use them for analyzing your data. To address this need, SQL offers a powerful construct, known as Common Table Expression.
In this article, I am going to explain what a Common Table Expression is, why it’s useful, and demonstrate its application through examples. Let’s get started!
Table of contents:
- What is a Common Table Expression?
- Setting up DBeaver and Database
- Three simple examples of CTEs
What is a Common Table Expression?
A Common Table Expression (CTE) is a temporary result set that simplifies complex queries, making them more readable and maintainable. It works by breaking a complex query into smaller, more manageable pieces — essentially building on sequential, modular subqueries. It doesn’t just limit on managing complicated queries. It can also be useful an alternative to a view, self-reference a table or be used for recursion.
To create a common table expression, the keyword WITH is followed by the name of the CTE. Within the parentheses, you specify the SQL query that defines the CTE. After, you can select the entire result set by specifying the name on the query.
WITH cte_name (
QUERY
)
SELECT *
FROM cte_nameAlternatively, it can also happen that you select only few columns from the CTE:
WITH cte_name (
QUERY
)
SELECT column1, column2
FROM cte_nameSetting up DBeaver and Database
Dbeaver is a free and open-source database management system that supports relational data stored in databases. Compared to other database tools, the interface is very intuitive and simple. For that reason, I would recommend to install it. It can be installed on Windows, Mac, and Linux.
Once you download it, it’s time to create the database. For this tutorial, I decided to create synthentic data to demonstrate the strenght of CTE. This is possible by asking Chat-GPT to create it.
Prompt: I want to create a database about sales of a fashion online company,
zalando: create tables and insert rows using SQL.
The goal of this database is to demonstrate the strenghts of SQL CTE.
It should contain syntetic data that resemble real data between 2023 and 2024. After sending the prompt, ChatGPT designs the structure of this new database and the rows that should contain. The output is very long and I will just show a GIF to have an idea of what you can obtain.
Even if it’s only a very small database, the result is astonishing! It created simple and meaningful tables linked between each other.
After we can finally create a new database in DBeaver. We just need to create a new connection with SQLite, that can be suitable for a light and local database. After, we press the button "Create" and select the path where we want to store the database.
After we copy and execute the SQL code generated by Chat-GPT to create the tables and insert the rows in each table.
The new database is composed of five main tables:
- Customers
- Categories
- Products
- Orders
- OrderDetails
Example 1: Simple CTE
To understand how to define a CTE, let’s start with a simple example. Let’s suppose we want to know the number of customers that ordered on the company’s website by year.
WITH NumberCustomerByYear AS (
SELECT STRFTIME('%Y',c.join_date) AS Year, count(*) AS NumberCustomers
FROM Customers c
GROUP BY STRFTIME('%Y',c.join_date)
)
SELECT *
FROM NumberCustomerByYear
ORDER BY Year DESC;This is the output:
Year NumberCustomers
2024 5
2023 5Now we have the number of clients by Year, which is extracted by the column c.joint_data using the SQLite function STRFTIME. We prefer to show the number of customers in decreasing order based on the year to visualize the most recent data first.
Example 2: Simplify a Complex Query
In this section, we show a CTE that helps to list the products that have been sold more than 3 times. This time we need to do the left join between Products and OrderDetails to obtain the information.
WITH PopularProducts AS (
SELECT
p.name AS product_name,
SUM(od.quantity) AS total_quantity_sold
FROM Products p
LEFT JOIN OrderDetails od ON p.product_id = od.product_id
GROUP BY p.name
HAVING SUM(od.quantity)>3
)
SELECT *
FROM PopularProducts
ORDER BY total_quantity_sold DESC;
The output table is the following:
product_name total_quantity_sold
Sandals 5
T-shirt 5
Tracksuit 4That’s good! How we have obtained the names of the most popular products.
Example 3: Use Multiple CTEs in a Query
Previously, we have shown examples that didn’t use more than a single common table expression. This time, we can try to solve a problem that requires two CTEs.
Let’s suppose that we want to compare the number of orders each month with the previous month. The first CTE MonthlyOrders contains the number of orders by year and month.
The second CTE is MonthlyComparison and has five columns: order_year, order_month, current_month_orders, previous_month_orders and order_difference. The last two fields, previous_month_orders and order_difference, are obtained using a self-join, which is very useful when comparing a row with other rows within the same table.
When there is more than one CTE, we don’t put the clause WITH beside the second CTE, but we need a comma to define it.
WITH MonthlyOrders AS (
SELECT
STRFTIME('%Y',order_date) AS order_year,
CAST(STRFTIME('%m',order_date) AS INTEGER) AS order_month,
COUNT(order_id) AS total_orders
FROM Orders
GROUP BY STRFTIME('%Y',order_date), STRFTIME('%m',order_date)
),
MonthlyComparison AS (
SELECT
mo1.order_year,
mo1.order_month,
mo1.total_orders AS current_month_orders,
COALESCE(mo2.total_orders, 0) AS previous_month_orders,
mo1.total_orders - COALESCE(mo2.total_orders, 0) AS order_difference
FROM MonthlyOrders mo1
LEFT JOIN MonthlyOrders mo2
ON (mo1.order_year = mo2.order_year AND mo1.order_month = mo2.order_month + 1)
OR (mo1.order_year = mo2.order_year+1 AND mo1.order_month=1 AND mo2.order_month=12)
)
SELECT *
FROM MonthlyComparison
ORDER BY order_year DESC, order_month DESC;In the main query, we select all the columns from the second CTE that compares the number of orders each month with the previous month. The results of the query are the following:
order_year order_month current_month_orders previous_month_orders order_difference
2024 5 1 1 0
2024 4 1 1 0
2024 3 1 1 0
2024 2 1 1 0
2024 1 1 0 1
2023 7 1 1 0
2023 6 1 1 0
2023 5 1 1 0
2023 4 1 1 0
2023 3 1 0 1This is great! This is just a taste of what you can obtain with multiple CTEs! The numeric values are not very realistic, since the data is synthetic, but it can help
Summary of SQL functions used
COUNT(*)to return the number of recordsSUM(od.quantity)to sum the values of the field quantitySTRFTIME('%Y', order_date)to extract the year from the date columnCAST(STRFTIME('%Y','order_date') AS INTEGER)to convert the column from STRING to INTEGER typeCOALESCE(total_orders,0)to replace null values of total_orders with 0
Final thoughts
I hope that you have appreciated this guide for getting started with Common Table Expressions in SQL. It can be intimidating to understand this topic without practical examples from the most simple to the hardest.
Be aware that some of the SQL functions used in the example can change depending on the connection type selected, such as SQL server and Google Big Query. For example, STRFTIME is replaced by YEAR in SQL server and EXTRACT in Google BigQuery.
If you want to go deeper on CTE, check the resources below. The code for creating the tables, inserting the rows, and building the CTEs is here if you want to replicate the results that are based on the synthetic Database generated using Chat-GPT. Since the code is very long, I didn’t put all the code lines on the article for readability reasons.
Thanks for reading! Have a nice day!
Useful resources:
