SQL Series-CTEs

Common Table Expressions(CTEs) in Advanced Analytics

After evolution of advanced analytics, use of advance SQL functions (CTE, Windows functions) are very wide. we are going to see CTEs in this post. Common Table Expressions(CTEs) are a SQL functionality that allows you to perform complex, multi-step transformations in a single easy-to-read query.

What are CTEs?

CTE is a temporary named result set that is not store/saved anywhere, exists in memory while querying.
How do CTEs work?

This CTE result set only available within execution scope of a specific statement, which means this result set are only available to the CRUD statement following the WITH clause in CTE query.
Why do we need CTEs?

1.      CTEs solve “logic on top of logic” problems. This happens when you have to perform data manipulation and then use the resulting dataset to perform further more manipulation.
2.      CTEs make your code Better readable, Reusable.
Where do we use CTEs?

1.      When you need to break up a long, complex query into chunks of logic that can be referenced in your final query. They are a great way to help you write more DRY code in your data analysis and dbt models (where you are used same data in multiple subqueries).
2.      CTEs can be used to create recursive queries, which is helpful as a normal SELECT statement cannot reference itself.
When do we use CTEs? (Use Cases):

CTEs are helpful in whenever subqueries are needed.
1.      They are used to create Cohort tables (appears in many technical tests).
2.      Determine Rank (lowest, second-lowest)
3.      Category grouping before further grouping