How to Use CTEs in BigQuery

How do I write and use CTEs (WITH clauses) in BigQuery?

A Common Table Expression (CTE) lets you create a temporary, named result set with WITH, making BigQuery queries easier to read, reuse, and maintain.

Welcome to the Galaxy, Guardian!
You'll be receiving a confirmation email

Follow us on twitter :)

Oops! Something went wrong while submitting the form.

Description

Example H2

Example H3

What is a CTE in BigQuery?

A CTE is a named, temporary sub-query introduced with the WITH keyword. It exists only for the duration of the main query and can be referenced like a table.

Why choose a CTE over nested sub-queries?

CTEs improve readability, allow modular query building, and let you reference the same intermediate result multiple times without repeating code.

How do I write a basic CTE?

Start with WITH cte_name AS (SELECT …) SELECT … FROM cte_name;. Place the main SELECT after all CTE definitions.

Can I chain multiple CTEs?

Yes. Separate each CTE with a comma. Later CTEs can reference earlier ones, enabling step-by-step transformations.

Do CTEs hurt performance?

BigQuery inlines non-recursive CTEs during optimization, so performance usually matches equivalent sub-queries. Reusing a CTE may even lower cost by preventing redundant scans.

How do I reference ecommerce tables?

Use fully qualified names like project.dataset.Customers. Aliasing inside a CTE keeps column names clear.

Best practices for production CTEs

Use descriptive CTE names, keep each CTE focused, filter early to reduce scanned bytes, and comment complex logic.

What are common errors with BigQuery CTEs?

Placing a semicolon after a CTE, reusing a name twice, or selecting from a later CTE cause syntax errors. Order matters.

How do I debug a CTE?

Run only the CTE body as a standalone query, or add LIMIT clauses to inspect intermediate results.

Recap

CTEs make BigQuery SQL cleaner and more maintainable. Mastering them speeds up query writing and collaboration.

Why How to Use CTEs in BigQuery is important

How to Use CTEs in BigQuery Example Usage


-- Identify products that sold out in the last week
WITH
  weekly_sales AS (
    SELECT oi.product_id, SUM(oi.quantity) AS qty_sold
    FROM   `shop.OrderItems` AS oi
    JOIN   `shop.Orders`     AS o
    ON     oi.order_id = o.id
    WHERE  o.order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 7 DAY)
    GROUP  BY oi.product_id
  ),
  updated_stock AS (
    SELECT p.id, p.name, p.stock - ws.qty_sold AS remaining_stock
    FROM   `shop.Products` AS p
    LEFT  JOIN weekly_sales AS ws
    ON     p.id = ws.product_id
  )
SELECT *
FROM   updated_stock
WHERE  remaining_stock <= 0;

How to Use CTEs in BigQuery Syntax


WITH
  recent_orders AS (
    SELECT id, customer_id, order_date, total_amount
    FROM   `shop.Orders`
    WHERE  order_date >= DATE_SUB(CURRENT_DATE(), INTERVAL 30 DAY)
  ),
  top_customers AS (
    SELECT customer_id, SUM(total_amount) AS spend_30d
    FROM   recent_orders
    GROUP  BY customer_id
    HAVING spend_30d > 500
  )
SELECT c.id, c.name, c.email, t.spend_30d
FROM   `shop.Customers` AS c
JOIN   top_customers AS t
ON     c.id = t.customer_id
ORDER  BY t.spend_30d DESC;

Common Mistakes

Using a semicolon after the CTE block: BigQuery treats the semicolon as the end of the statement, so the main SELECT never runs. Remove the semicolon until after the final query.
Re-using the same CTE name twice in one query: BigQuery throws a duplicate name error. Give each CTE a unique, descriptive identifier.