3 Real-Life Examples for SQL Common Table Expressions

We answer three SQL business questions using CTEs.

There are business problems that are often solved in a very roundabout way. While they might get you the correct result, there’s usually a more elegant way to write a solution.

Enter the CTEs! They are extremely helpful when it comes to getting a more direct and elegant solution. I’ll show you three business situations where CTEs can be very helpful. You’ll get an idea of when CTEs can be useful. From there, you may start to recognize many of your own situations where you can use CTEs.

The CTE is only one of the many SQL possibilities that are useful in business situations. You can find all of them, including the CTE, in the SQL from A to Z learning track. I think the name says it all.

If you’re still a student, you probably don’t have much experience with real-life business situations. That’s normal, and you’ll get that experience once you start working. You can use this article to prepare for your future or your upcoming job interview. If you’re an aspiring business analyst, here are some questions you might expect in an interview.

And now, back to CTEs!

What Is a CTE?

A common table expression, or a CTE, is a temporary data set returned by a query. This data set is then used by another query like any other table.

A CTE may be non-recursive or recursive. In this article, I’ll show you only examples using non-recursive CTEs.

The CTE syntax is rather simple, and it looks like this:

WITH 	expression_name AS (
SELECT ...
FROM ...
)

You start a CTE with the keyword WITH, then you name your CTE. This name will be used later in a FROM clause like any table name. Once you’ve named the CTE, you write the keyword AS, then you define the CTE in parentheses.

When I say “define the CTE,” it simply means writing the SELECT statement as you would do in any other case. You’ll see what that looks like in the examples.

Once the CTE is defined, you use it like any other table with the following syntax:

SELECT ...
FROM expression_name

You can find more detailed syntax explanations (and examples) in this SQL CTE article.

Multiple CTEs and Nested CTEs

You can use any number of CTEs if that is what you need. If you want to write two CTEs, the syntax will look like this:

WITH 	expression_name_1 AS (
SELECT ...
FROM ...
),
expression_name_2 AS (
SELECT ...
FROM ...
)

It is pretty simple! When you write the second CTE, you omit the WITH keyword. Simply start writing the CTE name, and the rest is the same as in the first CTE. Every CTE must be separated by a comma. In this way, you can write as many CTEs as you desire.

The nested CTE is when you write one CTE then reference it in another CTE. The syntax is:

WITH 	expression_name_1 AS (
SELECT ...
FROM ...
),
expression_name_2 AS (
SELECT ...
FROM expression_name_1
)

Want to know how multiple and nested CTEs work? The article about CTEs with examples is a great way to quench your thirst for knowledge.

Now, let’s get started with the examples!

SQL Business Query Example 1

In this query, I’ll use only one table. Let’s take a look.

The Data Used

The table salaries has the following columns:

• id – The salary ID and the primary key (PK) of the table.
• first_name – The first name of the employee.
• last_name – The last name of the employee.
• salary – The salary amount of the employee.

Here’s the data:

The SQL Business Question

Using the data above, show all the employees and their respective salaries. Also, show the overall average salary at the company and calculate the difference between each employee’s salary and the company average.

The Solution

You only need one CTE for this one:

WITH avg_salary AS (
	 SELECT	AVG(salary) AS average_salary
	 FROM salaries
)

SELECT	first_name,
		last_name,
		salary,
		average_salary,
		salary - average_salary AS salary_diff
FROM salaries, avg_salary;

In this SQL business query, I use a CTE to calculate the overall average salary. As explained earlier, I start with the WITH keyword. The name of my CTE is avg_salary, and the CTE is defined in the parentheses. It’s a simple SELECT statement using the AVG() function to calculate the average salary in the company.

The second step is to write a SELECT statement referencing the CTE. I select the columns first_name, last_name, and salary from the table salaries. Then, I include the column average_salary from the CTE. Finally, the column salary_diff is calculated as the difference between the employee’s salary and the overall average salary.

The table salaries is joined with the CTE avg_salary like any other table.

The Result

When you run the query, you get this result:

It shows the names and the salaries of all employees. The average_salary column is the same for every employee. Finally, the salary of each employee is compared with the average salary. Taking Jasen Gypps as an example, we have 5,635.30 - 7,388.29 = -1,752.99. This means Jasen Gypps’ salary is 1,752.99 monetary units below the company average.

SQL Business Query Example 2

I’ll again use just one table in this example. But it’s different data and a different problem.

The Data Used

This time, the table is named share_price. It contains the daily share price data for Mephistopheles Solutions Inc.:

• date – The date traded and the primary key (PK) of the table.
• ticker – The abbreviation used to identify the share traded.
• company – The company that issued the share.
• open – The opening share price for the day.
• close – The closing share price for the day.

The data in the table looks like this:

The SQL Business Question

The task before you is: find the largest single-day decrease and the largest single-day increase in share price.

The Solution

This task could look a little hairy. Fortunately, we can use a CTE to make it easy-peasy:

WITH difference AS (
		SELECT	date,
				close - open AS price_diff
		FROM share_price
	)
		
SELECT	MIN(price_diff) AS highest_daily_decrease,
		MAX(price_diff) AS highest_daily_increase
FROM share_price sp JOIN difference d ON sp.date = d.date;

Before calculating the largest single-day price increase and decrease, I first calculate the daily price differences. This is done via simple math: closing price - opening price = price difference. The CTE difference does exactly that, with the data shown in the column price_diff.

