How to Write a Common Table Expression in Redshift

Common table expressions (CTEs) are a great way to break up complex queries. Using CTEs usually result in a more readable and maintainable query versus using subqueries. 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 (beta_users.id = events.user_id);

You can find more complex examples of using CTEs in How to Avoid Gaps in Series Data in Redshift and in Calculating Cumulative Sums in Redshift.

Cta

SQL editing that just rocks

PopSQL and Redshift, better together