Introduction to sub-queries and CTE

There are some situations where you’ll need several different steps in analysis to get to the result you want, like getting aggregated sales metrics on a per order level. But since your sales numbers are in the order_line table with several rows per order, you’d only be able to aggregate per order line. So to be able to get the aggregations on an order level, you’d need to split the query up into several steps. One way to go about this is to first create a model in Weld that uses ‘group by order_id’ to get to a state where you have one row for each order, and then make a separate model to aggregate these orders.

But, there might be cases where you’d like to keep the steps in a single query. You can use Common Table Expressions (CTEs) to achieve this. CTEs create a temporary table which you can then treat in a separate step in your query. As a rule of thumb, it’s a good idea to split the queries into separate models and reference these to each other in the ‘from’ statement if the logic you apply in the different steps will have to be repeated for other queries later on. But if the logic you apply is only relevant in the specific case you’re working on, you can just as well put it in one query through CTEs to keep it all in one place.