How to Write a Common Table Expression in BigQuery

Common table expressions (CTEs) help you break up complex queries in BigQuery. Here's a simple query to illustrate how to write a CTE:

with beta_users as (
  select *
  from users
  where beta is true

select events.*
from events
inner join beta_users on = events.user_id;

You can see a real example of using CTEs to generate a time series to avoid gaps in data.

database icon
Finally, a unified workspace for your SQL development
Get more done, together, with PopSQL and BigQuery