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 — for example, getting aggregated sales metrics on a per order level.

The challenge is that your sales data lives in the order_line table, where each order has multiple rows. That makes it hard to calculate sales per order, because your data is structured per order line.

There are two ways to handle this:

Option 1: Use Multiple Models

One solution is to break your analysis into separate steps:

1. First, create a model that groups data by order_id, so you get one row per order.
2. Then, create a second model that performs calculations (like total revenue) based on that.

This method is great when the same logic needs to be reused in other queries — it keeps things modular and clean.

Option 2: Use a CTE (Common Table Expression)

If your analysis is specific to just one case, you can use a CTE to keep everything in a single query. A CTE is like creating a temporary table inside your query that you can reference later on.

So, use separate models when the logic will be reused, 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.