Common Table Expressions (CTEs) in Snowflake

Common Table Expression (CTE) in Snowflake

A Common Table Expression (CTE) in Snowflake is a named subquery used within the execution scope of the query in which it is specified. The CTEs are defined in a WITH clause which represents a temporary named result set.

For example, consider a scenario where you need to extract the employee details with the second highest salary.

The following query assigns a rank to employees based on their salaries using a Common Table Expression (CTE), and then the employee with the second highest salary is filtered out from the result set of the CTE.

WITH CTE AS(
   SELECT
      EMPLOYEE_ID,  NAME,  SALARY ,
      DENSE_RANK() OVER(ORDER BY SALARY DESC) AS SALARY_RANK
   FROM EMPLOYEES
)
SELECT
  EMPLOYEE_ID,   NAME,  SALARY
FROM CTE
WHERE SALARY_RANK =2
;

Common Table Expressions (CTEs) offer several advantages:

• CTEs enhance the readability of complex SQL queries by breaking them down into smaller understandable parts.
• CTEs enable you to define complex sub queries once and refer them multiple times within the main query.
• Overall CTEs contribute to more efficient and manageable SQL codebases.

In Snowflake, CTEs offer more functionality than traditional CTEs, allowing you to define expressions beyond simple SELECT statements.

1. Using CTEs for Defining Constants

CTEs can be used to define constants within a SQL query, making the code more readable and maintainable.

In the given example, ‘CTE1’ and ‘CTE2’ define the constants ‘MIN_KEY’ and ‘MAX_KEY’, which are then used to filter the ‘CUSTOMER’ table based on these values.

WITH CTE1 AS(
    SELECT 60001 AS MIN_KEY
)
,CTE2 AS(
    SELECT 60010 AS MAX_KEY
)
SELECT * FROM CUSTOMER
WHERE C_CUSTKEY BETWEEN (SELECT MIN_KEY FROM CTE1) AND (SELECT MAX_KEY FROM CTE2)
;

2. Using CTEs for Defining Variables

CTEs can also be defined directly as variables within a SQL query, making it much easier to define and maintain variables.

In the example provided, ‘MIN_KEY’ and ‘MAX_KEY’ are defined as CTEs with values 60001 and 60010, respectively. These variables are then used to filter the ‘CUSTOMER’ table, making the query more concise and easier to manage.

WITH MIN_KEY AS(
    60001
)
,MAX_KEY AS(
    60010
)
SELECT * FROM CUSTOMER
WHERE C_CUSTKEY BETWEEN MIN_KEY AND MAX_KEY
;

3. Using CTEs for Defining Multiple Variables at Once

Multiple Variables can be defined inside a single CTE, making it much more easier to organize and maintain variables inside your query.

In the example provided, a single CTE ‘VAR’ is used to define both ‘MIN_KEY’ and ‘MAX_KEY’ with values 60001 and 60010, respectively. These variables are then used in the main query to filter the ‘CUSTOMER’ table, making the query more organized and easier to manage.

WITH VAR AS(
  {
     'MIN_KEY': 60001,
     'MAX_KEY': 60010
  }
)
SELECT * FROM CUSTOMER
WHERE C_CUSTKEY BETWEEN VAR:MIN_KEY AND VAR:MAX_KEY
;

4. Using CTEs for Defining a List of Values as Variables

CTEs can be used to define a list of values as a variable within a SQL query.

In the example provided, the CTE ‘COUNTRY_LIST’ is used to define a list of countries, [‘INDIA’, ‘JAPAN’]. This list is then utilized in the main query to filter the ‘NATION’ table, checking if the country names in ‘N_NAME’ are contained within the ‘COUNTRY_LIST’, making the query more organized and easier to manage.

WITH COUNTRY_LIST AS(
    ['INDIA','JAPAN']
)
SELECT * FROM NATION
WHERE ARRAY_CONTAINS(N_NAME::VARIANT, COUNTRY_LIST)
;

5. Using CTEs for Defining Alias to Any Expression

CTEs can be utilized to define an alias for any expression within a SQL query, significantly enhancing readability, and reusability.

 In the example provided, the CTE ‘NET’ is used to define an alias for the expression ‘L_EXTENDEDPRICE – L_DISCOUNT – L_TAX’. This alias is then utilized in the main query to select the calculated net value from the ‘LINEITEM’ table, making the query more readable and easier to manage.

WITH NET AS(
    L_EXTENDEDPRICE-L_DISCOUNT-L_TAX
)
SELECT NET FROM LINEITEM;
;

Subscribe to our Newsletter !!

Related Articles: