What is a CTE?
A CTE (Common Table Expression) is a named, temporary result set that you define within a SQL query using a WITH clause. Think of it as a virtual table that exists only for the duration of the query it's part of, it doesn't get stored in the database, but you can reference it just like a regular table within that query.
Example:
WITH cte_name AS (
SELECT column1, column2
FROM acb_table
WHERE condition
)
SELECT *
FROM cte_name
WHERE another_condition;
You can also define multiple CTEs in a single query, and have later CTEs reference earlier ones:
WITH sales_summary AS (
SELECT customer_id, SUM(amount) AS total_sales
FROM orders
GROUP BY customer_id
),
top_customers AS (
SELECT customer_id, total_sales
FROM sales_summary
WHERE total_sales > 10000
)
SELECT *
FROM top_customers
ORDER BY total_sales DESC;
How is a CTE Used?
CTEs are typically used to:
Break complex queries into readable steps: instead of one dense, nested query, you build up logic in clearly named stages.
Reuse a result set multiple times: within the same query, without repeating the same subquery logic over and over.
Simplify aggregations and joins: by pre-calculating a result set before joining or filtering on it.
Enable recursive queries: (e.g., traversing hierarchical data like org charts or category trees) using
WITH RECURSIVE.
Why is a CTE Better Than a Subquery?
Both CTEs and subqueries can often achieve the same result, but CTEs tend to be the better choice for several reasons:
Aspect | CTE | Subquery |
Readability | Named, step-by-step structure, easy to follow | Can become deeply nested and hard to read |
Reusability | Can be referenced multiple times in the same query | Must be repeated each time it's needed |
Maintainability | Easy to isolate, test, and modify a single step | Harder to isolate logic buried inside nested queries |
Recursion | Supports recursive logic ( | Not supported |
Debugging | You can run the CTE block on its own to check results | Harder to test in isolation without rewriting the query |
In short: subqueries work fine for simple, one-off filtering, but once a query involves multiple steps, repeated logic, or needs to stay readable for other people to maintain, a CTE is almost always the cleaner and more maintainable option.
Using CTEs in Bright Analytics
Bright Analytics allows you to use CTEs directly when building a subquery datasource. This is useful when you want to shape, filter, or pre-aggregate data before it becomes an available datasource for your reports and dashboards.
Steps
Select your datasource table. From the datasource list, choose the base table you want to build your query on.
Create a new subquery datasource. Use the "New Subquery Datasource" option to open the query editor.
Write your CTE query. In the query editor, you can write a full CTE (
WITH ... AS (...)) query, just as you would in standard SQL. This lets you structure your logic into clear, named steps before producing the final result set.Save the subquery datasource. Once your query runs successfully, save it.
As like other datasources, you can reference the fields when creating metric and dimensions.