How do I use a CTE to make a multi-step SELECT easier to read?

Give the intermediate result a name, such as totals. The CTE calculates one row per customer; the next SELECT reads those rows and applies the threshold. Here is a complete SQLite example. The table and values are invented, and assume orders has numeric customer_id and amount columns.

WITH totals AS (
  SELECT customer_id, SUM(amount) AS total
  FROM orders
  GROUP BY customer_id
)
SELECT customer_id, total
FROM totals
WHERE total > 100;

An ordinary SQLite CTE is a temporary, view-like definition that lasts for one SQL statement. A WITH clause can define one or more CTEs and precedes the main SELECT. [S1]

Assume the invented orders rows are (customer_id, amount): (101, 40), (101, 75), (202, 60), (202, 30), (303, 120), (404, 50), and (404, 55).

The CTE named totals groups rows by customer and sums each group. Its intermediate result is:

customer_id | total
------------+------
101         | 115
202         | 90
303         | 120
404         | 105

The final SELECT reads from that intermediate result and retains totals greater than 100:

customer_id | total
------------+------
101         | 115
303         | 120
404         | 105

Customer 202 is absent because 90 does not pass the filter. These displayed results are worked out from the assumed sample rows; they are not a report from a real database.

The CTE gives the aggregation a meaningful name, so the outer query can focus on the next step: choosing which totals to show. That separation can make a multi-step query easier to read. [S1] It is an organization technique, not a guarantee that the database will run the query faster; the cited SQLite guidance does not establish that a CTE is necessarily materialized or provide a performance result. [S1]

When adapting the example, check that the table and column names match your schema, amount represents the values you intend to add, and the GROUP BY uses the right customer identifier. Also decide whether “over 100” means strictly greater than 100 (> 100) or includes 100 (>= 100). This example uses the strict version. If a customer has no matching order rows, that customer will not appear in this aggregation from orders; the example does not add customers with zero orders.

Replace the bracketed placeholders with names from your own schema, and replace the sample aggregate and filter with the calculation you need. This is a template, not a query to run unchanged:

WITH [step_name] AS (
  SELECT [group_column], [aggregate_expression] AS [result_name]
  FROM [table_name]
  GROUP BY [group_column]
)
SELECT [group_column], [result_name]
FROM [step_name]
WHERE [result_name] [comparison] [threshold];

For the order example, the choices are totals, customer_id, SUM(amount), total, orders, >, and 100. Keep the CTE definition and the SELECT that uses it in the same SQL statement. In SQLite, the CTE is a temporary, view-like definition for that statement. [S1]