5 Practical Examples of SQL’s WITH Clause

These five practical use cases will make you want to use the SQL WITH clause daily.

The WITH clause can help you write readable SQL queries  and break complex calculations into logical steps. It was added to SQL to simplify complicated long queries. In this article, we’ll show you 5 practical examples of the WITH clause and explain how using it makes SQL queries more readable.

The WITH clause is also called a Common Table Expression (CTE). Learning how to work with it is not basic SQL knowledge, so we recommend practicing WITH through a structured interactive course, like our Recursive Queries course. In over 100 exercises, you will learn the basic syntax of CTEs as well as the advanced concepts of nested and recursive CTEs.

What Is the WITH Clause in SQL?

The WITH clause’s other name, Common Table Expression, gives a clue to what it does. A WITH clause allows you to create a SELECT statement that returns a temporary result; you can name this result and reference it in another query. Basically, it’s a named subquery, but it can be recursive. Here’s how a WITH clause compares with a subquery.

The CTE can’t run without the main query, so it must be followed by a regular query. This query is usually also a SELECT statement, but it can be INSERT, UPDATE, or DELETE.

Think of a CTE as a temporary table with a query that always has to be run for the table to be used. Since it’s a kind of table, you can reference the CTE in the FROM clause like a regular table.

Basic WITH Clause Syntax

What we explained above can be shown as an SQL code:

WITH cte AS (
  SELECT …
  FROM table
)

SELECT …
FROM cte;

Let’s take a look at the key parts of the syntax. The CTE is always, without exception, initiated by the WITH clause. The CTE name then follows, which is cte in the above generic example. After the CTE name comes the keyword AS. What follows in the parentheses is the definition of CTE. In a very simple example, this is a just a SELECT statement. Finally, there’s the main query (like with regular subqueries) that references the CTE.

Mind you, this is basic syntax. We’re going to revisit it in the examples. Along the way, you’ll also see how this basic syntax can be extended by writing multiple or nested CTEs.

Examples 1 & 2: Salary Information

Example Data

We will use the table employees in the first two examples. It has the following columns:

• id – The employee’s ID.
• first_name – The employee’s first name.
• last_name – The employee’s last name.
• salary – The employee’s salary.
• department – The employee’s department.

Here’s the data:

And here’s  a link to a query you can run to create the table:

Example 1: Show How Each Employee’s Salary Compares to the Company’s Average

To solve this problem, you need to show all data from the table employees. Also, you need to show the company’s average salary and then the difference to each employee’s salary.

Here’s the solution:

WITH avg_total_salary AS (
	SELECT AVG(salary) AS average_company_salary
	FROM employees
)
	
SELECT id,
	 first_name,
	 last_name,
	 salary,
	 department,
	 average_company_salary,
	 salary - average_company_salary  AS salary_difference
FROM employees, avg_total_salary;

First, initiate the CTE using the WITH clause. The CTE’s name (‘avg_total_salary’) comes after that. Open the parentheses after AS, and write the regular SELECT statement. It calculates the company’s average salary.

To use the CTE output, write another SELECT statement as the main query. Notice that the CTE and the main query are only separated by parentheses. The new row is only there for readability. The main query selects all the columns from both the table and the CTE. The CTE and the table employees are joined like any two regular tables.  Additionally, there’s the calculated column salary_difference.

The query returns this:

We can see all the employees’ data and how much their salary is above or below the company average. For example, Melissa Allman’s salary is 293.48 higher than the average. Nina Longhetti’s salary is 993.20 below the average.

Example 2: Show the Employees’ Data Along with the Average Department and Average Company Salary

Let’s go a little further from the previous example. This time, you don’t have to calculate the difference between salaries. But you need to show all the employees’ analytical data and then show the average departmental and company salary.

You can do that by writing two CTEs instead of one:

WITH avg_total_salary AS (
	SELECT AVG(salary) AS average_company_salary
	FROM employees
),
	
avg_dpt_salary AS (
	SELECT department,
		 AVG(salary) AS average_department_salary
	FROM employees
	GROUP BY department
)
	
SELECT e.id,
	   e.first_name,
	   e.last_name,
	   e.salary,
	   average_department_salary,
	   average_company_salary
FROM employees e JOIN avg_dpt_salary ads ON e.department = ads.department, avg_total_salary;

Remember that we mentioned the basic syntax could be extended. Here is one example of doing this. Yes, you can write multiple CTEs, one after another, and then reference them in the main query.

Let’s see how to do that. Everything is usual with the first CTE: WITH clause, the CTE name, AS, and the CTE definition in parentheses. This CTE calculates the company’s average salary.

The second CTE calculates the average salary by department. But there’s something different here! Notice two key things: there’s no other WITH clause, and the CTEs are separated by a comma.

This is how you write multiple CTEs: the WITH clause is written only before the first query (it mustn’t appear before any other CTE!), and a comma must separate the CTEs.

No matter the number of CTEs, they must always be followed by the main query. And there’s no comma between the last CTE and the main query!

The main query joins the table with the two CTEs and selects relevant data. Joining is done the same way as with regular tables: specify the type of join and the column the tables will be joined on.

Here’s the output:

Now you have all the data in one place: individual salaries, department average, and company average. From here, you can go further in your data analysis.

Examples 3 & 4: Revenue

Example Data

In the next two examples, we will use the table revenue. We created it with the CREATE TABLE query found here. It has the following columns:

• id – The revenue record ID.
• year – The year of the revenue.
• quarter – The quarter of the revenue.
• revenue_amount – The revenue’s amount.

Get yourself familiar with the data shown below:

Example 3: Show Each Year with the Corresponding Annual Revenue and Total Revenue

Use the above data to show all the available years. Along with each year, show that year’s annual revenue and total company revenue in all years.

This task is similar to Example 1, but we’ll use a different aggregate function:

WITH total_revenue AS (
	SELECT SUM(revenue_amount) AS total_company_revenue
	FROM revenue
)
	
SELECT year,
	 SUM (revenue_amount) AS annual_revenue,
	 total_company_revenue
FROM revenue, total_revenue
GROUP BY year, total_company_revenue
ORDER BY year;

The CTE uses the SUM() aggregate function to calculate the total company’s revenue.

The main query joins the CTE with the table revenue. We use this SELECT statement to show the year, then calculate the annual revenue for each year and show the total revenue.

This is the query’s output:

The result shows that the annual revenue in 2019 was 10,489,632.27. In 2020, it was 20,919,087.62, and so on. If all four years are summed, the total revenue is 80,812,015.89.

Example 4: Show Each Quarter Along with the Highest and Lowest Quarterly Revenue for That Year and Overall

You need to list all the years and quarters with the corresponding revenue. So far, so good. Then you need to show the lowest quarterly revenue for that year and the lowest quarterly revenue for all years. Next, you do the same for the highest revenue.

Here’s the solution:

WITH yearly_min_max_quarter AS (
	SELECT year,
		   MIN(revenue_amount) AS minimum_quarterly_revenue_annual,
		   MAX(revenue_amount) AS maximum_quarterly_revenue_annual
FROM revenue
GROUP BY year),

min_max_overall AS (
	SELECT MIN(revenue_amount) AS overall_min_revenue,
	       MAX(revenue_amount) AS overall_max_revenue
FROM revenue)

SELECT r.year,
	   quarter,
	   revenue_amount,
	   minimum_quarterly_revenue_annual,
	   overall_min_revenue,
	   maximum_quarterly_revenue_annual, 
	   overall_max_revenue
FROM revenue r 
JOIN yearly_min_max_quarter ymmq 
ON r.year = ymmq.year, min_max_overall
ORDER BY year, quarter ASC;

This solution again requires two CTEs. You probably already know how to write this, but let’s explain each step.

The first CTE finds the lowest and highest quarterly revenue for each year. To achieve that, use the MIN() and MAX() functions and group data by year.

Then write the second CTE without the WITH clause and separate it by a comma from the first one. This CTE returns the lowest and highest quarterly revenue in all years.

The main query joins the table revenue with the first and then with the second CTE. It will show data from the table and the CTE as one table.

Take a look at the result:

Along with each quarter’s revenue, you now have some other important info. You know that the lowest revenue in 2019 was 984,157.15, and you can see it happened in Q3. The lowest revenue in 2020 (2,497,441.68) was in the year's first three months. You can analyze all other years similarly by looking at the column minimum_quarterly_revenue_annual.

The value in the column overall_min_revenue represents the all-time lowest revenue. It’s the same in all rows and corresponds with 2019Q3. The next two columns are similar, but they show the highest instead of the lowest revenue. In other words, the highest revenue in 2019 was 5,417,884.15, which is from Q4. The highest all-time revenue is 12,478,945.47, which was realized in 2021Q1.

Example 5: Hours Worked

Example Data

The table for the last example is named employee_timesheet. It records the working hours of  employees. Its columns are self-explanatory, so we’ll just look at the data:

Example 5: Show the Lowest and Highest Average Working Hours

This example wants you to first find the average working hours by employee and then only output the lowest and the highest average.

Here’s the code to solve this problem:

WITH login_times AS (
SELECT 
id,
employee_id,
start_time,
end_time,
end_time - start_time AS working_hours
FROM employee_timesheet),

avg_login AS (
	SELECT 
employee_id,
AVG(working_hours) AS average_working_hours
FROM login_times
GROUP BY employee_id)

SELECT MIN(average_working_hours) AS min_average_working_hours,
	 MAX(average_working_hours) AS max_average_working_hours
FROM avg_login;

At first, this could seem like any query with two CTEs. Look closer, and you’ll see it’s not! Yes, there are two CTEs. But the difference is the second CTE references the first, which wasn’t the case in Examples 2 and 4.

This is called a nested CTE. The first CTE is used to get the difference between the login start and end; that’s how you get the working hours for each session.

One employee has multiple sessions, so we need to find the average session length, i.e. the average hours worked. The second CTE is used for that purpose. In terms of the syntax, nothing’s new except that the CTE references the first CTE in the FROM clause.

Then, in the main query, we do something called a multi-level aggregation. We take the average working hours by employee (first-level aggregation) and find the minimum and maximum of these values (second-level aggregation).

Here’s the result:

The result tells us the lowest average working hours per employee are 5 hours, 57 minutes, and 4 seconds. The highest average is 13:24:29.

Benefits of the SQL WITH Clause

The five examples we’ve shown you were carefully selected to showcase the typical usage and benefits of the WITH clause.

The first benefit becomes clear when you have several steps of calculation, like we saw in the above examples. Using the WITH clause, you can nicely organize the code and break it into logical parts.

When calculations become more complicated, the length and complexity of the code also increases. Using the WITH clause is great to keep that in check. While the codes in the above examples may seem long, they would get significantly longer (and less readable) if we used subqueries instead of the WITH clause. And as you saw in the last example, using the WITH clause lets you easily compute multi-level aggregations.

One other benefit is that the WITH clause lets you write recursive queries in SQL, which opens up a whole new world of possibilities.

Learning the WITH clause can sometimes be overwhelming, so we prepared a guide that will help you structure your approach. Happy learning, and we know the SQL WITH clause will more than repay your efforts!