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!

Example: Revenue per customer

Say you want to know how much revenue you get from each customer.

Due to the nature of the raw data, you’ll need to combine all three tables in the sample e-commerce dataset:

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.

A few things to note:

• You only need the WITH keyword once, at the beginning of the first CTE.
• If you use multiple CTEs, just separate them with commas after each closing parenthesis.

In the final SELECT statement:

The customer table is used as the primary (left) 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: