Preview
Video Player
00:00
00:00
Using Multiple CTEs in a Query
7:07Learn how to use multiple common table expressions within a query, and even how to reference one CTE within another CTE to create more complex reports with aggregate data.
We've seen how common table expressions
help us write queries that are easier
0:00
to read.
0:04
CTEs also help organize our queries to
match how we think about a data question.
0:05
They're also really helpful when you want
to analyze data from a single result set
0:10
in different ways to create a new result
set that summarizes aggregate data.
0:13
Let's look at a simple example.
0:18
Say I want to see a list of sales people,
the total number of sales for
0:21
each employee, the total revenue
generated by each employee,
0:24
as well as the average
revenue generated per sale.
0:28
Data like this is stored in a database
that includes several tables.
0:31
In this basic example,
I only need to think about three tables.
0:35
The employees table,
with each employees ID and
0:39
name, the Orders table that tracks each
sale from an employee to a customer,
0:42
and an order details table which
includes line items in an invoice.
0:47
In other words, one record for
each product sold as part of an order.
0:51
Obviously, there'll be more columns
in each of these tables and
0:55
more tables in this database.
0:58
But this is all we need to think about for
this query.
1:00
The way I think about this problem
breaks down into three sets.
1:03
I can convert each of those
into a common table expression.
1:07
First, I want to generate a list
of all sales by each employee.
1:10
In other words, one line for each order.
1:14
That includes the order ID, employer ID,
and the amount invoiced for that order.
1:16
I'll start by creating a common
table expression named all_sales.
1:24
And I'll build this up in several stages.
1:29
First, I'll get a list of all orders and
employee IDs.
1:32
To see what data the CTE generates,
I'll add a temporary query and run it.
1:40
So this lists all sales for each employee.
1:48
But I also want to get the revenue
generated from the sales.
1:51
That's the quantity and unit price and
1:54
that's contained in
the order details table.
1:56
So we need to join
the order details table and
1:59
group all the line items from
the same order together.
2:02
Then, to calculate the sum,
we'll take the unit price,
2:14
multiply it by the quantity for
each line item in each order.
2:18
Because we've grouped
the results by OrderId,
2:23
we generated the total revenue for
each invoice.
2:26
So now we have a results set like a
virtual table with all the data we need to
2:31
answer our questions.
2:35
The second CTE I'm after calculates
the total revenue per per employee.
2:37
One really useful feature of CTEs
is that you can reference them
2:41
later in other CTEs.
2:44
In this case, I can use all sales like
a table within another common table
2:47
expression, like this.
2:51
I'll call this revenue_by_employee and
I'll select the employee ID.
2:58
And I'll create a sum of the invoices,
that gives me total revenue.
3:05
And I'll pull it from all sales,
our first common table expression.
3:09
One really important
thing to keep in mind,
3:14
you can only reference a CTE
created earlier in your query.
3:17
In other words, the first CTE in your
query can't reference the second CTE,
3:20
third or fourth.
3:25
Likewise, the second CTE can't reference
a third CTE or the fourth one.
3:27
You'll get a syntax error if you try.
3:32
The last CTE will use the all
sales common table expression
3:35
to count the number of orders
recorded by each employee.
3:39
I'll call it sales by employee.
3:44
And I'll select the employee ID and
I'll simply do a count.
3:49
And this gives me the total sales for
that employee.
3:53
Now let's see what kind
of data this gives us.
4:07
We can see that employee
number 1 has had 123 sales.
4:20
Employee 2 has had 96.
4:25
Now, to get our final result,
4:27
it's just a matter of joining
the revenue by employee and
4:29
sales by employee common table expressions
to generate one record per employee.
4:33
I can join the two CTEs by
the employee ID number.
4:39
You'll notice that the first common table
expression that I created isn't even used
4:48
in this final query.
4:53
The first CTE is simply a result
set that I used to create
5:02
the other two result sets.
5:05
Let's see the results.
5:08
Not much to see here,
just a list of all employees with sales.
5:10
Let's calculate sales figures based
on information in the two CTEs.
5:13
Total revenue comes from the second
common table expression.
5:21
A total count of the number
of orders is from the third.
5:25
To calculate the average per order,
5:27
I'll just divide the revenue
by the sales count.
5:29
Now, the last step is to grab
the employee's last name,
5:39
which we can do simply by joining on
the employees table by the EmployeeId.
5:43
And to determine which sales
person generated the most revenue,
5:54
I'll order this by
the total_revenue descending.
5:58
And then I just need to
add the employees name.
6:04
Now, there are many ways we
could've achieved the same results.
6:13
I didn't have to use
common table expressions.
6:16
The same results could be achieved
by joining all of these tables in
6:18
a single query.
6:21
However, the resulting query will
be much more complicated looking.
6:23
It also would have forced me to work
through all the logic to weave the data
6:26
together in one long query.
6:30
Using CTE, I was able to
breakdown my data analysis problem
6:32
into three different queries,
then combine those queries
6:36
using some very simple joints
to get the report I was after.
6:39
Even better, this query is very readable.
6:43
Anyone looking at it can easily see
what information is being queried
6:45
at each step and
how the queries come together.
6:49
I hope you can see how CTE's can simplify
your SQL by breaking down queries
6:52
into easily created and easily understood
modules that can be combined in
6:57
interesting ways to solve
data analysis problems.
7:02
Have fun.
7:05