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:
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:
