Using Multiple CTEs in a Query7:07 with Dave McFarland
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.
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
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 up