How to Write a Common Table Expression

Common Table Expressions (CTEs) are a powerful SQL feature that allow you to break complex queries into simpler, reusable parts. In BigQuery, CTEs improve readability and make your SQL easier to maintain.

What is a CTE?

A Common Table Expression is a temporary result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement. It’s defined using the WITH clause and lasts only for the duration of the query.

Basic Syntax

WITH cte_name AS (
    SELECT column1, column2
    FROM dataset.table
    WHERE conditions
)
SELECT *
FROM cte_name;

Example: Simplifying a Query

Without a CTE:

SELECT name, COUNT(*) AS order_count
FROM dataset.orders
WHERE status = 'completed'
GROUP BY name
HAVING order_count > 5;

With a CTE:

WITH completed_orders AS (
    SELECT name
    FROM dataset.orders
    WHERE status = 'completed'
)
SELECT name, COUNT(*) AS order_count
FROM completed_orders
GROUP BY name
HAVING order_count > 5;

Multiple CTEs

You can define multiple CTEs by separating them with commas:

WITH cte1 AS (...),
     cte2 AS (...)
SELECT *
FROM cte1
JOIN cte2 ON cte1.id = cte2.id;

Why Use CTEs?

• ✅ Improve query readability
• ✅ Avoid repeating subqueries
• ✅ Make complex queries easier to debug

Pro Tips

• Use meaningful names for your CTEs.
• Keep CTEs focused on a single purpose.
• Consider performance: sometimes materialized views or table exports are better for very large datasets.

Summary

CTEs in BigQuery are a fantastic tool to simplify your SQL queries. By using the WITH clause, you can break down complex logic into easy-to-understand parts, making your code cleaner and more maintainable.

Next time you write a BigQuery query, try using a CTE—you’ll thank yourself later!