Combining CTEs with JOIN

You’ve made it this far, and soon you’ll be able to start using your newly acquired SQL skills out in the real world. But before you go, here’s how to make the last two things you’ve learned (CTEs and left joins) even more useful — by combining them! 

Say you want to know how much revenue you get from each customer. Due to the nature of the raw data, this would require combining all three of the tables in the sample e-commerce data set. You can combine as many tables as you want with left joins. From the order table, you know which customer made which orders, and so you can aggregate revenue per order to a per customer level. But before that you need to aggregate the quantity times price for the order lines to a per order level. This can be done in a query like this:

1with
2    customers as (
3        select
4            id as customer_id
5          , first_name
6          , last_name
7        from
8            {{raw.e_commerce_sample.webshop_customer}}
9    )
10  , orders as (
11        select
12            id as order_id
13          , customer_id
14        from
15            {{raw.e_commerce_sample.webshop_order}}
16    )
17  , order_lines as (
18        select
19            order_id
20          , sum(price * quantity) as revenue_per_order
21        from
22            {{raw.e_commerce_sample.webshop_order_line}}
23        group by
24            order_id
25    )
26select
27    orders.customer_id
28  , first_name
29  , last_name
30  , sum(order_lines.revenue_per_order) as revenue_per_customer
31from
32    customers
33    left join orders on customers.customer_id = orders.customer_id
34    left join order_lines on orders.order_id = order_lines.order_id
35group by
36    orders.customer_id
37  , first_name
38  , last_name
39order by
40    revenue_per_customer desc

As you see, CTEs are used for the first step of the analysis, and to make sure there are common identifiers that can be used to join the tables. Note that the ‘with’ statement for the CTEs is only added for the first CTE, and the other CTEs are then separated by commas after the closing parenthesis. In the final ‘select’ statement, the customer table is used as the primary table, as this is the level you want the aggregations to be at. You can then join the customer table with the order table, with the customer id as the identifier. The order line and order tables are joined with the order id as the identifier. The resulting table looks like this:

‍