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.