Then, I write the SELECT statement that references the CTE and calculates the largest single-day decrease and increase. I do that with the MIN() and MAX() aggregate functions. The MIN() function uses the column price_diff from the CTE to calculate the largest daily decrease. This might seem a little confusing, but it shouldn’t be. The price decrease will have a negative sign; so, to find the largest price decrease, you have to find the lowest price difference. Remember, the biggest decrease in everyday language is the lowest value in the mathematical language (and correctly so!).

After that, I use the MAX() function to find the largest single-day price increase. The CTE is again treated as any other table and joined with the table share_price.

The Result

Run the query, and this is what you get:

This result shows the largest single-day drop (i.e., change in the negative direction) in share price was -35.26. The largest increase was 31.06.

SQL Business Query Example 3

In this final example, I’ll use data from two tables.

The Data Used

The first of the two tables is the customers table:

• id – The customer ID and the primary key (PK) for the table.
• first_name – The first name of the customer.
• last_name – The last name of the customer.

Here’s the data in customers:

The second table, orders, has the following columns:

• id – The order ID and the primary key (PK) for the table.
• order_date – The date the order was placed.
• ship_date – The date the order was shipped.
• delivery_date – The delivery date of the order.
• order_amount – The amount of the order.
• customer_id – The customer ID, referencing the table customer.

Here’s the data in orders:

The SQL Business Question

This SQL business question asks you the following: write a query that shows the following three columns for each customer:

• The average number of days between the order placement date and the order delivery date for each customer.
• The average number of days between the order shipment date and the order placement date.
• The average order amount for each customer.

The Solution

WITH delivery_days AS (
		SELECT 	o.customer_id,
				c.first_name,
				c.last_name,
				o.delivery_date - o.order_date AS order_delivery_diff,
				o.delivery_date - o.ship_date AS ship_delivery_diff,
				o.order_amount
		FROM orders o JOIN customers c ON o.customer_id = c.id ),

average_order AS (
		SELECT	customer_id,
				AVG(order_amount) AS avg_order_amount
		FROM orders
		GROUP BY customer_id
)
		
SELECT	first_name,
		last_name,
		AVG(order_delivery_diff) AS avg_order_delivery_diff,
		AVG(ship_delivery_diff) AS avg_ship_delivery_diff,
		avg_order_amount
FROM delivery_days dd JOIN average_order ao ON dd.customer_id = ao.customer_id
GROUP BY first_name, last_name, avg_order_amount;

As you can see, this one’s a little bit longer and more complex. But don’t worry, I’ll explain what it does.

The first CTE calculates the differences between the dates for every order. The column order_delivery_diff contains the difference between the delivery and the order dates. The difference between the delivery and the ship dates is in the column ship_delivery_diff. I also need the customer names, so I join the tables orders and customers to get them.

Note: If you use databases other than PostgreSQL, you may need to use specific functions to get differences between dates – for instance, the DATEDIFF() in MS SQL Server.

Back to the code. The second CTE gets the average order amount for each customer. To do so, I use the AVG() function. I can’t do that in the first CTE, because the first CTE is at the order level rather than at the customer level.

Notice the rules for writing two (or more) CTEs apply here. The CTEs are separated by commas, and the second CTE omits the keyword WITH.

The final step is to write the SELECT statement. The columns first_name and last_name are taken from the CTE delivery_days. We have the difference between various dates, but we need the average difference. I use the AVG() function again to get this. I calculate averages on the columns order_delivery_diff and ship_delivery_diff from the CTE average_order. The last column is avg_order_amount, again from the CTE delivery_days. The CTEs are joined like any other two tables.

Also, don’t forget to group the data to get the result at the customer level.

The Result

The four rows this query returns should look like this:

Here’s how to read this table. For Sileas Kiehnlt, there are two days between order placement and order delivery on average. It takes a day and a half on average between order shipment and order delivery. Finally, the average amount of all orders by Sileas Kiehnlt is 263.17.

After this third example, you're probably getting the hang of when the CTEs are useful.

When Can CTEs Help you?

I always like to use CTEs in situations that require what I call “calculating something before calculating something.” You see, in all three of these situations, there’s some preliminary or preparatory calculation you have to do before you do other calculations. Whenever you have to do that, CTEs are extremely helpful.

They follow the mathematical logic of the calculations nicely. This is because the result from a CTE can always be re-used and referenced as any other table. Your code will be nicely (and logically!) laid out, and it’ll be easy for you to understand what it does so that you can change it according to your needs.

If the calculations are more complex, you can always use multiple CTEs. This way, you break up every calculation into a logical part. Also, if you need a “calculation within the calculation,” there are nested CTEs. They allow you to write a CTE and reference it multiple times in other CTEs and the main query. If you try to use subqueries in the same way, you’d have to write the same subquery over and over again.

Make Common Table Expressions More Common in Your SQL Business Queries!

These example business questions are not comprehensive by any means. But I think they’re enough to show CTEs are useful in solving SQL business questions you come across in your day-to-day work.

Make CTEs more common in your problem solving with SQL! You’ll need to practice CTEs and learn their other aspects, usages, and possibilities.

The best way to do so is to check out the Common Table Expressions course. There are also other ways SQL can help you in different business situations. I’m sure you’ll find something for yourself in the SQL Basic Reporting course.

If that’s not enough for you, here’s our choice of the five best online courses for business analysts.