Heads up! To view this whole video, sign in with your Courses account or enroll in your free 7-day trial. Sign In Enroll
Preview
Start a free Courses trial
to watch this video
Learn 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.
Example Code
CTEs Referencing a CTE
WITH
all_sales AS (
SELECT Orders.Id AS OrderId, Orders.EmployeeId,
SUM(OrderDetails.UnitPrice * OrderDetails.Quantity) AS invoice_total
FROM Orders
JOIN OrderDetails ON Orders.id = OrderDetails.OrderId
GROUP BY Orders.ID
),
revenue_by_employee AS (
SELECT EmployeeId, SUM(invoice_total) AS total_revenue
FROM all_sales
GROUP BY EmployeeID
),
sales_by_employee AS (
SELECT EmployeeId, COUNT(*) AS sales_count
FROM all_sales
GROUP BY EmployeeId
)
SELECT
Employees.Id,
Employees.LastName,
revenue_by_employee.total_revenue,
sales_by_employee.sales_count,
revenue_by_employee.total_revenue/sales_by_employee.sales_count AS
avg_revenue_per_sale
FROM revenue_by_employee
JOIN sales_by_employee ON revenue_by_employee.EmployeeId = sales_by_employee.EmployeeId
JOIN Employees ON revenue_by_employee.EmployeeId = Employees.Id
ORDER BY total_revenue DESC
Related Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign upRelated Discussions
Have questions about this video? Start a discussion with the community and Treehouse staff.
Sign up
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
You need to sign up for Treehouse in order to download course files.
Sign upYou need to sign up for Treehouse in order to set up Workspace
Sign up